Introduction to the Oracle Pipeline Function (PipelinedTableFunction)

I. Overview:

1. The pipeline function is a function that can return a set of rows (nested table or array varray can be used). We can query it like a physical table or

Assign a value to the set variable.

2. Pipeline functions are executed in parallel. If dbms_output output information is used in common functions, it must be returned to the client at one time after the server executes the complete functions. If you need to output some information about the function Execution Process in real time on the client, you can use the pipeline function (pipeline function) after oracle9i ).

3. the keyword PIPELINED indicates that this is an oracle pipeline function. the return value type of the oracle pipeline function must be set. In the function, the pipe row statement is used to return a single element of the set.

The function ends with an empty RETURN statement to indicate that it has been completed.

4. Due to the concurrent multi-pipeline stream Design of pipeline functions and the real-time return of query results, removing intermediate links can significantly improve performance.

Ii. How to compile pipeline functions:
Example 1:
Create or replace package pkg1
TYPE numset_t is table number;
FUNCTION f1 (x NUMBER) RETURN numset_t PIPELINED;
END pkg1;

Create or replace package body pkg1
FUNCTION f1 (x NUMBER) RETURN numset_t PIPELINED IS
BEGIN
FOR I IN 1. x LOOP
Pipe row (I );
End loop;
RETURN;
END;
END pkg1; SELECT * from table (pkg1.f1 (5); COLUMN_VALUE
------------------------
1
2
3
4
5

Third-party Pipeline Function for Data Conversion

Example 2: The pipeline function can receive any parameter like the conventional function. The parameter in the following Pipeline Function is ref cursor.

Create or replace package refcur_pkg IS
TYPE refcur_t is ref cursor return emp % ROWTYPE;
TYPE outrec_typ is record (
Var_num NUMBER (6 ),
Var_char1 VARCHAR2 (30 ),
Var_char2 VARCHAR2 (30 ));
TYPE outrecset is table of outrec_typ;
FUNCTION f_trans (p refcur_t)
RETURN outrecset PIPELINED;
END refcur_pkg;

Create or replace package body refcur_pkg IS
FUNCTION f_trans (p refcur_t)
RETURN outrecset PIPELINED IS
Out_rec outrec_typ;
In_rec p % ROWTYPE;
BEGIN
LOOP
FETCH p INTO in_rec;
Exit when p % NOTFOUND;
-- First row
Out_rec.var_num: = in_rec.empno;
Out_rec.var_char1: = in_rec.ename;
Out_rec.var_char2: = in_rec.mgr;
Pipe row (out_rec );
-- Second row
Out_rec.var_num: = in_rec.deptno;
Out_rec.var_char1: = in_rec.deptno;
Out_rec.var_char2: = in_rec.job;
Pipe row (out_rec );
End loop;
CLOSE p;
RETURN;
END;
END refcur_pkg;

SELECT * from table (
Refcur_pkg.f_trans (CURSOR (SELECT * FROM emp WHERE empno = 7782 )));

VAR_NUM VAR_CHAR1 VAR_CHAR2
----------------------------------------------------------------------
7782 CLARK 7839
10 10 MANAGER