手动转发一个序列——oracle sql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9609463/
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
Manually forward a sequence - oracle sql
提问by Jeremy
I need to forward a set of sequences with only DML access. Due to a bug in a piece of code several values were grabbed without a sequence but instead manually, so now the sequence is duplicating those values. So, I would like to push the sequence to the max value so that the next time nextval is called, it gives a value higher than the maximum. I've got about 50 sequences that each have to go a few thousand forward.
我需要转发一组仅具有 DML 访问权限的序列。由于一段代码中的一个错误，几个值在没有序列的情况下被抓取，而是手动，所以现在序列正在复制这些值。所以，我想将序列推到最大值，以便下次调用 nextval 时，它给出的值高于最大值。我有大约 50 个序列，每个序列都必须向前移动几千个。
Is this possible with only DML access? If so, how should I go about it?
只有 DML 访问才能做到这一点吗？如果是这样，我应该怎么做？
回答by Justin Cave
You can use dynamic SQL to do this. For example, this bit of code will select the next 10,000 values from each of a list of sequences.
您可以使用动态 SQL 来执行此操作。例如，这段代码将从每个序列列表中选择接下来的 10,000 个值。
DECLARE
l_num INTEGER;
BEGIN
FOR seq IN (select *
from all_sequences
where sequence_name in (<<list of 50 sequences>>)
and sequence_owner = <<owner of sequences>>)
LOOP
FOR i IN 1 .. 10000
LOOP
execute immediate
'select ' || seq.sequence_owner || '.' || seq.sequence_name || '.nextval from dual'
into l_num;
END LOOP;
END LOOP;
END;
If you had the ability to issue DDL against the sequence, you could use a similar approach to set the INCREMENTto 10,000, select one value from the sequence, and set the INCREMENTback down to 1 (or whatever it is now).
如果您有能力针对序列发出 DDL，您可以使用类似的方法将 the 设置INCREMENT为 10,000，从序列中选择一个值，然后将INCREMENTback设置为 1（或现在的任何值）。
回答by Donato Szilagyi
You should determine the difference between the next value of the sequence and the required value. The required value is typically the max value of a primary key column (let's name it ID).
您应该确定序列的下一个值与所需值之间的差异。所需的值通常是主键列的最大值（让我们将其命名为 ID）。
DECLARE
maxid NUMBER;
maxseq NUMBER;
temp NUMBER; -- without this variable Oracle would skip to query the sequence
BEGIN
SELECT MAX(ID) INTO maxid FROM MYTABLE;
SELECT MYSEQ.NEXTVAL INTO maxseq FROM DUAL;
FOR i IN maxseq .. maxid LOOP
SELECT MYSEQ.NEXTVAL INTO temp FROM DUAL;
END LOOP;
END;
/
回答by Randy
you can just
你可以
select seq.nextval from dual
until it is big enough...
直到它足够大...
回答by helrich
If you have a table with at least as many rows as the amount you want to add to your sequences, the following will work. This increments each sequence by the same amount, which may not suit you, but it's quick and easy without requiring PL/SQL or the need to drop/re-create the sequence. I use it all the time when I want to get development server sequences ahead of production.
如果您有一个表的行数至少与要添加到序列中的数量一样多，则以下操作将起作用。这会将每个序列增加相同的数量，这可能不适合您，但是不需要 PL/SQL 或删除/重新创建序列，它既快速又简单。当我想在生产之前获得开发服务器序列时，我一直使用它。
SELECT seq1.nextval, seq2.nextval, ..., seqN.nextval
FROM very_large_table
WHERE ROWNUM <= number_of_rows_to_add
回答by Tim Gage
To restart the sequence at a different value you need to drop and recreate it.
要以不同的值重新启动序列，您需要删除并重新创建它。
See the Oracle docs for ALTER SEQUENCEhere.
请参阅ALTER SEQUENCE此处的 Oracle 文档。
And for CREATE SEQUENCEhere
而对于CREATE SEQUENCE这里
So, no I don't think it's possible with DML access, unless you just increment repeatedly like Randy suggests.
所以，不，我认为 DML 访问是不可能的，除非您像 Randy 建议的那样重复递增。
