8 - More Introduction to Oracle PL/SQL

Table of Contents

coming soon

Cursor Attributes

Keyword and Parameter Description

cursor_name

An explicit cursor previously declared within the current scope.

cursor_variable_name

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

%FOUND Attribute

A cursor attribute that can be appended to the name of a cursor or cursor variable. Before the first fetch from an open cursor, cursor_name%FOUND returns NULL. Afterward, it returns TRUE if the last fetch returned a row, or FALSE if the last fetch failed to return a row.

host_cursor_variable_name

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.

%ISOPEN Attribute

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

%NOTFOUND Attribute

A cursor attribute that can be appended to the name of a cursor or cursor variable. Before the first fetch from an open cursor, cursor_name%NOTFOUND returns NULL. Thereafter, it returns FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row.

%ROWCOUNT Attribute

A cursor attribute that 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 returns 0. Thereafter, it returns the number of rows fetched so far. The number is incremented if the latest fetch returned a row.

Usage Notes

The cursor attributes apply to every cursor or cursor variable. 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. Before the first fetch, %NOTFOUND evaluates to NULL. If FETCH never executes successfully, the EXIT WHEN condition is never TRUE and the loop is never exited. To be safe, you might want to use the following EXIT statement instead: EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL; You can use the cursor attributes in procedural statements but not in SQL statements.

Examples

This PL/SQL block uses %FOUND to select an action.

    DECLARE
       CURSOR emp_cur IS SELECT * FROM employees ORDER BY employee_id;
       emp_rec employees%ROWTYPE;
    BEGIN
       OPEN emp_cur;
       LOOP   -- loop through the table and get each employee
          FETCH emp_cur INTO emp_rec;
          IF emp_cur%FOUND THEN
             dbms_output.put_line('Employee #' || emp_rec.employee_id ||
                ' is ' || emp_rec.last_name);
          ELSE
             dbms_output.put_line('--- Finished processing employees ---');
             EXIT;
          END IF;
       END LOOP;
       CLOSE emp_cur;
    END;

Instead of using %FOUND in an IF statement, the next example uses %NOTFOUND in an EXIT WHEN statement.

    DECLARE
       CURSOR emp_cur IS SELECT * FROM employees ORDER BY employee_id;
       emp_rec employees%ROWTYPE;
    BEGIN
       OPEN emp_cur;
       LOOP   -- loop through the table and get each employee
          FETCH emp_cur INTO emp_rec;
          EXIT WHEN emp_cur%NOTFOUND;
          dbms_output.put_line('Employee #' || emp_rec.employee_id ||
             ' is ' || emp_rec.last_name);
       END LOOP;
       CLOSE emp_cur;
    END;

The following example uses %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:

    DECLARE
       CURSOR c1 is
       SELECT last_name, employee_id, salary FROM employees
          ORDER BY salary DESC;   -- start with highest-paid employee
       my_name employees.last_name%TYPE;
       my_empno employees.employee_id%TYPE;
       my_sal   employees.salary%TYPE;
    BEGIN
       OPEN c1;
       LOOP
          FETCH c1 INTO my_name, my_empno, my_sal;
          EXIT WHEN (c1%ROWCOUNT > 5) OR (c1%NOTFOUND);
          dbms_output.put_line('Employee ' || my_name || ' (' || my_empno || ') makes ' || my_sal);
       END LOOP;
       CLOSE c1;
    END;

The following example raises an exception if many rows are deleted:

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