我想在 Oracle 中生成一个唯一的 id，它包含字母数字，长度为 9 位数字。请帮帮我
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21399764/
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
I want to generate a unique id in Oracle, it contains alphanumerics and length is 9-digits.please help me out
提问by Santhosh Kumar
I want to generate a unique id in Oracle, it contains alphanumerics and length is 9-digits.
我想在 Oracle 中生成一个唯一的 id，它包含字母数字，长度为 9 位。
I tried,
我试过，
==> select substr(sys_guid(),5,9) guid from dual;
==> 从双中选择 substr(sys_guid(),5,9) guid；
will it have the unique nature? please anyone help me out.
它会有独特的性质吗？请任何人帮助我。
Thank u.
感谢你。
采纳答案by MT0
Seems to be overcomplicated when you cound just use a numeric sequence but you could do:
当您只使用数字序列时似乎过于复杂，但您可以这样做：
Oracle 11g R2 Schema Setup:
Oracle 11g R2 架构设置：
CREATE OR REPLACE FUNCTION numberToAlnumString(
n IN NUMBER
) RETURN VARCHAR2
AS
i NUMBER := n;
s VARCHAR2(9);
r NUMBER(2,0);
BEGIN
WHILE i > 0 LOOP
r := MOD( i, 36 );
i := ( i - r ) / 36;
IF ( r < 10 ) THEN
s := TO_CHAR(r) || s;
ELSE
s := CHR( 55 + r ) || s;
END IF;
END LOOP;
RETURN LPAD( s, 9, '0' );
END;
/
CREATE SEQUENCE test__id__seq INCREMENT BY 1 START WITH 1
/
CREATE TABLE test (
id CHAR(9) NOT NULL,
name VARCHAR2(20)
)
/
CREATE OR REPLACE TRIGGER test_ins_trig
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
:new.id := numberToAlnumString( test__id__seq.NEXTVAL );
END;
/
INSERT INTO test ( name )
SELECT TO_CHAR( LEVEL )
FROM DUAL
CONNECT BY LEVEL < 100
/
Query 1:
查询 1：
SELECT * FROM test
结果：
| ID | NAME |
|-----------|------|
| 000000001 | 1 |
| 000000002 | 2 |
| 000000003 | 3 |
| 000000004 | 4 |
| 000000005 | 5 |
| 000000006 | 6 |
| 000000007 | 7 |
| 000000008 | 8 |
| 000000009 | 9 |
| 00000000A | 10 |
| 00000000B | 11 |
| 00000000C | 12 |
| 00000000D | 13 |
| 00000000E | 14 |
| 00000000F | 15 |
| 00000000G | 16 |
| 00000000H | 17 |
| 00000000I | 18 |
| 00000000J | 19 |
| 00000000K | 20 |
| 00000000L | 21 |
| 00000000M | 22 |
| 00000000N | 23 |
| 00000000O | 24 |
| 00000000P | 25 |
| 00000000Q | 26 |
| 00000000R | 27 |
| 00000000S | 28 |
| 00000000T | 29 |
| 00000000U | 30 |
| 00000000V | 31 |
| 00000000W | 32 |
| 00000000X | 33 |
| 00000000Y | 34 |
| 00000000Z | 35 |
| 000000010 | 36 |
| 000000011 | 37 |
| 000000012 | 38 |
| 000000013 | 39 |
| 000000014 | 40 |
| 000000015 | 41 |
| 000000016 | 42 |
| 000000017 | 43 |
| 000000018 | 44 |
| 000000019 | 45 |
| 00000001A | 46 |
| 00000001B | 47 |
| 00000001C | 48 |
| 00000001D | 49 |
| 00000001E | 50 |
| 00000001F | 51 |
| 00000001G | 52 |
| 00000001H | 53 |
| 00000001I | 54 |
| 00000001J | 55 |
| 00000001K | 56 |
| 00000001L | 57 |
| 00000001M | 58 |
| 00000001N | 59 |
| 00000001O | 60 |
| 00000001P | 61 |
| 00000001Q | 62 |
| 00000001R | 63 |
| 00000001S | 64 |
| 00000001T | 65 |
| 00000001U | 66 |
| 00000001V | 67 |
| 00000001W | 68 |
| 00000001X | 69 |
| 00000001Y | 70 |
| 00000001Z | 71 |
| 000000020 | 72 |
| 000000021 | 73 |
| 000000022 | 74 |
| 000000023 | 75 |
| 000000024 | 76 |
| 000000025 | 77 |
| 000000026 | 78 |
| 000000027 | 79 |
| 000000028 | 80 |
| 000000029 | 81 |
| 00000002A | 82 |
| 00000002B | 83 |
| 00000002C | 84 |
| 00000002D | 85 |
| 00000002E | 86 |
| 00000002F | 87 |
| 00000002G | 88 |
| 00000002H | 89 |
| 00000002I | 90 |
| 00000002J | 91 |
| 00000002K | 92 |
| 00000002L | 93 |
| 00000002M | 94 |
| 00000002N | 95 |
| 00000002O | 96 |
| 00000002P | 97 |
| 00000002Q | 98 |
| 00000002R | 99 |
回答by René Nyffenegger
No, this approach will not have the unique nature.
不，这种方法不会有独特的性质。
回答by 124
if you want auto increment in your column value you can use Sequencefor the this.
如果你想在你的列值中自动递增，你可以使用Sequence这个。
CREATE OR REPLACE SEQUENCE dept_seq
INCREMENT BY 1
START WITH 100000000
NOMAXVALUE
NOCYCLE
CACHE 10;
after creating sequence you can use After Insert Triggerto insert identical value.
here is trigger example...
创建序列后，您可以使用After Insert Trigger插入相同的值。这是触发器示例...
CREATE OR REPLACE TRIGGER dep_ins_trig
BEFORE INSERT ON <table_name>
FOR EACH ROW
BEGIN
SELECT dept_seq.NEXTVAL
INTO :new.emp_id
FROM dual;
END;
/
---------------------------------------------------------------------------------------
-------------------------------------------------- -------------------------------------
Triggerand Sequencecan be used when you want serialized (Auto Increment) number that anyone can easily read/remember/understand. But if you don't want to manage ID Column (like emp_id) by this way, and value of this column is not much considerable, you can use SYS_GUID()at Table Creation to get Auto Increment like this.
Trigger并且Sequence可以在您想要任何人都可以轻松阅读/记住/理解的序列化（自动增量）编号时使用。但是如果你不想这样管理ID列（比如emp_id），而且这个列的值又不是很大，那么可以SYS_GUID()在Table Creation中使用这样的Auto Increment。
CREATE TABLE <table_name>
(emp_id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
name VARCHAR2(30));
Now your emp_idcolumn will accept "globally unique identifier value".
you can insert value in table by ignoring emp_id column like this.
现在您的emp_id列将接受“全局唯一标识符值”。您可以通过忽略这样的 emp_id 列在表中插入值。
INSERT INTO <table_name> (name) VALUES ('name value');
So, it will insert unique value to your emp_idColumn.
因此，它将为您的emp_id列插入唯一值。
