SQL 如何在 Oracle 10g 中每年自动将序列的值重置为 0？

How do I automatically reset a sequence's value to 0 every year in Oracle 10g?

提问by tth

As in the question, how do I automatically reset an Oracle sequence's value back to 0 every year in Oracle 10g?

在问题中，如何在 Oracle 10g 中每年自动将 Oracle 序列的值重置回 0？

I'm using the sequence to generate an identifier in the format YYYY<sequence value>and the sequence value has to be reset to 0 every year.

我正在使用序列以格式生成标识符，YYYY<sequence value>并且每年必须将序列值重置为 0。

YYYYis obtained from java and concatenated with the sequence value from Oracle. The format of the identifier can't be changed due to external 3rd party requirements. Thanks for any help in advance.

YYYY从 java 获得并与来自 Oracle 的序列值连接。由于外部 3rd 方要求，标识符的格式无法更改。提前感谢您的任何帮助。

采纳答案by spencer7593

Sequences aren't really designed to be reset. But there are some cases where resetting a sequence is desirable, for example, when setting up test data, or merging production data back into a test environment. This type of activity is notnormally done in production.

序列并不是真正设计为可以重置的。但是在某些情况下需要重置序列，例如，在设置测试数据或将生产数据合并回测试环境时。这种类型的活动通常不会在生产中进行。

IF this type of operation is going to be put into production, it needs to thoroughly tested. (What causes the most concern is the potential for the reset procedure to be accidentally performed at the wrong time, like, in the middle of the year.

如果这种类型的操作要投入生产，则需要进行彻底的测试。（最令人担忧的是重置程序可能会在错误的时间意外执行，例如在年中。

Dropping and recreating the sequence is one approach. As an operation, it's fairly straightforward as far as the SEQUENCE goes:

删除并重新创建序列是一种方法。作为一项操作，就 SEQUENCE 而言，它相当简单：

    DROP SEQUENCE MY_SEQ;
    CREATE SEQUENCE MY_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 0;

[EDIT] As Matthew Watson correctly points out, every DDL statement (such as a DROP, CREATE, ALTER) will cause an implicit commit. [/EDIT]

[编辑] 正如 Matthew Watson 正确指出的那样，每个 DDL 语句（例如 DROP、CREATE、ALTER）都会导致隐式提交。[/编辑]

But, any privileges granted on the SEQUENCE will be dropped, so those will need to be re-granted. Any objects that reference the sequence will be invalidated. To get this more generalized, you would need to save privileges (before dropping the sequence) and then re-grant them.

但是，授予 SEQUENCE 的任何权限都将被删除，因此需要重新授予这些权限。任何引用该序列的对象都将失效。为了使这更普遍，您需要保存特权（在删除序列之前），然后重新授予它们。

A second approach is to ALTER an existing SEQUENCE, without dropping and recreating it. Resetting the sequence can be accomplished by changing the INCREMENT value to a negative value (the difference between the current value and 0), and then do exactly one .NEXTVAL to set the current value to 0, and then change the INCREMENT back to 1. I've used a this same approach before (manually, in a test environment), to set a sequence to a larger value as well.

第二种方法是改变现有的序列，而不是删除和重新创建它。重置序列可以通过将INCREMENT值改为负值（当前值与0的差值），然后精确执行一次.NEXTVAL将当前值设置为0，然后将INCREMENT改回1来完成。我以前使用过相同的方法（在测试环境中手动），也将序列设置为更大的值。

Of course, for this to work correctly, you need to insureno other sessions reference the sequence while this operation is being performed. An extra .NEXTVAL at the wrong instant will screw up the reset. (NOTE: achieving that on the database side is going to be difficult, if the application is connecting as the owner of the sequence, rather than as a separate user.)

当然，为了使其正常工作，您需要确保在执行此操作时没有其他会话引用该序列。在错误的时刻额外的 .NEXTVAL 会搞砸重置。（注意：如果应用程序作为序列的所有者而不是单独的用户进行连接，那么在数据库端实现这一点将会很困难。）

To have it happen every year, you'd need to schedule a job. The sequence reset will have to be coordinated with the reset of the YYYY portion of your identifier.

要让它每年都发生，你需要安排一份工作。序列重置必须与标识符的 YYYY 部分的重置协调。

Here's an example:

下面是一个例子：

http://www.jaredstill.com/content/reset-sequence.html

http://www.jaredstill.com/content/reset-sequence.html

[EDIT]

[编辑]

UNTESTEDplaceholder for one possible design of a PL/SQL block to reset sequence

UNTESTED占位符为PL / SQL块的一种可能的设计，以复位序列

    declare
      pragma autonomous_transaction;
      ln_increment       number;
      ln_curr_val        number;
      ln_reset_increment number;
      ln_reset_val       number;
    begin

      -- save the current INCREMENT value for the sequence
      select increment_by
        into ln_increment
        from user_sequences
       where sequence_name = 'MY_SEQ';

      -- determine the increment value required to reset the sequence
      -- from the next fetched value to 0
      select -1 - MY_SEQ.nextval into ln_reset_increment from dual;

      -- fetch the next value (to make it the current value)
      select MY_SEQ.nextval into ln_curr from dual;

      -- change the increment value of the sequence to 
      EXECUTE IMMEDIATE 'alter sequence MY_SEQ increment by '
        || ln_reset_increment ||' minvalue 0';

      -- advance the sequence to set it to 0
      select MY_SEQ.nextval into ln_reset_val from dual;

      -- set increment back to the previous(ly saved) value
      EXECUTE IMMEDIATE 'alter sequence MY_SEQ increment by '
        || ln_increment ;
    end;
    /

NOTES:

笔记：

• how to best protect the sequence from access while it's being reset, RENAME it?
• Several test cases to work through here.
• First pass, check normative cases of positive, ascending, increment 1 sequence.
• would a better approach be to create new SEQUENCE, add permissions, rename existing and new sequences, and then re-compile dependencies?

• 如何在重置时最好地保护序列不被访问，重命名它？
• 几个测试用例在这里工作。
• 第一遍，检查正数、升序、增量 1 序列的规范情况。
• 更好的方法是创建新序列、添加权限、重命名现有序列和新序列，然后重新编译依赖项？

回答by Jeffrey Kemp

Just throwing this out there as an idea:

只是把它作为一个想法扔出去：

If you want a solution that requires no ongoing DDL (i.e. no dropping and creating or resetting sequences), or even any jobs, you could consider something like this (this is in principle only, I haven't tested this approach but I'm sure it'll work):

如果您想要一个不需要持续 DDL（即不需要删除和创建或重置序列），甚至不需要任何作业的解决方案，您可以考虑这样的事情（这只是原则上，我没有测试过这种方法，但我是肯定会起作用）：

1. Create a single sequence.

2. Create a reference table, with one row for each year, e.g.

   YEARS (year NUMBER(4,0) PRIMARY KEY, starting_value NUMBER)

3. When you get NEXTVALfrom the sequence, you then have to subtract the starting_valuewhen queried from the YEARS table for the current year. If the year is not found, a new row should be inserted (i.e. the first process run in any given year will insert the new value).

1. 创建单个序列。

2. 创建一个参考表，每年一行，例如

   YEARS（年份 NUMBER(4,0) PRIMARY KEY，starting_value NUMBER）

3. 当您NEXTVAL从序列中获取时，您必须starting_value从当前年份的 YEARS 表中减去查询时间。如果未找到年份，则应插入一个新行（即在任何给定年份中运行的第一个进程将插入新值）。

e.g. a function, e.g. get_year_starting_value (pn_year IN NUMBER) RETURN NUMBERcould query this table and return the starting_valuefor the given year; if it gets NO_DATA_FOUND, it could call a procedure to insert it using the NEXTVALfrom the sequence (committed in an autonomous transaction so that the new value is immediately available to other sessions, and so that the function doesn't fail due to the side effect)

例如一个函数，例如get_year_starting_value (pn_year IN NUMBER) RETURN NUMBER可以查询这个表并返回starting_value给定年份的；如果它得到NO_DATA_FOUND，它可以调用一个过程来使用NEXTVALfrom 序列插入它（在自治事务中提交，以便新值立即可供其他会话使用，并且该函数不会因副作用而失败）

Probably not a solution for all cases, but I think this approach may help in at least some scenarios.

可能不是所有情况的解决方案，但我认为这种方法至少在某些情况下可能会有所帮助。

回答by Mac

Use a job to do the trick. First, create a stored procedure to reset your sequence (I usually go with the DROP/CREATE solution, but you could use spencer7593's trick) :

使用工作来解决问题。首先，创建一个存储过程来重置您的序列（我通常使用 DROP/CREATE 解决方案，但您可以使用spencer7593的技巧）：

CREATE OR REPLACE PROCEDURE my_seq_reset AS
BEGIN
    EXECUTE IMMEDIATE 'DROP SEQUENCE my_seq';
    EXECUTE IMMEDIATE
      'CREATE SEQUENCE my_seq' ||
      '  MINVALUE 1 ' ||
      '  MAXVALUE 999999 ' ||
      '  START WITH 1 ' ||
      '  INCREMENT BY 1 ' ||
      '  NOCACHE';
END;

Then create the job (see herefor the reference) :

然后创建作业（参见此处的参考）：

BEGIN
  dbms_scheduler.create_job(
    job_name        => 'job$my_seq_reset',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'my_seq_reset',
    start_date      => TO_DATE('01-01-09', 'DD-MM-RR'),
    repeat_interval => 'FREQ=YEARLY;BYDATE=0101',
    enabled         => TRUE,
    auto_drop       => FALSE,
    comments        => 'My sequence yearly reset job.'
  );
END;

You're done.

你完成了。

回答by Matthew Watson

I'm not sure there is a good way to do it, this isn't really what sequences are designed for. they are just purely incrementing unique numbers.

我不确定有什么好的方法可以做到，这并不是序列的真正目的。它们只是纯粹增加唯一数字。

2 thoughts come to mind.

2个想法浮现在脑海中。

1. At 12am on the first, reset the sequence, this is hard, because you need to make sure you beat any code.
2. Create a sequence for each year, perhaps even have it in your code to be able to create the sequence, then dynamically call the correct sequence for the year.

1. 在第一个凌晨 12 点，重置序列，这很难，因为您需要确保您击败任何代码。
2. 为每一年创建一个序列，甚至可能将它包含在您的代码中以便能够创建序列，然后动态调用该年份的正确序列。

I'd tend to favor option 2, as its not trying to do anything fancy and is always going to work without fail, any options trying to manipulate the sequence itself are bound to bite you.

我倾向于支持选项 2，因为它不会尝试做任何花哨的事情并且总是会成功，任何试图操纵序列本身的选项都一定会咬你。

回答by tekBlues

First of all, it doesn't seem to be a way to make the sequence restart automatically every year. Read this for reference:

首先，这似乎不是让序列每年自动重启的方法。阅读本文以供参考：

http://www.psoug.org/reference/OLD/sequences.html?PHPSESSID=5949da378678fa6d24b6fcc6eaae9888

http://www.psoug.org/reference/OLD/sequences.html?PHPSESSID=5949da378678fa6d24b6fcc6eaae9888

My approach will be:

我的方法是：

1. create a table with the year and the starting sequence for that year (lets call this table year_seed)

2. create a procedure that receives the year, checks the year_seed table and if it's the first check for the year generates the register with the starting sequence. This procedure must also return the sequence minus the starting sequence for the year.

1. 创建一个包含年份和该年份开始序列的表（我们称这个表为 year_seed）

2. 创建一个接收年份的过程，检查 year_seed 表，如果它是年份的第一次检查，则生成具有起始序列的寄存器。此过程还必须返回序列减去年份的起始序列。

Maybe it's not so simple but I think it's the best solution. Good luck

也许它不是那么简单，但我认为这是最好的解决方案。祝你好运

回答by Leandro Almeida

I have found that was best to create a trigger and a table. The table will contain the year and sequence for the year. The trigger gets the current year, verifies the table, if none registry found, then inserts a new one starting from 1. Otherwise, select the last and increments by one, updating the corresponding table.

我发现最好创建一个触发器和一个表。该表将包含年份和年份顺序。触发器获取当前年份，验证表，如果没有找到注册表，则从 1 开始插入一个新的。否则，选择最后一个并递增 1，更新相应的表。

The Table:

该表：

create table GDRDOCUMENTOSEQ
(
  noano NUMBER(4),
  noseq NUMBER(6)
)
;
alter table GDRDOCUMENTOSEQ
  add unique (NOANO);

The Trigger:

该触发器：

CREATE OR REPLACE TRIGGER "GDRGUIARESSARCIMENTONODOC_BIR"
BEFORE INSERT ON GDR.GDRGUIARESSARCIMENTO
FOR EACH ROW

DECLARE
  lNoAno number;
  lNoSeq number;
  lQtd   number;
begin

  SELECT EXTRACT(YEAR FROM SYSDATE) into lNoAno FROM DUAL;

  SELECT COUNT(0)
    INTO lQtd
    FROM gdr.gdrdocumentoseq ds
   WHERE ds.noano = lNoAno;

  IF lQtd = 0 then
    lNoSeq := 1;
    INSERT INTO GDR.GDRDOCUMENTOSEQ (NOANO, NOSEQ) VALUES (lNoAno, lNoSeq);
  else
    SELECT nvl(max(ds.noseq), 0) + 1
      INTO lNoSeq
      FROM gdr.gdrdocumentoseq ds
     WHERE ds.noano = lNoAno;

    UPDATE GDR.GDRDOCUMENTOSEQ ds
       SET ds.noseq = lNoSeq
     WHERE ds.noano = lNoAno;
  end if;

  :new.nodocumento := SUBSTR(lNoAno, 3) || lpad(lNoSeq, 6, '0');

end;

I have this code running in production from 2016. Currenty state of the table is:

我从 2016 年开始在生产中运行此代码。表的当前状态是：

NOANO   NOSEQ   
2017    1411    
2016    237

回答by Ajnabi

create or replace procedure Reset_Sequence(pSeqName in varchar2) is
  vLastValue number;
begin
  execute immediate 'select ' || pSeqName || '.nextval from dual'
    INTO vLastValue;

  execute immediate 'alter sequence ' || pSeqName || ' increment by -' ||
                    vLastValue || ' minvalue 0';

  execute immediate 'select ' || pSeqName || '.nextval from dual'
    INTO vLastValue;

  execute immediate 'alter sequence ' || pSeqName ||
                    ' increment by 1 minvalue 0';

end;