oracle删sequ_[oracle package]sequence处理（批量创建、删除sequence，将sequence恢复至指定值）...

之前对于oracle的包不太熟悉，最近深入学习了plsql中的包。在做项目过程中，操作数据库时，存在大量重复的工作，正好写个package来巩固下所学。

一.先对写的东西碰到的问题作下说明，也方便以后自己回顾解决问题的过程：

1.如何给存储过程或函数传递数组参数

2.如果直接运行创建sequence的语句可以正常运行，用的也是dba权限，但是在存储过程、函数、包中写的创建sequence的语句运行后却提示没有权限

3.如何给序列做回退操作

(1)网上有说无法回退，只能删掉重新创建；

(2)其实还有别的方法，可以利用sequence本身的increment值来处理

下面写的是第二个链接的情况(借鉴的也是第二种。这两个链接实现效果不同，但原理都是一样的，都利用了sequence本身的increment值来处理)

二.接下来，直接上代码：create or replace package myutil_pkg authid current_user as

--定义通用的字符串数组参数类型(传入或返回参数)

type tab_str is table of varchar2(30);

/**

* 将序列的当前值恢复至指定数字

* seqName 序列名称

* num 需要恢复到哪个数字

**/

--使用示例：

/**

* begin

* myutil_pkg.updateSeqToNum('seq_mytest',1);

* end;

*

* 运行select seq_mytest.currval from dual;可以进行验证

*/

procedure updateSeqToNum(seqName varchar2, num number);

/**

* 批量创建序列

* seqNames 序列名称数组

**/

--使用示例：

/*

declare

seqNames myutil_pkg.tab_str:=myutil_pkg.tab_str(null);

begin

seqNames.extend(2,1);

seqNames(1):='seq_mytest1';

seqNames(2):='seq_mytest2';

seqNames(3):='seq_mytest3';

myutil_pkg.createSeqs(seqNames);

end;

*/

procedure createSeqs(seqNames tab_str);

/**

* 批量删除序列

* seqNames 序列名称数组

**/

--使用示例：

/*

declare

seqNames myutil_pkg.tab_str:=myutil_pkg.tab_str(null);

begin

seqNames.extend(2,1);

seqNames(1):='seq_mytest1';

seqNames(2):='seq_mytest2';

seqNames(3):='seq_mytest3';

myutil_pkg.dropSeqs(seqNames);

end;

*/

procedure dropSeqs(seqNames tab_str);

end myutil_pkg;create or replace package body myutil_pkg as

/**

* 将序列的当前值恢复至指定数字

* seqName 序列名称

* num 需要恢复到哪个数字

**/

procedure updateSeqToNum(seqName varchar2, num number) as

n number;

comm_exception exception;

begin

if num < 1 then

raise comm_exception;

else

--https://blog.csdn.net/u010999809/article/details/79943924

--https://blog.csdn.net/pete_emperor/article/details/82853277

--获取序列的下一个值

execute immediate 'select '||seqName||'.nextval from dual' into n;

--修改序列的minvalue参数

execute immediate 'alter sequence '||seqName||' minvalue 1';

if n>1 then

n:=-(n-num); --这里是要恢复到num(num>=1)

--修改increment参数

execute immediate 'alter sequence '||seqName||' increment by '||n;

execute immediate 'select '||seqName||'.nextval from dual' into n;

--恢复increment参数值

execute immediate 'alter sequence '||seqName||' increment by 1';

end if;

end if;

exception

when comm_exception then

raise_application_error(-20001,'序列数字不能小于1');

end updateSeqToNum;

/**

* 批量创建序列

* seqNames 序列名称数组

**/

procedure createSeqs(seqNames tab_str) as

begin

--https://blog.csdn.net/zzkongfu/article/details/7480958

--https://blog.csdn.net/wonder4/article/details/649869

--提示权限不足

--https://blog.csdn.net/jerryitgo/article/details/79220598

--http://www.cnblogs.com/yhoralce/p/6817010.html?utm_source=itdadao&utm_medium=referral

--循环table中的数据

for i in 1 .. seqNames.count loop

--如果存放的不是空字符串，则拼接语句执行创建序列

if seqNames(i) is not null then

--dbms_output.put_line('++++++'||seqNames(i));

execute immediate 'create sequence '||seqNames(i)||' '||

'minvalue 1 start with 1 '||

'increment by 1 cache 20';

end if;

end loop;

end createSeqs;

/**

* 批量删除序列

* seqNames 序列名称数组

**/

procedure dropSeqs(seqNames tab_str) as

begin

for i in 1 .. seqNames.count loop

if seqNames(i) is not null then

execute immediate 'drop sequence '||seqNames(i);

end if;

end loop;

end dropSeqs;

end myutil_pkg;