SQL “无法在查询中执行 DML 操作”的解决方案？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8729236/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Solution to "cannot perform a DML operation inside a query"?
提问by Joshua1729
I am using a Data Analysis tool and the requirement I have was to accept a value from the user, pass that as a parameter and store it in a table. Pretty straighforward so I sat to write this
我正在使用数据分析工具，我的要求是接受用户的值，将其作为参数传递并将其存储在表中。很直接，所以我坐下来写这个
create or replace
procedure complex(datainput in VARCHAR2)
is
begin
insert into dumtab values (datainput);
end complex;
I executed this in SQL Developerusing the following statement
我使用以下语句在SQL Developer 中执行此操作
begin
complex('SomeValue');
end;
It worked fine, and the value was inserted into the table. However, the above statements are not supported in the Data Analysis tool, so I resorted to use a function instead. The following is the code of the function, it compiles.
它工作正常，并且值被插入到表中。但是，数据分析工具不支持上述语句，因此我改用了函数。以下是函数的代码，它编译。
create or replace
function supercomplex(datainput in VARCHAR2)
return varchar2
is
begin
insert into dumtab values (datainput);
return 'done';
end supercomplex;
Once again I tried executing it in SQL Developer, but I got cannot perform a DML operation inside a queryupon executing the following code
我再次尝试在SQL Developer 中执行它，但是在执行以下代码时我无法在查询中执行DML 操作
select supercomplex('somevalue') from dual;
My question is - I need a statement that can run the mentioned function in SQL Developeror - A function that can perform what I am looking for which can be executed by the select statement. - If it is not possible to do what I'm asking, I would like a reason so I can inform my manager as I am very new (like a week old?) to PL/SQL so I am not aware of the rules and syntaxes.
我的问题是 - 我需要一个可以在SQL Developer 中运行上述函数的语句，或者 - 一个可以执行我正在寻找的可以由 select 语句执行的函数。- 如果无法执行我要求的操作，我需要一个原因，以便我可以通知我的经理，因为我对 PL/SQL 非常陌生（比如一个星期大？），所以我不了解规则和语法。
P.S. How I wish this was C++ or even Java :(
PS 我多么希望这是 C++ 甚至 Java :(
EDIT
编辑
I need to run the function on SQL Developer because before running it in DMine (which is the tool) in order to test if it is valid or not. Anything invalid in SQL is also invalid in DMine, but not the other way around.
我需要在 SQL Developer 上运行该函数，因为在 DMine（这是该工具）中运行它之前，以测试它是否有效。SQL 中任何无效的东西在 DMine 中也无效，但反之则不然。
Thanks for the help, I understood the situation and as to why it is illegal/not recommended
感谢您的帮助，我了解情况以及为什么它是非法/不推荐的
回答by Vincent Malgrat
You could use the directive pragma autonomous_transaction. This will run the function into an independant transaction that will be able to perform DML without raising the ORA-14551.
你可以使用指令pragma autonomous_transaction。这会将函数运行到一个独立的事务中，该事务将能够在不引发 ORA-14551 的情况下执行 DML。
Be aware that since the autonomous transactionis independent, the results of the DML will be commited outside of the scope of the parent transaction. In most cases that would not be an acceptable workaround.
请注意，由于自治事务是独立的，因此 DML 的结果将被提交到父事务范围之外。在大多数情况下，这不是可接受的解决方法。
SQL> CREATE OR REPLACE FUNCTION supercomplex(datainput IN VARCHAR2)
2 RETURN VARCHAR2 IS
3 PRAGMA AUTONOMOUS_TRANSACTION;
4 BEGIN
5 INSERT INTO dumtab VALUES (datainput);
6 COMMIT;
7 RETURN 'done';
8 END supercomplex;
9 /
Function created
SQL> SELECT supercomplex('somevalue') FROM dual;
SUPERCOMPLEX('SOMEVALUE')
--------------------------------------------------------------------------------
done
SQL> select * from dumtab;
A
--------------------------------------------------------------------------------
somevalue
Tom Kyte has a nice explanationabout why the error is raised in the first place. It is not safe because it may depend upon the order in which the rows are processed. Furthermore, Oracle doesn't guarantee that the function will be executed at least once and at most once per row.
Tom Kyte有一个很好的解释为什么首先会引发错误。这是不安全的，因为它可能取决于处理行的顺序。此外，Oracle 不保证该函数每行至少执行一次，最多执行一次。
回答by John Doyle
Just declare a variable to accept the return value, for example:
只需声明一个变量来接受返回值，例如：
declare
retvar varchar2(4);
begin
retvar := supercomplex('somevalue');
end;
The select doesn't work because the function is performing an insert, if all it did was return a value then it would work.
select 不起作用，因为该函数正在执行插入，如果它所做的只是返回一个值，那么它将起作用。
回答by 0xdb
Just execute the function in a dummy if ... end if;statement to ignore the return value:
只需在虚拟if ... end if;语句中执行该函数即可忽略返回值：
exec if supercomplex('somevalue') then null; end if;
Or execute it as a parameter for put_lineprocedure to output the return value:
或者作为put_lineprocedure的参数执行，输出返回值：
exec dbms_ouput ('result of supercomplex='||supercomplex('somevalue'));
result of supercomplex=done
