批量删除表

--批量删除表
--方法1
select 'truncate table ' || OBJECT_NAME || ';'
  from user_objects
where OBJECT_NAME like 'KY_PRJ_TEMP%'
   and OBJECT_TYPE = 'TABLE';
--command windows 下执行查询结果
--方法2
--select * from tab
SELECT 'truncate table ' || t.tname || ';'
    FROM tab t
     WHERE t.tname like 'KY_PRJ_TEMP%' AND t.tabtype = 'TABLE';
----
--附  输出表
---
set serveroutput on;

create or replace procedure dada_truncate_tables(tablename in String) as
  table_names VARCHAR2(4000);
  --v_pos NUMBER :=1;
  CURSOR c IS select tname from tab where upper(tname) like upper(tablename)||'%' ;
BEGIN
FOR cc IN c LOOP
  --EXECUTE IMMEDIATE ' truncate table '||cc.tname;
  table_names := cc.tname||','||table_names;
  --dbms_output.put_line(cc.tname);
  if LENGTH(table_names) > 200 then
           dbms_output.put_line(table_names);
           table_names :='';
      end if;
  END LOOP;  
end;