Using a Cursor Variable to Retrieve Result Set Rows One at a Time

The following procedure uses each of the necessary statements in its simplest form, but provides references to their complete syntax.

To use a cursor variable to retrieve result set rows one at a time:

1. In the declarative part:

   1. Declare the REF CURSOR type:

      TYPE cursor_type IS REF CURSOR [ RETURN return_type ];
      

      For complete REF CURSOR type declaration syntax, see Oracle Database PL/SQL Language Reference.

   2. Declare a cursor variable of that type:

      cursor_variable cursor_type;
      

      For complete cursor variable declaration syntax, see Oracle Database PL/SQL Language Reference.

   3. Declare a record to hold the row returned by the cursor:

      record_name return_type;
      

      For complete information about record declaration syntax, see Oracle Database PL/SQL Language Reference.

2. In the executable part:

   1. Open the cursor variable for a specific query:

      OPEN cursor_variable FOR query;
      

      For complete information about OPEN FOR statement syntax, see Oracle Database PL/SQL Language Reference.

   2. Fetch rows from the cursor variable (rows from the result set) one at a time, using a LOOP statement that has syntax similar to this:

      LOOP
        FETCH cursor_variable INTO record_name;
        EXIT WHEN cursor_variable%NOTFOUND;
        -- Process row that is in record_name:
        statement;
        [ statement; ]...
      END LOOP;
      

      For complete information about FETCH statement syntax, see Oracle Database PL/SQL Language Reference.

   3. Close the cursor variable:

      CLOSE cursor_variable;
      

      Alternatively, you can open the cursor variable for another query, which closes it for the current query.

      For complete information about CLOSE statement syntax, see Oracle Database PL/SQL Language Reference.