Tuesday, July 12, 2005

The IN Thing, a simpler example

Things are a little simpler when you're moving around PL/SQL tables of data rather than ref cursors. At that point the data used in the table case no longer needs to be available outside of the procedure that uses it. The object and table definitions still do however.
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.

  1. 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.

  2. 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: , ,

0 Comments:

Post a Comment