按一定数量递增 Oracle 序列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6548612/
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
Incrementing Oracle Sequence by certain amount
提问by Tim Meyer
I am programming a Windows Application (in Qt 4.6) which - at some point - inserts any number of datasets between 1 and around 76000 into some oracle (10.2) table. The application has to retrieve the primary keys, or at least the primary key range, from a sequence. It will then store the IDs in a list which is used for Batch Execution of a prepared query.
我正在编写一个 Windows 应用程序（在 Qt 4.6 中），它在某个时候将 1 到 76000 之间的任意数量的数据集插入到某个 oracle (10.2) 表中。应用程序必须从序列中检索主键，或者至少是主键范围。然后它将 ID 存储在一个列表中，该列表用于批量执行准备好的查询。
(Note: Triggers shall not be used, and the sequence is used by other tasks as well)
（注意：不应使用触发器，其他任务也使用该序列）
In order to avoid calling the sequence X times, I would like to increment the sequence by X instead.
为了避免调用序列 X 次，我想将序列增加 X。
What I have found out so far, is that the following code would be possible in a procedure:
到目前为止，我发现以下代码可以在过程中使用：
ALTER SEQUENCE my_sequence INCREMENT BY X;
SELECT my_sequence.CURVAL + 1, my_sequence.NEXTVAL
INTO v_first_number, v_last_number
FROM dual;
ALTER SEQUENCE my_sequence INCREMENT BY 1;
I have two major concerns though:
不过，我有两个主要问题：
- I have read that ALTER SEQUENCE produces an implicit commit. Does this mean the transaction started by the Windows Application will be commited? If so, can you somehow avoid it?
Is this concept multi-user proof? Or could the following thing happen:
Sequence is at 10,000 Session A sets increment to 2,000 Session A selects 10,001 as first and 12,000 as last Session B sets increment to 5,000 Session A sets increment to 1 Session B selects 12,001 as first and 12,001 as last Session B sets increment to 1Even if the procedure would be rather quick, it is not that unlikely in my application that two different users cause the procedure to be called almost simultaneously
- 我已经读过 ALTER SEQUENCE 会产生一个隐式提交。这是否意味着将由 Windows 应用程序启动的事务将被提交？如果是这样，你能以某种方式避免它吗？
这个概念是多用户证明吗？或者可能会发生以下事情：
Sequence is at 10,000 Session A sets increment to 2,000 Session A selects 10,001 as first and 12,000 as last Session B sets increment to 5,000 Session A sets increment to 1 Session B selects 12,001 as first and 12,001 as last Session B sets increment to 1即使该过程相当快，但在我的应用程序中，两个不同的用户导致该过程几乎同时被调用也不太可能
采纳答案by Michal Pravda
Altering the sequence in this scenario is really bad idea. Particularly in multiuser environment. You'll get your transaction committed and probably several "race condition" data bugs or integrity errors. It would be appropriate if you had legacy data alredy imported and want to insert new data with ids from sequence. Then you may alter the sequence to move currval to max existing ...
在这种情况下改变顺序是非常糟糕的主意。特别是在多用户环境中。您将提交事务，并且可能会出现几个“竞争条件”数据错误或完整性错误。如果您已经导入了旧数据并希望插入带有序列 id 的新数据，这将是合适的。然后您可以更改顺序以将 currval 移动到最大现有...
It seems to me that here you want to generate Ids from the sequence. That need not to be done by
在我看来，您想在这里从序列中生成 Id。这不需要由
select seq.nextval into l_variable from dual;
insert into table (id, ...) values (l_variable, ....);
You can use the sequence directly in the insert:
您可以直接在插入中使用该序列：
insert into table values (id, ...) values (seq.nextval, ....);
and optionally get the assigned value back by
并可选择通过以下方式获取分配的值
insert into table values (id, ...) values (seq.nextval, ....)
returning id into l_variable;
It certainly is possible even for bulk operations with execBatch. Either just creating the ids or even returning them. I am not sure about the right syntax in java but it will be something about the lines
即使对于使用 execBatch 的批量操作，这当然也是可能的。要么只是创建 ID，要么甚至返回它们。我不确定 java 中的正确语法，但它会与行有关
insert into table values (id, ...) values (seq.nextval, ....)
returning id bulk collect into l_cursor;
and you'll be given a ResultSet to browse the assigned numbers.
并且您将获得一个 ResultSet 来浏览指定的数字。
回答by Justin Cave
1) ALTER SEQUENCEis DDL so it implicitly commits before and after the statement. The database transaction started by the Windows application will be committed. If you are using a distributed transaction coordinator other than the Oracle database, hopefully the transaction coordinator will commit the entire distributed transaction but transaction coordinators will sometimes have problems with commits issued that it is not aware of.
There is nothing that you can do to prevent DDL from committing.
1)ALTER SEQUENCE是 DDL，因此它在语句之前和之后隐式提交。由 Windows 应用程序启动的数据库事务将被提交。如果您使用的是 Oracle 数据库以外的分布式事务协调器，则希望事务协调器将提交整个分布式事务，但事务协调器有时会遇到它不知道的提交问题。您无法阻止 DDL 提交。
2) The scenario you outline with multiple users is quite possible. So it doesn't sound like this approach would behave correctly in your environment.
2）您为多个用户概述的场景很有可能。所以听起来这种方法在您的环境中表现不正确。
You could potentially use the DBMS_LOCKpackage to ensure that only one session is calling your procedure at any point in time and then call the sequence N times from a single SQL statement. But if other processes are also using the sequence, there is no guarantee that you'll get a contiguous set of values.
您可以潜在地使用该DBMS_LOCK包来确保在任何时间点只有一个会话正在调用您的过程，然后从单个 SQL 语句调用序列 N 次。但是，如果其他进程也在使用该序列，则无法保证您将获得一组连续的值。
CREATE PROCEDURE some_proc( p_num_rows IN NUMBER,
p_first_val OUT NUMBER,
p_last_val OUT NUMBER )
AS
l_lockhandle VARCHAR2(128);
l_lock_return_code INTEGER;
BEGIN
dbms_lock.allocate_unique( 'SOME_PROC_LOCK',
l_lockhandle );
l_lock_return_code := dbms_lock.request( lockhandle => l_lockhandle,
lockmode => dbms_lock.x_mode,
release_on_commit => true );
if( l_lock_return_code IN (0, 4) ) -- Success or already owned
then
<<do something>>
end if;
dbms_lock.release( l_lockhandle );
END;
回答by noo
In Oracle, you can use following query to get next N values from a sequence that increments by one:
在 Oracle 中，您可以使用以下查询从递增 1 的序列中获取接下来的 N 个值：
select level, PDQ_ACT_COMB_SEQ.nextval as seq from dual connect by level <= 5;
select level, PDQ_ACT_COMB_SEQ.nextval as seq from dual connect by level <= 5;
回答by Martin Schapendonk
You can't prevent the implicit commit.
Your solution is not multi user proof. It is perfectly possible that another session will have 'restored' the increment to 1, just as you described.
您无法阻止隐式提交。
您的解决方案不是多用户证明。正如您所描述的那样，另一个会话完全有可能将增量“恢复”为 1。
I would suggest you keep fetching values one by one from the sequence, store these IDs one by one on your list and have the batch execution operate on that list.
我建议您继续从序列中一一获取值，将这些 ID 一一存储在您的列表中，并在该列表上进行批处理。
What is the reason that you want to fetch a contiguous block of values from the sequence? I would not be too worried about performance, but maybe there are other requirements that I don't know of.
您想从序列中获取连续的值块的原因是什么？我不会太担心性能，但也许还有其他我不知道的要求。
