Introduction to Oracle Pipe functions (pipelined Table function)

an overview:1. A pipe function is a function that can return a rowset (a table or array varray can be nested), and we can query it like a physical table or
Assignment to a collection variable.
2, pipeline function for parallel execution, in the ordinary function of the use of Dbms_output output information, need to perform the entire function after the server to return to the client. If required on the client
Some information in the execution of a real-time output function, which can be used after oracle9i (pipeline function).

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

element, the function ends with an empty return statement to indicate that it has been completed.

4, due to the pipeline function of the concurrent multi-channel flow design and real-time return of the query results to be removed in addition to the intermediate link so that can bring considerable performance.

Ii. How to write a piping 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 are
        BEGIN for
            i 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
three pipe functions for data conversion:
Example 2: A pipe function can receive any parameter like a regular function, and the following pipe function has the parameter REF CURSOR.

CREATE OR REPLACE PACKAGE refcur_pkg is
  TYPE refcur_t be 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
    ou T_rec Outrec_typ;
    In_rec  P%rowtype;
  BEGIN
  LOOP
    FETCH p into In_rec;
    EXIT when P%notfound;
    --The
    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
Ten MANAGER


Four usage extensions:1. Transfer data between table functions: SELECT * FROM table (f (CURSOR (SELECT * from table (g)));

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

3. Use multiple cursor variables to enter the parameter:
Example 3:

--Define The REF CURSOR types CREATE PACKAGE refcur_pkg is TYPE REFCUR_T1 was 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 are FUNCTION G_trans (P1 refcur_t1, p2 refcur_t2) return outrecset pipelined is O
    Ut_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;
    --the 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 function select * FROM table (Refcur_pkg.g_trans (CURSOR) (SELECT * FROM Employe Es WHERE department_id = CURSOR (SELECT * FROM departments WHERE department_id = 60));

4. Piping functions are used as aggregate functions:
Example 4:

CREATE TABLE gradereport (student VARCHAR2), subject VARCHAR2 (a), weight number, grade Numbe
R);
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 is TYPE the GPA is TABLE of number;
FUNCTION weighted_average (input_values sys_refcursor) return GPA pipelined;
End Pkg_gpa; /CREATE PACKAGE body Pkg_gpa are FUNCTION weighted_average (input_values sys_refcursor) return GPA pipelined is grade N
  umber;
  Total number: = 0;
  Total_weight number: = 0;
Weight number: = 0; BEGIN--The function accepts a REF CURSOR and loops through all of the input rows LOOP FETCH input_values into weight
     , Grade;
EXIT when Input_values%notfound;
     --accumulate the weighted average total_weight: = total_weight + weight;
  Total: = Total + grade*weight; End LOOP;
  PIPE ROW (total/total_weight); return;
--The function returns a single result end;
End Pkg_gpa; /--The query result comes back as a nested table with a single row--Column_value are a keyword that returns the content S ' A nested table select W.column_value ' weighted result ' from table (Pkg_gpa.weighted_average (CURSOR (select Weig HT, grade from Gradereport)) W;

5, in the pipeline function DML operation, we use autonomous transactions to make the pipe function as a separate transaction processing:
CREATE FUNCTION F (P sys_refcursor)
Return Colltype pipelined is
PRAGMA autonomous_transaction;
BEGIN NULL; End;
/
6. DML operation on piping function:
In fact, we cannot perform DML operations directly on a pipe function, for example, the following statements will fail:
UPDATE F (CURSOR (SELECT * from tab)) SET col = value;
INSERT into F (...) VALUES (' Any ', ' thing ');

The official scenario is to create a view based on the pipe function, and then create the corresponding instead OF triggers on the view. Examples of actions are given below:

CREATE OR REPLACE VIEW V_f_trans as
SELECT x.var_num, X.var_char1, x.var_char2 from
TABLE (Refcur_pkg.f_trans ( CURSOR (SELECT * from EMP)))
                                x;

CREATE OR REPLACE TRIGGER tri_f_trans
INSTEAD of INSERT on V_f_trans for each
ROW
BEGIN
  dbms_output.put_ Line (' Trigger of a pipelined funtion based view is on fire! ');
End;

scott@orcl> INSERT INTO V_f_trans values (102, ' abc ', ' Def ');
Trigger of a pipelined funtion based view is on fire!


























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