Oracle 获取表唯一ID（字符串）-函数（按日期）

create or replace function FN_GETSEQNOFIXLEN(v_SEQCODE in varchar2) return varchar2 is
  FunctionResult VARCHAR2(24);
  v_SEQ_PREFIX VARCHAR2(10);
    v_SEQ_DTRULE VARCHAR2(20);
  v_SEQ_SUFFIX VARCHAR2(4);
  v_SEQ_MIN NUMBER(16);
  v_SEQ_MAX NUMBER(16);
  v_SEQ_CUR NUMBER(16);
  v_SEQ_INC NUMBER(2);
  v_SEQ_LOOP_FLG CHAR(1);
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  BEGIN
        select TC_SEQ_PREFIX,TC_SEQ_SUFFIX,TC_SEQ_MIN,TC_SEQ_MAX,TC_SEQ_CUR,TC_SEQ_INC,TC_SEQ_LOOP_FLG,TC_SEQ_DTRULE
      into v_SEQ_PREFIX,v_SEQ_SUFFIX,v_SEQ_MIN,v_SEQ_MAX,v_SEQ_CUR,v_SEQ_INC,v_SEQ_LOOP_FLG,v_SEQ_DTRULE from TB_DIC_SEQUENCE
        where TC_SEQ_CODE=v_SEQCODE;
    v_SEQ_CUR:=v_SEQ_CUR+v_SEQ_INC;
    IF v_SEQ_CUR>=v_SEQ_MAX THEN
      IF v_SEQ_LOOP_FLG=‘Y‘ OR v_SEQ_LOOP_FLG=‘y‘ OR v_SEQ_LOOP_FLG=‘1‘ THEN
        v_SEQ_CUR:=v_SEQ_MIN;
      ELSE
        v_SEQ_CUR:=NULL;
      END IF;
    END IF;
    IF v_SEQ_CUR IS NOT NULL THEN
      UPDATE TB_DIC_SEQUENCE SET TC_SEQ_CUR=v_SEQ_CUR WHERE TC_SEQ_CODE=v_SEQCODE;
            IF instr(v_SEQ_DTRULE,‘yyyy‘)>0 THEN
                v_SEQ_DTRULE:=REPLACE(v_SEQ_DTRULE,‘yyyy‘,to_char(SYSDATE,‘YYYY‘));
            END IF;
            IF instr(v_SEQ_DTRULE,‘YYYY‘)>0 THEN
                v_SEQ_DTRULE:=REPLACE(v_SEQ_DTRULE,‘YYYY‘,to_char(SYSDATE,‘YYYY‘));
            END IF;
            IF instr(v_SEQ_DTRULE,‘yy‘)>0 THEN
                v_SEQ_DTRULE:=REPLACE(v_SEQ_DTRULE,‘yy‘,to_char(SYSDATE,‘YY‘));
            END IF;
            IF instr(v_SEQ_DTRULE,‘YY‘)>0 THEN
                v_SEQ_DTRULE:=REPLACE(v_SEQ_DTRULE,‘YY‘,to_char(SYSDATE,‘YY‘));
            END IF;
            IF instr(v_SEQ_DTRULE,‘mm‘)>0 THEN
                v_SEQ_DTRULE:=REPLACE(v_SEQ_DTRULE,‘mm‘,to_char(SYSDATE,‘MM‘));
            END IF;
            IF instr(v_SEQ_DTRULE,‘MM‘)>0 THEN
                v_SEQ_DTRULE:=REPLACE(v_SEQ_DTRULE,‘MM‘,to_char(SYSDATE,‘MM‘));
            END IF;
            IF instr(v_SEQ_DTRULE,‘dd‘)>0 THEN
                v_SEQ_DTRULE:=REPLACE(v_SEQ_DTRULE,‘dd‘,to_char(SYSDATE,‘DD‘));
            END IF;
            IF instr(v_SEQ_DTRULE,‘DD‘)>0 THEN
                v_SEQ_DTRULE:=REPLACE(v_SEQ_DTRULE,‘DD‘,to_char(SYSDATE,‘DD‘));
            END IF;
      FunctionResult:=v_SEQ_DTRULE||LPAD(to_char(v_SEQ_CUR),length(to_char(v_SEQ_MAX)),‘0‘)||v_SEQ_SUFFIX;
      COMMIT;
    ELSE
      FunctionResult := ‘ERR_‘||to_char(current_timestamp,‘yyyymmddhh24missff6‘);
    END IF;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        FunctionResult:=‘PFM_‘||to_char(current_timestamp,‘yyyymmddhh24missff6‘);
      WHEN OTHERS THEN
        FunctionResult := ‘OTH_‘||to_char(current_timestamp,‘yyyymmddhh24missff6‘);
        ROLLBACK;
  END;
  return(FunctionResult);
end FN_GETSEQNOFIXLEN;