如何在 Oracle 10g PL/SQL 中获取序列的 MINVALUE？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1374502/
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
How do I get a sequence's MINVALUE in Oracle 10g PL/SQL?
提问by Jason Baker
I wrote a PL/SQL script to set a sequence's value to the maximum value of a table's primary key:
我编写了一个 PL/SQL 脚本来将序列的值设置为表主键的最大值：
DECLARE
max_idn NUMERIC(18, 0);
seq_nextval NUMERIC(18, 0);
increment_amount NUMERIC(18, 0);
BEGIN
SELECT MAX(mbr_idn)
INTO max_idn
FROM mbr;
SELECT mbr_seq.nextval
INTO seq_nextval
FROM DUAL;
increment_amount := max_idn - seq_nextval;
EXECUTE IMMEDIATE
'ALTER SEQUENCE mbr_seq
increment by ' || increment_amount;
END;
However, I get an error if the sequence's MINVALUE is greater than the max primary key:
但是，如果序列的 MINVALUE 大于最大主键，我会收到错误消息：
ORA-08004: sequence MBR_SEQ.NEXTVAL goes below MINVALUE and cannot be instantiated
ORA-06512: at line 10
ORA-08004: 序列 MBR_SEQ.NEXTVAL 低于 MINVALUE 且无法实例化
ORA-06512：在第 10 行
What's the easiest way to say "increment the sequence by increment_amount, but don't go below MINVALUE"?
说“按 increment_amount 增加序列，但不要低于 MINVALUE”的最简单方法是什么？
采纳答案by Justin Cave
You can query the DBA_SEQUENCES table to get the MIN_VALUE
您可以查询 DBA_SEQUENCES 表以获取 MIN_VALUE
SELECT min_value
INTO l_min_value
FROM all_sequences
WHERE sequence_name = 'MBR_SEQ'
AND owner = <<sequence owner>>
You could then incorporate that into your code, i.e.
然后您可以将其合并到您的代码中，即
increment_amount := GREATEST( max_idn, l_min_value ) - seq_nextval;
