通过数据库函数生成自定义规则编码

我们在项目中，经常需要将某个字段的值按照一定规则生成，比如订单编号需要包含：时间+地区+产品类别；且不能重复。那么如何生成呢？

下面介绍一种方式：
生成编码由：地区简称+年月+自增序列 组成 ，本文采用的是oracle数据库

1、建一个针对此编码的序列

create sequence OVERSEE_CODE_SEQ
minvalue 1
maxvalue 9999999999999999999999999999
start with 794
increment by 1
nocache;

2、新建一个函数

CREATE OR REPLACE FUNCTION fn_no_make_oversee(v_type VARCHAR2,v_seq_name VARCHAR2,v_date_type VARCHAR2 DEFAULT 'YYYYMMDD')
/*
  * 参数说明：
  * v_type: 编码前缀
  * v_seq_name：序列名称
  */
 RETURN VARCHAR2 IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_seq NUMBER;
  v_new_num  VARCHAR2(10); --新编码后五位编号
  v_maked_no VARCHAR2(50); --新编码
  v_date_no  VARCHAR2(20); --当前日期编号
  v_sql      VARCHAR2(4000);
BEGIN
    -- 找出其中最大的
  v_sql := 'SELECT ' || v_seq_name || '.nextval FROM dual';
  EXECUTE IMMEDIATE v_sql
    INTO v_seq;
    -- 将当前日期取出
  v_sql := 'SELECT SUBSTR(TO_CHAR(SYSDATE,'''||v_date_type||'''), 1, 8) AS DATE_NO FROM DUAL';
  EXECUTE IMMEDIATE v_sql
    INTO v_date_no;
  v_new_num := to_char(v_seq);
    -- 填充流水号为五位数
  WHILE length(v_new_num) < 2
  LOOP
    v_new_num := '0' || v_new_num;
  END LOOP;
     /*
      * 如果日期相同或者当前表为空
      * 执行流水号为第一个001
      * 否则将上面计算好的流水号加入到新的流水号里面
      */

  v_maked_no := v_type || v_date_no || v_new_num;
    -- 最后返回新的流水号
  RETURN(v_maked_no);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
    RETURN(NULL);
END fn_no_make_oversee;

3、新建一个存储过程，用于定时任务执行

create or replace procedure seq_reset(v_seqname varchar2)
is
n number(10);
tsql varchar2(100);
begin
tsql := 'select '||v_seqname||'.nextval from dual';
  EXECUTE IMMEDIATE tsql INTO n;
n:=-(n);
tsql:='alter sequence '||v_seqname||' increment by '|| n;
execute immediate tsql;
execute immediate 'select '||v_seqname||'.nextval from dual' into n;
tsql:='alter sequence '||v_seqname||' increment by 1';
execute immediate tsql;
end seq_reset;

4、新建一个定时任务，定时将序列的值还原为开始值，可每天还原到开始值，也可每月

在PL/SQL中左侧找到DBMS_JOBS一栏，点击新建

在what值中填写待执行的存储过程，多个可以，隔开
在间隔中填写执行时间策略；
间隔/interval是指上一次执行结束到下一次开始执行的时间间隔，当interval设置为null时，该job执行结束后，就被从队列中删除。假如我们需要该job周期性地执行，则要用‘sysdate＋m’表示。
（1）.每分钟执行
Interval => TRUNC(sysdate,‘mi’) + 1/ (24*60)

每小时执行

Interval => TRUNC(sysdate,‘hh’) + 1/ (24)

（2）.每天定时执行
例如：每天的凌晨1点执行
Interval => TRUNC(sysdate+ 1) +1/ (24)

（3）.每周定时执行
例如：每周一凌晨1点执行
Interval => TRUNC(next_day(sysdate,‘星期一’))+1/24

（4）.每月定时执行
例如：每月1日凌晨1点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24

（5）.每季度定时执行
例如每季度的第一天凌晨1点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),‘Q’) + 1/24

（6）.每半年定时执行
例如：每年7月1日和1月1日凌晨1点
Interval => ADD_MONTHS(trunc(sysdate,‘yyyy’),6)+1/24

（7）.每年定时执行
例如：每年1月1日凌晨1点执行
Interval =>ADD_MONTHS(trunc(sysdate,‘yyyy’),12)+1/24

4、通过SQL获得该自定义编码

SELECT FN_NO_MAKE2('TH','OVERSEE_CODE_SEQ','YYYYMM') CASENO FROM DUAL

备注：TH为地区简称，OVERSEE_CODE_SEQ为序列名称，YYYYMM为年月

最终得到的编码：TH201908815
最后的815是自增序列生成的，序列规则可自定义

说明：此方式只是提供一钟方式，如需扩展可修改参数或者修改存储过程