【Oracle】仿Oracle Sequence的自定义年份Sequence(适合任何数据库)

Oracle的Sequence很好用，自增，不用维护是它最大的特点，但碰到以下情况就比较麻烦了。

系统开发时常被要求业务流水号的形式为年份+流水号，如20100001，每年的流水号都必须重1开始。

上面的需求如通过Sequence实现方法有多种：

方法1：只使用一个Sequence，程序判断年份，需要在年后第一天使用前重置Sequence到最小值。

优点：开发方便，不需修改程序。

缺点：需要人工干预，且要有很强的重置Sequence意识，不能直观反映出上一年Sequence的使用情况。

方法2：使用多个Sequence，每年使用一个Sequence，程序根据年份使用不同的Sequence，或程序只适用一个Sequence名称，通过手工干预更改当前使用Sequence名称。

优点：比方法1相对灵活，可不需修改程序，可反映历年Sequence使用情况。

缺点：同样需要人工干预，且也需要在年后第一天使用前更改程序或更改当前使用Sequence名称。

综上所见，Sequence在这里使用不太正确，鉴于这种情况，我想出了使用表和函数模拟出Sequence的方法。

（以下方法同样可以在其他数据库上根据实际情况修改语法实现，这里以Oracle为例）

首先表记录的是Sequence的使用情况，参考Oracle的Sequence内容，设计如下：

YEAR：年份

MINVALUE：最小值

MAXVALUE：最大值

NEXTNUMBER：当前值

INCREASEBY：增量

CYCLE：是否循环

create table IDSEQUENCE
(
  YEAR       NUMBER not null,
  MINVALUE   NUMBER not null,
  MAXVALUE   NUMBER not null,
  NEXTNUMBER NUMBER not null,
  INCREASEBY NUMBER not null,
  CYCLE      NUMBER not null
)

实现类似NextVal的函数：

create or replace function IDNextval return number is
  PRAGMA AUTONOMOUS_TRANSACTION; --Oracle函数中需要添加此参数才能在函数中执行SQL
  Result Number;
  lYear number;
  cursor idsequence_cursor(nYear in varchar2) is select * from idsequence where year=nYear;
  refidsequence idsequence_cursor%rowtype;
begin
  lYear := to_char(sysdate, 'yyyy');
  Result := -1;
  
  open idsequence_cursor(lYear);
  fetch idsequence_cursor into refidsequence;
  if not idsequence_cursor%notfound then
    Result := refidsequence.nextnumber;
    
    if refidsequence.nextnumber >= refidsequence.minvalue then --当前值大于等于最小值
      if refidsequence.nextnumber >= refidsequence.maxvalue then --当前值大于等于最大值
        if refidsequence.cycle = 1 then --循环则当前值等于最小值
          update idsequence set nextnumber=minvalue where year=lYear;
        else --不循环则当前值为最小值-1，即无效值
          update idsequence set nextnumber=minvalue-1 where year=lYear;
        end if;
      else --当前值大于等于最小值小于最大值
        if refidsequence.nextnumber+refidsequence.increaseby > refidsequence.maxvalue then --当前值加增量大于最大值
          update idsequence set nextnumber=minvalue where year=lYear;
        else  
          update idsequence set nextnumber=nextnumber+reffeesdocidsequence.increaseby where year=lYear;
        end if;
      end if;  
      commit;
    end if;
  end if;
  close idsequence_cursor;
  
  return(Result);
end IDNextval;

 实现类似CurrVal的函数：

create or replace function IDCurrval return number is
  Result Number;
  lYear number;
  lCount number;
begin
  Result := -1;
  lYear := to_char(sysdate, 'yyyy');
  
  select count(1) into lCount from idsequence where year=lYear;
  if lCount > 0 then
    select nextnumber into Result from idsequence where year=lYear;
  end if;

  return(Result);
end IDCurrval;

使用的时候，用select idnextval from dual获取下一值，用select idcurrval from dual获取当前值。

对于文章开头需求的应用，只需要在开发前把年份记录一次性添加够就行了，在年份切换时函数自动会使用相应年份的记录。

上面函数有一点需要注意，当当前值大于最大值，且循环开关没设置(CYCLE)时，当前值置为最小值-1，即无效值。

同样，当年份记录没有初始化时，获取到的当前值是-1。