将 Oracle 序列重置为 MIN VALUE = 1 和 STARTING number from 1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30158866/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reset Oracle Sequence to have MIN VALUE = 1 and STARTING number from 1
提问by Hatjhie
I have a problem resetting Oracle Sequence to have MIN VALUE = 1 and starting next number used is 1.
我在将 Oracle Sequence 重置为 MIN VALUE = 1 并且开始使用的下一个数字为 1 时遇到问题。
I have followed through the answer of this question: How do I reset a sequence in Oracle?
我已经完成了这个问题的答案： 如何在 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;
But, the problem is the code above is resetting the sequence with min value = 0, and the next val to be used is 2.
但是，问题是上面的代码是用最小值 = 0 重置序列，而下一个要使用的 val 是 2。
Is it possible to set the min_value = 1, and nextval to be 1, after the reset is done?
是否可以在重置完成后将 min_value = 1 和 nextval 设置为 1？
I tried to set it but getting the error mentioning approximately as below:
我试图设置它，但得到的错误提到大约如下：
The current value cannot be higher then Minimum Value.
当前值不能高于最小值。
Thanks!
谢谢！
采纳答案by Lalit Kumar B
Is it possible to set the min value = 1, and next val to be 1, after the reset is done?
重置完成后，是否可以将最小值设置为 1，并将下一个 val 设置为 1？
You could do it in two steps:
您可以分两步完成：
- increment_by value one less than the current value of the sequence.
- reset increment_by back to 1.
- increment_by 值比序列的当前值小一。
- 将 increment_by 重置回 1。
The logic is that, you shouldn't decrement the sequence back to zero, since the minvalueyou want is 1, so, the nextval cannot be less than the minval.
逻辑是，您不应该将序列递减为零，因为您想要的minvalue是1，因此nextval 不能小于 minval。
For example,
例如，
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
So, the min_valueand increment_byis now resetto 1respectively. The nextvaluecould be 1 only once before you reset the increment_byto 1again.
因此，MIN_VALUE和increment_by现在恢复到1分别。该nextvalue可能是1只，一旦你重置之前increment_by来1一次。
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:
要在您的程序中实现上述逻辑，请执行以下操作：
Setup
设置
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
Modify your procedureas:
将您的程序修改为：
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
As I said, I don't see any practical use of it. Your nextvalis practically usable only from 2. When it is 1, you need to do an ALTER SEQUENCEonce again to reset the increment_byback to 1.
正如我所说，我没有看到它的任何实际用途。您的nextval实际上只能从2. 如果是1，您需要再次执行ALTER SEQUENCE以将increment_by重置回1.
