Oracle 函数执行修改和游标传递

ORA-14551：不能在查询语句中执行dml语句，开始误以为函数里不能执行DML或DDL语句，后发现使用自治事务 可解决 。

 

 

目的： 输入查询，清空数据表并返回清空条数（可不要求返回）。

 

一：创建类型

 

create or replace type tab_desc as table of varchar(200);

 

二：创建清空函数   

 

create or replace function func_trunc_tab(l_cursor in sys_refcursor)
  return tab_desc
  pipelined
-- parallel_enable(partition l_cursor by any)
 is
  v_schma   varchar2(200); --对象名
  v_tabname varchar2(200); --表名

  v_row_count   number := 0;
  v_total_count number := 0;

  PRAGMA AUTONOMOUS_TRANSACTION;

begin

  loop
    fetch l_cursor
      into v_schma, v_tabname;
    exit when l_cursor%notfound; 
    
    execute immediate 'select count(1) from ' || v_schma || '.' ||
                      v_tabname
      into v_row_count;  
    v_total_count := v_total_count + v_row_count;    
    pipe row('trancate table ' || v_schma || '.' || upper(v_tabname));
    execute immediate 'truncate table ' || v_schma || '.' || v_tabname;
  end loop;

  pipe row('Delete ' || v_total_count || ' Rows');
  close l_cursor;
  return;

exception
  when others then
    if l_cursor%isopen then
      close l_cursor;
    end if;
    raise;
end func_trunc_tab;

 

 

调用：（清空当前用户里所有以'TMP' 开头的表，下面的sql语句就是函数的参数

 

 

 

SELECT *
  FROM TABLE(FUNC_TRUNC_TAB(CURSOR (SELECT USER AS OWNER, OBJECT_NAME
                               FROM USER_OBJECTS T
                              WHERE T.OBJECT_TYPE = 'TABLE'
                                AND T.OBJECT_NAME LIKE 'TMP%')));