Sometimes it’s not only a single value we need to return but a whole set of data. In this case, it might be handy to return the data set as a record in the OUT parameter of a procedure or package. See the sample code below to demonstrate it’s usage.
--package spec
CREATE OR REPLACE PACKAGE product_details AS
TYPE products IS RECORD( p_id NUMBER
,p_name VARCHAR2(50)
,p_price NUMBER(8,2)
,p_dt_load DATE
);
TYPE table_products IS TABLE OF products;
PROCEDURE get_orders( p_cat IN product.category%TYPE
,p_type IN product.prod_type%TYPE
,prod_rec OUT table_products);
END product_details;
/
-- package body
CREATE OR REPLACE PACKAGE BODY product_details AS
PROCEDURE get_orders( p_cat IN product.category%TYPE
,p_type IN product.prod_type%TYPE
,prod_rec OUT table_products) IS
BEGIN
SELECT id_product
,product_name
,product_price
,dtime_loaded
BULK COLLECT INTO prod_rec
FROM product
WHERE 1=1
AND category = p_cat
AND prod_type = p_type;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END get_orders;
END product_details;
/
-- call the package.procedure
DECLARE
r_prod_rec_type product_details.table_products;
BEGIN
-- call the procedure inside the package
product_details.get_orders('TV', 'Electronics', r_prod_rec_type);
-- print header
dbms_output.put_line('Product details');
dbms_output.put_line('');
dbms_output.put_line('ID;product_name;product_price;dtime_loaded');
dbms_output.put_line('------------------------------------');
-- loop through the record set
FOR r_rec IN 1 .. r_prod_rec_type.count
LOOP
dbms_output.put_line(r_prod_rec_type(r_rec).p_id || ';' ||
r_prod_rec_type(r_rec).p_name || ';' ||
r_prod_rec_type(r_rec).p_price || ';' ||
r_prod_rec_type(r_rec).p_dt_load);
END LOOP;
END;
/
/* output >>
Product details
ID;product_name;product_price;dtime_loaded
------------------------------------------
1337;Alfa product;11999;30-OCT-18
1338;Premium stuff;19899;30-OCT-18
1339;Discounted product;20000;30-OCT-18
*/