2011-11-2 游标和管道函数

http://www.itpub.net/thread-1499223-11-1.html

105楼

我创建了这张表并填入数据：

CREATE TABLE plch_parts
(
   partnum    INTEGER PRIMARY KEY
, partname   VARCHAR2 (100) UNIQUE
)
/
BEGIN
   INSERT INTO plch_parts VALUES (1, 'Mouse');
   INSERT INTO plch_parts VALUES (100, 'Keyboard');
   INSERT INTO plch_parts VALUES (500, 'Monitor');
   COMMIT;
END;
/

我建立了如下的嵌套表类型和包说明：

CREATE OR REPLACE TYPE numbers_t IS TABLE OF NUMBER;
/
CREATE OR REPLACE PACKAGE plch_pipeline
IS
   CURSOR refcur_c
   IS
      SELECT line FROM user_source;
   TYPE refcur_t IS REF CURSOR
      RETURN refcur_c%ROWTYPE;
   FUNCTION double_values (dataset refcur_t)
      RETURN numbers_t
      PIPELINED;
END plch_pipeline;
/

下列的选项中哪些实现了包体，从而使得这个查询执行之后：

SELECT *
  FROM TABLE (plch_pipeline.double_values (
                CURSOR (SELECT line
                          FROM user_source
                         WHERE name = 'PLCH_PIPELINE'
                           AND type = 'PACKAGE'
                           AND line <= 3
                         ORDER BY line)))
/

这三行会显示出来：

2
4
6

(A)

CREATE OR REPLACE PACKAGE BODY plch_pipeline
IS
   FUNCTION double_values (dataset refcur_t)
      RETURN numbers_t PIPELINED
   IS
      l_number   NUMBER;
   BEGIN
      LOOP
         FETCH dataset INTO l_number;
         EXIT WHEN dataset%NOTFOUND;
         UPDATE plch_parts SET partnum = partnum;
            
         PIPE ROW (l_number * 2);
      END LOOP;
      CLOSE dataset;
      RETURN;
   END;
END plch_pipeline;
/

SQL> SELECT *
  2    FROM TABLE (plch_pipeline.double_values (
  3                  CURSOR (SELECT line
  4                            FROM user_source
  5                           WHERE name = 'PLCH_PIPELINE'
  6                             AND type = 'PACKAGE'
  7                             AND line <= 3
  8                           ORDER BY line)))
  9  /
SELECT *
  FROM TABLE (plch_pipeline.double_values (
                CURSOR (SELECT line
                          FROM user_source
                         WHERE name = 'PLCH_PIPELINE'
                           AND type = 'PACKAGE'
                           AND line <= 3
                         ORDER BY line)))
ORA-14551: 无法在查询中执行 DML 操作
ORA-06512: 在 "YOGA.PLCH_PIPELINE", line 12
SQL>

(B)

CREATE OR REPLACE PACKAGE BODY plch_pipeline
IS
   FUNCTION double_values (dataset refcur_t)
      RETURN numbers_t PIPELINED
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      l_number   NUMBER;
   BEGIN
      LOOP
         FETCH dataset INTO l_number;
         EXIT WHEN dataset%NOTFOUND;
         UPDATE plch_parts SET partnum = partnum;
         PIPE ROW (l_number * 2);
      END LOOP;
      CLOSE dataset;
      RETURN;
   END;
END plch_pipeline;
/

SQL> SELECT *
  2    FROM TABLE (plch_pipeline.double_values (
  3                  CURSOR (SELECT line
  4                            FROM user_source
  5                           WHERE name = 'PLCH_PIPELINE'
  6                             AND type = 'PACKAGE'
  7                             AND line <= 3
  8                           ORDER BY line)))
  9  /
SELECT *
  FROM TABLE (plch_pipeline.double_values (
                CURSOR (SELECT line
                          FROM user_source
                         WHERE name = 'PLCH_PIPELINE'
                           AND type = 'PACKAGE'
                           AND line <= 3
                         ORDER BY line)))
ORA-06519: 检测到活动的独立的事务处理, 已经回退
ORA-06512: 在 "YOGA.PLCH_PIPELINE", line 15
SQL>

(C)

CREATE OR REPLACE PACKAGE BODY plch_pipeline
IS
   FUNCTION double_values (dataset refcur_t)
      RETURN numbers_t PIPELINED
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      l_number   NUMBER;
   BEGIN
      LOOP
         FETCH dataset INTO l_number;
         EXIT WHEN dataset%NOTFOUND;
         UPDATE plch_parts SET partnum = partnum;
         PIPE ROW (l_number * 2);
      END LOOP;
      CLOSE dataset;
      COMMIT;
      RETURN;
   END;
END plch_pipeline;
/

SQL> SELECT *
  2    FROM TABLE (plch_pipeline.double_values (
  3                  CURSOR (SELECT line
  4                            FROM user_source
  5                           WHERE name = 'PLCH_PIPELINE'
  6                             AND type = 'PACKAGE'
  7                             AND line <= 3
  8                           ORDER BY line)))
  9  /
SELECT *
  FROM TABLE (plch_pipeline.double_values (
                CURSOR (SELECT line
                          FROM user_source
                         WHERE name = 'PLCH_PIPELINE'
                           AND type = 'PACKAGE'
                           AND line <= 3
                         ORDER BY line)))
ORA-06519: 检测到活动的独立的事务处理, 已经回退
ORA-06512: 在 "YOGA.PLCH_PIPELINE", line 15
SQL>

(D)

CREATE OR REPLACE PACKAGE BODY plch_pipeline
IS
   FUNCTION double_values (dataset refcur_t)
      RETURN numbers_t PIPELINED
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      l_number   NUMBER;
   BEGIN
      LOOP
         FETCH dataset INTO l_number;
         EXIT WHEN dataset%NOTFOUND;
         UPDATE plch_parts SET partnum = partnum;
         COMMIT;
         
         PIPE ROW (l_number * 2);
      END LOOP;
      CLOSE dataset;
      RETURN;
   END;
END plch_pipeline;
/

SQL> SELECT *
  2    FROM TABLE (plch_pipeline.double_values (
  3                  CURSOR (SELECT line
  4                            FROM user_source
  5                           WHERE name = 'PLCH_PIPELINE'
  6                             AND type = 'PACKAGE'
  7                             AND line <= 3
  8                           ORDER BY line)))
  9  /
COLUMN_VALUE
------------
           2
           4
           6
SQL>

答案说明在109楼

2011-11-2 答案D.
A: 在SQL中调用的函数不能有DML, 除非是自治事务；
B: 虽然用了自治事务，但是在返回之前（PIPE ROW 就是返回一行数据）必须提交或回滚这个事务；
C: 虽然用了自治事务而且有COMMIT, 但是位置不对，COMMIT放在循环外面，这样在返回（PIPE ROW）之前还是没有提交。