Oracle PL/SQL – Treating collections as tables

It is possible to perform SQL manipulations on collections as if there are database tables. The TABLE() and CAST() functions can be used to accomplish this. This article will cover two ways to assemble the collection to be CASTed: PIPELINED functions and directly populating a collection.

To explore this concept, we’ll need a test object type and a nested table of those objects:

SQL> CREATE OR REPLACE
  2  TYPE person_tp IS OBJECT
  3  (
  4    first_name VARCHAR2( 100 ),
  5    last_name VARCHAR2( 100 ),
  6    age NUMBER
  7  );
  8  /

Type created.

SQL>
SQL> CREATE OR REPLACE
  2  TYPE person_table_tp AS TABLE OF person_tp;
  3  /

Type created.

Below are two example functions to generate some test data. The first is called a pipelined function. Pipelined functions efficiently assemble data into the specified return collection type without the need to maintain a collection object. The second function directly populates a collection and then returns it:

SQL> CREATE OR REPLACE
  2  FUNCTION create_person_list_pipe
  3  RETURN person_table_tp
  4  PIPELINED
  5  IS
  6    person PERSON_TP;
  7  BEGIN
  8    person := PERSON_TP( 'Robert', 'Smith', 43 );
  9    PIPE ROW( person );
 10    person := PERSON_TP( 'Jane', 'Jennings', 23 );
 11    PIPE ROW( person );
 12    person := PERSON_TP( 'Karen', 'Patreas', 51 );
 13    PIPE ROW( person );
 14  END;
 15  /

Function created.

SQL>
SQL> CREATE OR REPLACE
  2  FUNCTION create_person_list_array
  3  RETURN person_table_tp
  4  IS
  5    person PERSON_TP;
  6    person_tbl PERSON_TABLE_TP;
  7  BEGIN
  8    person_tbl := PERSON_TABLE_TP();
  9
 10    person := PERSON_TP( 'Robert', 'Smith', 43 );
 11    person_tbl.EXTEND();
 12    person_tbl( person_tbl.LAST ) := person;
 13
 14    person := PERSON_TP( 'Jane', 'Jennings', 23 );
 15    person_tbl.EXTEND();
 16    person_tbl( person_tbl.LAST ) := person;
 17
 18    person := PERSON_TP( 'Karen', 'Patreas', 51 );
 19    person_tbl.EXTEND();
 20    person_tbl( person_tbl.LAST ) := person;
 21
 22    RETURN person_tbl;
 23  END;
 24  /

Function created.

Both these functions return the same results:

SQL> SELECT create_person_list_pipe
  2  FROM dual;

CREATE_PERSON_LIST_PIPE(FIRST_NAME, LAST_NAME, AGE)
--------------------------------------------------------------------------------
PERSON_TABLE_TP(PERSON_TP('Robert', 'Smith', 43), PERSON_TP('Jane', 'Jennings',
23), PERSON_TP('Karen', 'Patreas', 51))

SQL> SELECT create_person_list_array
  2  FROM dual;

CREATE_PERSON_LIST_ARRAY(FIRST_NAME, LAST_NAME, AGE)
--------------------------------------------------------------------------------
PERSON_TABLE_TP(PERSON_TP('Robert', 'Smith', 43), PERSON_TP('Jane', 'Jennings',
23), PERSON_TP('Karen', 'Patreas', 51))

Finally, below is an anonymous block that makes use of the TABLE() and CAST() functions to perform various SQL manipulations on the collection:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    pipe_test_name VARCHAR2( 100 );
  3    array_test_name VARCHAR2( 100 );
  4    pipe_cnt NUMBER;
  5    array_cnt NUMBER;
  6  BEGIN
  7
  8    SELECT COUNT(*)
  9    INTO pipe_cnt
 10    FROM TABLE( CAST( create_person_list_pipe AS PERSON_TABLE_TP ) );
 11
 12    SELECT COUNT(*)
 13    INTO array_cnt
 14    FROM TABLE( CAST( create_person_list_array AS PERSON_TABLE_TP ) );
 15
 16    DBMS_OUTPUT.PUT_LINE( 'Pipe Count: ' || pipe_cnt );
 17    DBMS_OUTPUT.PUT_LINE( 'Array Count: ' || array_cnt );
 18
 19    SELECT first_name
 20    INTO pipe_test_name
 21    FROM TABLE( CAST( create_person_list_pipe AS PERSON_TABLE_TP ) )
 22    WHERE last_name = 'Jennings';
 23
 24    DBMS_OUTPUT.PUT_LINE( 'Pipe name test: ' || pipe_test_name );
 25
 26    SELECT first_name
 27    INTO array_test_name
 28    FROM TABLE( CAST( create_person_list_array AS PERSON_TABLE_TP ) )
 29    WHERE last_name = 'Patreas';
 30
 31    DBMS_OUTPUT.PUT_LINE( 'Array name test: ' || array_test_name );
 32
 33  END;
 34  /
Pipe Count: 3
Array Count: 3
Pipe name test: Jane
Array name test: Karen

PL/SQL procedure successfully completed.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s