oracle 无法在查询中执行 DML 操作

cannot perform DML operation inside a query

提问by PPShein

I cannot convince why I can't add DML operation inside Oracle Functionespecially inside cursor loop. I feel Oracle don't support DML operation inside cursor loop.

我无法说服为什么我不能在Oracle Function 中添加 DML 操作，尤其是在cursor loop 中。我觉得 Oracle 不支持游标循环内的 DML 操作。

How can I do If I need to insert into table inside cursor loop? Create new store procedure inside it or something else?

如果我需要在游标循环内插入表格，我该怎么办？在其中创建新的存储过程还是其他什么？

Error Message : cannot perform DML operation inside a query

错误消息：无法在查询中执行 DML 操作

Here is my function,

这是我的功能，

CREATE OR REPLACE FUNCTION TEST_FUNC(U_ID IN VARCHAR2)
RETURN VARCHAR2
IS
  V_MESSAGE VARCHAR2(30);
  CURSOR C_PERSON (V_ID VARCHAR2) IS
         SELECT NAME_UPPER
         FROM TBL_PERSON
         WHERE NAME_UPPER = V_ID;                  
BEGIN
   FOR C_PERSON_CURSOR IN C_PERSON(U_ID) 
   LOOP
       INSERT INTO TMP_PERSON(NAME) VALUES (C_PERSON_CURSOR.NAME_UPPER);
   END LOOP;

   RETURN V_MESSAGE;

EXCEPTION
WHEN OTHERS THEN
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

回答by Tony Andrews

You canuse DML inside a PL/SQL function - no problem. However, the function can only be called from PL/SQL, not from SQL - i.e. it can be called like this:

您可以在 PL/SQL 函数中使用 DML - 没问题。但是，该函数只能从 PL/SQL 调用，不能从 SQL 调用 - 即它可以像这样调用：

declare
   l_message varchar2(30);
begin
   l_message := test_func('123');
end;

... but not like this:

...但不是这样：

select test_func(empno) from emp;

That leads to the error message you posted.

这会导致您发布的错误消息。

Many people (including me) don't like functions that have "side effects" like this, but that is a matter of best practice and standards, not a technical issue.

许多人（包括我）不喜欢有这样“副作用”的函数，但这是最佳实践和标准的问题，而不是技术问题。

回答by Bruno Gautier

You can perform DML operations inside an Oracle PL/SQL function and, although this is generally not a good practice, call it from SQL. The function has to be marked with a pragma AUTONOMOUS_TRANSACTIONand the transaction has to be committed or rolled back before exiting the function (see AUTONOMOUS_TRANSACTION Pragma).

您可以在 Oracle PL/SQL 函数内执行 DML 操作，尽管这通常不是一个好的做法，但可以从 SQL 调用它。该函数必须用 pragma 标记，AUTONOMOUS_TRANSACTION并且在退出函数之前必须提交或回滚事务（请参阅AUTONOMOUS_TRANSACTION Pragma）。

You should be aware that this kind of function called from SQL can dramatically degrade your queries performances. I recommend you use it only for audit purposes.

您应该知道这种从 SQL 调用的函数会显着降低您的查询性能。我建议您仅将其用于审计目的。

Here is an example script starting from your function:

这是从您的函数开始的示例脚本：

CREATE TABLE TBL_PERSON (NAME_UPPER VARCHAR2(30));
CREATE TABLE TMP_PERSON (NAME VARCHAR2(30));

INSERT INTO TBL_PERSON (NAME_UPPER) VALUES ('KING');

CREATE OR REPLACE FUNCTION TEST_FUNC(U_ID IN VARCHAR2)
RETURN VARCHAR2
IS
  PRAGMA AUTONOMOUS_TRANSACTION; -- Needed to be called from SQL

  V_MESSAGE VARCHAR2(2000);
  CURSOR C_PERSON (V_ID VARCHAR2) IS
         SELECT NAME_UPPER
         FROM TBL_PERSON
         WHERE NAME_UPPER = V_ID;                  
BEGIN
   FOR C_PERSON_CURSOR IN C_PERSON(U_ID) 
   LOOP
       INSERT INTO TMP_PERSON(NAME) VALUES (C_PERSON_CURSOR.NAME_UPPER);

       V_MESSAGE := SQL%ROWCOUNT
          || ' Person record successfully inserted into TMP_PERSON table';
   END LOOP;

   COMMIT; -- The current autonomous transaction need to be commited
           -- before exiting the function.

   RETURN V_MESSAGE;

EXCEPTION
WHEN OTHERS THEN
    ROLLBACK;
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/

PROMPT Call the TEST_FUNC function and insert a new record into TMP_PERSON table
SELECT TEST_FUNC('KING') FROM DUAL;

PROMPT Content of the TMP_PERSON table
COL NAME FOR A30
SELECT * FROM TMP_PERSON;

When running the previous script we get the following output:

运行前面的脚本时，我们得到以下输出：

Table created.

Table created.

1 row created.

Function created.

Calling the TEST_FUNC function and insert a new record into TMP_PERSON table

TEST_FUNC('KING')
------------------------------------------------------------
1 Person record successfully inserted into TMP_PERSON table

Content of the TMP_PERSON table

NAME
------------------------------
KING