PL/SQL: Cursors Cheat Sheet

Implicit Cursors

Below is table provides the description of the most used attributes

%FOUNDReturns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
%NOTFOUNDThe logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
%ISOPENAlways returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
%ROWCOUNTReturns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

Any SQL cursor attribute will be accessed asĀ sql%attribute_name

Explicit Cursors
CURSOR cursor_name IS select_statement; 
Declaring the Cursor
CURSOR c_customers IS 
   SELECT id, name, address FROM customers; 
Opening the Cursor
OPEN c_customers; 
Fetching the Cursor
FETCH c_customers INTO c_id, c_name, c_addr; 
Closing the Cursor
CLOSE c_customers;

Leave a Reply

Your email address will not be published. Required fields are marked *