Introduction to Oracle Pipelined Table Functions

// Overview
// Basically, when you want a PLSQL (or java or c) program as a data source rather than a table,
// You may use the pipeline function ).
// The pipelined function operates like a table.
// A pl/SQL function may be used in the database of the data warehouse to convert a large amount of data.
// This may include formatting a series of conversion data, which is the result of different function execution.
// Before Oracle database 9i, a large amount of data conversion requires significant memory overhead,
// Data must be stored in the intermediate table at each stage of the conversion. In both cases,
// Loading processes will cause performance degradation.

// PL/SQL table functions can effectively reduce overhead during data conversion. PL/SQL table functions
// Multiple rows can be received and returned to deliver the data. When they are ready, they are not processed at a time;
// PL/SQL table functions can also execute operations in parallel.
--
// Instance 1: generate a random number
// How do you use an SQL statement to generate random numbers between 1 and 49?
// We may query from a set of generated numbers (note the innermost query below );
// Any table with records greater than or equal to 49 rows can do this. If you do not use pipeline functions, the following is the best solution:
Select r
From (select r
From (select rownum r
From all_objects
Where rownum <50)
Order by dbms_random.value)
Where rownum <= 6;
/
R
----------
36
40
30
26
3
42
// We call the most internal query an embedded view, which generates numbers 1 .. 49. We use DBMS_RANDOM.VALUE,
// Sort the 49 numbers. We package the result set in another embedded view and only return 6 rows,
// If we execute this query repeatedly, the number of each six queries will be different.
--
// This problem often occurs. Maybe it's not about how to generate 6 random numbers, but how to get N rows?
// For example, we want to get all the dates between and; how can we not use a "real" table,
// Solve this problem? The built-in PIPELINED function of Oracle9i/10g will tell you the answer.
// We compile a PL/SQL function, which works like a table. We need to use the SQL collection type,
// It describes the value returned by the PIPELINED function. In this example, we select a number table, the virtual table we created,
// It will return the following numbers: 1, 2, 3,... N:
Create type array
As table of number;
/
// Next, we create this PIPELINED function, which will receive an input to limit the number of returned rows. If no,
// This function will generate many rows for a long time (therefore, be careful in this query to ensure that rownum is used,
// Or other numbers to limit the number of rows ).
// The PIPELINED keyword in row 4th will make this function work like 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;
/
// Assume we need three rows of data. Now we can use one of the following queries:
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 going to answer the first question. The statement is as follows:
Select *
From (
Select *
From (select * from table (gen_numbers (49 )))
Order by dbms_random.random
)
Where rownum <= 6
/
COLUMN_VALUE
------------
27
24
46
17
44
45
// We can use this virtual table function to do many things, such as generating all dates within a range:
Select to_date ('1970-05-01 ', 'yyyy-mm-dd') +
Column_value-1
From TABLE (gen_numbers (15 ))
/
TO_DATE ('2'
-----------
2011-05-01
2011-05-
2011-05-
2011-05-04
2011-05-05
2011-05-06
2011-05-07
2011-05-
2011-05-09
2011-05-10
2011-05-11
2011-05-12
2011-05-
2011-05-14
2011-05-15
// Note that column_value is used above. This is the name of the default column returned by a PIPELINED function.
// Typical Pipelined example
// When PL/SQL table functions are used, the following are typical steps:
// · The production function must use the PIPELINED keyword in its definition.
// · The production function must use an out parameter. this parameter is a set and corresponds to the returned result set.
// Once a result is generated, it is delivered to the consumption function by using the pipe row keyword.
// · The production function must end with a RETURN statement, but the RETURN value does not need to be specified.
// The consumption function must use the TABLE keyword to treat the row set returned from the PIPELINE function as a regular TABLE.
// Step 1. Define the format of a returned row set.
// In this instance, we return the values of these three types: int, date, varchar2 (25)
Create or replace type myObjectFormat
AS OBJECT
(
A int,
B DATE,
C VARCHAR2 (25)
)
/
// Next, define a set type for the type defined in step 1
Create or replace type myTableType
As table of myObjectFormat
/
// Finally, the production function is packaged into a package. It is a pipelined function, just as it is marked by the pipelined keyword.
Create or replace package myDemoPack
AS
FUNCTION prodFunc RETURN myTableType PIPELINED;
END;
/
Create or replace package body myDemoPack
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 Result:
Alter session set nls_date_format = 'yyyy-mm-dd ';
SELECT * from table (myDemoPack. prodFunc ());
A B C
----------------------------------------
1 2011-05-05 Row 1
2 2011-05-06 Row 2
3 2011-05-07 Row 3
4 2011-05-08 Row 4
5 Row 5
// Conclusion:
// In a select statement, we need a data source instead of a table. Pipelined functions is very useful.