删除TEST模式下所有的表

删除模式下所有的表

不带参数默认删除SYSDBA模式下所有表##表除外

CREATE OR REPLACE 
PROCEDURE "SYSDBA"."DROP_ALL_TABLE"("VAR1" IN VARCHAR(50) DEFAULT 'SYSDBA') 
    /***默认值删除SYSDBA模式下所有表***/
    AUTHID DEFINER
as
    TAB_NAME VARCHAR(100);
    SQL1     VARCHAR(500);
    SQL2     VARCHAR(500);
    C1 
    CURSOR;
    BEGIN
        SQL1 = 'SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = '''||VAR1||'''and TABLE_NAME not like ''##%'';';
        OPEN C1 FOR SQL1;
        LOOP
            FETCH C1 INTO TAB_NAME;
         
        EXIT                WHEN C1%NOTFOUND;
        SQL2 = 'DROP TABLE "' || VAR1 || '"."' ||TAB_NAME || '";' ;
        EXECUTE IMMEDIATE SQL2;
    END LOOP;
    CLOSE C1;
END