ORACLE实现自定义序列号生成的方法

实际工作中，难免会遇到序列号生成问题，下面就是一个简单的序列号生成函数

（1）创建自定义序列号配置表如下：

--自定义序列
create table s_autocode
(
 pk1   varchar2(32) primary key,
 atype   varchar2(20) not null,
 owner   varchar2(10) not null,
 initcycle  char(1) not null,
 cur_sernum varchar2(50) not null,
 zero_flg  varchar(2) not null,
 sequencestyle varchar2(50),
 memo   varchar2(60)
);
-- add comments to the columns 
comment on column s_autocode.pk1 is '主键';
comment on column s_autocode.atype is '序列号类型';
comment on column s_autocode.owner is '序列号所有者';
comment on column s_autocode.initcycle is '序列号递增';
comment on column s_autocode.cur_sernum is '序列号';
comment on column s_autocode.zero_flg is '序列号长度';
comment on column s_autocode.sequencestyle is '序列号样式';
comment on column s_autocode.memo is '备注';
-- create/recreate indexes 
create index pk_s_autocode on s_autocode (atype, owner);

（2）初始化配置表，例如：

insert into s_autocode (pk1, atype, owner, initcycle, cur_sernum, zero_flg, sequencestyle, memo)
values ('0a772aedfbed4feea46442003ce1c6a6', 'zdbcontcn', '012805', '1', '200000', '7', '$year$年$orgapp$质字第$ser$号', '质押合同中文编号');

（3）自定义序列号生成函数：

 创建函数：sf_sys_gen_autocode

create or replace function sf_sys_gen_autocode(
   i_atype in varchar2, /*序列类别*/
   i_owner in varchar2 /*序列所有者*/
) return varchar2 is
  /**************************************************************************************************/
  /* procedure name : sf_sys_gen_autocode               */
  /* developed by : wangxf                  */
  /* description : 主要用来生成自定义的序列号             */     
  /* developed date : 2016-10-08                 */
  /* checked by  :                    */
  /* load method : f1-delete insert                */
  /**************************************************************************************************/

  o_autocode varchar2(100);      /*输出的序列号*/
  v_initcycle s_autocode.initcycle%type;  /*序列号递增*/
  v_cur_sernum s_autocode.cur_sernum%type;  /*序列号*/
  v_zero_flag s_autocode.zero_flg%type;  /*序列号长度*/
  v_sequencestyle s_autocode.sequencestyle%type;/*序列号样式*/
  v_seq_num varchar2(100);      /*本次序列号*/
  v_date_year char(4);       /*年份，如2016*/
  v_date_year_month char(6);     /*年份月份，如201610*/
  v_date_date char(8);       /*年份月份日，如20161008*/
  v_date_date_all char(14);     /*完整年份序列，如20161008155732*/
  
  /*
   支持的参数序列：
   $year$ --> 年份
   $year_month$ --> 年份+月份，不含汉子
   $date$ --> 年份+月份+日期，不含汉子
   $date_all$ --> 完整日期，不含汉子
   $orgapp$ --> 所有者
   $ser$ --> 当前序列号
  */
  
  --解决查询事务无法执行dml的问题
  pragma autonomous_transaction;
begin
  -- 查询复核条件的序列号配置
  select t.initcycle,
    t.cur_sernum, 
    t.zero_flg,
    t.sequencestyle 
    into v_initcycle,v_cur_sernum,v_zero_flag,v_sequencestyle
  from s_autocode t where t.atype=i_atype and t.owner=i_owner ;
  
  --格式化当前日期
  select
   to_char(sysdate,'yyyy'),
   to_char(sysdate,'yyyymm'),
   to_char(sysdate,'yyyymmdd'),
   to_char(sysdate,'yyyymmddhh24miss')
   into v_date_year,v_date_year_month,v_date_date,v_date_date_all
  from dual;
  
  -- 日期处理
  o_autocode := replace(v_sequencestyle,'$year$',v_date_year);
  o_autocode := replace(o_autocode,'$year_month$',v_date_year_month);
  o_autocode := replace(o_autocode,'$date$',v_date_date);
  o_autocode := replace(o_autocode,'$date_all$',v_date_date_all);
  
  --所有者处理
  o_autocode := replace(o_autocode,'$orgapp$',i_owner);
  
  --序号处理
  v_seq_num := to_char(to_number(v_cur_sernum)+to_number(v_initcycle));
  
  --反写当前序列号，确保每次都是递增
  update s_autocode t set t.cur_sernum=v_seq_num where t.atype=i_atype and t.owner=i_owner ;
  
  --不满足长度的前面补0
  if length(v_seq_num) < to_number(v_zero_flag)
   then
　　　　　　/*
   loop 
    v_seq_num := '0'||v_seq_num;
   exit when length(v_seq_num) = to_number(v_zero_flag);
   end loop;
　　　　　　*/
 　　　　　　v_seq_num := lpad(v_seq_num,to_number(v_zero_flag),'0');
  end if;
   
  o_autocode := replace(o_autocode,'$ser$',v_seq_num);
  
  commit;
  return o_autocode;
exception 
  --如果没有对应的配置项，则返回error值
  when no_data_found then
   rollback;
   dbms_output.put_line('there is no config as you need...');
   return 'error';
end sf_sys_gen_autocode;

（4）测试：

配置项：$year$年$orgapp$质字第$ser$号

select sf_sys_gen_autocode('zdbcontcn','012805') from dual;

（5） 结果

2016年012805质字第0200001号

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。