PL/SQL User's Guide and Reference

Using Cursor Attributes

Each cursor or cursor variable has four attributes: %FOUND, %ISOPEN %NOTFOUND, and %ROWCOUNT. When appended to the cursor or cursor variable, these attributes return useful information about the execution of a data manipulation statement. You can use cursor attributes in procedural statements but not in SQL statements.

Explicit Cursor Attributes

Explicit cursor attributes return information about the execution of a multi-row query. When an explicit cursor or a 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.

%FOUND

After a cursor or cursor variable is opened but before the first fetch, %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. In the following example, you use %FOUND to select an action:

LOOP
   FETCH c1 INTO my_ename, my_sal, my_hiredate;
   IF c1%FOUND THEN  -- fetch succeeded
      ...
   ELSE  -- fetch failed, so exit loop
      EXIT;
   END IF;
END LOOP;

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

%ISOPEN

%ISOPEN yields TRUE if its cursor or cursor variable is open; otherwise, %ISOPEN yields FALSE. In the following example, you use %ISOPEN to select an action:

IF c1%ISOPEN THEN  -- cursor is open
   ...
ELSE  -- cursor is closed, so open it
   OPEN c1;
END IF;

%NOTFOUND

%NOTFOUND is the logical opposite of %FOUND. %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row. In the following example, you use %NOTFOUND to exit a loop when FETCH fails to return a row:

LOOP
   FETCH c1 INTO my_ename, my_sal, my_hiredate;
   EXIT WHEN c1%NOTFOUND;
   ...
END LOOP;

If a cursor or cursor variable is not open, referencing it with %NOTFOUND raises INVALID_CURSOR.

%ROWCOUNT

When its cursor or cursor variable is opened, %ROWCOUNT is zeroed. Before the first fetch, %ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far. The number is incremented if the last fetch returned a row. In the next example, you use %ROWCOUNT to take action if more than ten rows have been fetched:

LOOP
   FETCH c1 INTO my_ename, my_deptno;
   IF c1%ROWCOUNT > 10 THEN
      ...
   END IF;
   ...
END LOOP;

If a cursor or cursor variable is not open, referencing it with %ROWCOUNT raises INVALID_CURSOR.

Table 5 - 1 shows what each cursor attribute yields before and after you execute an OPEN, FETCH, or CLOSE statement.

%FOUND %ISOPEN %NOTFOUND %ROWCOUNT
OPEN before exception FALSE exception exception
after NULL TRUE NULL 0
first FETCH before NULL TRUE NULL 0
after TRUE TRUE FALSE 1
middle FETCHes before TRUE TRUE FALSE 1
after TRUE TRUE FALSE data dependent
last FETCH before TRUE TRUE FALSE data dependent
after FALSE TRUE TRUE data dependent
CLOSE before FALSE TRUE TRUE data dependent
after exception FALSE exception exception
Notes: 1. Referencing %FOUND, %NOTFOUND, or %ROWCOUNT before a cursor is opened or after it is closed raises INVALID_CURSOR. 2. After the first FETCH, if the result set was empty, %FOUND yields FALSE, %NOTFOUND yields TRUE, and %ROWCOUNT yields 0.

Table 5 - 1. Cursor Attribute Values

Some Examples

Suppose you have a table named data_table that holds data collected from laboratory experiments, and you want to analyze the data from experiment 1. In the following example, you compute the results and store them in a database table named temp:

-- available online in file EXAMP5
DECLARE
   num1   data_table.n1%TYPE;  -- Declare variables
   num2   data_table.n2%TYPE;  -- having same types as
   num3   data_table.n3%TYPE;  -- database columns
   result temp.col1%TYPE;
   CURSOR c1 IS
      SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO num1, num2, num3;
      EXIT WHEN c1%NOTFOUND;  -- yields TRUE when FETCH
                              -- finds no more rows
      result := num2/(num1 + num3);
      INSERT INTO temp VALUES (result, NULL, NULL);
   END LOOP;
   CLOSE c1;
   COMMIT;
END;

In the next example, you check all storage bins that contain part number 5469, withdrawing their contents until you accumulate 1000 units:

-- available online in file EXAMP6
DECLARE
   CURSOR bin_cur(part_number NUMBER) IS 
      SELECT amt_in_bin FROM bins
         WHERE part_num = part_number AND amt_in_bin > 0
         ORDER BY bin_num
         FOR UPDATE OF amt_in_bin;
   bin_amt        bins.amt_in_bin%TYPE;
   total_so_far   NUMBER(5) := 0;
   amount_needed  CONSTANT NUMBER(5) := 1000;
   bins_looked_at NUMBER(3) := 0;
BEGIN
   OPEN bin_cur(5469);

   WHILE total_so_far < amount_needed LOOP
      FETCH bin_cur INTO bin_amt;
      EXIT WHEN bin_cur%NOTFOUND;
         -- if we exit, there's not enough to fill the order
      bins_looked_at := bins_looked_at + 1;
      IF total_so_far + bin_amt < amount_needed THEN
         UPDATE bins SET amt_in_bin = 0
             WHERE CURRENT OF bin_cur;
                -- take everything in the bin
         total_so_far := total_so_far + bin_amt;
      ELSE  -- we finally have enough
         UPDATE bins SET amt_in_bin = amt_in_bin
             - (amount_needed - total_so_far)
             WHERE CURRENT OF bin_cur;
         total_so_far := amount_needed;
      END IF;
   END LOOP;

   CLOSE bin_cur;

   INSERT INTO temp 
      VALUES (NULL, bins_looked_at, '<- bins looked at');
   COMMIT;
END;

Implicit Cursor Attributes

Implicit cursor attributes return information about the execution of an INSERT, UPDATE, DELETE, or SELECT INTO statement. The values of the cursor attributes always refer to the most recently executed SQL statement. Before Oracle opens the SQL cursor, the implicit cursor attributes yield NULL.

%FOUND

Until a SQL data manipulation statement is executed, %FOUND yields NULL. Thereafter, %FOUND 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, %FOUND yields FALSE. In the following example, you use %FOUND to insert a row if a delete succeeds:

DELETE FROM emp WHERE empno = my_empno;
IF SQL%FOUND THEN  -- delete succeeded
   INSERT INTO new_emp VALUES (my_empno, my_ename, ...);
   ...
END IF;

%ISOPEN

Oracle closes the SQL cursor automatically after executing its associated SQL statement. As a result, %ISOPEN always yields FALSE.

%NOTFOUND

%NOTFOUND is the logical opposite of %FOUND. %NOTFOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, %NOTFOUND yields FALSE. In this example, you use %NOTFOUND to insert a new row if an update fails:

UPDATE emp SET sal = sal * 1.05 WHERE empno = my_empno;
IF SQL%NOTFOUND THEN  -- update failed
   INSERT INTO errors VALUES (...);
END IF;

%ROWCOUNT

%ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. %ROWCOUNT yields 0 if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. In the following example, you use %ROWCOUNT to take action if more than ten rows have been deleted:

DELETE FROM emp WHERE ...
IF SQL%ROWCOUNT > 10 THEN  -- more than 10 rows were deleted
   ...
END IF;

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

Guidelines

The values of the cursor attributes always refer to the most recently executed SQL statement, wherever that statement is. It might be in a different scope (for example, in a sub-block). So, if you want to save an attribute value for later use, assign it to a Boolean variable immediately. In the following example, relying on the IF condition is dangerous because the procedure check_status might have changed the value of %NOTFOUND:

UPDATE parts SET quantity = quantity - 1 WHERE partno = part_id;
check_status(part_id);  -- procedure call
IF SQL%NOTFOUND THEN  -- dangerous!

You can debug the code as follows:

UPDATE parts SET quantity = quantity - 1 WHERE partno = part_id;
sql_notfound := SQL%NOTFOUND;  -- assign value to Boolean variable
check_status(part_id);
IF sql_notfound THEN ...

If a SELECT INTO statement fails to return a row, PL/SQL raises the predefined exception NO_DATA_FOUND whether you check %NOTFOUND on the next line or not. Consider the following example:

BEGIN
   ...
   SELECT sal INTO my_sal FROM emp WHERE empno = my_empno;
      -- might raise NO_DATA_FOUND
   IF SQL%NOTFOUND THEN  -- condition tested only when false
      ...  -- this action is never taken
   END IF;

The check is useless because the IF condition is tested only when %NOTFOUND is false. When PL/SQL raises NO_DATA_FOUND, normal execution stops and control transfers to the exception-handling part of the block.

However, a SELECT INTO statement that calls a SQL group function never raises NO_DATA_FOUND because group functions always return a value or a null. In such cases, %NOTFOUND yields FALSE, as the following example shows:

BEGIN
   ...
   SELECT MAX(sal) INTO my_sal FROM emp WHERE deptno = my_deptno;
      -- never raises NO_DATA_FOUND
   IF SQL%NOTFOUND THEN  -- always tested but never true
      ...  -- this action is never taken
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND THEN ...  -- never invoked

		%FOUND	%ISOPEN	%NOTFOUND	%ROWCOUNT
OPEN	before	exception	FALSE	exception	exception
	after	NULL	TRUE	NULL	0
first FETCH	before	NULL	TRUE	NULL	0
	after	TRUE	TRUE	FALSE	1
middle FETCHes	before	TRUE	TRUE	FALSE	1
	after	TRUE	TRUE	FALSE	data dependent
last FETCH	before	TRUE	TRUE	FALSE	data dependent
	after	FALSE	TRUE	TRUE	data dependent
CLOSE	before	FALSE	TRUE	TRUE	data dependent
	after	exception	FALSE	exception	exception
Notes: 1. Referencing %FOUND, %NOTFOUND, or %ROWCOUNT before a cursor is opened or after it is closed raises INVALID_CURSOR. 2. After the first FETCH, if the result set was empty, %FOUND yields FALSE, %NOTFOUND yields TRUE, and %ROWCOUNT yields 0.