PL/SQL : table function

	Oracle Pipelined Table Functions
Overview Basically, when you would like a PLSQL (or java or c) routine to be the «source» of data -- instead of a table -- you would use a pipelined function. PIPELINED functions will operate like a table. A PL/SQL function may be used in a data warehouse database to transform large amounts of data. This might also involve massaging the data in a series of transformations, each performed by different functions. Prior to Oracle Database 9, large transformations required either significant memory overhead, or storing the data in intermediate tables between each stage of the transformation. The loading process caused immense performance degradations in both cases.
Using PL/SQL table functions can significantly lower the over-head of doing such transformations. PL/SQL table functions accept and return multiple rows, delivering them as they are ready rather than all at once, and can be made to execute as parallel operations. Simple Example - Generating Some Random Data How could you create six unique random numbers between 1 and 49 with one SQL statement? We would generate the set of numbers to pick from (see the innermost query that follows); any table with 49 or more records would do it. First the quick-and-dirty solution without a pipelined function. select r   from (select r            from (select rownum r                    from all_objects                   where rownum < 50)           order by dbms_random.value)   where rownum <= 6;          R ----------         10          2         19         34         12         21 That query works by generating the numbers 1 .. 49, using the inline view. We wrap that innermost query as an inline view and sort it by a random value, using DBMS_RANDOM.VALUE. We wrap that result set in yet another inline view and just take the first six rows. If we run that query over and over, we'll get a different set of six rows each time. This sort of question comes up frequently—maybe not about how to generate a set of six random numbers but rather, "how can we get N rows?" For example, we'd like the inclusive set of all dates between 25-FEB-2004 and 10-MAR-2004. The question becomes how to do this without a "real" table, and the answer lies in Oracle9i/10g with its PIPELINED function capability. We can write a PL/SQL function that will operate like a table. We need to start with a SQL collection type; this describes what the PIPELINED function will return. In this case, we are choosing a table of numbers; the virtual table we are creating will simply return the numbers 1, 2, 3, ... N: create type array     as table of number / Type created. Next, we create the actual PIPELINED function. This function will accept an input to limit the number of rows returned. If no input is provided, this function will just keep generating rows for a very long time (so be careful and make sure to use ROWNUM or some other limit in the query itself!). The PIPELINED keyword on line 4 allows this function to work as if it were a table: create function   gen_numbers(n in number default null)   return array   PIPELINED   as   begin      for i in 1 .. nvl(n,999999999)      loop          pipe row(i);      end loop;      return;   end; / Function created. Suppose we needed three rows for something. We can now do that in one of two ways: select * from TABLE(gen_numbers(3));  COLUMN_VALUE  ------------            1            2            3 or select * from TABLE(gen_numbers)  where rownum <= 3;  COLUMN_VALUE  ------------            1            2            3 Now we are ready to re-answer the original question, using the following functionality: select *   from (   select *     from (select * from table(gen_numbers(49)))   order by dbms_random.random   ) where rownum <= 6 /  COLUMN_VALUE  ------------           47           42           40           15           48           23 We can use this virtual table functionality for many things, such as generating that range of dates: select to_date('25-feb-2004')+         column_value-1   from TABLE(gen_numbers(15)) / TO_DATE(' --------- 25-FEB-04 26-FEB-04 27-FEB-04 28-FEB-04 29-FEB-04 01-MAR-04 02-MAR-04 03-MAR-04 04-MAR-04 05-MAR-04 06-MAR-04 07-MAR-04 08-MAR-04 09-MAR-04 10-MAR-04 Note the name of the column we used: COLUMN_VALUE. That is the default name for the column coming back from the PIPELINED function. Typical Pipelined Example This are the typical steps to perform when using PL/SQL Table Functions: The producer function must use the PIPELINED keyword in its declaration.   The producer function must use an OUT parameter that is a record, corresponding to a row in the result set.   Once each output record is completed, it is sent to the consumer function through the use of the PIPE ROW keyword.   The producer function must end with a RETURN statement that does not specify any return value.   The consumer function or SQL statement then must use the TABLE keyword to treat the resulting rows from the PIPELINE function like a regular table. The first step is to define the format of the rows that are going to be returned. In this case here, we're going to return a INT, DATE followed by a VARCHAR2(25). CREATE OR REPLACE TYPE myObjectFormat AS OBJECT ( A INT, B DATE, C VARCHAR2(25) ) / Next a collection type for the type previously defined must be created. CREATE OR REPLACE TYPE myTableType    AS TABLE OF myObjectFormat / Finally, the producer function is packaged in a package. It is a pipelined function as indicated by the keyword pipelined. CREATE OR REPLACE PACKAGE myDemoPack AS       FUNCTION prodFunc RETURN myTableType PIPELINED; END; / CREATE OR REPLACE PACKAGE BODY myDemoPack AS FUNCTION prodFunc RETURN myTableType PIPELINED IS BEGIN   FOR i in 1 .. 5     LOOP       PIPE ROW (myObjectFormat(i,SYSDATE+i,'Row '||i));     END LOOP;     RETURN;   END; END; / Test It: ALTER SESSION SET NLS_DATE_FORMAT='dd.mm.yyyy'; SELECT * FROM TABLE(myDemoPack.prodFunc());          A B          C ---------- ---------- ---------          1 31.05.2004 Row 1          2 01.06.2004 Row 2          3 02.06.2004 Row 3          4 03.06.2004 Row 4          5 04.06.2004 Row 5 Conclusion Pipelined functions are useful if there is a need for a data source other than a table in a select statement. ======================================================================== Returning a 'table' from a PL/SQL function With collections, it is possible to return a table from a pl/sql function. First, we need to create a new object type that contains the fields that are going to be returned: create or replace type t_col as object ( i number, n varchar2(30) ); / Then, out of this new type, a nested table type must be created. create or replace type t_nested_table as table of t_col; / Now, we're ready to actually create the function: create or replace function return_table return t_nested_table as v_ret t_nested_table; begin v_ret := t_nested_table(); v_ret.extend; v_ret(v_ret.count) := t_col(1, 'one'); v_ret.extend; v_ret(v_ret.count) := t_col(2, 'two'); v_ret.extend; v_ret(v_ret.count) := t_col(3, 'three'); return v_ret; end return_table; / Here's how the function is used: select * from table(return_table); 1 one 2 two 3 three Returning a dynamic set Now, the function is extended so as to return a dynamic set. The function will return the object_name and the object_id from user_objects whose object_id is in the range that is passed to the function. create or replace function return_objects( p_min_id in number, p_max_id in number ) return t_nested_table as v_ret t_nested_table; begin select cast( multiset( select object_id, object_name from user_objects where object_id between p_min_id and p_max_id) as t_nested_table) into v_ret from dual; return v_ret; end return_objects; / And here's how the function is called. select * from table(return_objects(37900,38000)); Using this technique poor man's text index is an example that uses this technique to search in the middle of words in a table. (ie where filed like '%word%') Thanks Thanks to Vikram Singh Rathore for a suggestion for this page.