mysql 存储过程 序列_Oracle 生成序列号存储过程

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

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

一、序列号存储表

-- 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;

，

本文原创发布php中文网，转载请注明出处，感谢您的尊重！