函数中调用游标并实现对数据的删除

SQL> create table test_table(a int ,v varchar2(20),d date,c varchar2(20)) ;

Table created.

 

SQL> create or replace function test_function return varchar2
  2   is
  3    v_col varchar2(120);
  4  CURSOR cur is
  5  select count(*) from sys.test_table;
  6    BEGIN
  7     open cur;
  8     fetch cur into v_col;
  9     if cur%notfound then
 10        insert into test_table values(4,'a',sysdate,'b');
 11        commit;
 12     else
 13        delete from test_table ;
 14        commit;
 15     end if;
 16     close cur;
 17     return v_col;
 18  END;
 19  /

Function created.

SQL> select test_function from dual ;
select test_function from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SYS.TEST_FUNCTION", line 13

 

SQL> create or replace function test_function return varchar2
  2   is
  3    PRAGMA AUTONOMOUS_TRANSACTION;
  4    v_col varchar2(120);
  5  CURSOR cur is
  6  select count(*) from sys.test_table;
  7    BEGIN
  8     open cur;
  9     fetch cur into v_col;
 10     if cur%notfound then
 11        insert into test_table values(4,'a',sysdate,'b');
 12        commit;
 13     else
 14        delete from test_table ;
 15        commit;
 16     end if;
 17     close cur;
 18     return v_col;
 19  END;
 20  /

Function created.

SQL> select test_function from dual ;

TEST_FUNCTION
--------------------------------------------------------------------------------
0

转载于:https://www.cnblogs.com/wangqianqiannb/p/10594246.html