cursor.sql 1.21 KB
Newer Older
O'Reilly Media, Inc. committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
-- The SQL*Plus script cursor.sql displays the rows in the
-- customers table using a cursor.

DECLARE

  -- step 1: declare the variables
  v_id         customers.id%TYPE;
  v_first_name customers.first_name%TYPE;
  v_last_name  customers.last_name%TYPE;
  v_dob        customers.dob%TYPE;
  v_phone      customers.phone%TYPE;

  -- step 2: declare the cursor
  CURSOR customer_cursor IS
    SELECT
      id, first_name, last_name, dob, phone
    FROM
      customers
    ORDER BY
      id;

BEGIN

  -- step 3: open the cursor
  OPEN customer_cursor;
  LOOP

    -- step 4: fetch the rows from the cursor
    FETCH
      customer_cursor
    INTO
      v_id, v_first_name, v_last_name, v_dob, v_phone;

    -- exit the loop when there are no more rows, as indicated by
    -- the boolean customer_cursor%NOTFOUND
    EXIT WHEN customer_cursor%NOTFOUND;

    -- use DBMS_OUTPUT.PUT_LINE() to print the variables
    DBMS_OUTPUT.PUT_LINE(
      'v_id = ' || v_id || ', v_first_name = ' || v_first_name ||
      ', v_last_name = ' || v_last_name || ', v_dob = ' || v_dob ||
      ', v_phone = ' || v_phone
    );

  END LOOP;

  -- step 5: close the cursor
  CLOSE customer_cursor;

END;
/