序列问题
create sequence BOSDATA.GET_PK_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1001630254
increment by 1
cache 2000
cycle;
select 'EAR' || to_char(sysdate,'YYYYMMDD') || lpad(GET_PK_SEQ.Nextval,9,0), lpad(GET_PK_SEQ.Nextval,9,0),GET_PK_SEQ.Nextval from dual;
EAR20171107100167825 100167825 1001678254
当序列GET_PK_SEQ.Nextval取值超过9位,是1001678254时候,lpad(GET_PK_SEQ.Nextval,9,0)=100167825,取值是从左往右取值,会忽略个位数,然后主键中 to_char(sysdate,'YYYYMMDD')=20171107只会是当天的值不变
,后面序列取值忽略个位数后也会不变,导致了主键冲突 unique constraint violated,建议序列不要随意建,需要考虑位数,是否循环
建议
select sysdate,'EAR' || to_char(sysdate,'YYMMDDhh24') || lpad(GET_PK_SEQ.Nextval,9,0) from dual;
有两个地方 改进
1、在 to_char(sysdate,'YYMMDDhh24'),可以去掉年的两位,精确到小时,
2、序列方面,可以设置序列的位数最大是9位,循环