最近在做序列定时重置时遇到了一些问题,下面一一描述。
1、通过改变步长(通过nextval获取)先把序列初始值改为1,再把步长改回原来的值
create or replace PROCEDURE SEQ_RESET AS --创建重置序列子程序
n NUMBER(10);
TSQL VARCHAR2(100);
BEGIN
execute immediate 'select sequence_name.nextval from dual' into n;
n := - (n-1);
TSQL := 'alter sequence sequence_name increment by '|| n; --修改步长
EXECUTE IMMEDIATE TSQL;
EXECUTE IMMEDIATE 'select sequence_name.nextval from dual' INTO n; --把当前序列设置为1
TSQL := 'alter sequence sequence_name increment by 1'; --把序列步长再修改为原来的1
EXECUTE IMMEDIATE TSQL;
END SEQ_RESET;
2、这样调用存储过程可以实现,但是通过job定时执行存储过程后就会有点问题了。
create or replace procedure proc_auto_exec_job as --创建定时子程序
begin
declare
job number;
BEGIN
DBMS_JOB.SUBMIT(
JOB => job, /*自动生成JOB_ID*/
WHAT => 'SEQ_RESET;', /*需要执行的过程或SQL语句*/
NEXT_DATE => TRUNC(SYSDATE+1), /*初次执行时间,0点0分*/
INTERVAL => 'TRUNC(SYSDATE+1)' /*每天0点0分*/
);
COMMIT;
DBMS_JOB.RUN(job);
end;
end proc_auto_exec_job;
--执行定时存储过程
begin
proc_auto_exec_job;
end;
调用该存储过程时也没问题,调用之后相当于已经经过nextval初始化,可以通过currval获得当前序列值1。
但是,在自动执行重置序列一次后,不手动执行nextval则得到的crrval仍然是重置之前的值;当执行nextval后,再通过currval获取当前序列,则得到的序列值为2。也就是说通过定时子程序调用重置子程序时,初次执行是没问题的,但是后面自动执行时,就会得不到为1的序列值。
3、通过删除重建序列实现序列重置
但是这种方法需要有create sequence权限,如果没有的话,在sqlplus里授权:grant create sequence to username,这里的username就是你的数据库用户名
create or replace procedure SEQ_RESET as
n number(10);
begin
select count(1) into n
from user_sequences t
where t.sequence_name = upper('sequence_name');
if n > 0 then
execute immediate 'drop sequence sequence_name';
end if;
execute immediate 'CREATE SEQUENCE sequence_name
START WITH 1;';
commit;
end;
在这里当用户没有create sequence权限的话就会报错:无法执行作业1。只要用管理员身份授权就可以了。
这两种方法各有优缺,使用的时候可以根据实际情况来定。