What is Cursor ?
Cursor is a pointer in memory area called context area.
What is context area ?
Context area is a memory area inside the Process Global Area (PGA) which helps Oracle server in processing an SQL statement by holding the important information about the statement.
This information include:
- Rows returned by a query.
- Number of rows processes by a query.
- A pointer to the parsed query in the shared pool.
Using cursor we can control the context area as it is a pointer to the same.
Types of cursors:
- Implicit cursors
- Explicit cursors
1. Implicit cursors:
- Automatically gets created by Oracle server when DML SQL statement gets executed.
- Users can not control the behavior of these cursors.
- Created in background for any PL/SQL block which executes an SQL statement.
2. Explicit cursors:
- User defined cursors which means user has to create this cursor for any statement.
- Unlike implicit cursor user has full control over explicit cursor.
Create an explicit cursor:
This can be done in 4 steps-
In case of implicit cursor Oracle performs all these steps automatically for us.
1. Declare cursor:
Declaring a cursor means initializing a cursor into memory.
CURSOR cursor_name IS select_statement;
2. Open cursor:
- In order to put the cursor at work we have to open it first.
- When we open the cursor the memory will be allotted to it., and it set put to the next step which is fetching the data from it.
3. Fetch cursor:
The process of retrieving the data from the cursor is known as fetching the cursor.
FETCH cursor_name INTO PL/SQL variable;OrFETCH cursor_name INTO PL/SQL record;
4. Close cursor:
Closing statement of a cursor will releases all the resources associated with it.
Example of a Cursor:
DECLARE CURSOR cursor_name IS select_statement; BEGIN OPEN cursor_name; FETCH cursor_name INTO PL/SQL variable; CLOSE cursor_name; END;