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

-- 序列号生成表 

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; 

/