Explicit cursors provide you with much greater control over the operations (opening, closing and fetching) of the cursors 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 continue our PLSQL tutorial with some examples of using explicit cursors.

Explicit Cursor For Select Statement

Remember, as we said earlier in this PLSQL 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 * 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 PLSQL 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 PLSQL 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 * 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 PLSQL 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 * FROM employees WHERE employee_id>100;

BEGIN

   OPEN emp_details;

LOOP
FETCH emp_details INTO emp;
EXIT WHEN emp_details%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE(
'Name: '||emp.first_name||' '||emp.last_name
||' salary = '||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 * FROM employees ORDER BY salary DESC;

BEGIN
   OPEN emp_details;
FETCH emp_details INTO emp;
FETCH emp_details INTO emp;
FETCH emp_details INTO emp;
   DBMS_OUTPUT.PUT_LINE(
'Name: '||emp.first_name||' '||emp.last_name
||' salary = '||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 record returned would be displayed. This method can be used to avoid a subquery but as mentioned earlier in thei PLSQL tutorial, it does have its drawbacks and you should test the %FOUND attribute instead of assuming that there will be at least 3 records (in this case).

Using Local Variable and Parameters with PL/SQL Explicit Cursors

The last examples in our PLSQL 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 * FROM employees
   WHERE employee_id BETWEEN start_id AND stop_id;

BEGIN

   OPEN emp_details;

LOOP
FETCH emp_details INTO emp;
EXIT WHEN emp_details%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE(
'Name: '||emp.first_name||' '||emp.last_name
||' salary = '||emp.salary);
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(
'Name: '||emp.first_name||' '||emp.last_name
||' salary = '||emp.salary);
END LOOP;

CLOSE emp_details;

END;

In the above example in our PLSQL 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 PLSQL 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 * FROM employees
      WHERE employee_id BETWEEN start_id AND stop_id;

BEGIN

   OPEN emp_details;

LOOP
FETCH emp_details INTO emp;
EXIT WHEN emp_details%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE(
'Name: '||emp.first_name||' '||emp.last_name
||' salary = '||emp.salary);
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(
'Name: '||emp.first_name||' '||emp.last_name
||' salary = '||emp.salary);
END LOOP;

CLOSE emp_details;

END;

In the above PLSQL 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. Note that 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 PLSQL tutorial by looking at the explicit cursor attributes. We've already seen one attribute - %NOTFOUND. The other attributes of PL/SQL cursors are %FOUND, %ROWCOUNT and %ISOPEN which are accessed by prefixing them with the cursor name.

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.

Looking for an Oracle PL/SQL training course? Learn PL/SQL from the experts and sky- rocket your career with Oracle PL/SQL training in the UK from Smartsoft.

Oracle PLSQL Tutorial - How To Use Explicit Cursors

Definition of a cursor