将Oracle Sequence重置为MIN VALUE = 1，并且STARTING number从1开始 [英] Reset Oracle Sequence to have MIN VALUE = 1 and STARTING number from 1

问题描述

我在将Oracle Sequence重置为MIN VALUE = 1且开始使用的下一个数字为1时遇到问题.

I have a problem resetting Oracle Sequence to have MIN VALUE = 1 and starting next number used is 1.

我遵循了这个问题的答案: 如何在Oracle中重置序列?

I have followed through the answer of this question: How do I reset a sequence in Oracle?

   create or replace procedure reset_seq( p_seq_name in varchar2 ) is
        l_val number; 
begin
        execute immediate
        'select ' || p_seq_name || '.nextval from dual' INTO l_val;

        execute immediate
        'alter sequence ' || p_seq_name || ' increment by -' || l_val || 
                                                              ' minvalue 0';

        execute immediate
        'select ' || p_seq_name || '.nextval from dual' INTO l_val;

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

但是，问题在于上面的代码正在用min值= 0重置序列，下一个要使用的val是2.

But, the problem is the code above is resetting the sequence with min value = 0, and the next val to be used is 2.

完成重置后，是否可以将min_value设置为1，将nextval设置为1?

Is it possible to set the min_value = 1, and nextval to be 1, after the reset is done?

我试图进行设置，但出现如下所示的错误提示:

I tried to set it but getting the error mentioning approximately as below:

当前值不能大于最小值.

The current value cannot be higher then Minimum Value.

谢谢！

推荐答案

完成重置后，是否可以将最小值设置为1，并将下一个val设置为1?

Is it possible to set the min value = 1, and next val to be 1, after the reset is done?

您可以分两个步骤进行操作:

You could do it in two steps:

• increment_by值比序列的当前值小一.
• 将crement_by重置为1.

逻辑是，您不应将序列递减为零，因为所需的 minvalue 是1，因此 nextval不能小于最小值.

The logic is that, you shouldn't decrement the sequence back to zero, since the minvalue you want is 1, so, the nextval cannot be less than the minval.

例如，

SQL> CREATE SEQUENCE s START WITH 20 MINVALUE 0 INCREMENT BY 1;

Sequence created.

SQL> SELECT s.nextval FROM dual;

   NEXTVAL
----------
        20

SQL> ALTER SEQUENCE s INCREMENT BY -19 MINVALUE 1;

Sequence altered.

SQL> SELECT s.nextval FROM dual;

   NEXTVAL
----------
         1

SQL> ALTER SEQUENCE s INCREMENT BY 1 MINVALUE 1;

Sequence altered.

SQL> SELECT s.nextval FROM dual;

   NEXTVAL
----------
         2

SQL> SELECT min_value, increment_by FROM user_sequences WHERE sequence_name='S';

 MIN_VALUE INCREMENT_BY
---------- ------------
         1            1

因此，最小值和 increment_by 现在分别重置为1.在再次将 increment_by 重置为1之前，下一个值只能为1.

So, the min_value and increment_by is now reset to 1 respectively. The nextvalue could be 1 only once before you reset the increment_by to 1 again.

因此，我看不到要实现的任何实际用途.但是，可以按照上面的说明进行操作.

So, I don't see any practical use of what you want to achieve. However, it could be done as demonstrated above.

要在您的过程中实现上述逻辑，请执行以下操作:

To implement the above logic in your procedure, do as following:

设置

SQL> DROP SEQUENCE S;

Sequence dropped.

SQL> CREATE SEQUENCE s START WITH 20 MINVALUE 0 INCREMENT BY 1;

Sequence created.

SQL> SELECT s.nextval FROM dual;

   NEXTVAL
----------
        20

将您的过程修改为:

SQL> CREATE OR REPLACE PROCEDURE reset_seq(
  2      p_seq_name IN VARCHAR2 )
  3  IS
  4    l_val NUMBER;
  5  BEGIN
  6    EXECUTE IMMEDIATE 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
  7    l_val := l_val - 1;
  8    dbms_output.put_line('l_val = '||l_val);
  9    EXECUTE IMMEDIATE 'alter sequence ' ||
 10                       p_seq_name || ' increment by -' || l_val || ' minvalue 1';
 11    EXECUTE IMMEDIATE 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
 12    dbms_output.put_line('1st Nextval is '||l_val);
 13    EXECUTE IMMEDIATE 'alter sequence ' || p_seq_name ||
 14                      ' increment by 1 MINVALUE 1';
 15    EXECUTE IMMEDIATE 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
 16    dbms_output.put_line('2nd Nextval is '||l_val);
 17  END;
 18  /

Procedure created.

SQL> SET serveroutput ON
SQL> EXEC reset_seq('S');
l_val = 20
1st Nextval is 1
2nd Nextval is 2

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT min_value, increment_by FROM user_sequences where sequence_name='S';

 MIN_VALUE INCREMENT_BY
---------- ------------
         1            1

正如我说的，我看不到它的任何实际用途.实际上，您的下一个仅可从2使用.当它是1时，您需要再次执行 ALTER SEQUENCE (更改顺序)，以将 increment_by 重置为1.

As I said, I don't see any practical use of it. Your nextval is practically usable only from 2. When it is 1, you need to do an ALTER SEQUENCE once again to reset the increment_by back to 1.

这篇关于将Oracle Sequence重置为MIN VALUE = 1，并且STARTING number从1开始的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！