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

-- 序列号生成表
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;
/