sql - "cannot perform a DML operation inside a query"的解决方案？

Question

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

create or replace
procedure complex(datainput in VARCHAR2)
is
begin
insert into dumtab values (datainput);
end complex;

我在 中执行了此操作SQL 开发人员 使用以下语句

begin
complex('SomeValue');  
end;

它工作正常，并且值被插入到表中。但是，数据分析工具不支持上述语句，因此我改用了函数。以下是函数的代码，它编译。

create or replace
function supercomplex(datainput in VARCHAR2)
return varchar2
is
begin
insert into dumtab values (datainput);
return 'done';
end supercomplex;   

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

select supercomplex('somevalue') from dual;

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

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

编辑

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

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

Answer 1

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

请注意，由于 autonomous transaction是独立的，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

汤姆·凯特 has a nice explanation关于为什么首先引发错误。这是不安全的，因为它可能取决于处理行的顺序。此外，Oracle 不保证该函数每行至少执行一次，最多执行一次。