【Oracle】PL/SQL制作唯一标识，固定字段+日期+流水码，流水码每日重置
首先创建序列
create sequence sequence_name
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;
对序列进行重置存储过程
create or replace procedure update_nextval as
n integer;
tsql varchar2(100);
begin
execute immediate 'select sequence_name.nextval from dual'
into n;
n := -(n-1);
tsql := 'alter sequence sequence_name increment by ' || n;
execute immediate tsql;
execute immediate 'select sequence_name.nextval from dual'
into n;
tsql := 'alter sequence sequence_name increment by 1';
execute immediate tsql;
end;
将重置序列加入到定时任务job中
declare
job number;
BEGIN
DBMS_JOB.SUBMIT(
JOB => job, /*自动生成JOB_ID，放着就行，不用管*/
WHAT => 'update_nextval;',
INTERVAL => 'TRUNC(SYSDATE + 1)'
);
commit;
end;
拼接TEXT+日期+流水码的格式的标识
create or replace procedure code_pro is
p_seq integer;
p_date varchar2(50);
p_seqcode varchar2(50);
p_code varchar2(50);
begin
--组合成TEXT+日期+流水码的格式的标识
p_seq := sequence_name.nextval;
select lpad(p_seq, '3', '0') into p_seqcode from dual;
select replace(to_char(sysdate, 'yyyy-mm-dd'), '-', '')
into p_date
from dual;
p_code := 'TEXT-' || p_date || '-' || p_seqcode;
dbms_output.put_line(p_zddh);
end;
这样就制作了一个每天都会更新流水码的标识
使用的话，java就call包，将存储过程改成函数或者作相应调整即可，sql就配合需求修改下存储过程里面的内容即可