Oracle PL/SQL Cursor: Implicit,
Explicit, Cursor FOR Loop
What
is CURSOR in PL/SQL?
A Cursor is a pointer to this
context area. Oracle creates context area for processing an SQL statement which
contains all information about the statement.
PL/SQL allows the programmer to
control the context area through the cursor. A cursor holds the rows returned
by the SQL statement. The set of rows the cursor holds is referred as active
set. These cursors can also be named so that they can be referred from another
place of the code.
What
is CURSOR in PL/SQL?
A Cursor is a pointer to this
context area. Oracle creates context area for processing an SQL statement which
contains all information about the statement.
PL/SQL allows the programmer to
control the context area through the cursor. A cursor holds the rows returned
by the SQL statement. The set of rows the cursor holds is referred as active
set. These cursors can also be named so that they can be referred from another
place of the code.
- Declaring the cursor
Declaring the cursor simply means to create one named context
area for the ‘SELECT’ statement that is defined in the declaration part. The
name of this context area is same as the cursor name.
- Opening Cursor
Opening the cursor will instruct the PL/SQL to allocate the
memory for this cursor. It will make the cursor ready to fetch the records.
- Fetching Data from the Cursor
In
this process, the ‘SELECT’ statement is executed and the rows fetched is stored
in the allocated memory. These are now called as active sets. Fetching data
from the cursor is a record-level activity that means we can access the data in
a record-by-record way.
Each fetch statement will fetch one active set and holds the
information of that particular record. This statement is same as ‘SELECT’
statement that fetches the record and assigns to the variable in the ‘INTO’
clause, but it will not throw any exceptions.
- Closing the Cursor
Once all the record is fetched now, we need to close the cursor
so that the memory allocated to this context area will be released.
- In
the above syntax, the declaration part contains the declaration of the
cursor and the cursor variable in which the fetched data will be assigned. - The cursor is created for the
‘SELECT’ statement that is given in the cursor declaration. - In execution part, the declared
cursor is opened, fetched and closed.
Cursor Attributes
|
Cursor Attribute |
Description |
|
%FOUND |
It returns the Boolean result ‘TRUE’ if the most recent fetch |
|
%NOTFOUND |
This works oppositely to %FOUND it will return ‘TRUE’ if the |
|
%ISOPEN |
It returns Boolean result ‘TRUE’ if the given cursor is |
|
%ROWCOUNT |
It returns the numerical value. It gives the actual count of |
Both Implicit cursor and the
explicit cursor has certain attributes that can be accessed. These attributes
give more information about the cursor operations. Below are the different
cursor attributes and their usage.
Example
1: In this example, we are going to
see how to declare, open, fetch and close the explicit cursor.
We will project all the
employee’s name from emp table using a cursor. We will also use cursor
attribute to set the loop to fetch all the record from the cursor.
DECLARE
CURSOR xhire_det IS SELECT emp_name FROM emp;
lv_emp_name emp.emp_name%type;
BEGIN
OPEN xhire_det;
LOOP
FETCH xhire_det INTO lv_emp_name;
IF xhire_det%NOTFOUND
THEN
EXIT;
END IF;
Dbms_output.put_line(‘Employee
Fetched:‘||lv_emp_name);
END LOOP;
Dbms_output.put_line(‘Total rows fetched
is‘||xhire_det%R0WCOUNT);
CLOSE xhire_det;
END;
/
Output
Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY
Total rows fetched is 3
Code
Explanation:
- Code line 2: Declaring
the cursor xhire_det for statement ‘SELECT emp_name FROM emp’. - Code line 3: Declaring
variable lv_emp_name.
- Code line 5: Opening the
cursor xhire_det. - Code line 6: Setting the
Basic loop statement to fetch all the records in the ’emp’ table. - Code line 7: Fetches the xhire_det
data and assign the value to lv_emp_name. - Code line 9: Using the
cursor attribute ‘%NOTFOUND’ to find whether all the record in the cursor
is fetched. If fetched then it will return ‘TRUE’ and control will exit
from the loop, else the control will keep on fetching the data from the
cursor and print the data. - Code line 11: EXIT
condition for the loop statement. - Code line 12: Print
the fetched employee name. - Code line 14: Using
the cursor attribute ‘%ROWCOUNT’ to find the total number of records that
got affected/fetched in the cursor. - Code line 15: After exiting
from the loop the cursor is closed and the memory allocated is set free.
FOR Loop Cursor statement
“FOR LOOP” statement can be used
for working with cursors. We can give the cursor name instead of range limit in
the FOR loop statement so that the loop will work from the first record of the
cursor to the last record of the cursor. The cursor variable, opening of
cursor, fetching and closing of the cursor will be done implicitly by the FOR
loop.
Syntax:
DECLARE
CURSOR <cursor_name> IS <SELECT statement>;
BEGIN
FOR
I IN <cursor_name>
LOOP
.
.
END
LOOP;
END;
- In
the above syntax, the declaration part contains the declaration of the
cursor. - The cursor is created for the
‘SELECT’ statement that is given in the cursor declaration. - In execution part, the declared
cursor is setup in the FOR loop and the loop variable ‘I’ will behave as
cursor variable in this case.
Example
1: In this example, we will project
all the employee name from emp table using a cursor-FOR loop.
DECLARE
CURSOR xhire_det IS SELECT emp_name FROM emp;
BEGIN
FOR lv_emp_name IN xhire_det
LOOP
Dbms_output.put_line(‘Employee
Fetched:‘||lv_emp_name.emp_name);
END LOOP;
END;
Output
Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY
Code
Explanation:
- Code line 2: Declaring
the cursor xhire_det for statement ‘SELECT emp_name FROM emp’. - Code line 4:
Constructing the ‘FOR’ loop for the cursor with the loop variable lv_emp_name. - Code line 5: Printing the
employee name in each iteration of the loop. - Code line 8: Exit
the loop
Note: In Cursor-FOR loop, cursor attributes cannot be
used since opening, fetching and closing of the cursor is done implicitly by
FOR loop.