Oracle PL/SQL for DBAs

Cursor Attributes

A key benefit of explicit cursors is the attributes they provide to facilitate logical programming. Consider the following example. Here, we want to look for an order and do something if it is found. The first procedure using implicit cursors has to rely on exception handling to determine whether a record was found or not.

    CREATE OR REPLACE PROCEDURE demo AS
      v_date      DATE;
      v_its_there BOOLEAN := TRUE;
    BEGIN
      BEGIN
        SELECT order_date
          INTO v_date
          FROM orders
         WHERE order_number = 1;
      EXCEPTION
        WHEN no_data_found THEN
          v_its_there := FALSE;
        WHEN OTHERS THEN
          RAISE;
      END;
      IF NOT v_its_there THEN
        do_something;
      END IF;
    END;

The following code, now using explicit cursors, is easier to follow because the availability of the cursor ’s %NOTFOUND attribute makes it obvious what is being checked. There is also no need to embed extra PL/SQL blocks (BEGIN-END) just to handle logic.

    CREATE OR REPLACE PROCEDURE demo AS
      CURSOR curs_get_date IS
      SELECT order_date
        FROM orders
       WHERE order_number = 1;
      v_date DATE;
    BEGIN
      OPEN curs_get_date;
      FETCH curs_get_date INTO v_date;
      IF curs_get_date%NOTFOUND THEN
        do_something;
      END IF;
      CLOSE curs_get_date;
    END;

Oracle supports the following cursor attributes:

Attribute	Description
%BULK_ROWCOUNT	Number of records returned by a bulk fetch (BULK COLLECT INTO) operation.
%FOUND	TRUE if the last FETCH was successful, FALSE if not.
%NOTFOUND	TRUE if the last FETCH was not successful, FALSE if it was.
%ISOPEN	TRUE if the cursor is open, FALSE if not. ...