oracle 生成序列号存储过程

项目中经常要根据年月日规则生成序列号，简单写了个存储过程可根据需要扩展

一、序列号存储表

-- Create table
create table SYS_GENKEY
(
  DATESTR  VARCHAR2(50),
  MAXCOUNT NUMBER,
  TYPE     NUMBER
)
-- Add comments to the columns
comment on column SYS_GENKEY.DATESTR
  is ' 时间';
comment on column SYS_GENKEY.MAXCOUNT
  is '当天最大值';
comment on column SYS_GENKEY.TYPE
  is '类型';

 

二、功能实现存储过程

CREATE OR REPLACE PROCEDURE "P_GEN_KEY"
(
  retVal out varchar2,  --返回生成序号
  intype in varchar2,  --序号类型
  inCount in varchar2  --序列号补0位数
)
 as
  nowDateStr varchar2(50);--当前年月日
  nowmaxcount number(20); --当天最大序号
 IS_EXIST NUMBER;  --数据是否存在
 begin
      --取到当前年月日
      select to_char(sysdate,'yyyymmdd') into nowDateStr from dual;
      --取到当天最大key
      select max(t.maxcount) into nowmaxcount from sys_genkey t where t.datestr = nowDateStr and t.type = intype;
      --每天加1
      --为空则设置为0
      if nowmaxcount is null then
         nowmaxcount := 0;
      end if;
      nowmaxcount := nowmaxcount + 1;

      --更新临时表
      SELECT COUNT(1) INTO IS_EXIST FROM sys_genkey t where t.datestr = nowDateStr and t.type = intype;
      IF (IS_EXIST != 0) THEN
         update sys_genkey set MAXCOUNT = nowmaxcount where datestr = nowDateStr and type = intype;
      else
         insert into sys_genkey (DATESTR,MAXCOUNT,TYPE)values(nowDateStr,nowmaxcount,intype);
      end if;

      --补0
      select nowDateStr||LPAD (nowmaxcount , inCount , '0') key into retVal from dual;
 end;