Open and Fetch Cursor

Create Cursor:

cursor TEST  is  select id, ad, soyad from perrsonel;

Open Cursor:

open TEST;

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

Fetch Cursor:

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

Close Cursor:

close  TEST;

Example:

 –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;
/