分布式通过Oracle 获取表唯一ID（字符串）-函数（按日期）

最新推荐文章于 2024-06-17 14:49:01 发布

Clausene

最新推荐文章于 2024-06-17 14:49:01 发布

阅读量437

点赞数

本文链接：https://blog.csdn.net/liuwanglin123/article/details/119774497

版权

-- Create table
create table TB_DIC_SEQUENCE
(
tc_seq_id CHAR(24) not null,
tc_seq_code VARCHAR2(16) not null,
tc_seq_name NVARCHAR2(32),
tc_seq_mnemonic VARCHAR2(32),
tc_seq_prefix VARCHAR2(4),
tc_seq_dtrule VARCHAR2(20),
tc_seq_suffix VARCHAR2(4),
tc_seq_cur NUMBER(16),
tc_seq_min NUMBER(16),
tc_seq_max NUMBER(16),
tc_seq_inc NUMBER(2),
tc_seq_loop_flg CHAR(1),
tc_seq_ord_no INTEGER,
tc_mod_by CHAR(24),
tc_mod_name NVARCHAR2(48),
tc_mod_dt DATE,
tc_seq_cycle_code CHAR(1),
tc_seq_cycle_upddt DATE
)
tablespace TS_HIS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table TB_DIC_SEQUENCE
is '系统序号分配说明表';
-- Add comments to the columns
comment on column TB_DIC_SEQUENCE.tc_seq_id
is '系统序号ID';
comment on column TB_DIC_SEQUENCE.tc_seq_code
is '系统序号编码';
comment on column TB_DIC_SEQUENCE.tc_seq_name
is '系统序号名称';
comment on column TB_DIC_SEQUENCE.tc_seq_mnemonic
is '助记码';
comment on column TB_DIC_SEQUENCE.tc_seq_prefix
is '序号前缀';
comment on column TB_DIC_SEQUENCE.tc_seq_dtrule
is '序号日期规则';
comment on column TB_DIC_SEQUENCE.tc_seq_suffix
is '序号后缀';
comment on column TB_DIC_SEQUENCE.tc_seq_cur
is '序号当前值';
comment on column TB_DIC_SEQUENCE.tc_seq_min
is '序号最小值';
comment on column TB_DIC_SEQUENCE.tc_seq_max
is '序号最大值';
comment on column TB_DIC_SEQUENCE.tc_seq_inc
is '序号增量值';
comment on column TB_DIC_SEQUENCE.tc_seq_loop_flg
is '是否循环标识';
comment on column TB_DIC_SEQUENCE.tc_seq_ord_no
is '显示序号';
comment on column TB_DIC_SEQUENCE.tc_mod_by
is '最后修改人';
comment on column TB_DIC_SEQUENCE.tc_mod_name
is '修改人姓名';
comment on column TB_DIC_SEQUENCE.tc_mod_dt
is '最后修改时间';
comment on column TB_DIC_SEQUENCE.tc_seq_cycle_code
is '循环周期编码(N-无,Y-年,M-月,D-日,W-周)';
comment on column TB_DIC_SEQUENCE.tc_seq_cycle_upddt
is '循环周期更新日期';
-- Create/Recreate primary, unique and foreign key constraints
alter table TB_DIC_SEQUENCE
add constraint PK_TB_DIC_SEQUENCE primary key (TC_SEQ_ID)
using index
tablespace TS_HIS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
alter table TB_DIC_SEQUENCE
add constraint AK_KEY_2_TB_DIC_S unique (TC_SEQ_CODE)
using index
tablespace TS_HIS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Grant/Revoke object privileges
grant select on TB_DIC_SEQUENCE to BIOLAP;
grant select on TB_DIC_SEQUENCE to BIQUERY;
grant select on TB_DIC_SEQUENCE to HISQRY;

create or replace function FN_GETBATSEQNOFIXLEN(v_SEQCODE in varchar2,v_SEQNUM IN number) return varchar2 is
FunctionResult VARCHAR2(4000);
v_SEQ_PREFIX VARCHAR2(4);
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);
v_SEQ_CYCLE_CODE CHAR(1);
v_SEQ_CYCLE_UPDDT DATE;
v_SEQ_CYCLE_UPDDTNEW DATE;
v_i NUMBER(3);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
Begin
FunctionResult:='';
IF v_SEQNUM>160 THEN
return(FunctionResult);
END IF;

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,TC_SEQ_CYCLE_CODE,TC_SEQ_CYCLE_UPDDT
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,v_SEQ_CYCLE_CODE,v_SEQ_CYCLE_UPDDT
from TB_DIC_SEQUENCE where TC_SEQ_CODE=v_SEQCODE;
SELECT decode(v_SEQ_CYCLE_CODE,'Y',decode(trunc(v_SEQ_CYCLE_UPDDT,'YYYY'),trunc(SYSDATE,'YYYY'),v_SEQ_CUR,v_SEQ_MIN)
,'M',decode(trunc(v_SEQ_CYCLE_UPDDT,'MM'),trunc(SYSDATE,'MM'),v_SEQ_CUR,v_SEQ_MIN)
,'D',decode(trunc(v_SEQ_CYCLE_UPDDT),trunc(SYSDATE),v_SEQ_CUR,v_SEQ_MIN)
,'W',decode(to_char(v_SEQ_CYCLE_UPDDT,'YYWW'),to_char(SYSDATE,'YYWW'),v_SEQ_CUR,v_SEQ_MIN),v_SEQ_CUR) INTO v_SEQ_CUR FROM dual;
IF v_SEQ_CUR=v_SEQ_MIN THEN
v_SEQ_CYCLE_UPDDTNEW:=SYSDATE;
ELSE
v_SEQ_CYCLE_UPDDTNEW:=v_SEQ_CYCLE_UPDDT;
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,'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;
for v_i in 0..v_SEQNUM-1 LOOP
IF v_i<>0 THEN
v_SEQ_CUR:=v_SEQ_CUR+v_SEQ_INC;
END IF;
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
FunctionResult:=FunctionResult||v_SEQ_PREFIX||v_SEQ_DTRULE||LPAD(to_char(v_SEQ_CUR),length(to_char(v_SEQ_MAX)),'0')||v_SEQ_SUFFIX;
ELSE
FunctionResult := FunctionResult||'ERR_'||to_char(current_timestamp,'yyyymmddhh24missff6');
END IF;
IF v_i<v_SEQNUM-1 THEN
FunctionResult := FunctionResult||',';
END IF;
END LOOP;
IF v_SEQ_CYCLE_UPDDT IS NULL OR v_SEQ_CYCLE_UPDDTNEW<>v_SEQ_CYCLE_UPDDT THEN
UPDATE TB_DIC_SEQUENCE SET TC_SEQ_CUR=v_SEQ_CUR+v_SEQ_INC,TC_SEQ_CYCLE_UPDDT=v_SEQ_CYCLE_UPDDTNEW WHERE TC_SEQ_CODE=v_SEQCODE;
ELSE
UPDATE TB_DIC_SEQUENCE SET TC_SEQ_CUR=v_SEQ_CUR+v_SEQ_INC WHERE TC_SEQ_CODE=v_SEQCODE;
END IF;
COMMIT;
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_GETBATSEQNOFIXLEN;