删除所有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;
/