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;