PLSQL程序调优系列九

PLSQL程序调优系列九（使用Pipelined函数设置转换）

       这种函数适合于需要将多个转换应用到数据上的环境中。

Pipelined表函数

       Pipelined表函数是产生一个可以像物理表一样查询或者可以赋给PL/SQL集合变量的函数。表函数可以将一个行集合作为输入，输入类型可以为VARRAY或PL/SQL表，或者REF CURSOR。

       表函数的执行可以并行化，返回的行可以直接流向下一个步骤，不需要中间汇集。表函数返回的集合的行也可以管道化，即迭代返回处理的值而不需要等待全部处理完成后一次性返回。

       流，管道，并行执行表函数都可以提高性能：

       ·通过启用多线程，并行执行表函数；

       ·在进程之间消除中间步骤；

       ·提高查询响应时间：使用非管道表函数，整个集合在查询返回第一行前必须返回结果集；对于管道，行以它们的顺序流水线返回，而不是批返回，减少了表函数所需的内存。

编写Pipelined表函数

       通过声明PIPELINED关键字创建Pipelined表函数，管道函数可以使用CREATE FUNCTION在模式级别声明，PIPELINED关键字通知函数迭代返回行。返回类型必须声明为支持的集合类型，如嵌套表或者关联数组。集合类型同正常，可以在模式级别声明，也可以在包中声明。在函数的内部，返回集合的元素。并且元素的类型必须是SQL支持的类型。

       如下：

CREATE 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;

END pkg1;

/

 

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

使用Pipelined表函数进行转换

       Pipelined表函数同正常的函数一样可以接受任何参数，接受REF CURSOR参数的表函数可以充当转换函数，这意味着可以使用REF CURSOR推进行，执行某些操作，然后pipeline结果。

-- Define the ref cursor types and function

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;

    EXIT WHEN p%NOTFOUND;

    -- first row

    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);

    -- second row

    out_rec.var_char1 := in_rec.email;

    out_rec.var_char2 := in_rec.phone_number;

    PIPE ROW(out_rec);

  END LOOP;

  CLOSE p;

  RETURN;

  END;

END refcur_pkg;

/

-- SELECT query using the f_transc table function

SELECT * FROM TABLE(

   refcur_pkg.f_trans(CURSOR(SELECT * FROM employees WHERE department_id = 60)));

       在CURSOR子查询被从SQL传递到REF CURSOR时，引用的游标在执行前已经被打开。

从Pipelined表函数返回结果集

       在PL/SQL中，PIPE ROW语句使pipelined表函数传送一行然后继续处理。该语句使PL/SQL表函数尽可能快地返回它们产生的行。为了性能原因，PL/SQL运行时系统以批方式提供行给消费者。

       PIPE ROW(out_rec)负责将数据传出PL/SQL表函数。PIPE ROW语句只能在pipelined表函数中使用，否则将会抛出错误。对于不返回行的pipelined表函数,PIPE ROW可以忽略。

       pipelined表函数可能包含一个不返回值的RETURN语句，RETURN语句将控制返回给消费者并确保下一次推进将会得到NO_DATA_FOUND异常。

       由于表函数随着行的产生在调用过程之间传递控制，在结合表函数和PRAGMA AUTONOMOUS_TRANSACTION时有一点限制。如果表函数是自治事务的一部分，在每次调用PIPE ROW前，必须先执行COMMIT或ROLLBACK，以避免在调用子程序中发生错误。

PL/SQL表函数之间传递数据

       如下：SELECT * FROM TABLE(f(CURSOR(SELECT * FROM TABLE(g()))));

从Pipelined表函数推进结果集

       PL/SQL游标和ref cursors游标可以定义为查询表函数：

       OPEN c FOR SELECT * FROM TABLE(f(...));

       在表函数上定义的游标和正常的游标一样，表函数上的REF CURSOR没有特定的语义。

       SQL语句不会优化交叉PL/SQL语句，如下：

DECLARE
  r SYS_REFCURSOR;
BEGIN
  OPEN r FOR SELECT *
    FROM TABLE(f(CURSOR(SELECT * FROM tab)));
  SELECT * BULK COLLECT INTO rec_tab FROM TABLE(g(r));
END;
/

       其执行不同于以下SQL语句：

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

使用游标变量传递数据

       可以使用REF CURSOR变量传递一个结果集到PL/SQL函数中，如下：

       FUNCTION f(p1 IN SYS_REFCURSOR) RETURN ... ;

       其中SYS_REFCURSOR是预定义的REF CURSOR类型。然后子查询的结果就可以直接传递到查询中：

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

CURSOR关键字必需的，因为其指示子查询的结果集是通过REF CURSOR参数传递的。

使用SYS_REFCURSOR，就可以直接在包中使用，而无需首先创建一个REF CURSOR类型。

为了使用强类型的REF CURSOR，仍需要创建一个PL/SQL类型，并在其中声明一个强类型的REF CURSOR类型。同时如果使用强类型的REF CURSOR作为表函数的参数，实际的REF CURSOR类型必须匹配列类型，否则将会产生一个错误。

PL/SQL也可以接受多个REF CURSOR输入变量，如下：

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;

END refcur_pkg;

/

 

-- SELECT query using the g_trans table function

SELECT * FROM TABLE(refcur_pkg.g_trans(

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

  CURSOR(SELECT * FROM departments WHERE department_id = 60)));

       可以通过创建REF CURSOR将表函数的返回的值传递到其他表函数，如下：

       SELECT * FROM TABLE(f(CURSOR(SELECT * FROM TABLE(g(...)))));

在Pipelined 表函数内执行DML

       为了执行DML，声明一个带AUTONOMOUS_TRANSACTION的Pipelined 表函数，这会使函数在一个新的事务中执行而不与其他进程共享，如下：

CREATE FUNCTION f(p SYS_REFCURSOR)
  RETURN CollType PIPELINED IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN NULL; END;
/

在Pipelined 表函数上执行DML

       Pipelined 表函数不能是UPDATE, INSERT，DELETE语句的TARGET，以下查询将会发生错误：

UPDATE F(CURSOR(SELECT * FROM tab)) SET col = value;
INSERT INTO f(...) VALUES ('any', 'thing');

       但是可以在表函数上创建一个视图，然后使用INSTEAD OF触发器更新：

CREATE VIEW BookTable AS SELECT x.Name, x.Author
  FROM TABLE(GetBooks('data.txt')) x;

CREATE TRIGGER BookTable_insert
INSTEAD OF INSERT ON BookTable
REFERENCING NEW AS n
FOR EACH ROW
BEGIN
...
END;
/

INSTEAD OF可以定义在所有基于表函数建立的视图的DML操作上。

Pipelined表函数的异常处理

       Pipelined表函数的异常处理同正常表。