日期:2014-05-16 浏览次数:20468 次
?
删除所有user_tables中的表 ,这样非常不安全,执行改存储过程则会非常的危险. 请谨慎使用.
Set ECHO ON set define off SPOOL logs/create_procedure.log --删除所有表的存储过程; create or replace procedure PROC_DROPTABLE_All as --引用user_tables表中的tableName的类型; tableName user_tables.table_name%type; type ty is record(table_name varchar2(30)); --定义ref类型游标;-强类型 type ref_type is ref cursor return ty; ref_t ref_type; --定义变量存储数量; mycount number(10); begin --打开游标; open ref_t for select table_name from user_tables; loop --从游标中获取一条记录,放入变量中; fetch ref_t into tableName; SELECT COUNT(*) INTO mycount FROM user_tables WHERE TABLE_NAME = tableName; if mycount>0 then execute immediate 'DROP TABLE '||tableName || ' CASCADE CONSTRAINT '; end if; exit when ref_t%notfound; --退出; end loop; close ref_t; end; /
?
----删除单个表的存储过程,需要传入一个表的名称;
create or replace procedure PROC_dropTable(tabName in varchar2) as --引用user_tables表中的tableName的类型; tableName user_tables.table_name%type; mycount number(10); begin --把存储过程传过来的参数,赋值给tableName; tableName:= tabName; SELECT COUNT(*) INTO mycount FROM user_tables WHERE TABLE_NAME = tableName ; if mycount>0 then execute immediate 'DROP TABLE '||tableName; end if; end; /
?
?
?