ORA-14551: cannot perform a DML operation inside a query

 

ORA-14551: cannot perform a DML operation inside a query

 

〖环境（Environment）〗

OS：SOLARIS 9

DB：ORACLE10G

 

〖现象(Symptom)   〗

Step01：创建函数f_stu。

SQL> create or replace function f_stu

 2 return varchar2

 3 is

 4 username varchar2(20);

 5 predicate varchar2(100);

 6 begin

 7 INSERT INTO student2 (ID，FIRST_NAME，LAST_NAME，MAJOR，CURRENT_CREDITS)

 8 VALUES(001，'MARY'，'KING'，'history'，0);

 9 commit;

 10 return 'ok';

 11 end;

 12 /

 

Function created

这个函数往表student2种插入数据。

 

Step02：在SELECT语句中调用函数f_stu。

SQL> select f_stu（）from dual;

select f_stu（）from dual

 

ORA-14551: cannot perform. a DML operation inside a query

ORA-06512: at "F_STU"，line 7

 

〖原理（Cause）   〗

在SELECT语句中不能对表进行修改（insert，update，delete）。本例中，SELECT语句中的函数试图往表student2中插入数据。

 

〖方法（Action）   〗

要想在SELECT语句中对表进行修改，可以使用自治事务（Autonomous Transactions，AT）。自治事务（Autonomous Transactions）是由另外一个事务调用的，这个叫事务做主事务（Main Transaction，MT）。自治事务（Autonomous Transactions）的提交（commit）和回滚（rollback）并不影响主事务（MT）。

Step01：将创建函数f_stu()的SQL语句

由：

create or replace function f_stu

return varchar2

is

username varchar2(20);

predicate varchar2(100);

begin

INSERT INTO student2(ID，FIRST_NAME，LAST_NAME，MAJOR，CURRENT_CREDITS)

VALUES(001，'MARY'，'KING'，'history'，0);

commit;

return 'ok';

end;

 

改成：

create or replace function f_stu

return varchar2

is

PRAGMA AUTONOMOUS_TRANSACTION;

username varchar2(20);

predicate varchar2(100);

begin

INSERT INTO student2(ID，FIRST_NAME，LAST_NAME，MAJOR，CURRENT_CREDITS)

VALUES(001，'MARY'，'KING'，'history'，0);

commit;

return 'ok';

end;

加入了PRAGMA AUTONOMOUS_TRANSACTION，表示使用自治事务（Autonomous Transactions）。

 

Step02：再次在SELECT语句中使用函数。

SQL> select f_stu() from dual;

F_STU()

--------------------------------------------------------------------------------

ok

SELECT语句执行成功，同时也往表student2中插入了数据。