PLSQL - 利用自治事务实现按月生成流水序号示例

在序列号的实际应用场景中，常会出现按照某种条件从头开始计算序号的需要，如按年月生成流水号，2019年10月的第一个流水号为19100001，而11月的第一个流水号则为19110001。这种情况下单纯使用序列SEQUENCE则无法实现，而如果每次生成序号时从表中选取最大值又容易造成重复序号。本例采用自治事务与序列相结合的方式，实现按年月生成“YYMM+四位流水”格式的序列号，流水号要求每月重新计算。

1、创建数据表记录已经生成过第一个流水号的月份

CREATE TABLE cux_gl_number_temp
(
  current_month NUMBER UNIQUE
);

2、创建序列

CREATE SEQUENCE cux_gl_number_s 
START WITH 1 
INCREMENT BY 1;

3、创建自治函数生成序列号

CREATE OR REPLACE FUNCTION get_cux_gl_number RETURN VARCHAR2 IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  l_current_month NUMBER := to_char(SYSDATE, 'yymm');
  l_gl_number     VARCHAR2(8);
BEGIN
  BEGIN
    INSERT INTO cux_gl_number_temp
      (current_month)
    VALUES
      (l_current_month);
  
    COMMIT;
  
    BEGIN
      EXECUTE IMMEDIATE 'drop sequence cux_gl_number_s';
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
    EXECUTE IMMEDIATE 'create sequence cux_gl_number_s start with 1 increment by 1';
  
    l_gl_number := l_current_month || lpad(cux_gl_number_s.nextval, 4, '0');
  EXCEPTION
    WHEN OTHERS THEN
      l_gl_number := l_current_month ||
                     lpad(cux_gl_number_s.nextval, 4, '0');
  END;
  RETURN l_gl_number;
END get_cux_gl_number;