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