This Oracle PLSQL tutorial is one of a series showing you how to use the various features of PL/SQL and just as importantly how not to use them! In this tutorial we're going to look at using cursors. Cursors are touched on briefly in our tutorial on PL/SQL loops but this PLSQL tutorial will explore them in more detail.

The first thing we need to do in this PLSQL tutorial on cursors is to explain what a cursor actually is. In fact it's quite simple - a cursor is just a pointer to a memory area that stores information about the SQL statement.

For this reason there are two types of cursor - explicit cursors and implicit cursors. As the names suggest, explicit cursors are defined explicitly (using the keyword CURSOR) for SQL SELECT statements and implicit cursors for both DML statements (UPDATE, INSERT, DELETE) and SELECT statements.

The major difference between explicit and implicit cursors is the degree of control. With implicit cursors there is none - opening, closing and fetching from the cursor is done automatically. Whereas with explicit cursors you have full control over opening, closing and fetching from the cursor.

As already mentioned in this plsql tutorial, implicit cursors can be used for SELECT statements and are always used for DML statements and you have no control over them. The cursor is opened at the point in time when the SQL statement is run by Oracle, the data is fetched if the statement is a query, and the cursor is closed as soon as the SQL statement finishes - all automatically.

You can however access the results of implicit cursors (such as how many rows were updated or whether or not any rows were found) as well as the data fetched from Oracle by an implicit cursor for a SELECT statement.

Let's continue our PLSQL tutorial and have a look at how to do this.

Implicit Cursor For Select Statement

Here's a sample piece of Oracle PL/SQL code that uses an implicit cursor for a  SELECT statement.

DECLARE
   emp employees%ROWTYPE; -- holds data from database
BEGIN
   /* open,fetch,close implicit cursor */
   SELECT * INTO emp
    FROM employees
    WHERE employee_id=100; 

   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 the only variable declared was a record (emp) to hold the employee data obtained from the database and the only difference between our select statement and one we would issue using SQL*Plus is the INTO clause.

Oracle Tips & Tricks to SKYROCKET Your Career!

It's also worth noting that with an implicit PL/SQL cursor, if the SELECT statement doesn't find a match then the Oracle database raises a NO_DATA_FOUND exception. Correspondingly, if more than one row is found, a TOO_MANY_ROWS exception is raised. The way to avoid those exceptions stopping execution of the PL/SQL code is either to use exception handlers or to use a loop.

Oracle PL/SQL Implicit Cursor FOR LOOP 

This next example in our PLSQL tutorial shows how an implicit PL/SQL cursor in a FOR LOOP makes it very easy to process multiple rows from a query and eliminates the need for a NO_DATA_FOUND exception handler.

BEGIN
 FOR emp IN (SELECT id, salary FROM employees) LOOP
 END LOOP;
END;

If the query doesn't find any matching rows in the Oracle database then the loop is not executed and if it finds more than one matching row in the database, then the loop is executed once for each row returned byte query. This obviously means that Oracle will never raise either the NO_DATA_FOUND exception or the TOO_MANY_ROWS exception when using a cursor for loop.

The other advantage 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 variable outside the loop.

Implicit Cursor For DML Statement

With SELECT statements we have the option of using implicit PL/SQL cursors or explicit PLSQL cursors. For DML statements there's no such thing as an explicit cursor.

Let's continue our PLSQL tutorial with an example of this.

BEGIN
    UPDATE employees
    SET salary = salary * 1.1
    WHERE employee_id=-100; 
END;

Note that for DML statements, no exception is raised if no records are found in the database matching the conditions in the where clause. Neither is an exception raised if more than one matching record is found by Oracle.

So how do we know if our implicit cursors using DML statements worked (in the sense of making any changes)?
Well, we could query the database to look for changes but that wouldn't be very efficient, fortunately Oracle provides us with a mechanism to determine the effects of our DML statements by enabling access to attributes of the cursors.

Let's expand our DML example to demonstrate this.

BEGIN

    UPDATE employees
    SET salary = salary * 1.1
    WHERE employee_id=-100; 

    IF SQL%FOUND THEN
      DBMS_OUTPUT.PUT_LINE(
                       SQL%ROWCOUNT||' rows updated');
    END IF;

   IF SQL%NOTFOUND THEN
     DBMS_OUTPUT.PUT_LINE('No rows updated');
   END IF;

END;

The attributes of implicit PL/SQL cursors are %FOUND, %NOTFOUND, %ROWCOUNT and %ISOPEN and these attributes are accessed by prefixing them with the name of the cursor - SQL.

Obviously SQL%FOUND and SQL%NOTFOUND are mutually exclusive. Also it is not necessary to test SQL%FOUND before accessing SQL%ROWCOUNT. If no rows were affected/returned by the SQL statement then SQL%ROWCOUNT will be zero.

These attributes are available for DML and SELECT implicit cursors but will always be NULL before any implicit cursor has been run, except for SQL%OPEN which is always FALSE.

---

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

Return to the introductory PLSQL tutorial for an overview of Oracle PL/SQL
Return to home page