Please forgive the scrolling window...
First we create the table from which we select:
CREATE TABLE product
( id NUMBER
, descr VARCHAR2( 100 ) )
/
INSERT INTO product ( id, descr ) VALUES ( 1, 'one' );
INSERT INTO product ( id, descr ) VALUES ( 2, 'two' );
INSERT INTO product ( id, descr ) VALUES ( 3, 'three' );
INSERT INTO product ( id, descr ) VALUES ( 4, 'four' );
INSERT INTO product ( id, descr ) VALUES ( 5, 'five' );
INSERT INTO product ( id, descr ) VALUES ( 6, 'six' );
COMMIT;
Then we need to define the object and table types that will be used for the communication.
CREATE TYPE gt_id_type
AS OBJECT ( id NUMBER )
/
CREATE TYPE gt_id_list_type AS TABLE OF gt_id_type
/
CREATE TYPE gt_product_type
AS OBJECT ( id NUMBER
, descr VARCHAR2(100 ) )
/
CREATE TYPE gt_product_list_type AS TABLE OF gt_product_type
/
Then the procedures that wil do the selection.
- GET_PRODUCTS, which will receive a list of IDs in a GT_ID_LIST_TYPE table, returning the list of products in a GT_PRODUCT_LIST_TYPE.
- GET_PRODS_BY_NAME, which will recieve a name and using GET_PRODUCTS will return a list of products whose descr contains the text specified.
You may note that the function GET_ID_LIST, used in the previous example, does not appear. This is since the data held in the ID list table isn't needed outside of GET_PRODUCTS if we prepare teh result set and pass it back in a PL/SQL table.
CREATE OR REPLACE PACKAGE product_pkg AS
FUNCTION get_products ( pt_id_list gt_id_list_type )
RETURN gt_product_list_type;
FUNCTION get_prods_by_name ( pc_product_name VARCHAR2 )
RETURN gt_product_list_type;
END;
/
CREATE OR REPLACE PACKAGE BODY product_pkg AS
--
FUNCTION get_products ( pt_id_list gt_id_list_type )
RETURN gt_product_list_type IS
--
vt_product_tab gt_product_list_type;
--
BEGIN
--
SELECT gt_product_type( id, descr )
BULK COLLECT
INTO vt_product_tab
FROM product
WHERE id IN ( SELECT id FROM TABLE ( pt_id_list ) );
--
RETURN vt_product_tab;
--
END;
--
FUNCTION get_prods_by_name ( pc_product_name VARCHAR2 )
RETURN gt_product_list_type IS
--
vt_product_ids gt_id_list_type;
vt_product_tab gt_product_list_type;
--
BEGIN
--
SELECT gt_id_type( id )
BULK COLLECT
INTO vt_product_ids
FROM product
WHERE descr LIKE '%'|| pc_product_name|| '%';
--
RETURN get_products( vt_product_ids );
--
END;
--
END;
/
Finally, a script to produce some output...
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
--
vt_product_tab gt_product_list_type;
--
BEGIN
--
vt_product_tab := product_pkg.get_prods_by_name( 't' );
--
FOR i IN 1..vt_product_tab.LAST LOOP
DBMS_OUTPUT.PUT_LINE( vt_product_tab( i ).descr );
END LOOP;
--
END;
/
The result should be the same as the previous example...
two
three
Technorati Tags: Robert+Baillie, oracle, software+development
