PL/SQL“ ORA-14551: 無法在查詢中執行 DML 操作”解決

環境

Oracle 11.2.0 + SQL Plus

問題

根據以下要求編寫函數：將scott.emp表中工資低於平均工資的職工工資加上200，並返回修改了工資的總人數。PL/SQL中有更新的操作，執行此函數報如下錯誤：ORA-16551: 無法在查詢中執行 DML 操作。

解決

在聲明函數時加上： PRAGMA AUTONOMOUS_TRANSACTION; 並在執行完DML後COMMIT。

動作記錄

--登入到Oracle
C:\Users\Wentasy>sqlplus wgb

SQL*Plus: Release 11.2.0.1.0 Production on 星期六 6月 29 15:32:21 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

輸入口令:

串連到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--編寫函數
SQL> CREATE OR REPLACE FUNCTION raise_sal
  2  RETURN NUMBER
  3  IS
  4  v_num NUMBER:=0;
  5  v_avg emp.sal%TYPE;
  6  BEGIN
  7    SELECT AVG(sal) INTO v_avg FROM emp;
  8    UPDATE emp SET sal=sal+200 WHERE sal < v_avg;
  9    v_num:=SQL%ROWCOUNT;
 10    RETURN v_num;
 11  END raise_sal;
 12  /

函數已建立。

--調用函數，出現錯誤
SQL> SELECT raise_sal() FROM DUAL;
SELECT raise_sal() FROM DUAL
      *
第 1 行出現錯誤:
ORA-14551: 無法在查詢中執行 DML 操作
ORA-06512: 在 "WGB.RAISE_SAL", line 8

--加上PRAGMA AUTONOMOUS_TRANSACTION和COMMIT。
SQL> CREATE OR REPLACE FUNCTION raise_sal
  2  RETURN NUMBER
  3  IS
  4  PRAGMA AUTONOMOUS_TRANSACTION;
  5  v_num NUMBER:=0;
  6  v_avg emp.sal%TYPE;
  7  BEGIN
  8    SELECT AVG(sal) INTO v_avg FROM emp;
  9    UPDATE emp SET sal=sal+200 WHERE sal < v_avg;
 10    v_num:=SQL%ROWCOUNT;
 11    COMMIT;
 12    RETURN v_num;
 13  END raise_sal;
 14  /

函數已建立。

--驗證第一步：查詢薪水平均值
SQL> SELECT AVG(sal) FROM emp;

  AVG(SAL)
----------
  2543.75

--驗證第二步：查詢薪水比平均薪水低的員工的總數
SQL> SELECT count(sal) FROM emp WHERE sal < (SELECT AVG(sal) FROM emp);

COUNT(SAL)
----------
        8

--驗證第三步：查詢資料
SQL> SELECT ename, sal FROM emp;

ENAME            SAL
---------- ----------
SMITH            1600
ALLEN            2400
WARD            2050
JONES            2975
MARTIN          2050
BLAKE            2850
CLARK            2450
KING            5000
TURNER          2300
JAMES            1750
FORD            3000

ENAME            SAL
---------- ----------
MILLER          2100

已選擇12行。

--驗證第四步：調用函數，如果為8，則實現功能
SQL> SELECT raise_sal() FROM dual;

RAISE_SAL()
-----------
          8

--驗證第五步：重新查詢表資料
SQL> SELECT ename, sal FROM emp;

ENAME            SAL
---------- ----------
SMITH            1800
ALLEN            2600
WARD            2250
JONES            2975
MARTIN          2250
BLAKE            2850
CLARK            2650
KING            5000
TURNER          2500
JAMES            1950
FORD            3000

ENAME            SAL
---------- ----------
MILLER          2300

已選擇12行。

參考資料

ORA-14551: 無法在查詢中執行 DML 操作

引用文字——更好的理解自治事務

資料庫事務是一種單元操作，要麼是全部操作都成功，要麼全部失敗。在Oracle中，一個事務是從執行第一個資料管理語言（DML）語句開始，直到執行一個COMMIT語句，提交儲存這個事務，或者執行一個ROLLBACK語句，放棄此次操作結束。事務的“要麼全部完成，要麼什麼都沒完成”的本性會使將錯誤資訊記入資料庫表中變得很困難，因為當事務失敗重新運行時，用來編寫日誌條目的INSERT語句還未完成。針對這種困境，Oracle提供了一種便捷的方法，即自治事務。自治事務從當前事務開始，在其自身的語境中執行。它們能獨立地被提交或重新運行，而不影響正在啟動並執行事務。正因為這樣，它們成了編寫錯誤記錄檔表格的理想形式。在事務中檢測到錯誤時，您可以在錯誤記錄檔表格中插入一行並提交它，然後在不丟失這次插入的情況下復原主事務。因為自治事務是與主事務相分離的，所以它不能檢測到被修改過的行的目前狀態。這就好像在主事務提交之前，它們一直處於單獨的會話裡，對自治事務來說，它們是停用。然而，反過來情況就不同了：主事務能夠檢測到已經執行過的自治事務的結果。要建立一個自治事務，您必須在匿名塊的最高層或者預存程序、函數、資料包或觸發的定義部分中，使用PL/SQL中的PRAGMA AUTONOMOUS_TRANSACTION語句。在這樣的模組或過程中執行的SQLServer語句都是自治的。觸發無法包含COMMIT語句，除非有PRAGMA AUTONOMOUS_TRANSACTION標記。但是，只有觸發中的語句才能被提交，主事務則不行。