Description of the Oracle pipeline function (pipelined Table function)

An overview:1. A pipe function is a function that returns a rowset (capable of making a nested table nested table or array varray), and we can query it like a physical table or
assigns a value to the collection variable.
2, the pipeline function for the parallel operation, in the normal function uses the Dbms_output output information, needs to be returned to the client once the server has finished running the whole function. Suppose you need to be in the client
The real-time output function runs some information in the process and can use the pipe function (pipeline functions) after oracle9i.

3, keywordpipelined indicates that this is an Oracle pipeline function, the return value type of the Oracle pipe function must be a collection, in a function, the pipe row statement is used to return a single element of the collection

The function ends with an empty return statement to indicate that it is complete.

4, because the pipeline function of concurrent multi-pipe flow design and real-time return query results and remove the intermediate link thus can bring considerable performance improvement.

second, how to write the pipeline function:Example 1:

CREATE OR REPLACE Package pkg1 as    TYPE numset_t is TABLE number;    FUNCTION F1 (x number) RETURN numset_t pipelined; END Pkg1;

CREATE OR REPLACE Package BODY pkg1 as    FUNCTION F1 (x number) RETURN numset_t pipelined was        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
The three-pipe function is used for data conversion:
Example 2: The pipe function can receive the same as a regular function, regardless of the number of parameters, the following pipe function is the REF CURSOR.

CREATE OR REPLACE Package refcur_pkg are  TYPE refcur_t is REF CURSOR RETURN emp%rowtype;  TYPE Outrec_typ is RECORD (     var_num number    (6),    var_char1  VARCHAR2 (),    var_char2  VARCHAR2 ( ());  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 are  FUNCTION F_trans (P refcur_t)    RETURN outrecset pipelined is    out_r EC 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 (The CURSOR (SELECT * from emp WHERE empno=7782));

Var_num var_char1 Var_char2
---------- ------------------------------ ------------------------------
7782 CLARK 7839
Ten MANAGER


Four use method extensions:1. Pass data between table functions:SELECT * FROM table (f (CURSOR (SELECT * from table (g ())));

2. Use the cursor variable to receive the result returned by the pipeline function: OPEN C for SELECT * from TABLE (f (...));

3. Use multiple cursor variables to participate in:
Example 3:

--Define the REF CURSOR Typescreate package refcur_pkg are TYPE REFCUR_T1 is ref CURSOR RETURN employees%rowtype;    TYPE Refcur_t2 is REF CURSOR RETURN departments%rowtype;  TYPE Outrec_typ is RECORD (var_num number (6), var_char1 VARCHAR2 (+), Var_char2 VARCHAR2 (30));  TYPE Outrecset is TABLE of Outrec_typ; FUNCTION G_trans (P1 refcur_t1, p2 refcur_t2) RETURN outrecset pipelined; END refcur_pkg;/create Package BODY refcur_pkg isfunction G_trans (P1 refcur_t1, p2 refcur_t2) RETURN outrecset Pipelin    ED is Out_rec Outrec_typ;    IN_REC1 P1%rowtype; IN_REC2 P2%rowtype;    BEGIN LOOP FETCH p2 into in_rec2;  EXIT when P2%notfound;  END LOOP;  CLOSE P2;    LOOP FETCH P1 into in_rec1;    EXIT when P1%notfound;    -First row Out_rec.var_num: = in_rec1.employee_id;    OUT_REC.VAR_CHAR1: = In_rec1.first_name;    OUT_REC.VAR_CHAR2: = In_rec1.last_name;    PIPE ROW (OUT_REC);    --second row out_rec.var_num: = in_rec2.department_id; OUT_REC.VAR_CHAR1: = In_rec2.department_name;    OUT_REC.VAR_CHAR2: = To_char (in_rec2.location_id);  PIPE ROW (OUT_REC);  END LOOP;  CLOSE P1; RETURN; END; END refcur_pkg;/--SELECT Query using the G_trans table Functionselect * FROM table (Refcur_pkg.g_trans (CURSOR (SELECT * F ROM employees where department_id =), CURSOR (SELECT * from departments where department_id = 60));

4. The pipeline function is used as an aggregate function:
Example 4:

CREATE TABLE Gradereport (Student VARCHAR2 (+), subject VARCHAR2 (+), weight number, grade number  INSERT into gradereport values (' Mark ', ' Physics ', 4, 4), insert into gradereport values (' Mark ', ' chemistry ', 4, 3); insert into gradereport values (' Mark ', ' Maths ', 3, 3); INSERT into gradereport values (' Mark ', ' Economics ', 3, 4);  CREATE package Pkg_gpa are TYPE GPA is TABLE of number; FUNCTION weighted_average (input_values sys_refcursor) RETURN GPA pipelined;  END pkg_gpa;/create Package BODY Pkg_gpa isfunction weighted_average (input_values sys_refcursor) RETURN GPA pipelined is  Grade number;  Total number: = 0;  Total_weight number: = 0; Weight number: = 0; begin--the function accepts a REF CURSOR and loops through all the input rows LOOP FETCH input_values into weight, g     Rade     EXIT when input_values%notfound;--accumulate the weighted average total_weight: = total_weight + weight;  Total: = all + Grade*weight;  END LOOP; PIPE ROW (Total/total_weight); RETURN; --The function returns a single resultend; END pkg_gpa;/--The query result comes back as a nested table with a single row--column_value was a keyword that returns t He contents of a nested tableselect w.column_value "weighted result" from TABLE (Pkg_gpa.weighted_average (CURSOR (SEL ECT weight, grade from Gradereport)) W;

5. To perform DML operations in a pipeline function, we use an autonomous transaction to make the pipeline function stand as a standalone transaction:
CREATE FUNCTION f(p SYS_REFCURSOR)
  RETURN CollType PIPELINED IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN  NULL; END;
/
6、对管道函数进行DML操作：
实际上我们无法直接对管道函数进行DML操作，比例如以下面语句都会失败：
UPDATE F(CURSOR(SELECT * FROM tab)) SET col = value;
  INSERT INTO f(...) VALUES (‘any‘, ‘thing‘);

The official solution is to create a view based on the pipe function, and then create the corresponding instead of trigger on this view. The following is an example of the operation:


CREATE OR REPLACE VIEW v_f_trans asselect x.var_num, X.var_char1, X.var_char2from TABLE (Refcur_pkg.f_trans (CURSOR ( SELECT * from EMP))                                x;

CREATE OR REPLACE TRIGGER tri_f_transinstead of INSERT on V_f_transfor each rowbegin  dbms_output.put_line (' TRIGGER of A pipelined funtion based view is on fire! '); END;

[Email protected]> INSERT INTO V_f_trans values (102, ' abc ', ' Def ');
Trigger of a pipelined funtion based view is on fire!


























---------------------------------------by Dylan.