Mega Code Archive
Create package and member cursor
<![CDATA[
SQL>
SQL>
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>
SQL>
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> CREATE OR REPLACE PACKAGE gift_package AS
2 TYPE t_ref_cursor IS REF CURSOR;
3 FUNCTION get_gifts_ref_cursor RETURN t_ref_cursor;
4 PROCEDURE updatePrice (p_gift_id IN gifts.gift_id%TYPE,p_factor IN NUMBER);
5 END gift_package;
6 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY gift_package AS
2 FUNCTION get_gifts_ref_cursor
3 RETURN t_ref_cursor IS
4 gifts_ref_cursor t_ref_cursor;
5 BEGIN
6 OPEN gifts_ref_cursor FOR SELECT gift_id, name, price FROM gifts;
7
8 RETURN gifts_ref_cursor;
9 END get_gifts_ref_cursor;
10
11 PROCEDURE updatePrice(p_gift_id IN gifts.gift_id%TYPE,p_factor IN NUMBER) AS
12 v_gift_count INTEGER;
13 BEGIN
14 SELECT COUNT(*)INTO v_gift_count FROM gifts WHERE gift_id = p_gift_id;
15 IF v_gift_count = 1 THEN
16 UPDATE gifts
17 SET price = price * p_factor
18 WHERE gift_id = p_gift_id;
19 COMMIT;
20 END IF;
21 EXCEPTION
22 WHEN OTHERS THEN
23 ROLLBACK;
24 END updatePrice;
25 END gift_package;
26 /
Package body created.
SQL>
SQL> drop table gifts;
Table dropped.
]]>