Cursors in PL/SQL

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