In this blog we will explore the feature available in PL/SQL to make data available within PL/SQL programs from database tables for data manipulation.
When we fire any SQL statement from a PL/SQL program, at back end Oracle Database assign a private work area for that SQL statement which holds the information for the statement like no of rows returned or affected.
There is also a way to assign a name to that private work area & manipulate the result or information of SQL statement i.e. declaring a cursor & using it in PL/SQL block.
Cursor is nothing but a pointer to result of a SQL query.
Life Cycle of Cursor:
- Declare (Initializing the cursor in memory)
- Open (Opening the cursor i.e. allocation memory or pointer to SQL Query )
- Fetch (Retrieving data from the cursor)
- Close (Closing Cursor i.e. releasing the allocated memory)
Types of Cursors
Implicit Cursors: Whenever you execute a SQL Statement (Select Into, Insert, Update, Delete or Merge) in PL/SQL program, automatically Oracle manage the cursor for that SQL statement. Oracle database handles many of cursor related operations example declaring, opening, fetching records and close operation.
Explicit Cursors: Select statement declared by user. Cursor related operations need to perform by user only explicitly.
If you see below simple example, I have declare a cursor CUST, opened the cursor, fetched the data in variable & finally closed the cursor.
Refer this blog for understanding the cursor based records: Records in PL/SQL
DECLARE CURSOR CUST IS SELECT * FROM CUSTOMERS; CUST_rec CUST%rowtype; BEGIN OPEN CUST; FETCH CUST INTO CUST_rec; dbms_output.put_line( CUST_rec.FULL_NAME || ', ' ||CUST_rec.EMAIL_ADDRESS); CLOSE CUST; END; /
Cursor Attributes
Cursor attributes generally used to get the state of the cursor in PL/SQL program. For example if you want to check if cursor is opened or not. Or how many records got affected from the latest DML operation.
To reference a cursor attribute, append cursor_name or variable with % & attribute name. ex. cursor_name%FOUND
For Implicit Cursors: Cursor name always as SQL. example. SQL%FOUND
- %FOUND – Return true or false based on most recent fetch. True if rows return in recent fetch. False if no row returned.
- %NOTFOUND – Opposite of %FOUND. True if no row returned. False if ow returned.
- %ROWCOUNT – Return no of rows fetched or affected in case of DML.
- %ISOPEN – Return true if cursor is open, otherwise false.
%ISOPEN – Always return false in case of implicit cursors. As implicit cursors operations handles by the Oracle automatically.
Example of Implicit Cursors:
BEGIN UPDATE CUSTOMERS SET FULL_NAME = 'Gregory Sanchez' WHERE CUSTOMER_ID=10; IF (sql%found) THEN dbms_output.put_line( 'Record updated'); dbms_output.put_line( 'No Record affected: ' ||sql%rowcount ); COMMIT; END IF; IF (sql%isopen) THEN dbms_output.put_line( 'Implicit cursor is open.'); else dbms_output.put_line( 'Implicit cursor is closed.'); END IF; END; /

Example of Explicit Cursor:
DECLARE CURSOR CUST IS select * from CUSTOMERS where CUSTOMER_ID<=10 order by 1 desc; CUST_rec CUST%rowtype; begin open CUST; LOOP fetch CUST into CUST_rec; dbms_output.put_line( CUST_rec.FULL_NAME || ', ' ||CUST_rec.EMAIL_ADDRESS); EXIT WHEN CUST%NOTFOUND; END LOOP; close CUST; end; /
Another example of Explicit Cursor:
DECLARE CURSOR CUST IS SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID<=10; CUST_rec CUST%rowtype; BEGIN OPEN CUST; FETCH CUST INTO CUST_rec; WHILE CUST%FOUND LOOP dbms_output.put_line( 'Customer Id: '|| CUST_rec.CUSTOMER_ID || ' ' || CUST_rec.FULL_NAME || ', ' ||CUST_rec.EMAIL_ADDRESS); FETCH CUST INTO CUST_rec; END LOOP; CLOSE CUST; END; /

Example of Parameterized Cursor:
DECLARE CURSOR CUST (INPUT_CUST_ID IN NUMBER) IS SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID=INPUT_CUST_ID; CUST_rec CUST%rowtype; BEGIN OPEN CUST(11); FETCH CUST INTO CUST_rec; dbms_output.put_line( 'Customer Id: '|| CUST_rec.CUSTOMER_ID || ' ' || CUST_rec.FULL_NAME || ', ' ||CUST_rec.EMAIL_ADDRESS); CLOSE CUST; END; /

You can have default values for the parameters in Parameterized Cursor . See below structure.
CURSOR cursor_name ( parameter_name1 datatype := default_value, parameter_name2 datatype := default_value, parameter_name3 datatype := default_value, ... ) IS SQL_Query;
Cursor For Loop
Here you don’t have to worry about cursor operations like open, fetch, close. In Cursor For Loop Oracle automatically take care of all these operation. Refer below sample example:
DECLARE CURSOR CUST IS SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID<=10; BEGIN for i in CUST LOOP if mod(i.CUSTOMER_ID,2)=0 then dbms_output.put_line( 'Customer Id: '|| i.CUSTOMER_ID || ' ' || i.FULL_NAME || ', ' ||i.EMAIL_ADDRESS); end if; END LOOP; END; /

Next: PL/SQL Cursor Variable – Ref Cursors (Strong & Weak Type)
Oracle Schema used in blog: Customer-Orders Sample Schema | Oracle 12c
Thanks!
Happy Learning! Your feedback would be appreciated!
Follow @shobhitsinghIN
One thought on “Cursors in PL/SQL”