Question

在oracle存储过程中建立序列

procedure gl_voucher_noadd(accyear varchar2,accmonth varchar2,info in out varchar2)
is
x number ;
begin
select max(num) into x from gl_voucher where year=accyear and period=accmonth and pk_group='0001V210000000004MCY';
if x=null then x := '500';
else x :=x;
execute immediate'drop SEQUENCE gl_voucher_num'; --删除序列
commit;
end if;
execute immediate 'create SEQUENCE gl_voucher_num INCREMENT BY 1 START WITH 500 NOMAXVALUE NOCYCLE CACHE 10 return varchar2 authid current_user';

commit;
-- 表头
execute immediate 'update gl_voucher a set num =gl_voucher_num.nextval'; /*where nvl(a.free9,'~')!='~' and year=accyear and period=accmonth and pk_group='0001V210000000004MCY'';
-- 表体*/
execute immediate 'update gl_detail a set nov =(select num from gl_voucher where pk_voucher=a.pk_voucher)'
/* where nvl(a.free9,'~')!='~' and yearv=accyear and periodv=accmonth and pk_group='0001V210000000004MCY''*/;

info:=info||'序列改变影响行数：'||SQL%rowcount||'; ';
exception
when others then
info:=info||'更新序列号失败！';

end;
单步执行的时候运行到execute immediate 'create SEQUENCE gl_voucher_num INCREMENT BY 1 START WITH 500 NOMAXVALUE NOCYCLE CACHE 10 return varchar2 authid current_user';这里的时候直接运行exception 然后就报错更新序列号失败！ 为什么

Answer 1

序列主要是用来生成固定的编号的，如：每次递增一即可。
create sequence SEQ_ID
minvalue 1
maxvalue 99999999
start with 1
increment by 1
cache 30
cycle;
之后执行：
insert into tab (auto_id) values (SEQ_ID.Nextval);
备注：sequence 是一个自增长的序列，每次调用nextval的时候会自动增加，可以定义起始值，最大值，增量，cycle是表示循环，即到最大值后从起始值重新开始。 每次执行都是会自增的。

Answer 2

execute immediate 'create SEQUENCE gl_voucher_num INCREMENT BY 1 START WITH 500 NOMAXVALUE NOCYCLE CACHE 10' return varchar2 authid current_user; 语法错误，注意引号位置

Answer 3

好 用函数实现