如何在 Oracle 中生成字母数字 ID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2515712/
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
How to generate alphanumeric id in Oracle
提问by banita
In my vb application I want an autogenerated id of alphanumeric characters, like prd100. How can I increment it using Oracle as backend?
在我的 vb 应用程序中，我想要一个自动生成的字母数字字符 id，例如prd100. 如何使用 Oracle 作为后端来增加它？
回答by dcp
Any particular reason it needs to be alphanumeric? If it can just be a number, you can use an Oracle sequence.
它需要是字母数字的任何特殊原因？如果它可以只是一个数字，则可以使用Oracle 序列。
But if you want just a random string, you could use the dbms_random function.
但是如果你只想要一个随机字符串，你可以使用 dbms_random 函数。
select dbms_random.string('U', 20) str from dual;
So you could probably combine these 2 ideas (in the code below, the sequence is called oid_seq):
所以你可以结合这两个想法（在下面的代码中，序列被称为 oid_seq）：
SELECT dbms_random.string('U', 20) || '_' || to_char(oid_seq.nextval) FROM dual
回答by APC
There are two parts to your question. The first is how to create an alphanumeric key. The second is how to get the generated value.
你的问题有两个部分。第一个是如何创建字母数字键。二是如何获取生成值。
So the first step is to determine the source of the alpha and the numeric components. In the following example I use the USER function and an Oracle sequence, but you will have your own rules. I put the code to assemble the key in a trigger which is called whenever a row is inserted.
因此，第一步是确定 alpha 和数字分量的来源。在以下示例中，我使用 USER 函数和 Oracle 序列，但您将拥有自己的规则。我将用于组装密钥的代码放入触发器中，每当插入一行时都会调用该触发器。
SQL> create table t1 (pk_col varchar2(10) not null, create_date date)
2 /
Table created.
SQL> create or replace trigger t1_bir before insert on t1 for each row
2 declare
3 n pls_integer;
4 begin
5 select my_seq.nextval
6 into n
7 from dual;
8 :new.pk_col := user||trim(to_char(n));
9 end;
10 /
Trigger created.
SQL>
The second step requires using the RETURNING INTOclause to retrieve the generated key. I am using SQL*PLus for this example. I confess to having no idea how to wire this syntax into VB. Sorry.
第二步需要使用RETURNING INTO子句来检索生成的密钥。我在这个例子中使用 SQL*PLus。我承认不知道如何将这种语法连接到 VB 中。对不起。
SQL> var new_pk varchar2(10)
SQL> insert into t1 (create_date)
2 values (sysdate)
3 returning pk_col into :new_pk
4 /
1 row created.
SQL> print new_pk
NEW_PK
--------------------------------
APC61
SQL>
Finally, a word of warning.
最后，警告一下。
Alphanumeric keys are a suspicious construct. They reek of "smart keys" which are, in fact, dumb. A smart key is a value which contains multiple parts. At somepoint you will find yourself wanting to retrieving all rows where the key starts with 'PRD', which means using SUBSTR() or LIKE. Even worse someday the definition of the smart key will change and you will have to cascade a complicated update to your table and its referencing foreign keys. A better ides is to use a surrogate key (number) and have the alphanumeric "key" defined as separate columns with a UNIQUE composite constraint to enforce the business rule.
字母数字键是一种可疑的结构。他们散发着“智能钥匙”的臭味，实际上是愚蠢的。智能键是包含多个部分的值。有时您会发现自己想要检索键以“PRD”开头的所有行，这意味着使用 SUBSTR() 或 LIKE。更糟糕的是，智能键的定义有一天会改变，您将不得不对表及其引用外键进行级联复杂的更新。更好的想法是使用代理键（数字）并将字母数字“键”定义为具有 UNIQUE 复合约束的单独列，以强制执行业务规则。
SQL> create table t1 (id number not null
2 , alpha_bit varchar2(3) not null
3 , numeric_bit number not null
4 , create_date date
5 , constraint t1_pk primary key (id)
6 , constraint t1_uk unique (alpha_bit, numeric_bit)
7 )
8 /
Table created.
SQL>
