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

e351r72l26

　　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%')));