Oracle PL/SQL Tutorial/PL SQL Data Types/Object
Using Objects in PL/SQL
<source lang="sql">
SQL> SQL> SQL> CREATE Or Replace TYPE ProductType AS OBJECT (
2 id NUMBER, 3 name VARCHAR2(15), 4 description VARCHAR2(22), 5 price NUMBER(5, 2), 6 days_valid NUMBER 7 ) 8 /
Type created. SQL> SQL> SQL> CREATE TABLE object_products OF ProductType
2 /
Table created. SQL> SQL> INSERT INTO object_products (
2 id, name, description, price, days_valid 3 ) VALUES ( 4 1, "AAA", "BBB", 2.99, 5 5 );
1 row created. SQL> SQL> select * from object_products;
ID NAME DESCRIPTION PRICE DAYS_VALID
--- --------------- ---------------------- ---------- ----------
1 AAA BBB 2.99 5
SQL> SQL> CREATE OR REPLACE PACKAGE product_package AS
2 TYPE ref_cursor_typ IS REF CURSOR; 3 FUNCTION get_products RETURN ref_cursor_typ; 4 PROCEDURE insert_product ( 5 p_id IN object_products.id%TYPE, 6 p_name IN object_products.name%TYPE, 7 p_description IN object_products.description%TYPE, 8 p_price IN object_products.price%TYPE, 9 p_days_valid IN object_products.days_valid%TYPE 10 ); 11 END product_package; 12 /
Package created. SQL> SQL> SQL> CREATE OR REPLACE PACKAGE BODY product_package AS
2 FUNCTION get_products 3 RETURN ref_cursor_typ IS 4 products_ref_cursor ref_cursor_typ; 5 BEGIN 6 OPEN products_ref_cursor FOR 7 SELECT VALUE(op) 8 FROM object_products op; 9 RETURN products_ref_cursor; 10 END get_products; 11 12 PROCEDURE insert_product ( 13 p_id IN object_products.id%TYPE, 14 p_name IN object_products.name%TYPE, 15 p_description IN object_products.description%TYPE, 16 p_price IN object_products.price%TYPE, 17 p_days_valid IN object_products.days_valid%TYPE 18 ) AS 19 product ProductType := 20 ProductType( 21 p_id, p_name, p_description, p_price, p_days_valid 22 ); 23 BEGIN 24 INSERT INTO object_products VALUES (product); 25 COMMIT; 26 EXCEPTION 27 WHEN OTHERS THEN 28 ROLLBACK; 29 END insert_product; 30 END product_package; 31 /
Package body created. SQL> SQL> CALL product_package.insert_product(101, "AAA", "AAA and AAA", 1.50, 20); Call completed. SQL> SQL> SELECT product_package.get_products
2 FROM dual;
GET_PRODUCTS
CURSOR STATEMENT : 1 CURSOR STATEMENT : 1 VALUE(OP)(ID, NAME, DESCRIPTION, PRICE, DAYS_VALID)
PRODUCTTYPE(1, "AAA", "BBB", 2.99, 5) PRODUCTTYPE(101, "AAA", "AAA and AAA", 1.5, 20)
SQL> SQL> select * from object_products;
ID NAME DESCRIPTION PRICE DAYS_VALID
--- --------------- ---------------------- ---------- ----------
1 AAA BBB 2.99 5
- AAA AAA and AAA 1.5 20
SQL> SQL> drop table object_products; Table dropped. SQL></source>