Implicit Cursor Attributes

Like explicit cursors, implicit cursors use attributes. The implicit cursor attributes are %isopen, %found, %notfound, and %rowcount. Because implicit cursors have no name, you must append SQL to the attributes. The implicit cursor contains information concerning the processing of the last SQL statement (INSERT, UPDATE, DELETE, and SELECT INTO) that was not associated with an explicit cursor. Implicit cursor attributes can be used only in PL/SQL statements and not in SQL statements. The following sections briefly describe each of these.

The %isopen Attribute

After the execution of the SQL statement, the associated SQL cursor is always closed automatically by Oracle. Hence the %isopen attribute always evaluates to false.

The %found Attribute

This attribute will equate to true if an INSERT, UPDATE, or DELETE affected one or more rows or a SELECT INTO returns one or more rows. Otherwise, it evaluates to false. %found will equate to null until a SQL DML statement is executed. The following is an example using the implicit %found attribute:

UPDATE employees
set pay_type = `S'
WHERE name = `Bernard' or name = `Stanton';

IF sql%found THEN
      COMMIT;
ELSE
     employee_not_found_procedure;
END IF;

The %notfound Attribute

UPDATE employees
set pay_type = `S'
WHERE name = `Bernard' or name = `Stanton';

IF sql%notfound THEN
     employee_not_found_procedure;
ELSE
     COMMIT;
END IF;

Careful code design must be exercised when using the SELECT INTO statements. The %notfound attribute cannot be used immediately after the SELECT INTO statement when no records are retrieved. This is because the no_data_found exception will be raised before the %notfound attribute is set. The following example illustrates this point:

/* The exception %notfound used with no exception handler in place.*/
/*Hence, proper processing might be inappropriately missed.*/
BEGIN
SELECT pay_type INTO hold_type
from employee WHERE name = `Catherine';
     IF SQL%notfound THEN            -- processing may never reach here if
  employee_not_found_procedure;   -- the prior SELECT statement returns no rows
     END IF;
exceptions
        ...
END;

The following shows the same example, except with an exception handler in place:

/* The exception %notfound is used with exception handler in place.*/
/* Hence proper processing takes place.*/
BEGIN
SELECT pay_type INTO hold_type
from employee WHERE name = `Catherine';   --processing goes to the exceptions
                                          -- area when no rows are returned.
when others THEN
     IF SQL%notfound THEN                 --not found logic is executed here
             employee_not_found_procedure;
     END IF;
END;

The %rowcount Attribute
This attribute equates to the total number of rows affected by the most recent SQL statement. An example of the %rowcount follows:

BEGIN
UPDATE employees
set pay_rate = pay_rate * 1.05
WHERE pay-type = `S';
message(`Total records updated are:'to_char(sql%rowcount));
END

Summary

Cursors are PL/SQL constructs that enable you to process, one row at a time, the results of a multirow query. Implicit cursors are created for each DML statement, whereas explicit cursors are created by users to process queries that return multiple rows. Furthermore, cursors improve code processing by reducing the need to parse code repeatedly.

Q&A

Q When would you use an explicit cursor instead of an implicit cursor?

A Explicit cursors must be declared and used when you want to process queries that return multiple rows and you want to handle these rows individually.

Q What are the four steps to using an explicit cursor?

A The four steps are

1. Declare the cursor.
2. Open the cursor.
3. Fetch the rows.
4. Close the cursor.

Q Is there any way to expedite or simplify the steps to using a cursor?

A Yes. The CURSOR FOR loop construct will cause Oracle to implicitly open, fetch, and close the cursor.

Workshop

The following workshop will test your understanding of PL/SQL cursors and their uses. The answers to the quiz and exercise can be found in Appendix A, "Answers."

Quiz

1. What are the cursor attributes and what is their purpose?
2. How many cursors can you use at a time?
3. Where is the cursor pointer when the cursor is first opened?

Exercise

Create a PL/SQL block that determines the top five highest-paid employees from your employee table. Be sure to incorporate the usage of the appropriate cursor attributes. Print these five employees to the screen.