Oracle PL/SQL Cursor: Implicit, Explicit, Cursor FOR Loop [Example]

Details

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.

In this tutorial you will learn-

The cursor is of two types.

Implicit Cursor

Whenever any DML operations occur in the database, an implicit cursor is created that holds the rows affected, in that particular operation. These cursors cannot be named and, hence they cannot be controlled or referred from another place of the code. We can refer only to the most recent cursor through the cursor attributes.

Explicit Cursor

Programmers are allowed to create named context area to execute their DML operations to get more control over it. The explicit cursor should be defined in the declaration section of the PL/SQL block, and it is created for the 'SELECT' statement that needs to be used in the code.

Below are steps that involved in working with explicit cursors.

Syntax: 

DECLARE
CURSOR <cursor_name> IS <SELECT statement^>
<cursor_variable declaration>
BEGIN
OPEN <cursor_name>;
FETCH <cursor_name> INTO <cursor_variable>;
.
.
CLOSE <cursor_name>;
END;

Cursor Attributes

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.

Cursor Attribute Description
%FOUND It returns the Boolean result 'TRUE' if the most recent fetch operation fetched a record successfully, else it will return FALSE.
%NOTFOUND This works oppositely to %FOUND it will return 'TRUE' if the most recent fetch operation could not able to fetch any record.
%ISOPEN It returns Boolean result 'TRUE' if the given cursor is already opened, else it returns 'FALSE'
%ROWCOUNT It returns the numerical value. It gives the actual count of records that got affected by the DML activity.

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.

SQL in PL/SQL 

DECLARE
CURSOR gtupapers_det IS SELECT emp_name FROM emp;
lv_emp_name emp.emp_name%type;

BEGIN
OPEN gtupapers_det;

LOOP
FETCH gtupapers_det INTO lv_emp_name;
IF gtupapers_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‘||gtupapers_det%R0WCOUNT);
CLOSE gtupapers_det;
END:
/

Output 

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY 
Total rows fetched is 3

Code Explanation:

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;

Example 1: In this example, we will project all the employee name from emp table using a cursor-FOR loop. 

DECLARE
CURSOR gtupapers_det IS SELECT emp_name FROM emp; 
BEGIN
FOR lv_emp_name IN gtupapers_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:

Note: In Cursor-FOR loop, cursor attributes cannot be used since opening, fetching and closing of the cursor is done implicitly by FOR loop.

 

YOU MIGHT LIKE: