如何自动将结束年份的最后一天的序列号重置? [英] How to reset sequence number every last day of the ending year automatically?

问题描述

是否有可能每年自动重置序列号?例如，在12月31日，序列为3465，在1月1日应为0001.

Is there is any possibility to reset the sequence number every year automatically ? For example on 31 Dec the sequence is 3465, it should be 0001 on 1st January .

现在我通过下面的查询手动将其重置

For now I reset it by below query manually

select p_seq_name.nextval from dual;
alter sequence p_seq_name increment by -3465 minvalue 0;
select p_seq_name.nextval from dual;
alter sequence p_seq_name increment by 1 minvalue 0;

谢谢

推荐答案

首先，您必须创建一个包含所提到的SQL的过程.然后，您必须创建一个计划作业，该作业将执行该过程，并且该作业将每年运行一次.

First you have to create a procedure which contain the SQL as you mention. Then you have to create a schedule job which will execute the procedure and this job will run once in a year.

CREATE OR REPLACE PROCEDURE RESET_SEQUENCE
IS
v_next_value number;
v_sequence_name varchar2(100):='SEQUENCE01';
begin
    execute immediate   'select ' || v_sequence_name || '.nextval from dual' INTO v_next_value;
    execute immediate   'alter sequence ' || v_sequence_name || ' increment by -' || v_next_value || ' minvalue 0';
    execute immediate  'select ' || v_sequence_name || '.nextval from dual' INTO v_next_value;
    execute immediate  'alter sequence ' || v_sequence_name || ' increment by 1 minvalue 0';
END RESET_SEQUENCE;
/



BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'RESET_SEQ_YEARLY'
      ,start_date      => TO_TIMESTAMP_TZ('2016/01/01 10:20:41.299669 Asia/Dacca','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'FREQ=YEARLY;BYMONTH=DEC; BYMONTHDAY=31;BYHOUR=23;BYMINUTE=59;BYSECOND=59'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'begin
                                    RESET_SEQUENCE;
                                    end;'
      ,comments        => 'Last day of the year'
    );
   SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'RESET_SEQ_YEARLY');
END;
/

这篇关于如何自动将结束年份的最后一天的序列号重置?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！