oracle 在一个查询中使用相同的 seq_name.nextval。甲骨文
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15638039/
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
Same seq_name.nextval in one query. ORACLE
提问by Werner van den Heever
how can you select the same sequence twice in one query?
如何在一个查询中两次选择相同的序列？
I have googled this and just cant get an answer.
我已经用谷歌搜索了这个，但无法得到答案。
Just to be clear, this is what I need, example :
为了清楚起见，这就是我需要的，例如：
select seq.nextval as v1, seq.nextval as v2 from dual(I know that doesn't work)
select seq.nextval as v1, seq.nextval as v2 from dual（我知道那行不通）
I have tried UNION as well, Just cant get my head around it.
我也尝试过 UNION，只是无法理解它。
采纳答案by Werner van den Heever
These are all great answers, unfortunately it was just not enough. Will definitely be able to return to this page when struggling in the future.
这些都是很好的答案，不幸的是这还不够。以后遇到困难一定能回到这个页面。
I have gone with a different method. Asked a new question and got my answer.
我采用了不同的方法。问了一个新问题并得到了我的答案。
You can go check it out here.
你可以去这里看看。
回答by Frank Schmitt
If you always need exactly two values, you could change the sequence to increment by 2:
如果您总是需要正好两个值，您可以更改序列以增加 2：
alter sequence seq increment by 2;
and then select the current value and its successor:
然后选择当前值及其后继值：
select seq.nextval,
seq.nextval + 1
from dual;
Not pretty, but it should do the job.
不漂亮，但它应该可以胜任。
UPDATE
更新
Just to clarify: the ALTER SEQUENCEshould be issued only once in a lifetime, not once per session!
只是为了澄清：ALTER SEQUENCE应该一生只发布一次，而不是每个会话一次！
回答by Tobias Twardon
The Answer by Frank has a downside:
弗兰克的回答有一个缺点：
You cannot use it in transactional system, because the ALTER SEQUENCE commits any pending DML.
您不能在事务系统中使用它，因为 ALTER SEQUENCE 提交任何挂起的 DML。
The Answer of Sean only pulls the sequence once. As I understand, you want to pull two values. As an alternative to Seans solution, you could also select two times from .nextval, due ORACLE gives you the same value twice.
肖恩的答案只拉序列一次。据我了解，您想提取两个值。作为 Seans 解决方案的替代方案，您还可以从 .nextval 中选择两次，因为 ORACLE 两次为您提供相同的值。
I'd prefer wrapping up the sequence in a procedure. This tricks oracle to pulling the sequence twice.
我更喜欢将序列包装在一个过程中。这会欺骗 oracle 两次拉取序列。
CREATE or replace FUNCTION GetSeq return number as
nSeq NUMBER;
begin
select seq.nextval into nSeq from dual;
return nSeq;
end;
/
If you need this generically, maybe you'd like:
如果您一般需要这个，也许您会想要：
CREATE or replace FUNCTION GetSeq(spSeq in VARCHAR2) return number as
nSeq NUMBER;
v_sql long;
begin
v_sql:='select '||upper(spSeq)||'.nextval from dual';
execute immediate v_sql into nSeq;
return nSeq;
end;
/
回答by Ed Gibbs
There are some limitations on how NEXTVALcan be used. There's a list in the Oracle docs. More to the point, the link includes a list of conditions where NEXTVAL will be called more than once.
关于如何NEXTVAL使用有一些限制。Oracle 文档中有一个列表。更重要的是，该链接包含一个条件列表，其中 NEXTVAL 将被多次调用。
The only scenario named on the page where a straight SELECT will call NEXTVALmore than once is in "A top-level SELECT statement". A crude query that will call NEXTVALtwice is:
页面上命名的唯一一个直接 SELECT 调用NEXTVAL不止一次的场景是在“顶级 SELECT 语句”中。将调用NEXTVAL两次的粗略查询是：
SELECT seq.NEXTVAL FROM (
SELECT * FROM DUAL
CONNECT BY LEVEL <= 2) -- Change the "2" to get more sequences
Unfortunately, if you try to push this into a subquery to flatten out the values to one row with columns v1and v2(like in your question), you'll get the ORA-02287: sequence number not allowed here.
不幸的是，如果您尝试将其推送到子查询中以将值展平为带有列的一行v1并且v2（如您的问题中所示），您将获得ORA-02287: sequence number not allowed here.
This is as close as I could get. If the query above won't help you get where you want, then check out the other answers that have been posted here. As I've been typing this, a couple of excellent answers have been posted.
这是我能得到的最接近的。如果上面的查询不能帮助您到达所需的位置，请查看此处发布的其他答案。当我一直在打字时，已经发布了几个很好的答案。
回答by tbone
Telling you to just call sequence.nextval multiple times would be boring, so here's what you can try:
告诉您多次调用 sequence.nextval 会很无聊，所以您可以尝试以下方法：
create type t_num_tab as table of number;
CREATE SEQUENCE SEQ_TEST
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 100
NOORDER;
create or replace function get_seq_vals(i_num in number) return t_num_tab is
seq_tab t_num_tab;
begin
select SEQ_TEST.nextval
bulk collect into seq_tab
from all_objects
where rownum <= i_num;
return seq_tab;
end;
And you can use it as follows. This example is pulling 7 numbers at once from the sequence:
您可以按如下方式使用它。此示例一次从序列中提取 7 个数字：
declare
numbers t_num_tab;
idx number;
begin
-- this grabs x numbers at a time
numbers := get_seq_vals(7);
-- this just loops through the returned numbers
-- to show the values
idx := numbers.first;
loop
dbms_output.put_line(numbers(idx));
idx := numbers.next(idx);
exit when idx is null;
end loop;
end;
Also note that I use "next" instead of first..last as its possible you may want to remove numbers from the list before iterating through (or, sometimes a sequence cache can results in numbers incrementing by more than 1).
另请注意，我使用“next”而不是 first..last 作为可能，您可能希望在迭代之前从列表中删除数字（或者，有时序列缓存可能导致数字增加超过 1）。
