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_pipelineIS   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)))/

这三行会显示出来：

246

(A)

CREATE OR REPLACE PACKAGE BODY plch_pipelineIS   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 12SQL>

(B)

CREATE OR REPLACE PACKAGE BODY plch_pipelineIS   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 15SQL>

(C)

CREATE OR REPLACE PACKAGE BODY plch_pipelineIS   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 15SQL>

(D)

CREATE OR REPLACE PACKAGE BODY plch_pipelineIS   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           6SQL>

答案说明在109楼

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