Oracle PL/SQL Cursor: Implicit, Explicit, For Loop with Example

What is CURSOR in PL/SQL?

A cursor is a pointer to the context area. Oracle creates a context area for processing an SQL statement, and this area contains all the 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, and the set of rows the cursor holds is referred to as the active set. These cursors can also be named so that they can be referred to from another place in the code.

The cursor is of two types:

• Implicit Cursor
• Explicit Cursor

Implicit Cursor

Whenever any DML operation occurs 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 to from another place in the code. We can refer only to the most recent cursor through the cursor attributes.

Explicit Cursor

Programmers are allowed to create a named context area to execute their DML operations and 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 the steps involved in working with explicit cursors:

• Declaring the cursor: Declaring the cursor simply means creating one named context area for the SELECT statement that is defined in the declaration part. The name of this context area is the same as the cursor name.
• Opening the cursor: Opening the cursor instructs PL/SQL to allocate the memory for this cursor. It makes the cursor ready to fetch the records.
• Fetching data from the cursor: In this process, the SELECT statement is executed and the fetched rows are stored in the allocated memory. These are now called active sets. Fetching data from the cursor is a record-level activity, which means we can access the data in a record-by-record way. Each fetch statement fetches one active set and holds the information of that particular record. This statement is the same as a SELECT statement that fetches the record and assigns it to the variable in the INTO clause, but it will not throw any exceptions.
• Closing the cursor: Once all the records are fetched, we need to close the cursor so that the memory allocated to this context area is released.

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;

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 the execution part, the declared cursor is opened, fetched, and closed.

Cursor Attributes

Both the implicit cursor and the explicit cursor have certain attributes that can be accessed. These attributes give more information about the cursor operations. Below are the different cursor attributes and their usage.

Explicit Cursor Example: In this example, we will see how to declare, open, fetch, and close an explicit cursor. We will project all the employee names from the emp table using a cursor. We will also use a cursor attribute to set the loop to fetch all the records from the cursor.

The screenshot below shows this explicit cursor example and its output in Oracle.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
lv_emp_name emp.emp_name%type;
BEGIN
OPEN guru99_det;
LOOP
FETCH guru99_det INTO lv_emp_name;
IF guru99_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'||guru99_det%ROWCOUNT);
CLOSE guru99_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 guru99_det for the statement ‘SELECT emp_name FROM emp’.
• Code line 3: Declaring the variable lv_emp_name with the %type anchored to emp.emp_name.
• Code line 5: Opening the cursor guru99_det.
• Code line 6: Setting the basic loop statement to fetch all the records in the emp table.
• Code line 7: Fetches the guru99_det data and assigns the value to lv_emp_name.
• Code line 8: Using the cursor attribute %NOTFOUND to check whether all records in the cursor are fetched. If fetched, it returns TRUE and control exits the loop; otherwise control keeps fetching the data from the cursor and prints it.
• Code line 10: 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 fetched by the cursor.
• Code line 15: After exiting the loop, the cursor is closed and the allocated memory is freed.

FOR Loop Cursor statement

A cursor FOR loop can be used for working with cursors. We can give the cursor name instead of a range limit in the FOR loop statement, so that the loop works from the first record of the cursor to the last record of the cursor. The cursor variable, opening of the cursor, fetching, and closing of the cursor are all 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 the execution part, the declared cursor is set up in the FOR loop, and the loop variable ‘I’ behaves as the cursor variable in this case.

Oracle Cursor for Loop Example: In this example, we will project all the employee names from the emp table using a cursor-FOR loop.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
BEGIN
FOR lv_emp_name IN guru99_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 guru99_det for the 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 6: Printing the employee name in each iteration of the loop.
• Code line 7: Exit the loop (END LOOP).

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

FAQs