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）之前還是沒有提交。