Explicit Cursors in PL/SQL

When you need to process multiple records one at a time through a SELECT statement, you can use an explicit cursor. A set of records returned by a query associated with an explicit cursor is called an active or result set. By default, the first record within the result set is the current record of the cursor. When you execute the FETCH statement, data from the current record is copied into variables, and the next record becomes the current record. You will learn more about the basic structure of a cursor block, including the FETCH statement, in the next lesson. The following series of images further explains explicit cursors.
Explicit Cursor Guidelines

• An explicit cursor is a SELECT statement that is defined within the declaration section of the PL/SQL code
• You cannot define an explicit cursor for UPDATE, INSERT, and DELETE statements.
• 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.

Explicit Cursor Attributes 2) You can get more information about the cursor through the following attributes.

• cursor_name%FOUND - validates whether any data can be fetched from
• cursor_name%NOTFOUND - informs that no more data can be fetched for a cursor
• cursor_name%ROWCOUNT - returns the number of records fetched from the cursor thus far.
• cursor_name%ISOPEN - validates whether a cursor is opened.

Example of PL/SQL code using an explicit cursor.

DECLARE
    pet_count NUMBER;
    CURSOR number_of_pets IS
        SELECT CURRENT_INVENTORY_COUNT
        FROM PRODUCT
        WHERE PRODUCT_NAME = 'Canary';
BEGIN
    OPEN number_of_pets;
    FETCH number_of_pets INTO pet_count;
    DBMS_OUTPUT.PUT_LINE('Number of Canary''s = ' || pet_count);
    CLOSE number_of_pets;
END;

This code snippet declares a cursor named `number_of_pets` to select the current inventory count of products named 'Canary' from the PRODUCT table. The cursor is then opened, and the inventory count is fetched into the `pet_count` variable. The count is outputted using `DBMS_OUTPUT.PUT_LINE`, and the cursor is closed.

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;
/

In this example, the cursor is a virtual table^[1], because the data selected has the same structure of a table, that is, records and columns, but it exists for the duration of execution of the SQL statement.

An explicit cursor can be used when:

1. Multiple records must be processed within a SELECT statement
2. You wish to have better control in cursor execution
3. You wish to handle exceptions within a PL/SQL block

An explicit cursor is a session cursor that you construct and manage. You must declare and define an explicit cursor, giving it a name and associating it with a query (typically, the query returns multiple rows). Then you can process the query result set in either of these ways:

1. Open the explicit cursor (with the OPEN statement), fetch rows from the result set (with the FETCH statement), and close the explicit cursor (with the CLOSE statement).
2. Use the explicit cursor in a cursor FOR LOOP statement

You cannot

1. assign a value to an explicit cursor,
2. use it in an expression, or
3. use it as a formal subprogram parameter or host variable.

You can do those things with a cursor variable.
Unlike an implicit cursor, you can reference an explicit cursor or cursor variable by its name. Therefore, an explicit cursor or cursor variable is called a named cursor. In the next lesson, we will define the basic structure of a cursor block.

[1]virtual table: A table stored in the computer’s memory. Virtual tables themselves are not stored in the database; rather, the definition of the view is stored and given a name. Users call up that name, and the view is created (from base tables) on the fly. When a user closes the view, it "disappears" from memory, only to be recreated the next time its name is invoked.