项目中经常要根据年月日规则生成序列号，简单写了个存储过程可根据需要扩展
一、序列号存储表
-- 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;