Oracle PL/SQL Tutorial - How To Use Explicit Cursors

This Oracle PL/SQL tutorial continues on from our tutorial on implicit cursors. We touched briefly on explicit cursors in that tutorial but this PLSQL tutorial will explore them in more detail. 

Definition of a cursor

Let's just recap quickly on the definitions we gave in the tutorial on implicit cursors. A cursor can be regarded as a pointer (or handle) to a memory area that stores information about a SQL statement being used in PL/SQL.

Both explicit cursors and implicit cursors need to have information stored about them so that Oracle can control the interaction of the SQL statement in the PL/SQL code with the database.

Explicit cursors are defined explicitly (using the keyword CURSOR) and used only for SQL SELECT statements. Implicit cursors are used for both DML statements (UPDATE, INSERT, DELETE) and SELECT statements. 

Explicit PL/SQL Cursors 

An explicit PL/SQL cursor provides you with much greater control over the operations (opening, closing and fetching) of the cursor for your SELECT statements (you have no control over implicit cursors). There is a downside however - you have to write the code to open, fetch from, and close the cursor, but as each operation is just one keyword followed by the cursor name, it's not a great hardship.

As with implicit cursors you can also access the cursor attributes to determine such things as how many rows were fetched and whether or not the cursor is open or closed, as we'll see shortly.

Let's look at an example of using explicit cursors.

Explicit Cursor For Select Statement

As we said earlier in this PL/SQL tutorial, explicit cursors can only be defined for SELECT statements. Here's a simple piece of Oracle PL/SQL code that uses an explicit cursor for a SELECT statement.

DECLARE
     CURSOR emp_details IS
       SELECT first_name
           ,last_name
           ,email
           ,salary
     FROM employees
     WHERE employee_id=100; 

   emp emp_details%ROWTYPE; -- holds data from database

BEGIN
   /* open,fetch,close cursor */
    OPEN emp_details;
    FETCH emp_details INTO emp;
    CLOSE emp_details;
    DBMS_OUTPUT.PUT_LINE(
        'Name: '||emp.first_name||' '||emp.last_name);
   DBMS_OUTPUT.PUT_LINE('email: '|| emp.email);
   DBMS_OUTPUT.PUT_LINE('salary: '|| emp.salary);
END;

Note that we had to declare a record variable (emp) to hold the employee data obtained from the database as well as the cursor for the SELECT statement.


Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber you're  missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer.Subscribe now and ignite your career.


With an explicit cursor, there is no need to worry about Oracle raising a NO_DATA_FOUND or TOO_MANY_ROWS exception as there is with an implicit cursor. The flip side to this is that you need to test whether or not any data was returned by your query. 

Oracle PL/SQL Explicit Cursor FOR LOOP 

Our first example in this PL/SQL tutorial was somewhat artificial because our query would only return at most one row from the database (assuming that employee_id is the primary key of the employees table). The second example in our PL/SQL tutorial shows how an explicit PL/SQL cursor can be used in a FOR LOOP in the same way as an implicit cursor, making it very easy to process multiple rows returned by a query. 

DECLARE
     CURSOR emp_details IS
       SELECT first_name
           ,last_name
           ,salary
     FROM employees
     WHERE employee_id>100; 

BEGIN
 FOR emp IN emp_details LOOP

   DBMS_OUTPUT.PUT_LINE('Name: '||emp.first_name|| ' '||emp.last_name||' salary = '||emp.salary);

 END LOOP;
END;
 

The only difference to using an implicit cursor is that we use the name of our explicit cursor instead of defining the query with the loop. 

With this way of querying Oracle, if the select statement doesn't match any rows in the database then the loop is not executed as there is an implicit check at the start of the loop, otherwise the loop is executed once for each row returned by the query. 

The other advantages of using a PL/SQL cursor for loop is that you don't need to declare a record variable to hold the results - it is declared for you automatically. However you can't access this record variable outside the loop. There is also less code required but the downside is that you are sacrificing control over the cursor just as you would with an implicit cursor.

Using an Oracle PL/SQL Explicit Cursor in a Loop

The previous example in our PL/SQL tutorial sacrificed control over the cursor for the benefit of a loop. The next example shows how we can have the best of both worlds - use a loop to retrieve multiple rows from Oracle and retain control over the cursor.

DECLARE
     CURSOR emp_details IS
        SELECT first_name
            ,last_name
            ,salary
      FROM employees
      WHERE employee_id>100; 

   emp emp_details%ROWTYPE; -- holds query results

BEGIN
    DBMS_OUTPUT.PUT_LINE('first name last name salary');
    OPEN emp_details;
    LOOP 
        FETCH emp_details INTO emp;

        EXIT WHEN emp_details%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(
            emp.first_name||' '||emp.last_name
             ||' $'||emp.salary);

    END LOOP;
    CLOSE emp_details;
END;

With this method we retain total control over the cursor but there is more coding to do. As this is an infinite loop we have to define the condition(s) for exiting the loop. In this case we exit once we've fetched all the matching rows from Oracle, which we determine by testing the NOTFOUND cursor attribute. It is necessary to define the exit condition for the loop because (as we mentioned earlier) Oracle won't raise an exception if you fetch from the cursor even when all the records have been retrieved.

Fetching Multiple Times From An Oracle PL/SQL Explicit Cursor

We've looked at using loops and we've looked at fetching a single record from the cursor. It is also possible to write multiple fetch statements for the cursor as our next example shows.

DECLARE
     CURSOR emp_details IS
        SELECT first_name
            ,last_name
            ,salary
      FROM employees
      WHERE employee_id>400; 

   emp emp_details%ROWTYPE; -- holds query results

BEGIN
    OPEN emp_details;
    FETCH emp_details INTO emp;

    FETCH emp_details INTO emp;
    FETCH emp_details INTO emp;
    DBMS_OUTPUT.PUT_LINE(emp.first_name||' '||emp.last_name
                                         ||' '||emp.salary);

    CLOSE emp_details;
END;

There is no guarantee that there will be 3 records matching the query and as always with explicit cursors, a NO_DATA_FOUND exception will not be raised by Oracle. If there were less than 3 records matching the query then the details of the last row returned would be displayed. This method can be used to avoid a subquery but as mentioned earlier in this PL/SQL tutorial, it does have its drawbacks and you should test the %FOUND attribute instead of assuming that any matching rows were found. If no rows at all were found then the output would be whatever the last values assigned were.

Using Local Variable and Parameters with PL/SQL Explicit Cursors

The last examples in our PL/SQL tutorial demonstrate how to parameterise our cursors. We can do this in two ways:-

  • declare formal parameters for our cursors
  • use local variables (which must be in scope)

Let's see an  example of each. First, using parameters.

DECLARE
     CURSOR 
       emp_details(
             start_id employees.employee_id%TYPE := 10
            ,stop_id  employees.employee_id%TYPE := 100
                  ) IS

        SELECT first_name
            ,last_name
      FROM employees
      WHERE employee_id BETWEEN start_id AND stop_id;

   emp emp_details%ROWTYPE; -- holds query results
BEGIN
    OPEN emp_details;
    LOOP 
        FETCH emp_details INTO emp;

        EXIT WHEN emp_details%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(emp.first_name
             ||' '||emp.last_name);

    END LOOP;
    CLOSE emp_details;

    OPEN emp_details(1,9);
    LOOP 
        FETCH emp_details INTO emp;

        EXIT WHEN emp_details%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(emp.first_name
             ||' '||emp.last_name);

    END LOOP;
    CLOSE emp_details;
END;


In the above example in our PL/SQL tutorial, when we declared the cursor we defined two parameters. These parameters can only be IN parameters (no values can be returned) and can be any valid SQL type. By providing default values for the parameters we can open the cursor without providing values explicitly. This can be useful to avoid the need to change existing code.

The next example in our PL/SQL tutorial
 demonstrates the use of local variables.

DECLARE
    start_id employees.employee_id%TYPE := 10;
    stop_id  employees.employee_id%TYPE := 100;

     CURSOR 
       emp_details IS

         SELECT first_name
            ,last_name
       FROM employees
       WHERE employee_id BETWEEN start_id AND stop_id;
        
   emp emp_details%ROWTYPE; -- holds query results
 BEGIN
    OPEN emp_details;
    LOOP 
        FETCH emp_details INTO emp;

        EXIT WHEN emp_details%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(
                           emp.first_name||' '||emp.last_name);

    END LOOP;
    CLOSE emp_details;

    start_id := 1;
    stop_id  := 9;

    OPEN emp_details;
    LOOP 
        FETCH emp_details INTO emp;

        EXIT WHEN emp_details%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(emp.first_name
             ||' '||emp.last_name);

    END LOOP;
    CLOSE emp_details;
END;

In the above PL/SQL example, we defined two local variables which were referenced in the cursor declaration giving the same effect as the previous example. The only difference is that we have to explicitly assign new values to these variables to change the values used by the cursor. These variables are only evaluated when the cursor is opened, so it's not possible to extend the range of the rows returned by changing the values inside a loop.

Explicit Oracle PL/SQL Cursor Attributes

Let's finish this PL/SQL tutorial by looking at the explicit cursor attributes which are %FOUND, %NOTFOUND, %ROWCOUNT and %ISOPEN. These attributes are accessed by prefixing them with the cursor name. eg emp_details %ROWCOUNT.


Obviously %FOUND and %NOTFOUND are mutually exclusive. Also if the cursor is not open when you access these attributes then Oracle raises an INVALID_CURSOR exception. If no rows were returned by the query (and the cursor is open) then %ROWCOUNT will be zero.
Back to implicit cursors                    On to pl/sql performance tuning


Looking for a PL/SQL training course? Learn PL/SQL from the experts and sky-rocket your career with guaranteed PL/SQL training.


Return to the overview Oracle PL/SQL tutoral 
Return to home page