Introduction to Oracle Pipelined Table Function

I. Overview: 1. The pipeline function is a function that can return a row set (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 want
Real-time output of some information in the function execution process. 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 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 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
The third pipeline function is used 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


Four usage extensions: 1. Data passing between TABLE functions: SELECT * from table (f (CURSOR (SELECT * from table (g ()))));

2. Use the cursor variable to receive the results returned by the pipeline function: OPEN c for select * from table (f (...));

3. Use multiple cursor variable input parameters:
Example 3:

-- Define the ref cursor typesCREATE PACKAGE refcur_pkg IS  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(30),    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 PIPELINED 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 * FROM employees WHERE department_id = 60),  CURSOR(SELECT * FROM departments WHERE department_id = 60)));

4. Use the MPs queue function as an aggregate function:
Example 4:

CREATE TABLE gradereport (student VARCHAR2(30), subject VARCHAR2(30),                          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 IS  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, 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 resultEND;END pkg_gpa;/-- the query result comes back as a nested table with a single row-- COLUMN_VALUE is a keyword that returns the contents of a nested tableSELECT w.column_value "weighted result" FROM TABLE(       pkg_gpa.weighted_average(CURSOR(SELECT weight, grade FROM gradereport))) w;

5. Perform DML operations in pipeline functions. We use autonomous transactions to process pipeline functions as independent transactions:
CREATE FUNCTION f(p SYS_REFCURSOR)
  RETURN CollType PIPELINED IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN NULL; END;
/
6. Perform DML operations on MPS queue functions:
In fact, we cannot directly perform DML operations on pipeline functions. For example, the following statements fail:
UPDATE F(CURSOR(SELECT * FROM tab)) SET col = value;
  INSERT INTO f(...) VALUES ('any', 'thing');

The alternative solution officially provided is to create a VIEW based on the pipeline function, and then create the corresponding instead of trigger on this VIEW. The following is an example:



























--------------------------------------- By Dylan.