在 Oracle 中生成唯一和连续数字的最佳方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1986064/
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
Best Way to Generate Unique and consecutives numbers in Oracle
提问by RRUZ
I need to generate unique and consecutivenumbers (for use on an invoice), in a fast and reliable way. currently use a Oracle sequence, but in some cases generated numbers are not consecutivebecause of exceptions that may occur.
我需要以快速可靠的方式生成唯一且连续的数字（用于发票）。目前使用的是 Oracle 序列，但在某些情况下，由于可能发生异常，生成的数字不连续。
I thought a couple of solutions to manage this problem, but neither of they convincing me. What solution do you recommend?
我想了几个解决方案来解决这个问题，但都没有让我信服。你推荐什么解决方案？
Use a select max ()
SELECT MAX (NVL (doc_num, 0)) +1 FROM invoicesUse a table to store the last number generated for the invoice.
UPDATE docs_numbers SET last_invoice = last_invoice + 1Another Solution?
使用选择最大值 ()
SELECT MAX (NVL (doc_num, 0)) +1 FROM invoices使用表格存储为发票生成的最后一个数字。
UPDATE docs_numbers SET last_invoice = last_invoice + 1另一个解决方案？
采纳答案by Jonathan Leffler
The gaps appear if a transaction uses a sequence number but is then rolled back.
如果事务使用序列号但随后回滚，则会出现间隙。
Maybe the answer is not to assign the invoice number until the invoice can't be rolled back. This minimizes (but probably does not eliminate) the possibilities of gaps.
也许答案是在发票无法回滚之前不分配发票编号。这最大限度地减少（但可能不会消除）差距的可能性。
I'm not sure that there is any swift or easy way to ensure no gaps in the sequence - scanning for MAX, adding one, and inserting that is probably the closest to secure, but is not recommended for performance reasons (and difficulties with concurrency) and the technique won't detect if the latest invoice number is assigned, then deleted and reassigned.
我不确定是否有任何快速或简单的方法来确保序列中没有间隙 - 扫描 MAX，添加一个，然后插入可能是最接近安全的，但出于性能原因（以及并发困难）不推荐) 并且该技术不会检测是否分配了最新的发票编号，然后删除并重新分配。
Can you account for gaps somehow - by identifying which invoice numbers were 'used' but 'not made permanent' somehow? Could an autonomous transaction help in doing that?
您能否以某种方式解释差距——通过识别哪些发票号码被“使用”但“不是永久的”？自治事务可以帮助做到这一点吗？
Another possibility - assuming that gaps are relatively few and far between.
另一种可能性 - 假设差距相对较少且相距甚远。
Create a table that records sequence numbers that must be reused before a new sequence value is grabbed. Normally, it would be empty, but some process that runs every ... minute, hour, day ... checks for gaps and inserts the missed values into this table. All processes first check the table of missed values, and if there are any present, use a value from there, going through the slow process of updating the table and removing the row that they use. If the table is empty, then grab the next sequence number.
创建一个表，记录在获取新序列值之前必须重用的序列号。通常，它会是空的，但某些进程每隔...分钟、小时、天运行...检查间隙并将遗漏的值插入此表中。所有进程首先检查缺失值表，如果存在缺失值，则使用那里的值，经历更新表和删除它们使用的行的缓慢过程。如果表为空，则抓取下一个序列号。
Not very pleasant, but the decoupling of 'issuing invoice numbers' from 'scan for missed values' means that even if the invoicing process fails for some thread when it is using one of the missed values, that value will be rediscovered to be missing and re-reissued next time around - repeating until some process succeeds with it.
不是很愉快，但是“发出发票编号”与“扫描遗漏值”的分离意味着即使某个线程在使用遗漏值之一时开票过程失败，该值将被重新发现丢失并且下次重新发行 - 重复直到某个过程成功。
回答by dpbradley
1 can always be made to fail some way in an environment with concurrent
users.2 will work, but will limit scalability (obligatory Tom Kyte
reference: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1508205334476
1 总是可以在并发环境中以某种方式失败
用户。2 会工作，但会限制可扩展性（强制性的 Tom Kyte
参考：http: //asktom.oracle.com/pls/asktom/f?p=100:11:0 ::::P11_QUESTION_ID: 1508205334476
As he recommends, you should really review the necessity for the "no gaps" requirement
正如他所建议的，您应该真正“无间隙”要求的必要性
回答by Bob Jarvis - Reinstate Monica
I think you'll find that using the MAX() of the existing numbers is prone to a new and exciting problem - duplicates can occur if multiple invoices are being created at the same time. (Don't ask me how I know...).
我认为您会发现使用现有数字的 MAX() 容易出现一个令人兴奋的新问题 - 如果同时创建多个发票，可能会发生重复。（别问我怎么知道的……）
A possible solution is to derive the primary key on your INVOICE table from a sequence, but have this NOT be the invoice number. After correctly and properly creating your invoice, and after the point at which an exception or user's whim could cause the creation of the invoice to be terminated, you go to a second sequence to get the sequential number which is presented as "the" invoice number. This means you'll have two unique, non-repeating numbers on your INVOICE table, and the obvious one (INVOICE_NO) will not be the primary key (but it can and should be UNIQUE) so there's a bit of evil creeping in, but the alternative - which is to create the INVOICE row with one value in the primary key, then change the primary key after the INVOICE is created - is just too evil for words. :-)
一种可能的解决方案是从序列中导出 INVOICE 表上的主键，但不要将其作为发票编号。在正确正确地创建发票后，在异常或用户的突发奇想可能导致发票创建终止的点之后，您转到第二个序列以获取序列号，该序列号显示为“发票号” . 这意味着您的 INVOICE 表上将有两个唯一的、非重复的数字，并且明显的一个 (INVOICE_NO) 不会是主键（但它可以而且应该是 UNIQUE）所以有一些邪恶的潜入，但是另一种选择 - 即在主键中使用一个值创建 INVOICE 行，然后在创建 INVOICE 后更改主键 - 太邪恶了。:-)
Share and enjoy.
分享和享受。
回答by OMG Ponies
Keep the current sequence - you can use the following to reset the value to the maximum of what is currently stored in the table(s):
保持当前序列 - 您可以使用以下命令将值重置为当前存储在表中的最大值：
-- --------------------------------
-- Purpose..: Resets the sequences
-- --------------------------------
DECLARE
-- record of temp data table
TYPE data_rec_type IS RECORD(
sequence_name VARCHAR2(30),
table_name VARCHAR2(30),
column_name VARCHAR2(30));
-- temp data table
TYPE data_table_type IS TABLE OF data_rec_type INDEX BY BINARY_INTEGER;
v_data_table data_table_type;
v_index NUMBER;
v_tmp_id NUMBER;
-- add row to temp table for later processing
--
PROCEDURE map_seq_to_col(in_sequence_name VARCHAR2,
in_table_name VARCHAR2,
in_column_name VARCHAR2) IS
v_i_index NUMBER;
BEGIN
v_i_index := v_data_table.COUNT + 1;
v_data_table(v_i_index).sequence_name := in_sequence_name;
v_data_table(v_i_index).table_name := in_table_name;
v_data_table(v_i_index).column_name := in_column_name;
END;
/**************************************************************************
Resets a sequence to a given value
***************************************************************************/
PROCEDURE reset_seq(in_seq_name VARCHAR2, in_new_value NUMBER) IS
v_sql VARCHAR2(2000);
v_seq_name VARCHAR2(30) := in_seq_name;
v_reset_val NUMBER(10);
v_old_val NUMBER(10);
v_new_value NUMBER(10);
BEGIN
-- get current sequence value
v_sql := 'SELECT ' || v_seq_name || '.nextval FROM DUAL';
EXECUTE IMMEDIATE v_sql
INTO v_old_val;
-- handle empty value
v_new_value := in_new_value;
if v_new_value IS NULL then
v_new_value := 0;
END IF;
IF v_old_val <> v_new_value then
IF v_old_val > v_new_value then
-- roll backwards
v_reset_val := (v_old_val - v_new_value) * -1;
elsif v_old_val < v_new_value then
v_reset_val := (v_new_value - v_old_val);
end if;
-- make the sequence rollback to 0 on the next call
v_sql := 'alter sequence ' || v_seq_name || ' increment by ' ||
v_reset_val || ' minvalue 0';
EXECUTE IMMEDIATE (v_sql);
-- select from the sequence to make it roll back
v_sql := 'SELECT ' || v_seq_name || '.nextval FROM DUAL';
EXECUTE IMMEDIATE v_sql
INTO v_reset_val;
-- make it increment correctly again
v_sql := 'alter sequence ' || v_seq_name || ' increment by 1';
EXECUTE IMMEDIATE (v_sql);
-- select from it again to prove it reset correctly.
v_sql := 'SELECT ' || v_seq_name || '.currval FROM DUAL';
EXECUTE IMMEDIATE v_sql
INTO v_reset_val;
END IF;
DBMS_OUTPUT.PUT_LINE(v_seq_name || ': ' || v_old_val || ' to ' ||
v_new_value);
END;
/*********************************************************************************************
Retrieves a max value for a table and then calls RESET_SEQ.
*********************************************************************************************/
PROCEDURE reset_seq_to_table(in_sequence_name VARCHAR2,
in_table_name VARCHAR2,
in_column_name VARCHAR2) IS
v_sql_body VARCHAR2(2000);
v_max_value NUMBER;
BEGIN
-- get max value in the table
v_sql_body := 'SELECT MAX(' || in_column_name || '+0) FROM ' ||
in_table_name;
EXECUTE IMMEDIATE (v_sql_body)
INTO v_max_value;
if v_max_value is null then
-- handle empty tables
v_max_value := 0;
end if;
-- use max value to reset the sequence
RESET_SEQ(in_sequence_name, v_max_value);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed to reset ' || in_sequence_name ||
' from ' || in_table_name || '.' ||
in_column_name || ' - ' || sqlerrm);
END;
BEGIN
--DBMS_OUTPUT.ENABLE(1000000);
-- load sequence/table/column associations
/***** START SCHEMA CUSTOMIZATION *****/
map_seq_to_col('Your_SEQ',
'your_table',
'the_invoice_number_column');
/***** END SCHEMA CUSTOMIZATION *****/
-- iterate all sequences that require a reset
FOR v_index IN v_data_table.FIRST .. v_data_table.LAST LOOP
BEGIN
RESET_SEQ_TO_TABLE(v_data_table(v_index).sequence_name,
v_data_table(v_index).table_name,
v_data_table(v_index).column_name);
END;
END LOOP;
END;
/
-- -------------------------------------------------------------------------------------
-- End of Script.
-- -------------------------------------------------------------------------------------
The example is an anonymous sproc - change it to be proper procedures in a package, and call it prior to inserting a new invoice to keep the numbering consistent.
该示例是一个匿名 sproc - 将其更改为包中的正确程序，并在插入新发票之前调用它以保持编号一致。
回答by maximdim
It's not clear what you mean by 'because of exceptions that may occur'. If you want number NOT to be incremented if your transaction eventually rolls back then SEQUENCE is not going to work for you, because as far as I know, once NEXTVAL is requested from sequence the sequence position is incremented and rollback won't reverse it.
不清楚您所说的“因为可能发生的异常”是什么意思。如果您希望在您的事务最终回滚时不增加数字，那么 SEQUENCE 将不会为您工作，因为据我所知，一旦从序列中请求 NEXTVAL，序列位置就会增加并且回滚不会逆转它。
If this is indeed a requirements then you probably would have to resort of storing current counter in a separate table, but beware of concurrent updates - from both 'lost update' and scalability prospective.
如果这确实是一个要求，那么您可能不得不求助于将当前计数器存储在单独的表中，但要注意并发更新 - 从“丢失更新”和可扩展性的角度来看。
回答by steve
If you really want to have no gaps, you need to completely serialize access, otherwise there will always be gaps. The reasons for gaps are:
如果真的想没有间隙，就需要完全序列化访问，否则总会有间隙。造成差距的原因是：
- rollback
- shutdown abort
- 回滚
- 关机中止
回答by Jeffrey Kemp
I've come across this problem before. In one case, we were able to convince the business to accept that "real" invoices might have gaps, and we wrote a job that ran every day to "fill in" the gaps with "void" invoices for audit purposes.
我以前遇到过这个问题。在一个案例中，我们能够说服企业接受“真实”发票可能存在空白，并且我们编写了一项每天运行的工作，以用于审计目的用“无效”发票“填补”空白。
In practice, if we put NOCACHE on the sequence, the number of gaps would be relatively low, so the auditors will usually be happy as long as their query on the "void" invoices don't return too many results.
在实践中，如果我们将 NOCACHE 放在序列上，差距的数量会相对较少，因此审计员通常会很高兴，只要他们对“无效”发票的查询不会返回太多结果。
回答by Khb
What we do is issue a sequence number to the transaction and then when the item we are processing is finalized we issue a permanent number (also a sequence). Works well for us.
我们所做的是为交易发布一个序列号，然后当我们正在处理的项目完成时，我们发布一个永久编号（也是一个序列）。对我们来说效果很好。
Regards
K
问候
K
回答by tmeisenh
You might have to re-think your process slighty and break it into more steps. Have one non-transactional step create the placeholder invoice (this not being in the transaction should eliminate gaps) and then within the transaction do the rest of your business. I think that was how we did it in a system I was stuck with years ago but I can't remember - I just remember it was "weird."
您可能需要稍微重新考虑您的流程并将其分解为更多步骤。让一个非交易步骤创建占位符发票（这不在交易中应该消除差距），然后在交易中完成您的其余业务。我认为这就是我们在多年前被困在一个系统中的方式，但我不记得了——我只记得这很“奇怪”。
I'd say the sequence will guarantee unique/consecutive numbers but when you throw transactions in the mix that can't be guaranteed unless the sequence generation isn't within that transaction.
我会说序列将保证唯一/连续的数字，但是当您将事务混合在一起时，除非序列生成不在该事务中，否则无法保证。
回答by Doug Porter
dpbradley's link in #2 sounds like your best bet. Tom keeps the transactionality with the caller, if you don't want that you could make it an autonomous transaction like so:
#2 中 dpbradley 的链接听起来是您最好的选择。Tom 保持与调用者的事务性，如果您不希望这样，您可以将其设为自治事务，如下所示：
create or replace
function getNextInvoiceNumber()
return number is
l_invoicenum number;
pragma autonomous_transaction;
begin
update docs_numbers
set last_invoice = last_invoice + 1
returning last_invoice
into l_invoicenum;
commit;
return l_invoicenum;
exception
when others then
rollback;
raise;
end;
