“无法在查询中执行 DML 操作"的解决方案? [英] Solution to "cannot perform a DML operation inside a query"?

问题描述

我正在使用数据分析工具，我的要求是接受来自用户的值，将其作为参数传递并将其存储在表中.很直接，所以我坐下来写这个

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;

我在 SQL Developer 中使用以下语句执行了此操作

I executed this in SQL Developer using the following statement

begin
complex('SomeValue');  
end;

它工作正常，并且值被插入到表中.但是，数据分析工具不支持上述语句，因此我使用函数代替.以下是该函数的代码，它编译.<​​/p>

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;   

我再次尝试在 SQL Developer 中执行它，但是我在执行以下代码时无法在查询中执行 DML 操作

Once again I tried executing it in SQL Developer, but I got cannot perform a DML operation inside a query upon executing the following code

select supercomplex('somevalue') from dual;

我的问题是- 我需要一个可以在 SQL Developer 中运行上述函数的语句或- 一个可以执行我正在寻找的可以由 select 语句执行的函数.- 如果无法执行我要求的操作，我需要一个原因，以便我可以通知我的经理，因为我对 PL/SQL 非常陌生(比如一个星期大?)，所以我不了解规则和语法.

My question is - I need a statement that can run the mentioned function in SQL Developer or - 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.

附言我多么希望这是 C++ 甚至是 Java :(

P.S. How I wish this was C++ or even Java :(

编辑

我需要在 SQL Developer 上运行该函数，因为在 DMine(该工具)中运行它之前，以测试它是否有效.任何在 SQL 中无效的内容在 DMine 中也无效，但反之则不然.

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.

感谢您的帮助，我了解情况以及为什么它是非法/不推荐的

Thanks for the help, I understood the situation and as to why it is illegal/not recommended

推荐答案

您可以使用指令 pragma allowed_transaction.这会将函数运行到一个独立的事务中，该事务将能够在不引发 ORA-14551 的情况下执行 DML.

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.

请注意，由于自治事务是独立的，因此DML 的结果将在父事务范围之外提交.在大多数情况下，这不是可接受的解决方法.

Be aware that since the autonomous transaction is 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.

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 有一个不错的解释 关于为什么首先出现错误的原因.这是不安全的，因为它可能取决于处理行的顺序.此外，Oracle 不保证该函数每行至少执行一次，最多执行一次.

Tom Kyte has a nice explanation about 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.

这篇关于“无法在查询中执行 DML 操作"的解决方案?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！