oracle存储过程 的删除数据库,Oracle存储过程-删除所有序列

在跑数据库脚本之前,

往往会重新删除数据库中所有的序列.

下面这种方式就提供一个存储过程，能做到删除所有的序列。

在创建序列之前调用此存储过程即可。

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;

/