创建以字母数字开头的 Oracle 序列

Creating Oracle sequence that starts with alphanumeric

提问by Francis John

I want to create sequence to start with character invand increment by 1

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

The values to be

要成为的价值观

INV01
INV02
INV03  
etc...

CREATE SEQUENCE invoice_nun
START WITH "INV"
INCREMENT BY 1

回答by miracle173

Only integer valued sequences can be created.

只能创建整数值序列。

So the statement must be:

所以语句必须是：

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;
/ 

you can now do

你现在可以做

select next_invoice_nun 
  from dual;

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

上面定义的序列使用一些默认值。这在数据库 SQL 语言参考中有记录。相当于下面的语句

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

You should be aware of the following:

您应该注意以下事项：

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

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

-- 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;

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

发票 ID INV00000001 andINV00000003are inserted in the发票table but the invoice idINV00000002` 丢失，因为获取它的语句已回滚

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.

2) 如果实例崩溃，则实例缓存中的所有序列都将丢失。在您的示例中，缓存的默认值为 20。因此，如果实例崩溃，最多 20 个序列值可能会丢失。NOCYCLE如果您创建序列，另一种方法是使用关键字，但这会带来性能损失。

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 INV00000021and the second statement gets the id INV00000001if 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 ORDERkeyword to avoid this but this again will bring performance penalties

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

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

因此，可以避免 2) 和 3) 的性能损失，但没有办法避免 2)。

回答by Jeffrey Kemp

Oracle only provides numeric sequences, but you can construct your identifiers by converting to a string, e.g. 'INV' || TO_CHAR(invoice_num.NEXTVAL,'fm00')

Oracle 只提供数字序列，但您可以通过转换为字符串来构造您的标识符，例如 'INV' || TO_CHAR(invoice_num.NEXTVAL,'fm00')

Mind you, you might need more than 2 digits, depending on how many records you're expecting.

请注意，您可能需要超过 2 位数字，具体取决于您期望的记录数。