PLSQL 配置工具的使用

一、准备工作：1.存储过程DelDBTable.prc；2.sql文件 calldel.sql

DelDBTable.prc

create or replace procedure DelDBTable
as
  sSql                   VARCHAR2(1000);  

  cursor cursor_DATABASE_LINK_name is
    select DB_LINK from user_db_links;
    dblinkname ALL_DB_LINKS.DB_LINK%type;
  cursor cursor_table_name is
    SELECT table_name FROM User_Tables;
    tablename ALL_TABLES.table_name%type;
  cursor cursor_view_name is
    SELECT view_name from User_Views ;
    viewname All_Views.view_name%type;
  cursor cursor_ssequence_name is
    SELECT sequence_name FROM User_Sequences;
    sequencename all_sequences.sequence_name%type; 
  cursor cursor_trigger_name is
    SELECT trigger_name FROM User_Triggers ;
    triggername all_triggers.trigger_name%type;

begin  
  dbms_output.enable(100000); 
open cursor_DATABASE_LINK_name;
  loop
    fetch cursor_DATABASE_LINK_name
      into dblinkname;
    exit when cursor_DATABASE_LINK_name%notfound;
    dbms_output.put_line(dblinkname);
    sSql := 'drop database link '||dblinkname;
    dbms_output.put_line(sSql);
    execute immediate sSql;
    end loop;       
close cursor_DATABASE_LINK_name;

open cursor_table_name;
  loop
    fetch cursor_table_name
      into tablename;
    exit when cursor_table_name%notfound;
    dbms_output.put_line(tablename);
    sSql := 'drop table '||tablename;
    dbms_output.put_line(sSql);
    execute immediate sSql;
    end loop;       
close cursor_table_name;

open cursor_view_name;
  loop
    fetch cursor_view_name
      into viewname;
    exit when cursor_view_name%notfound;
    dbms_output.put_line(viewname);
    sSql := 'drop view '||viewname;
    dbms_output.put_line(sSql);
    execute immediate sSql;
    end loop;       
close cursor_view_name;

open cursor_ssequence_name;
  loop
    fetch cursor_ssequence_name
      into sequencename;
    exit when cursor_ssequence_name%notfound;
    dbms_output.put_line(sequencename);
    sSql := 'drop sequence '||sequencename;
    dbms_output.put_line(sSql);
    execute immediate sSql;
    end loop;       
close cursor_ssequence_name;

open cursor_trigger_name;
  loop
    fetch cursor_trigger_name
      into triggername;
    exit when cursor_trigger_name%notfound;
    dbms_output.put_line(triggername);
    sSql := 'drop trigger '||triggername;
    dbms_output.put_line(sSql);
    execute immediate sSql;
    end loop;       
close cursor_trigger_name;

end DelDBTable;

/

calldel.sql

exec DelDBTable;
exit;

二、点击PLSQL工具--配置工具

     1、  点击新增图标，在详细资料【一般】选项卡中输入以下信息

     【描述】：删除所有表

      【可执行文件、脚本】：D:\oracle\product\10.2.0\client_1\BIN\sqlplusw.exe

      【参数】 #username/#password@ora10g @calldel.sql

      【默认路径】：D:\data\脚本\       (注：步骤一中2个文件存放的位置)

 

            

     2、切换到【按钮】选项卡

           勾选【工具烂按钮】

          【描述】： 删除备份表

          【图像】：（注：随便选一个）

 

三、把刚才设置的配置工具，添加到工具栏，方便使用

 

如下图，右击工具烂，点击定制

把新增的图标放到已选按钮这边