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

create or replace function FN_GETSEQNOFIXLEN(v_SEQCODE in varchar2) return varchar2 isFunctionResult 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

BEGINselectTC_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_DTRULEfromTB_DIC_SEQUENCEwhere 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‘)>0THEN

v_SEQ_DTRULE:=REPLACE(v_SEQ_DTRULE,‘yyyy‘,to_char(SYSDATE,‘YYYY‘));

END IF;

IF instr(v_SEQ_DTRULE,‘YYYY‘)>0THEN

v_SEQ_DTRULE:=REPLACE(v_SEQ_DTRULE,‘YYYY‘,to_char(SYSDATE,‘YYYY‘));

END IF;

IF instr(v_SEQ_DTRULE,‘yy‘)>0THEN

v_SEQ_DTRULE:=REPLACE(v_SEQ_DTRULE,‘yy‘,to_char(SYSDATE,‘YY‘));

END IF;

IF instr(v_SEQ_DTRULE,‘YY‘)>0THEN

v_SEQ_DTRULE:=REPLACE(v_SEQ_DTRULE,‘YY‘,to_char(SYSDATE,‘YY‘));

END IF;

IF instr(v_SEQ_DTRULE,‘mm‘)>0THEN

v_SEQ_DTRULE:=REPLACE(v_SEQ_DTRULE,‘mm‘,to_char(SYSDATE,‘MM‘));

END IF;

IF instr(v_SEQ_DTRULE,‘MM‘)>0THEN

v_SEQ_DTRULE:=REPLACE(v_SEQ_DTRULE,‘MM‘,to_char(SYSDATE,‘MM‘));

END IF;

IF instr(v_SEQ_DTRULE,‘dd‘)>0THEN

v_SEQ_DTRULE:=REPLACE(v_SEQ_DTRULE,‘dd‘,to_char(SYSDATE,‘DD‘));

END IF;

IF instr(v_SEQ_DTRULE,‘DD‘)>0THEN

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;