Oracle 生成序列号存储过程

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

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

一、序列号存储表

— Create table
create table SYS_GENKEY
(
DATESTR VARCHAR2(50),
MAXCOUNT NUMBER,
TYPE NUMBER
)
— Add comments to the columns
comme来&源gao@dai!ma.com搞$代^码%网nt 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;

，

喜欢 (0)赏

[搞代码]

分享 (0)