删除oracle一批表 批量删除oracle中表 -清理回收站

declare
type refcursor is ref cursor;
  v_cursor refcursor;
  v_table varchar2(50);
begin
  open v_cursor for  SELECT TABLE_NAME FROM  user_tables t where t.TABLE_NAME  like '%TPCRM_%';
   loop
      fetch v_cursor into v_table;
          exit when v_cursor%notfound; 
          execute immediate 'drop table '||v_table||' purge';
    end loop;
 end ;
/

--过程删除

  PROCEDURE sp_droptable (stablename   VARCHAR2,
                          squerytype   VARCHAR2,
                          sothercon    VARCHAR2)
  IS
  BEGIN
    IF TRIM (stablename) = '' OR stablename IS NULL
    THEN
      RETURN;
    END IF;

    IF squerytype = '1'
    THEN
      FOR t IN (SELECT 'truncate table ' || table_name trun_tbl,
                       'drop table ' || table_name || ' '
                       || sothercon drop_tbl
                  FROM user_tables
                 WHERE table_name LIKE UPPER (stablename))
      LOOP
         EXECUTE IMMEDIATE t.trun_tbl;

         EXECUTE IMMEDIATE t.drop_tbl;
      END LOOP;
    ELSE
      EXECUTE IMMEDIATE 'truncate table ' || stablename;

      EXECUTE IMMEDIATE 'drop table ' || stablename || sothercon;
    END IF;
  END sp_droptable;