PL/SQL User's Guide and Reference

Cursor Attributes

Description

Cursors and cursor variables have four attributes that give you useful information about the execution of a data manipulation statement. For more information, see "Using Cursor Attributes"

There are two kinds of cursors: implicit and explicit. PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including single-row queries. For multi-row queries, you can explicitly declare a cursor or cursor variable to process the rows.

Syntax

cursor_attribute ::=

{  cursor_name 
 | cursor_variable_name
 | :host_cursor_variable_name
 | SQL}{%FOUND | %ISOPEN | %NOTFOUND | %ROWCOUNT}

Keyword and Parameter Description

cursor_name

This identifies an explicit cursor previously declared within the current scope.

cursor_variable_name

This identifies a PL/SQL cursor variable (or parameter) previously declared within the current scope.

host_cursor_variable_ name

This identifies a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.

SQL

This is the name of the implicit SQL cursor. For more information, see "SQL Cursor"

%FOUND

This is a cursor attribute, which can be appended to the name of a cursor or cursor variable. After a cursor is opened but before the first fetch, cursor_name%FOUND yields NULL. Thereafter, it yields TRUE if the last fetch returned a row, or FALSE if the last fetch failed to return a row.

Until a SQL statement is executed, SQL%FOUND yields NULL. Thereafter, it yields TRUE if the statement affected any rows, or FALSE if it affected no rows.

%ISOPEN

This is a cursor attribute, which can be appended to the name of a cursor or cursor variable. If a cursor is open, cursor_name%ISOPEN yields TRUE; otherwise, it yields FALSE.

Oracle automatically closes the implicit SQL cursor after executing its associated SQL statement, so SQL%ISOPEN always yields FALSE.

%NOTFOUND

This is a cursor attribute, which can be appended to the name of a cursor or cursor variable. After a cursor is opened but before the first fetch, cursor_name%NOTFOUND yields NULL. Thereafter, it yields FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row.

Until a SQL statement is executed, SQL%NOTFOUND yields NULL. Thereafter, it yields FALSE if the statement affected any rows, or TRUE if it affected no rows.

%ROWCOUNT

This is a cursor attribute, which can be appended to the name of a cursor or cursor variable. When a cursor is opened, %ROWCOUNT is zeroed. Before the first fetch, cursor_name%ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far. The number is incremented if the latest fetch returned a row.

Until a SQL statement is executed, SQL%ROWCOUNT yields NULL. Thereafter, it yields the number of rows affected by the statement. SQL%ROWCOUNT yields 0 if the statement affected no rows.

Usage Notes

You can use the cursor attributes in procedural statements but not in SQL statements.

The cursor attributes apply to every cursor or cursor variable. So, for example, you can open multiple cursors, then use %FOUND or %NOTFOUND to tell which cursors have rows left to fetch. Likewise, you can use %ROWCOUNT to tell how many rows have been fetched so far.

If a cursor or cursor variable is not open, referencing it with %FOUND, %NOTFOUND, or %ROWCOUNT raises the predefined exception INVALID_CURSOR.

When a cursor or cursor variable is opened, the rows that satisfy the associated query are identified and form the result set. Rows are fetched from the result set one at a time.

If a SELECT INTO statement returns more than one row, PL/SQL raises the predefined exception TOO_MANY_ROWS and sets %ROWCOUNT to 1, not the actual number of rows that satisfy the query.

Examples

The PL/SQL block below uses %FOUND to select an action. The IF statement either inserts a row or exits the loop unconditionally.

-- available online in file EXAMP12
DECLARE
   CURSOR num1_cur IS SELECT num FROM num1_tab
      ORDER BY sequence;
   CURSOR num2_cur IS SELECT num FROM num2_tab
      ORDER BY sequence;
   num1     num1_tab.num%TYPE;
   num2     num2_tab.num%TYPE;
   pair_num NUMBER := 0;
BEGIN
   OPEN num1_cur;
   OPEN num2_cur;
   LOOP   -- loop through the two tables and get
         -- pairs of numbers
      FETCH num1_cur INTO num1;
      FETCH num2_cur INTO num2;
      IF (num1_cur%FOUND) AND (num2_cur%FOUND) THEN
         pair_num := pair_num + 1;
         INSERT INTO sum_tab VALUES (pair_num, num1 + num2);
      ELSE
         EXIT;
      END IF;
   END LOOP;
   CLOSE num1_cur;
   CLOSE num2_cur;
END;

The next example uses the same block. However, instead of using %FOUND in an IF statement, it uses %NOTFOUND in an EXIT WHEN statement.

-- available online in file EXAMP13
DECLARE
   CURSOR num1_cur IS SELECT num FROM num1_tab
      ORDER BY sequence;
   CURSOR num2_cur IS SELECT num FROM num2_tab
      ORDER BY sequence;
   num1     num1_tab.num%TYPE;
   num2     num2_tab.num%TYPE;
   pair_num NUMBER := 0;
BEGIN
   OPEN num1_cur;
   OPEN num2_cur;
   LOOP   -- loop through the two tables and get
          -- pairs of numbers
      FETCH num1_cur INTO num1;
      FETCH num2_cur INTO num2;
      EXIT WHEN (num1_cur%NOTFOUND) OR (num2_cur%NOTFOUND);
      pair_num := pair_num + 1;
      INSERT INTO sum_tab VALUES (pair_num, num1 + num2);
   END LOOP;
   CLOSE num1_cur;
   CLOSE num2_cur;
END;

In the following example, you use %ISOPEN to make a decision:

IF NOT (emp_cur%ISOPEN) THEN
   OPEN emp_cur;
END IF;
FETCH emp_cur INTO emp_rec;

The following PL/SQL block uses %ROWCOUNT to fetch the names and salaries of the five highest-paid employees:

-- available online in file EXAMP14
DECLARE
   CURSOR c1 is
   SELECT ename, empno, sal FROM emp
      ORDER BY sal DESC;   -- start with highest-paid employee
   my_ename CHAR(10);
   my_empno NUMBER(4);
   my_sal   NUMBER(7,2);
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO my_ename, my_empno, my_sal;
      EXIT WHEN (c1%ROWCOUNT > 5) OR (c1%NOTFOUND);
      INSERT INTO temp VALUES (my_sal, my_empno, my_ename);
      COMMIT;
   END LOOP;
   CLOSE c1;
END;

In the final example, you use %ROWCOUNT to raise an exception if an unexpectedly high number of rows is deleted:

DELETE FROM accts WHERE status = 'BAD DEBT';
IF SQL%ROWCOUNT > 10 THEN
    RAISE out_of_bounds;
END IF;