Using Explicit Cursors  «Prev 

Explicit Cursor Guidelines in PL/SQL

Declaring and Defining Explicit Cursors

You can either declare an explicit cursor first and then define it later in the same block, subprogram, or package, or declare and define it at the same time. An explicit cursor declaration, which only declares a cursor, has this syntax: 
CURSOR cursor_name [ parameter_list ] RETURN return_type;

An explicit cursor definition has this syntax: 
CURSOR cursor_name [ parameter_list ] [ RETURN return_type ]
IS select_statement;
If you declared the cursor earlier, then the explicit cursor definition defines it; otherwise, it both declares and defines it.
Example 6-4 declares and defines three explicit cursors
Example 6-4: Explicit Cursor Declaration and Definition 
DECLARE
CURSOR c1 RETURN departments%ROWTYPE; -- Declare c1
CURSOR c2 IS -- Declare and define c2
SELECT employee_id, job_id, salary FROM employees
WHERE salary > 2000;
CURSOR c1 RETURN departments%ROWTYPE IS -- Define c1,
SELECT * FROM departments -- repeating return type
WHERE department_id = 110;
CURSOR c3 RETURN locations%ROWTYPE; -- Declare c3
CURSOR c3 IS -- Define c3,
SELECT * FROM locations -- omitting return type
WHERE country_id = 'JP';
BEGIN
NULL;
END;
/

An explicit cursor is a SELECT statement that is defined within the declaration section of the PL/SQL code.
1)
  1. An explicit cursor is a SELECT statement that is defined within the declaration section of the PL/SQL code.
  2. You cannot define an explicit cursor for UPDATE, INSERT, and DELETE statements.
  3. When defining explicit cursors, you have complete control over how to open, when to fetch, how many records to fetch, and when to close a cursor.

2) You can get more information about the cursor through the following attributes.
2)
  1. You can get more information about the cursor through the following attributes.
  2. cursor_name%FOUND - validates whether any data can be fetched from
  3. cursor_name%NOTFOUND - informs that no more data can be fetched for a cursor
  4. cursor_name%ROWCOUNT - returns the number of records fetched from the cursor thus far.
  5. cursor_name%ISOPEN - validates whether a cursor is opened.

3) Example of an explicit cursor
3) 
DECLARE
  pet_count NUMBER;
  CURSOR number_of_pets is
    SELECT CURRENT_INVENTORY_COUNT
    FROM PRODUCT
    WHERE PRODUCT_NAME = 'Canary';
Example of an explicit cursor