存储过程生成系统中所有表的序列号

-- 序列号生成表
begin pr_droptable('best_tablesequence'); end;
/
create table best_tablesequence(
name varchar2(32) not null, --表名
value number not null, --序列取值
constraint pk_best_tablesequence primary key (name)
);

-- 生成序列号
create or replace procedure pr_next_index
( in_name in varchar2, --表名
in_minvalue in number, --生成序列时的最小值
in_maxvalue in number, --生成序列时的最大值
out_value out number --输出的序列号
)
is
v_count number;
v_value number;
begin
select count(1) into v_count from best_tablesequence where name=in_name;
if(v_count=0) then
insert into best_tablesequence(name,value) values(in_name,1);
commit;
out_value:=1;
return;
else
select value into v_value from best_tablesequence where name=in_name;
if(v_value+1<=in_maxvalue)
then
update best_tablesequence set value=value+1 where name=in_name;
commit;
select value into out_value from best_tablesequence where name=in_name;
return;
else
update best_tablesequence set value=in_minvalue where name=in_name;
commit;
select value into out_value from best_tablesequence where name=in_name;
return;
end if;
end if;
end pr_next_index;
/