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

问题描述

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

 创建或替换
过程复杂（VARCHAR2中的datainput）
是
 begin 
插入dumtab值（datainput）; 
结束复杂; 
  

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

  begin 
 complex（'SomeValue'）; 
 end; 
  

它工作正常，并将该值插入表中。但是，上述语句在数据分析工具中不受支持，所以我使用函数来代替。
$ b

 创建或替换
函数supercomplex（VARCHAR2中的datainput） 
 return varchar2 
是
 begin 
插入到dumtab值（datainput）; 
返回'完成'; 
结束超复合; 
  

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

  select supercomplex（'somevalue'）来自双重; 
  

我的问题是
- 我需要一个可以在 SQL Developer 或
- 这个函数可以执行我正在查找的可以由select语句执行的内容。
- 如果不能做我想问的问题，我想要一个理由让我可以通知我的经理，因为我是非常新的（比如一个星期？）PL / SQL，所以我不知道的规则和语法。

PS我希望这是C ++甚至是Java：（$ / b>
$ b

编辑

我需要在SQL Developer上运行这个函数，因为在运行它之前需要在DMine（这是工具）中测试它是否有效。在SQL中任何无效的东西在DMine中也是无效的，但是反过来也是一样。

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

解决方案你可以使用伪指令 pragma autonomous_transaction 。这会将函数运行到一个独立的事务中，该事务将能够执行DML而不会引起ORA-14551 。

请注意，自自主事务是独立的，DML的结果将在父事务处理范围之外进行提交，在大多数情况下这不是一个可接受的解决方法。

  SQL>创建或替换函数supercomplex（datainput IN VARCHAR2）
 2 RETURN VARCHAR2 IS 
 3 PRAGMA AUTONOMOUS_TRANSACTION; 
 4 BEGIN 
 5插入到dumtab VALUES（datainput）; 
 6 COMMIT; 
 7 RETURN'done'; 
 8 END supercomplex; 
 9 / 
 
创建的函数
 
 SQL> SELECT supercomplex（'somevalue'）FROM dual; 
 
 SUPERCOMPLEX（'SOMEVALUE'）
 -------------------------------- ------------------------------------------------ 
完成
 
 SQL>从dumtab中选择*; 
 
 A 
 ------------------------------------- ------------------------------------------- 
 somevalue 
  

Tom Kyte 有一个很好的解释，说明为什么错误首先出现。这是不安全的，因为它可能取决于行处理的顺序。此外，Oracle不保证该函数每行至少执行一次，最多执行一次。

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 Developer using the following statement

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 query upon executing the following code

select supercomplex('somevalue') from dual;

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.

P.S. How I wish this was C++ or even 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.

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

解决方案

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.

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 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屋！