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: Robert+Baillie, oracle, software+development