Using Explicit Cursors  «Prev  Next»

Lesson 4Explicit Cursors
Objective Describe Explicit Cursors

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.

An explicit cursor is a SELECT statement that is defined within the declaration section of the PL/SQL code.
1) An explicit cursor is a SELECT statement that is defined within the declaration section of the PL/SQL code
  1. You cannot define an explicit cursor for UPDATE, INSERT, and DELETE statements.
  2. 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) You can get more information about the cursor through the following attributes.
  1. cursor_name%FOUND - validates whether any data can be fetched from
  2. cursor_name%NOTFOUND - informs that no more data can be fetched for a cursor
  3. cursor_name%ROWCOUNT - returns the number of records fetched from the cursor thus far.
  4. cursor_name%ISOPEN - validates whether a cursor is opened.

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

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

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.

When to use Explicit Cursors

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.

SEMrush Software