Oracle Pipelined Table Functions 与 性能优化 说明

时间:2022-10-19 21:57:04


 

 

一. Pipelined table functions

       官网说明如下:

       ​​ http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/tuning.htm#BCGICBDF​

 

       Chainingpipelined table functions is an efficient way to perform multipletransformations on data.

Note:

       You cannot run a pipelined table function over a databaselink. The reason is that the return type of a pipelined table functionis a SQL user-defined type, which can be used only in a single database(as explained in ​​ OracleDatabase Object-Relational Developer's Guide​​). Although the return type ofa pipelined table function might appear to be a PL/SQL type, the databaseactually converts that PL/SQL type to a corresponding SQL user-defined type.

 

1.1 Overview of Table Functions

       A table function is a user-definedPL/SQL function that returns a collection of rows (a nestedtable or varray). You can select from thiscollection as if it were a database table by invoking the table function insidethe TABLE clause in a SELECT statement. For example:


        SELECT * FROM TABLE(table_function_name(parameter_list))


 

       A table function can take a collection of rows as input(that is, it can have an input parameter that is a nested table, varray, orcursor variable). Therefore, output from table function tf1 can be input totable function tf2, and output from tf2 can be input to table function tf3, andso on.

 

To improve theperformance of a table function, you can:

       (1)Enable the function for parallel execution, with the PARALLEL_ENABLE option. Functions enabled for parallelexecution can run concurrently.

       (2)Stream the function results directly to the next process, withOracle Streams.

       Streamingeliminates intermediate staging between processes. For information about OracleStreams, see ​​ OracleStreams Concepts and Administration​​.

       (3)Pipeline the function results, with the PIPELINEDoption.

 

       A pipelined table function returns a row to its invoker immediatelyafter processing that row and continues to process rows. Response time improves because the entire collection need not beconstructed and returned to the server before the query can return a singleresult row. (Also, the function needs lessmemory, because the object cache need not materialize the entirecollection.)

       -- pipelined table function 立即返回row 记录,而不同等待这个collection,从而减少constructed的时间,并且占用较少的内存。

 

Caution:

       A pipelined table function always references the currentstate of the data. If the data in the collection changes after thecursor opens for the collection, then the cursor reflects the changes. PL/SQL variables are private to a session and are nottransactional. Therefore, read consistency, well known for itsapplicability to table data, does not apply to PL/SQL collectionvariables.

 

1.2 CreatingPipelined Table Functions

       Apipelined table function must be either a standalone stored function or apackage function.

 

1.2.1 PIPELINED Option (Required)

       Fora standalone stored function, specify the PIPELINEDoption in the CREATE FUNCTION statement (for syntax, see ​​ "CREATEFUNCTION Statement"​​​). For a package function, specify the PIPELINEDoption in both the function declaration and function definition (for syntax,see ​​ "FunctionDeclaration and Definition"​​).

 

1.2.2 PARALLEL_ENABLE Option(Recommended)

       Toimprove its performance, enable the pipelined table function for parallel executionby specifying the PARALLEL_ENABLE option.

 

1.2.3 AUTONOMOUS_TRANSACTION Pragma

       Ifthe pipelined table function runs DML statements, thenmake it autonomous, with the AUTONOMOUS_TRANSACTIONpragma (described in ​​ "AUTONOMOUS_TRANSACTIONPragma"​​). Then, during parallel execution, each instance of thefunction creates an independent transaction.

 

1.2.4 DETERMINISTIC Option(Recommended)

       Multipleinvocations of a pipelined table function, in either the same query or separatequeries, cause multiple executions of the underlying implementation. If thefunction is deterministic, specify the DETERMINISTICoption, described in ​​ "DETERMINISTIC"​​.

 

1.2.5 Parameters

       Typically,a pipelined table function has one or more cursor variable parameters. Forinformation about cursor variables as function parameters, see ​​ "CursorVariables as Subprogram Parameters"​​.

See Also:

·        ​​"CursorVariables"​​ for general information about cursor variables

·        ​​"SubprogramParameters"​​ for general information about subprogram parameters

1.2.6 RETURN Data Type

       Thedata type of the value that a pipelined table function returns must be acollection type defined either at schema level or inside a package (therefore,it cannot be an associative array type). The elements ofthe collection type must be SQL data types, not data types supported only byPL/SQL (such as PLS_INTEGER and BOOLEAN). For information about collectiontypes, see ​​ "CollectionTypes"​​​. For information about SQL data types, see ​​ OracleDatabase SQL Language Reference​​.

       Youcan use SQL data types ANYTYPE, ANYDATA,and ANYDATASET to dynamically encapsulate and accesstype descriptions, data instances, and sets of data instances of any other SQLtype, including object and collection types. You can also use these types tocreate unnamed types, including anonymous collection types. For informationabout these types, see ​​ OracleDatabase PL/SQL Packages and Types Reference​​.

 

1.2.7 PIPE ROW Statement

       Insidea pipelined table function, use the PIPE ROW statementto return a collection element to the invoker without returning control to theinvoker. See ​​ "PIPEROW Statement"​​ for its syntax and semantics.

 

1.2.8 RETURN Statement

       Asin every function, every execution path in a pipelined table function must leadto a RETURN statement, which returns control to the invoker. However, in apipelined table function, a RETURN statement need not return a value to theinvoker. See ​​ "RETURNStatement"​​ for its syntax and semantics.

 

Example

       ​​ Example12-29​​ creates a package that includes a pipelined table function, f1, andthen selects from the collection of rows that f1 returns.

 

Example 12-29 Creating and Invoking Pipelined Table Function


CREATE OR REPLACE PACKAGE pkg1 AS


  TYPE numset_t IS TABLE OF NUMBER;


  FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;


END pkg1;


/


 


CREATE PACKAGE BODY pkg1 AS


  -- FUNCTION f1 returns a collection of elements (1,2,3,... x)


  FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS


  BEGIN


    FOR i IN 1..x LOOP


      PIPE ROW(i);


    END LOOP;


    RETURN;


  END f1;


END pkg1;


/


 


SELECT * FROM TABLE(pkg1.f1(5));


 


Result:


COLUMN_VALUE


------------


           1


           2


           3


           4


           5


5 rows selected.


 


1.3 PipelinedTable Functions as Transformation Functions

       Apipelined table function with a cursor variable parameter can serve as atransformation function. Using the cursor variable, thefunction fetches an input row. Using the PIPE ROWstatement, the function pipes the transformed row or rows to theinvoker. If the FETCH and PIPE ROW statements areinside a LOOP statement, the function can transform multiple input rows.

 

       In​​Example12-30​​​, the pipelined table function transforms each selected row of the employeestable to two nested table rows, which it pipes to the SELECT statement thatinvokes it. The actual parameter that corresponds tothe formal cursor variable parameter is a CURSOR expression; forinformation about these, see ​​ "PassingCURSOR Expressions to Pipelined Table Functions"​​.

 

Example 12-30 Pipelined Table Function Transforms Each Row toTwo Rows


CREATE OR REPLACE PACKAGE refcur_pkg IS


  TYPE refcur_t IS REF CURSOR RETURN employees%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;  -- input row


      EXIT WHEN p%NOTFOUND;


 


      out_rec.var_num := in_rec.employee_id;


      out_rec.var_char1 := in_rec.first_name;


      out_rec.var_char2 := in_rec.last_name;


      PIPE ROW(out_rec);  -- first transformed output row


 


      out_rec.var_char1 := in_rec.email;


      out_rec.var_char2 := in_rec.phone_number;


      PIPE ROW(out_rec);  -- second transformed output row


    END LOOP;


    CLOSE p;


    RETURN;


  END f_trans;


END refcur_pkg;


/


 


SELECT * FROM TABLE (


  refcur_pkg.f_trans (


    CURSOR (SELECT * FROM employees WHERE department_id = 60)


  )


);


 


Result:


VAR_NUM VAR_CHAR1                      VAR_CHAR2


---------- ------------------------------


       103 Alexander                      Hunold


       103 AHUNOLD                        590.423.4567


       104 Bruce                          Ernst


       104 BERNST                         590.423.4568


       105 David                          Austin


       105 DAUSTIN                        590.423.4569


       106 Valli                          Pataballa


       106 VPATABAL                       590.423.4560


       107 Diana                          Lorentz


       107 DLORENTZ                       590.423.5567


 


10 rows selected.


 


1.4 ChainingPipelined Table Functions

       Tochain pipelined table functions tf1 and tf2 is to make the output of tf1 theinput of tf2. For example:


        SELECT * FROM TABLE(tf2(CURSOR(SELECT * FROM TABLE(tf1()))));


 

       Therows that tf1 pipes out must be compatible actual parameters for the formalinput parameters of tf2.

       Ifchained pipelined table functions are enabled for parallel execution, then eachfunction runs in a different process (or set of processes).

See Also:

​"PassingCURSOR Expressions to Pipelined Table Functions"​

 

 

1.5 Fetching fromResults of Pipelined Table Functions

       Youcan associate a named cursor witha query that invokes a pipelined table function. Such a cursor has no specialfetch semantics, and such a cursor variable has no special assignmentsemantics.

      

       However,the SQL optimizer does not optimizeacross PL/SQL statements. Therefore, in ​​ Example12-31​​, the first PL/SQL statement is slower thanthe second—despite the overhead of running two SQL statements in thesecond PL/SQL statement, and even if function results are piped between the twoSQL statements in the first PL/SQL statement.

      

       In​​Example12-31​​, assume that f and g are pipelined table functions, and that eachfunction accepts a cursor variable parameter. The first PL/SQL statementassociates cursor variable r with a query that invokes f, and then passes r to g.The second PL/SQL statement passes CURSOR expressions to both f and g.

 

Example12-31 Fetching from Results of Pipelined Table Functions


DECLARE


  r SYS_REFCURSOR;


  ...


  -- First PL/SQL statement (slower):


BEGIN


  OPEN r FOR SELECT * FROM TABLE(f(CURSOR(SELECT * FROM tab)));


  SELECT * BULK COLLECT INTO rec_tab FROM TABLE(g(r));


 


  -- NOTE: When g completes, it closes r.


END;


 


-- Second PL/SQL statement (faster):


 


SELECT * FROM TABLE(g(CURSOR(SELECT * FROM


  TABLE(f(CURSOR(SELECT * FROM tab))))));


/


See Also:

​"CursorVariables as Subprogram Parameters"​

 

 

1.6 Passing CURSORExpressions to Pipelined Table Functions

       As​​Example12-31​​ shows, the actual parameter for the cursor variable parameter of apipelined table function can be either a cursor variable or a CURSOR expression, and the latter ismore efficient.

 

Note:

       Whena SQL SELECT statement passes a CURSOR expression to a function, the referencedcursor opens when the function begins to run and closes when the functioncompletes.

See Also:

​"CURSORExpressions"​​ for general information about CURSOR expressions

 

       ​​ Example12-32​​ creates a package that includes a pipelined table function with twocursor variable parameters and then invokes the function in a SELECT statement,using CURSOR expressions for actual parameters.

 

Example 12-32 Pipelined Table Function with Two CursorVariable Parameters


CREATE OR REPLACE 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 IS


  FUNCTION 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 g_trans;


END refcur_pkg;


/


 


SELECT * FROM TABLE (


  refcur_pkg.g_trans (


    CURSOR (SELECT * FROM employees WHERE department_id = 60),


    CURSOR (SELECT * FROM departments WHERE department_id = 60)


  )


);


Result:


VAR_NUM VAR_CHAR1                      VAR_CHAR2


---------- ------------------------------ ------------------------------


       103 Alexander                      Hunold


        60 IT                             1400


       104 Bruce                          Ernst


        60 IT                             1400


       105 David                          Austin


        60 IT                             1400


      106 Valli                          Pataballa


        60 IT                             1400


       107 Diana                          Lorentz


        60 IT                             1400


10 rows selected.


       ​​ Example12-33​​​ uses a pipelined table function as an aggregate function, which takes a set of input rows andreturns a single result. The SELECT statement selects the function result. (Forinformation about the pseudocolumn COLUMN_VALUE, see ​​ OracleDatabase SQL Language Reference​​.)

 

Example 12-33 Pipelined Table Function as Aggregate Function


DROP TABLE gradereport;


CREATE TABLE gradereport (


  student VARCHAR2(30),


  subject VARCHAR2(30),


  weight NUMBER,


  grade NUMBER


);


 


INSERT INTO gradereport (student, subject, weight, grade)


VALUES ('Mark', 'Physics', 4, 4);


INSERT INTO gradereport (student, subject, weight, grade)


VALUES ('Mark','Chemistry', 4, 3);


INSERT INTO gradereport (student, subject, weight, grade)


VALUES ('Mark','Maths', 3, 3);


INSERT INTO gradereport (student, subject, weight, grade)


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 IS


  FUNCTION weighted_average (input_values SYS_REFCURSOR)


    RETURN gpa PIPELINED


  IS


    grade         NUMBER;


    total         NUMBER := 0;


    total_weight  NUMBER := 0;


    weight        NUMBER := 0;


  BEGIN


    LOOP


      FETCH input_values INTO weight, grade;


      EXIT WHEN input_values%NOTFOUND;


      total_weight := total_weight + weight;  -- Accumulate weighted average


      total := total + grade*weight;


    END LOOP;


    PIPE ROW (total / total_weight);


    RETURN; -- returns single result


  END weighted_average;


END pkg_gpa;


/


 


SELECT w.column_value "weighted result" FROM TABLE (


  pkg_gpa.weighted_average (


    CURSOR (SELECT weight, grade FROM gradereport)


  )


) w;


Result:


weighted result


---------------


            3.5


1 row selected.


 


1.7 DML Statementson Pipelined Table Function Results

       The "table"that a pipelined table function returns cannot be thetarget table of a DELETE, INSERT, or UPDATE statement. However, you cancreate a view of such a table and create INSTEAD OFtriggers on the view. For information about INSTEAD OF triggers,see ​​ "INSTEADOF Triggers"​​.

 

See Also:

​OracleDatabase SQL Language Reference​​ for information about the CREATE VIEWstatement

 

1.8 NO_DATA_NEEDEDException

You must understand the predefinedexception NO_DATA_NEEDED in two cases:

       (1)You include an OTHERS exception handler in ablock that includes a PIPE ROW statement

       (2)Your code that feeds a PIPE ROW statement must be followed by aclean-up procedure。

       Typically,the clean-up procedure releases resources that the code no longer needs.

 

       Whenthe invoker of a pipelined table function needs no more rows from the function,the PIPE ROW statement raises NO_DATA_NEEDED.

       If the pipelined table function does not handle NO_DATA_NEEDED,as in ​​ Example12-34​​, then the function invocation terminates but the invoking statementdoes not terminate.

       If the pipelined table function handles NO_DATA_NEEDED,its exception handler can release the resources that it no longer needs, as in ​​ Example12-35​​.

 

      In ​​ Example 12-34​​, the pipelined table functionpipe_rows does not handle the NO_DATA_NEEDED exception.The SELECT statement that invokes pipe_rows needs only four rows.Therefore, during the fifth invocation of pipe_rows, the PIPE ROW statementraises the exception NO_DATA_NEEDED. The fifth invocation of pipe_rowsterminates, but the SELECT statement does not terminate.

 

Example12-34 Pipelined Table Function Does Not Handle NO_DATA_NEEDED


CREATE TYPE t IS TABLE OF NUMBER


/


CREATE OR REPLACE FUNCTION pipe_rows RETURN t PIPELINED IS


  n NUMBER := 0;


BEGIN


  LOOP


    n := n + 1;


    PIPE ROW (n);


  END LOOP;


END pipe_rows;


/


SELECT COLUMN_VALUE


  FROM TABLE(pipe_rows())


  WHERE ROWNUM < 5


/


Result:


COLUMN_VALUE


------------


           1


           2


           3


           4


 


4 rows selected.


 

       Ifthe exception-handling part of a block that includes a PIPE ROW statementincludes an OTHERS exception handler to handle unexpected exceptions, then itmust also include an exception handler for the expected NO_DATA_NEEDEDexception. Otherwise, the OTHERS exception handler handles the NO_DATA_NEEDEDexception, treating it as an unexpected error. The following exception handlerreraises the NO_DATA_NEEDED exception, instead of treating it as airrecoverable error:

 


EXCEPTION


  WHEN NO_DATA_NEEDED THEN


    RAISE;


  WHEN OTHERS THEN


    -- (Put error-logging code here)


    RAISE_APPLICATION_ERROR(-20000, 'Fatal error.');


END;


 

       In​​Example12-35​​, assume that the package External_Source contains these public items:

(1)Procedure Init, which allocates andinitializes the resources that Next_Row needs

(2)Function Next_Row, which returnssome data from a specific external source and raises the user-defined exceptionDone (which is also a public item in the package) when the external source hasno more data

(3)Procedure Clean_Up, which releasesthe resources that Init allocated

 

       Thepipelined table function get_external_source_data pipes rows from the externalsource by invoking External_Source.Next_Row until either:

       (1)The external source has no more rows.

              Inthis case, the External_Source.Next_Row function raises the user-definedexception External_Source.Done.

       (2)get_external_source_data needs no more rows.

              Inthis case, the PIPE ROW statement in get_external_source_data raises the NO_DATA_NEEDEDexception.

 

       Ineither case, an exception handler in block b in get_external_source_datainvokes External_Source.Clean_Up, which releases the resources that Next_Rowwas using.

 

Example12-35 Pipelined Table Function Handles NO_DATA_NEEDED


CREATE OR REPLACE FUNCTION get_external_source_data


  RETURN t AUTHID DEFINER PIPELINED IS


BEGIN


  External_Source.Init();           -- Initialize.


  <<b>> BEGIN


    LOOP                            -- Pipe rows from external source.


      PIPE ROW (External_Source.Next_Row());


    END LOOP;


  EXCEPTION


    WHEN External_Source.Done THEN  -- When no more rows are available,


      External_Source.Clean_Up();   --  clean up.


    WHEN NO_DATA_NEEDED THEN        -- When no more rows are needed,


      External_Source.Clean_Up();   --  clean up.


      RAISE NO_DATA_NEEDED;           -- Optional, equivalent to RETURN.


  END b;


END get_external_source_data;


/


 

小结:

       You cannot run a pipelined table function over a databaselink. The reason is that the return type of a pipelinedtable function is a SQL user-defined type, which can be used only in a singledatabase

 

       A pipelined table function returns a row to itsinvoker immediately after processing that rowand continues to process rows. Response time improvesbecause the entire collection need not be constructed and returned to the serverbefore the query can return a single result row. (Also, the function needs less memory, because the objectcache need not materialize the entire collection.)

 

       The"table" that a pipelined table functionreturns cannot be the target table of a DELETE, INSERT, or UPDATE statement.However, you can create a view of such a table and create INSTEAD OF triggerson the view.

 

       pipelined表函数的基本思路:在函数执行过程中就返回记录,而不用等函数全部执行完,再返回到calling context。这个好处是减少了大量的PGA使用,从而节省了内存的分配和使用。

       在传输大数据量的时候,对于性能的提升特别有效。pipelined表函数还可以使用parallel处理。

      

       要注意的是 pipelined table funcation返回target table不能是delete,insert 或者update语句。

      

 

二.  测试

这里我们运行下文档里的几个示例。

 

2.1 Test 1

 

SYS@anqing2(rac2)> CREATE TABLE gradereport(

 2    student VARCHAR2(30),

 3    subject VARCHAR2(30),

 4    weight NUMBER,

 5    grade NUMBER

 6  );

Table created.

 

SYS@anqing2(rac2)> INSERT INTOgradereport (student, subject, weight, grade) VALUES ('Mark', 'Physics', 4, 4);

1 row created.

 

SYS@anqing2(rac2)>  INSERT INTO gradereport (student, subject,weight, grade) VALUES ('Mark','Chemistry', 4, 3);

1 row created.

 

SYS@anqing2(rac2)>  INSERT INTO gradereport (student, subject,weight, grade) VALUES ('Mark','Maths', 3, 3);

1 row created.

 

SYS@anqing2(rac2)> INSERT INTOgradereport (student, subject, weight, grade) VALUES ('Mark','Economics', 3,4);

1 row created.

 

SYS@anqing2(rac2)> commit;

Commit complete.


 


SYS@anqing2(rac2)> CREATE PACKAGEpkg_gpa IS

 2    TYPE gpa IS TABLE OF NUMBER;

 3    FUNCTIONweighted_average(input_values SYS_REFCURSOR)

 4      RETURN gpa PIPELINED;

 5  END pkg_gpa;

 6  /

Package created.


 


CREATE PACKAGE BODY pkg_gpa IS

  FUNCTIONweighted_average (input_values SYS_REFCURSOR)

    RETURN gpa PIPELINED

  IS

   grade         NUMBER;

   total         NUMBER := 0;

   total_weight  NUMBER := 0;

   weight        NUMBER := 0;

 BEGIN

    LOOP

     FETCH input_values INTO weight, grade;

     EXIT WHEN input_values%NOTFOUND;

     total_weight := total_weight + weight; -- Accumulate weighted average

     total := total + grade*weight;

   END LOOP;

    PIPEROW (total / total_weight);

   RETURN; -- returns single result

  ENDweighted_average;

END pkg_gpa;

/


 


调用pipelined table functions:


 


SYS@anqing2(rac2)> SELECT w.column_value"weighted result" FROM TABLE (pkg_gpa.weighted_average (CURSOR(SELECT weight, grade FROM gradereport) )) w;

 

weighted result

---------------

           3.5

 

2.2 Test 2

SYS@anqing2(rac2)> CREATE OR REPLACEPACKAGE pkg1 AS

 2    TYPE numset_t IS TABLE OFNUMBER;

 3    FUNCTION f1(x NUMBER) RETURNnumset_t PIPELINED;

 4  END pkg1;

 5  /

Package created.


 


CREATE PACKAGE BODY pkg1 AS

  --FUNCTION f1 returns a collection of elements (1,2,3,... x)

  FUNCTIONf1(x NUMBER) RETURN numset_t PIPELINED IS

 BEGIN

   FOR i IN 1..x LOOP

     PIPE ROW(i);

   END LOOP;

    RETURN;

  ENDf1;

END pkg1;

/


 


SYS@anqing2(rac2)> SELECT * FROMTABLE(pkg1.f1(5));

 

COLUMN_VALUE

------------

          1

          2

          3

          4

          5