Oracle错误:无法在查询中执行DML

Question

为什么会出现以下错误？

Error starting at line 1 in command:

select FUNC from dual
Error report:
SQL Error: ORA-14551: cannot perform a DML operation inside a query 
ORA-06512: at "ANONYMOUS.FUNC", line 15
14551. 00000 -  "cannot perform a DML operation inside a query "
*Cause:    DML operation like insert, update, delete or select-for-update
           cannot be performed inside a query or under a PDML slave.
*Action:   Ensure that the offending DML operation is not performed or
           use an autonomous transaction to perform the DML operation within
           the query or PDML slave.

功能：

create or replace function FUNC
return types.ref_cursor
AS
result_set types.ref_cursor;
alarm ofalarmmessages.ALARMID% TYPE;
username ofalarmmessages.USERNAME% TYPE;
alarmmsg ofalarmmessages.ALARMMESSAGE% TYPE;
dvice ofalarmmessages.DEVICEID% TYPE;
begin
OPEN result_set FOR
       SELECT USERNAME,ALARMID,ALARMMESSAGE,DEVICEID
    FROM   ofalarmmessages where newoldflag='N';
LOOP
    FETCH  result_set into username,alarm,alarmmsg,dvice;
        update ofalarmmessages set newoldflag ='Y' where alarmid= alarm;
END LOOP;
RETURN result_set;
CLOSE result_set;
END;
/
show errors;

Answer 1

这里有两个问题。您会得到'ORA-14551‘错误，因为您在SELECT语句中执行函数，并且错误消息清楚地指出，当函数执行DML时，我们不能这样做。此问题的解决方案是在PL/SQL或SQL*Plus中执行它。

另一个问题是引用游标是指向结果集的指针，只能提取一次。您的函数遍历引用游标，然后返回它。当您尝试对返回的引用游标执行某些操作时，这将导致错误，因为它不再有效。

哦，顺便说一下，函数中RETURN语句后面的任何代码都不会被执行。

Answer 2

您可以避免在SELECT语句中使用函数，如下所示：

var rc refcursor
exec :rc := func;

Answer 3

你可以把它叫做

SET SERVEROUTPUT ON
EXEC DBMS_OUTPUT.PUT_LINE(your_fn_name(your_fn_arguments))