Using an Explicit Cursor to Retrieve Result Set Rows One at a Time

The following procedure uses each necessary statement in its simplest form, but provides references to its complete syntax.

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

1. In the declarative part:

   1. Declare the cursor:

      CURSOR cursor_name IS query;
      

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

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

      record_name cursor_name%ROWTYPE;
      

      For complete %ROWTYPE syntax, see Oracle Database PL/SQL Language Reference.

2. In the executable part:

   1. Open the cursor:

      OPEN cursor_name;
      

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

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

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

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

   3. Close the cursor:

      CLOSE cursor_name;
      

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