创建以字母数字开头的Oracle序列 [英] Creating Oracle sequence that starts with alphanumeric

问题描述

我想创建以字符inv开头并递增1

I want to create sequence to start with character inv and increment by 1

值为

INV01
INV02
INV03  
etc...

CREATE SEQUENCE invoice_nun
START WITH "INV"
INCREMENT BY 1

推荐答案

只能创建整数值的序列.

Only integer valued sequences can be created.

因此声明必须为:

CREATE SEQUENCE invoice_nun
  START WITH 1
  INCREMENT BY 1;

您可以将获取的值转换为字符串并添加适当的前缀.

You can convert the fetched value to a string and add an appropriate prefix.

select 'INV'||to_char(invoice_nun.nextval,'FM09999999') 
  from dual;

您可以创建一个函数来模拟返回适当字符串值的序列

You can create a function to simulate a sequence returning appropriate string values

create or replace function next_invoice_nun return varchar2
  as
  begin
  return('INV'||to_char(invoice_nun.nextval,'FM09999999') );
  end;
/ 

您现在可以做

select next_invoice_nun 
  from dual;

上面定义的序列使用一些默认值. 数据库SQL语言参考中对此进行了说明.它等效于以下语句

The sequence as defined above uses some default values. This is documented in the Database SQL Language Reference. It is equivalent to the following statement

CREATE SEQUENCE invoice_nun
  CACHE 20
  NOORDER
  START WITH 1
  INCREMENT BY 1;

您应该注意以下几点:

1)如果事务获取序列值并回滚，则序列值将丢失.因此，如果您执行以下操作:

1) If a transaction fetches a sequence value and rolls back then the sequence value is lost. So if you do the following:

-- fetch invoice_id INV00000001
insert into invoices(invoice_id,...) values (next_invoice_nun,...);   
commit;
-- fetch invoice_id INV00000002
insert into invoices(invoice_id,...) values (next_invoice_nun,...);
rollback;
-- fetch invoice_id INV00000003
insert into invoices(invoice_id,...) values (next_invoice_nun,...);
commit;

发票ID INV00000001 and INV00000003 are inserted in the发票table but the invoice id INV00000002`丢失，因为获取该发票的语句已回滚

the invoice idsINV00000001andINV00000003are inserted in theinvoicestable but the invoice idINV00000002` is lost because the statement that fetched it was rolled back

2)如果实例崩溃，则实例高速缓存中的所有序列都将丢失.在您的示例中，使用的缓存默认值为20.因此，如果实例崩溃，最多将丢失20个序列值.如果您创建序列，则可以使用关键字NOCYCLE来替代本机，但这会带来性能上的损失.

2) If an instance crashes all sequences that are in the cache of the instance are lost. In your example the default value for cache is used which is 20. So if the instances crashes at most 20 sequence values can be be get lost. an alter native is to use the keyword NOCYCLEif you create the sequence but this will bring performance penalties.

3)如果您在RAC系统上，则序列号不代表提取语句的顺序.因此，如果第二条语句在与第一条语句不同的实例上执行，则第一条语句可能获得id INV00000021，第二条语句可能获得id INV00000001.这是因为该实例在其缓存中获取了前20个序列号，而另一个实例在其缓存中获取了后20个序列号.在获取第二个20个序列号的实例上执行第一条语句.您可以使用ORDER关键字来避免这种情况，但这会带来性能上的损失

3) If you are on a RAC system sequence number does not represent the order of fetching the statement. So it is possible that the first statement gets the id INV00000021 and the second statement gets the id INV00000001 if the second statement is executed on a different instance than the first statement. This is because the instance fetched the first 20 sequences numbers in its cache and the other instance fetched the second 20 sequences numbers in its cache. The first statement is executed on the instance that fetched the second 20 sequence numbers. You can use the ORDER keyword to avoid this but this again will bring performance penalties

因此，对于性能罚款，可以避免2)和3)，但是无法避免2).

So one can avoid 2) and 3) for the price of performance penalties but there is no way to avoid 2).

这篇关于创建以字母数字开头的Oracle序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！