Fetch and Close a Cursor

FETCH a cursor:

  FETCH cursor_name INTO [variable1, variable2,...]
                         | record_name;

The variables must match (both in number and positionally) the columns listed in the cursor definition.
Also the data types must either match or be compatible.

A fetch statement retrieves rows one at a time from the result set of a multi-row query - in other words it
advances the cursor to the next row.

CLOSE a cursor:

  CLOSE cursor_name;

Closing a cursor releases the context area.

Cursor Attributes:

cursor%ROWCOUNT - int - number of rows fetched so far
cursor%ROWTYPE  - returns the datatype of the underlying table
cursor%FOUND    - bool - TRUE if >1 row returned
cursor%NOTFOUND - bool - TRUE if 0 rows returned
cursor%ISOPEN   - bool - TRUE if cursor still open 

Notes:

Cursor%ROWCOUNT will display the number of rows retrieved so far.
Until Oracle has retrieved all the rows then by definition it does not have an accurate record of how many there are. Of course user_tables or dba_tables will have a count of the number of rows (NUM_ROWS), but this is only as up to date as the statistics.

Examples:

--Opening a cursor only if necessary

   IF NOT trip_cursor%ISOPEN THEN
      OPEN trip_cursor
   END IF;

--Fetching a maximum of 20 records (or less if the table is smaller)

DECLARE
   v_trip_id    business_trips.bt_id_pk%TYPE;
   v_hotel_id   business_trips.bt_hotel_id%TYPE;
   CURSOR trip_cursor IS 
      SELECT
          bt_id_pk, bt_hotel_id
      FROM
          business_trips;
BEGIN
   OPEN trip_cursor;
   LOOP
      FETCH trip_cursor INTO v_trip_id, v_hotel_id;
      EXIT WHEN trip_cursor%ROWCOUNT > 20 OR trip_cursor%NOTFOUND;
   ...
   END LOOP;
   CLOSE trip_cursor;
END;

--Taking this a step further by fetching into a ROWTYPE Record variable

DECLARE
   CURSOR trip_cursor IS 
      SELECT bt_id_pk, bt_hotel_id
      FROM business_trips;
   trip_record trip_cursor%ROWTYPE

BEGIN
   OPEN trip_cursor;
   LOOP
      FETCH trip_cursor INTO trip_record;
      EXIT WHEN trip_cursor%NOTFOUND;
      INSERT INTO copy_of_business_trips (bt_id_pk, bt_hotel_id)
      VALUES (trip_record.bt_id_pk, trip_record.bt_hotel_id);
   END LOOP;
   CLOSE job_cursor;
END;

--Retrieve all trips from the t_business_trips table using a cursor 
and print only those with a duration of one day:

SET SERVEROUTPUT ON
DECLARE
   CURSOR trip_cursor IS
      SELECT bt_id_pk, bt_duration
      FROM business_trips;
BEGIN
   FOR trip_record IN trip_cursor LOOP
      -- implicit open/fetch occur
      IF trip_record.bt_duration = 1 THEN
        DBMS_OUTPUT_LINE ('Trip Number ' || trip_record.bt_id_pk
                        || ' is a one day trip');
      END IF;
   END LOOP; -- IMPLICIT CLOSE OCCURS
END;
/

--In reality this last example would be better written 
as the trivial SQL statement:

  SELECT 
      'Trip Number ' || bt_id_pk || ' is a one day trip'
  FROM
      business_trips
  WHERE
      bt_duration = 1;

Related Commands:

Fetch and CLOSE a REF cursor