自增序列号生成与重置

需求场景：生成自增的6位数（如：000001-999999），并且每年都要重新开始（000001开始）。

实现思路：Oracle创建自增序列、重置。

    1、Oracle创建自增序列。

--获取用户下的所有序列
select * from dba_sequences t where sequence_owner='BUSINESS';
--创建自增序列
create sequence ZXG_SEQ increment by 1 start with 1 minvalue 0 maxvalue 999999;
--修改序列
alter  sequence ZXG_SEQ increment by 2 minvalue 0 maxvalue 999999;
--删除序列
drop sequence ZXG_SEQ;
--获取自增序列
select ZXG_SEQ.Nextval XLH from dual;
--获取左侧补0：lpad('AAA',6,'0')序列
select lpad(ZXG_SEQ.Nextval,6,'0') XLH from dual;
--获取右侧补0：rpad('AAA',6,'0')序列
select rpad(ZXG_SEQ.Nextval,6,'0') XLH from dual;

    2、序列重置

         2.1、Oracle创建一个存储过程来重置序列号，Oracle定时器执行。

--查看存储过程
select object_name,object_type from dba_objects where object_type='PROCEDURE'
 and object_name = 'ZXG_SEQ_RESET';
--删除储存过程
DROP PROCEDURE ZXG_SEQ_RESET;
--创建储存过程重置序列号
CREATE OR REPLACE PROCEDURE ZXG_SEQ_RESET(v_seqname in varchar2) as
  --定义变量n 
  n number(10);
  --定义变量tsql
  tsql varchar2(100);
BEGIN
  --处理逻辑
  execute immediate 'select '||v_seqname||'.nextval from dual' into n;
  n:=-n;
  tsql:='alter sequence '||v_seqname||' increment by '|| n;
  execute immediate tsql;
  execute immediate 'select '||v_seqname||'.nextval from dual' into n;
  tsql:='alter sequence '||v_seqname||' increment by 1';
  execute immediate tsql;
  --提交事务
  commit;
Exception
  When others then
    Dbms_output.Put_line(sqlerrm);--打印输出错误
    Rollback;--回滚事务
END;

     

--定时器执行存储过程
declare 
ZXG_JOB number; 
begin 
dbms_job.submit(ZXG_JOB, 'ZXG_SEQ_RESET(''ZXG_SEQ'');',ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24); 
end
commit;

　　

或：Oracle命令窗口执行脚本
--执行存储过程
exec ZXG_SEQ_RESET('ZXG_SEQ');
--删除定时器
exec dbms_job.remove(43);//select job,broken,what,interval,t.* from user_jobs t;

　　

       2.2、JAVA代码里执行重置

//定时器：每年1月1日00时01分00秒
@Scheduled(cron = "0 1 0 1 1 ?")
public void resetSequence(){
	this.resetSequenceByName("ZXG_SEQ");
}

//重置序列号
private void resetSequenceByName(String sequencename){
	//获取下一个序列号 XLH
	StringBuffer nextSeqSql = new StringBuffer();
	nextSeqSql.append("select "+sequencename+".nextval XLH from dual");
	List<Map<String, Object>> list = this.jdbcTemplate.queryForList(nextSeqSql.toString());
	if(list.size() > 0) {
		//修改序列号递减 -XLH
		StringBuffer alertSeqSql = new StringBuffer();
		alertSeqSql.append("alter sequence "+sequencename+" increment by -"+Integer.parseInt(list.get(0).get("XLH").toString()));
		this.jdbcTemplate.execute(alertSeqSql.toString());

		//序列号重置为 0
		this.jdbcTemplate.queryForList(nextSeqSql.toString());

		//修改序列号递增 1
		StringBuffer alertSeqToOneSql = new StringBuffer();
		alertSeqToOneSql.append("alter sequence "+sequencename+" increment by 1 ");
		this.jdbcTemplate.execute(alertSeqToOneSql.toString());
	}
}

　　注：2.2 方式使用的持久层是 JdbcTemplate，Maven：

<!-- jdbcTemplate -->
<dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>