在跑数据库脚本之前,
往往会重新删除数据库中所有的序列.
下面这种方式就提供一个存储过程,能做到删除所有的序列。
在创建序列之前调用此存储过程即可。
Set ECHO ON
set define off
SPOOL logs/create_procedure.log
--删除所有序列的存储过程;
create or replace procedure PROC_DROPSEQUENCE_All
as
--引用user_sequences表中的sequenceName的类型;
sequenceName user_sequences.sequence_name%type;
type ty is record(sequence_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 sequence_name from user_sequences;
loop
--从游标中获取一条记录,放入变量中;
fetch ref_t into sequenceName;
SELECT COUNT(*) INTO mycount FROM user_sequences WHERE sequence_name = sequenceName;
if mycount>0 then
execute immediate 'DROP SEQUENCE '||sequenceName;
end if;
exit when ref_t%notfound; --退出;
end loop;
close ref_t;
end;
/
可以保存一个.sql文件放在批处理里面执行。
--删除单个序列的存储过程,需要传入一个序列的名称;
create or replace procedure PROC_DROP_SEQUENCE(procName in varchar2)
as
--引用user_tables表中的tableName的类型;
sequenceName user_sequences.sequence_name%type;
mycount number(10);
begin
--把存储过程传过来的参数,赋值给tableName;
sequenceName :=procName;
SELECT COUNT(*) INTO mycount FROM user_sequences WHERE sequence_name = sequenceName;
if mycount > 0 then
execute immediate 'DROP SEQUENCE '||sequenceName;
end if;
end;
/