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