Chapter 10:PL/SQL Cursors

What are Cursors?

A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it.

This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

There are two types of cursors in PL/SQL:

Implicit cursors

These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed. 

Implicit Cursors: Application

When you execute DML statements like DELETE, INSERT, UPDATE and SELECT statements, implicit statements are created to process these statements.

Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN. 

For example, When you execute INSERT, UPDATE, or DELETE statements the cursor attributes tell us whether any rows are affected and how many have been affected. 
When a SELECT… INTO statement is executed in a PL/SQL Block, implicit cursor

attributes can be used to find out whether any row has been returned by the SELECT statement. PL/SQL returns an error when no data is selected.

The status of the cursor for each of these attributes are defined in the below table. 

AttributesReturn ValueExample
%FOUNDThe return value is TRUE, if the DML statements like INSERT, DELETE and UPDATE affect at least one row and if SELECT ….INTO statement return at least one row.The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE do not affect row and if SELECT….INTO statement do not return a row.SQL%FOUND
%NOTFOUNDThe return value is FALSE, if DML statements like INSERT, DELETE and UPDATE at least one row and if SELECT ….INTO statement return at least one row.The return value is TRUE, if a DML statement like INSERT, DELETE and UPDATE do not affect even one row and if SELECT ….INTO statement does not return a row.SQL%NOTFOUND
%ROWCOUNTReturn the number of rows affected by the DML operations INSERT, DELETE, UPDATE, SELECTSQL%ROWCOUNT
Implicit Cursor Example

Following one emp_information table:

1Sushil KumarWeb Developer5000
2Manish BhaleraoProgram Developer8000
3Seema RautProgram Developer4000
4Kunal SharmaWeb Developer6000

Now above employee information table update the employee name ‘Saulin’ department ‘Program Developer’ update to ‘Web Developer’.


SQL>edit implicit_cursorBEGIN
    UPDATE emp_information SET emp_dept='Web Developer'
        WHERE emp_name='Seema Raut';

        dbms_output.put_line('Updated - If Found');
    END IF;

        dbms_output.put_line('NOT Updated - If NOT Found');
    END IF; 

        dbms_output.put_line(SQL%ROWCOUNT||' Rows Updated');
        dbms_output.put_line('NO Rows Updated Found');


Updated - If Found
1 Rows Updated

PL/SQL procedure successfully operation.

Explicit cursors

They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.

Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.

The syntax for creating an explicit cursor is −

CURSOR cursor_name IS select_statement; 

Working with an explicit cursor includes the following steps −

  • Declaring the cursor for initializing the memory
  • Opening the cursor for allocating the memory
  • Fetching the cursor for retrieving the data
  • Closing the cursor to release the allocated memory

Declaring the Cursor

Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example −

CURSOR cursor_name [ parameter ] RETURN return_type;

CURSOR cursor_name [ parameter ] [ RETURN return_type ]

Opening the Cursor

Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open the above defined cursor as follows −

OPEN cursor_name [( cursor_parameter )];

Fetching the Cursor

Fetching the cursor involves accessing one row at a time. For example, we will fetch rows from the above-opened cursor as follows −

FETCH cursor_name INTO variable;

Closing the Cursor

Closing the cursor means releasing the allocated memory. For example, we will close the above-opened cursor as follows −

CLOSE cursor_name [( cursor_parameter )];
SQL>edit explicit_cursorDECLARE
  cursor c is select * from emp_information
  where emp_name='bhavana';
  tmp emp_information%rowtype;BEGIN 
  OPEN c;
  Loop exit when c%NOTFOUND;
    FETCH c into tmp;
    update emp_information set tmp.emp_dept='Web Developer'
    where tmp.emp_name='Seema Raut';
  dbms_output.put_line(SQL%ROWCOUNT||' Rows Updated');ELSE
  dbms_output.put_line('NO Rows Updated Found');END IF;CLOSE c;


1 Rows Updated

PL/SQL procedure successfully completed.