Using Objects in PLSQL

Using Objects in PL/SQL

You can use objects in PL/SQL. In this section, you ll see a package named product_package that contains the following items:

• A function named get_products() that returns the objects in the object_products table

• A procedure named insert_product() that adds an object to the object_products table

The object_product.sql script contains the following package specification:

 CREATE OR REPLACE PACKAGE product_package AS  TYPE ref_cursor_typ IS REF CURSOR;  FUNCTION get_products RETURN ref_cursor_typ;  PROCEDURE insert_product (p_id IN object_products.id%TYPE,  p_name IN object_products.name%TYPE,  p_description IN object_products.description%TYPE,  p_price IN object_products.price%TYPE,  p_days_valid IN object_products.days_valid%TYPE); END product_package; / 

The object_product.sql script contains the following package body:

 CREATE OR REPLACE PACKAGE BODY product_package AS  FUNCTION get_products  RETURN ref_cursor_typ IS  products_ref_cursor ref_cursor_typ;  BEGIN  -- get the REF CURSOR  OPEN products_ref_cursor FOR  SELECT VALUE(op)  FROM object_products op;  -- return the REF CURSOR  RETURN products_ref_cursor;  END get_products;  PROCEDURE insert_product (p_id IN object_products.id%TYPE,  p_name IN object_products.name%TYPE,  p_description IN object_products.description%TYPE,  p_price IN object_products.price%TYPE,  p_days_valid IN object_products.days_valid%TYPE) AS  product product_typ :=  product_typ(p_id, p_name, p_description, p_price, p_days_valid);  BEGIN  INSERT INTO object_products VALUES (product);  COMMIT;  EXCEPTION  WHEN OTHERS THEN  ROLLBACK;  END insert_product; END product_package; / 

Notice the following about product_package :

• The get_products() function returns the contents of the object_products table as product_typ objects using VALUE .

• The insert_product() procedure accepts a number of parameters that are used to set the attributes of the product object, which is of type product_typ . The product object is then inserted into the object_products table.

The following example calls product_package.insert_product() to add a new row to the object_products table:

 CALL product_package.insert_product(4, 'salsa',  '15 oz jar of salsa', 1.50, 20); 

The next example calls product_package.get_products() to retrieve the products from object_products :

  SELECT product_package.get_products   FROM dual;  GET_PRODUCTS -------------------- CURSOR STATEMENT : 1 CURSOR STATEMENT : 1 VALUE(OP)(ID, NAME, DESCRIPTION, PRICE, DAYS_VALID) ------------------------------------------------------------ PRODUCT_TYP(1, 'Pasta', '20 oz bag of pasta', 3.95, 10) PRODUCT_TYP(2, 'Sardines', '12 oz box of sardines', 2.99, 5) PRODUCT_TYP(3, 'salsa', '15 oz jar of salsa', 1.5, 20)