SQL Cursor

Oracle implicitly opens a cursor to process each SQL statement not associated with an explicit cursor. PL/SQL lets you refer to the most recent implicit cursor as the SQL cursor, which always has these attributes: %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. They give you useful information about the execution of data manipulation statements. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. For more information, see "Managing Cursors".

Syntax

Text description of the illustration sql_cursor.gif

Keyword and Parameter Description

%BULK_ROWCOUNT

This is a composite attribute designed for use with the FORALL statement. This attribute has the semantics of an index-by table. Its ith element stores the number of rows processed by the ith execution of an UPDATE or DELETE statement. If the ith execution affects no rows, %BULK_ROWCOUNT(i) returns zero.

%BULK_EXCEPTIONS

%FOUND

This attribute yields TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it yields FALSE.

%ISOPEN

This attribute always yields FALSE because Oracle closes the SQL cursor automatically after executing its associated SQL statement.

%NOTFOUND

This attribute is the logical opposite of %FOUND. It yields TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it yields FALSE.

%ROWCOUNT

This attribute yields the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

SQL

This is the name of the Oracle implicit cursor.

Usage Notes

You can use cursor attributes in procedural statements but not in SQL statements. Before Oracle opens the SQL cursor automatically, the implicit cursor attributes yield NULL.

The values of cursor attributes always refer to the most recently executed SQL statement, wherever that statement appears. It might be in a different scope. So, if you want to save an attribute value for later use, assign it to a Boolean variable immediately.

If a SELECT INTO statement fails to return a row, PL/SQL raises the predefined exception NO_DATA_FOUND whether you check SQL%NOTFOUND on the next line or not. However, a SELECT INTO statement that calls a SQL aggregate function never raises NO_DATA_FOUND because those functions always return a value or a null. In such cases, SQL%NOTFOUND yields FALSE.

%BULK_ROWCOUNT is not maintained for bulk inserts because that would be redundant. For example, the FORALL statement below inserts one row per iteration. So, after each iteration, %BULK_ROWCOUNT would return 1:

FORALL i IN 1..15
   INSERT INTO emp (sal) VALUES (sals(i));

You can use the scalar attributes %FOUND, %NOTFOUND, and %ROWCOUNT with bulk binds. For example, %ROWCOUNT returns the total number of rows processed by all executions of the SQL statement.

%FOUND and %NOTFOUND refer only to the last execution of the SQL statement. However, you can use %BULK_ROWCOUNT to infer their values for individual executions. For example, when %BULK_ROWCOUNT(i) is zero, %FOUND and %NOTFOUND are FALSE and TRUE, respectively.

Examples

In the following example, %NOTFOUND is used to insert a row if an update affects no rows:

UPDATE emp SET sal = sal * 1.05 WHERE empno = my_empno;
IF SQL%NOTFOUND THEN
   INSERT INTO emp VALUES (my_empno, my_ename, ...);
END IF;

In the next example, you use %ROWCOUNT to raise an exception if more than 100 rows are deleted:

DELETE FROM parts WHERE status = 'OBSOLETE';
IF SQL%ROWCOUNT > 100 THEN  -- more than 100 rows were deleted
   RAISE large_deletion;
END IF;

Here is an example that uses %BULK_ROWCOUNT:

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(10, 20, 50);
BEGIN
   FORALL j IN depts.FIRST..depts.LAST
      UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(j);
   IF SQL%BULK_ROWCOUNT(3) = 0 THEN
      ...
   END;
END;

Related Topics

Cursors, Cursor Attributes