问题描述
问题
在回滚的情况下如何使用Oracle DB序列而不丢失下一个序列号?
How to use Oracle DB sequences without losing the next sequence number in case of roll-back?
收集到的事实
1-在Oracle中，我们可以创建一个序列并使用两个主调用( NEXTVAL )获取下一个序列值，并使用( CURRVAL )获得当前序列值.
1 - In Oracle, we can create a sequence and use two main calls (NEXTVAL) to get the next sequence value and (CURRVAL) to get the current sequence value.
2-当我们调用( NEXTVAL )时，将始终获得下一个数字，如果发生回滚，我们将丢失该数字.换句话说，Oracle序列并不关心是否存在回滚或提交；每当您呼叫它时，它都会给出一个新号码.
2 - When we call (NEXTVAL) will always get the next number and we will lose it if there is a rollback. In other words, Oracle sequence does not care if there is a roll-back or commit; whenever you are calling it, it will give a new number.
到目前为止我发现的可能答案
1-我正在考虑创建一个简单的表，该表具有一个类型为(NUMBER)的列以实现此目的.只需选择值并使用它即可.如果操作成功，我将增加列值.否则，我将保留它，以备下一次应用程序调用.
1 - I was thinking to create a simple table with one column of type (NUMBER) to service this purpose. Simply pick the value and use it. If operation succeeded I will increment column value. Otherwise, I will keep it as it is for the next application call.
2-我在这里找到的另一种方式(如何重置Oracle中的序列?)是要使用( ALTER SEQUENCE )，如下所示:
2 - Another way I found here (How do I reset a sequence in Oracle?) is to use (ALTER SEQUENCE) like the following if I want to go one step back.
也就是说，如果序列为101，我可以通过以下方式将其设置为100
That is, if the sequence is at 101, I can set it to 100 via
ALTER SEQUENCE serial INCREMENT BY -1;
SELECT serial.NEXTVAL FROM dual;
ALTER SEQUENCE serial INCREMENT BY 1;
结论
任何建议的解决方案都好吗?他们有更好的方法吗?
Are any of the suggested solutions is good? Is their any better approach?
推荐答案
从我的角度来看，您应该使用序列并停止担心差距.
From my point of view, you should use a sequence and stop worrying about gaps.
从您的角度来看，我说改变顺序比拥有一张桌子更糟糕.请注意，对该表的访问必须仅限于一个用户，否则，如果两个(或多个)用户同时访问它，您将获得重复的值.
From your point of view, I'd say that altering the sequence is worse than having a table. Note that access to that table must be restricted to a single user, otherwise you'll get duplicate values if two (or more) users access it simultaneously.
这是示例代码；看看，如果需要，可以使用/调整它.
Here's a sample code; have a look, use/adjust it if you want.
SQL> create table broj (redni_br number not null);
Table created.
SQL>
SQL> create or replace function f_get_broj
2 return number
3 is
4 pragma autonomous_transaction;
5 l_redni_br broj.redni_br%type;
6 begin
7 select b.redni_br + 1
8 into l_redni_br
9 from broj b
10 for update of b.redni_br;
11
12 update broj b
13 set b.redni_br = l_redni_br;
14
15 commit;
16 return (l_redni_br);
17 exception
18 when no_data_found
19 then
20 lock table broj in exclusive mode;
21
22 insert into broj (redni_br)
23 values (1);
24
25 commit;
26 return (1);
27 end f_get_broj;
28 /
Function created.
SQL> select f_get_broj from dual;
F_GET_BROJ
----------
1
SQL> select f_get_broj from dual;
F_GET_BROJ
----------
2
SQL>
这篇关于在回滚的情况下如何使用Oracle DB序列而不丢失下一个序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！