ORA-14551：不能在查询语句中执行dml语句，开始误以为函数里不能执行DML或DDL语句，后查到前辈通过采用自治事务解决，在此基础上演例 传递SQL语句对数据进行操作。

?

?

目的： 查询出需要清空的表，然后全部清空，要求返回清空信息。

?

一：创建类型

?

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 'truncate table ' || v_schma || '.' || v_tabname;
    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));
  end loop;

  pipe row('All Table Row Count is ' || v_total_count);
  close l_cursor;
  return;

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

?

?

调用：（清空当前用户里所有以'ZY_TMP' 开头的表，这里sql 语句就是FUNC_TRUNC_TAB函数的参数）

?

?

?

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

COLUMN_VALUE
trancate table ZHANGYONG.ZY_TMP_001
trancate table ZHANGYONG.ZY_TMP_002
trancate table ZHANGYONG.ZY_TMP_003
All Table Row Count is 0

?

?