Fetch cursor value to three variables

 

SQL> CREATE TABLE gifts (
  2    gift_id             INTEGER          CONSTRAINT gifts_pk PRIMARY KEY,
  3    gift_type_id        INTEGER          ,
  4    name                   VARCHAR2(30)     NOT NULL,
  5    description            VARCHAR2(50),
  6    price                  NUMBER(5, 2)
  7  );

Table created.

SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (1, 1, 'Flower', 'Birthday', 19.95);

1 row created.

SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (2, 1, 'Computer', 'New Year', 30.00);

1 row created.

SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (3, 2, 'iPod', 'Birthday', 25.99);

1 row created.

SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (4, 2, 'iPhone', 'New Year', 13.95);

1 row created.

SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (5, 2, 'Book', 'Birthday', 49.99);

1 row created.

SQL>
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2    v_gift_id gifts.gift_id%TYPE;
  3    v_name       gifts.name%TYPE;
  4    v_price      gifts.price%TYPE;
  5
  6    CURSOR giftCursor IS SELECT gift_id, name, price FROM gifts ORDER BY gift_id;
  7
  8  BEGIN
  9    OPEN giftCursor;
 10
 11    LOOP
 12      FETCH giftCursor INTO v_gift_id, v_name, v_price;
 13
 14      EXIT WHEN giftCursor%NOTFOUND;
 15
 16      DBMS_OUTPUT.PUT_LINE('v_gift_id = ' || v_gift_id || ', v_name = ' || v_name ||', v_price = ' || v_price);
 17
 18    END LOOP;
 19
 20    CLOSE giftCursor;
 21
 22  END;
 23  /
v_gift_id = 1, v_name = Flower, v_price = 19.95
v_gift_id = 2, v_name = Computer, v_price = 30
v_gift_id = 3, v_name = iPod, v_price = 25.99
v_gift_id = 4, v_name = iPhone, v_price = 13.95
v_gift_id = 5, v_name = Book, v_price = 49.99

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table gifts;

Table dropped.

Related examples in the same category

1.	Below is a function that demonstrates how to use the FETCH statement
2.	Fetch value from cursor
3.	Fetches the records from a cursor variable that returns employee information
4.	Fetch cursor to two variables
5.	legal and illegal FETCH statements.
6.	"ORA-1002: fetch out of sequence" because of the commit inside the SELECT..FOR UPDATE loop.
7.	A WHILE cursor fetch loop.
8.	Different BULK_COLLECT statements used for bulk binds
9.	Check Cursor%FOUND after fetching
10.	Fetch value in cursor until NOTFOUND
11.	ORA-01002: fetch out of sequence
12.	Fetch out cursor value and insert to another table
13.	PLS-00394: wrong number of values in the INTO list of a FETCH statement
14.	FETCH cursor BULK COLLECT
15.	Fetch cursor data to number variable
16.	Fetch cursor till cursorVariable%notfound
17.	Fetch cursor to table collection of row type
18.	Fetch row by row
19.	Fetch single column cursor to varchar2 variable

Fetch cursor value to three variables : Cursor Fetch « Cursor « Oracle PL / SQL

Related examples in the same category