|
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.
|