|
|
|
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 affected by last SQL statement
cursor%FOUND - bool - TRUE if >1 row returned
cursor%NOTFOUND - bool - TRUE if 0 rows returned
cursor%ISOPEN - bool - TRUE if cursor still open
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
Related Views:
