Oracle 的自动增量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9733085/
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
Auto Increment for Oracle
提问by David Garcia
I need to create a sequence and a trigger to auto-increment the primary key on a table but I have no idea on how to do it.
我需要创建一个序列和一个触发器来自动增加表上的主键，但我不知道如何去做。
回答by Justin Cave
Create the table and the sequence
创建表和序列
SQL> create table staff (
2 emp_id number primary key,
3 staff_name varchar2(100)
4 );
Table created.
SQL> create sequence emp_id_seq;
Sequence created.
Now, you can create a trigger that uses the sequence to populate the primary key
现在，您可以创建一个使用序列填充主键的触发器
SQL> create trigger trg_emp_id
2 before insert on staff
3 for each row
4 begin
5 select emp_id_seq.nextval
6 into :new.emp_id
7 from dual;
8 end;
9 /
Trigger created.
Now, when you insert data, you woon't need to specify the EMP_IDcolumn-- it will automatically be populated by the trigger
现在，当你插入数据时，你不需要指定EMP_ID列——它会被触发器自动填充
SQL> insert into staff( staff_name ) values ('Justin');
1 row created.
SQL> select * from staff;
EMP_ID STAFF_NAME
---------- --------------------
1 Justin
回答by mr_eclair
Read this, Beautiful article.
阅读这篇，美丽的文章。
how sequence [auto increment in oracle]
如何序列[oracle中的自动增量]
syntax
句法
Create sequence sequence_name
start with value
increment by value
minvalue value
maxvalue value;
example
例子
SQL> create table emp (
emp_id number(10),
fname varchar2(25),
lname varchar2(25),
constraint pk_emp_id PRIMARY KEY(emp_id)
);
SQL> Create sequence emp_sequence
start with 1
increment by 1
minvalue 1
maxvalue 10000;
SQL> insert into emp (emp_id,fname,lname) values(emp_sequence.nextval,'Darvin','Johnson');
SQL> insert into emp (emp_id,fname,lname) values(emp_sequence.nextval,'Mig','Andrews');
SQL> insert into emp (emp_id,fname,lname) values(emp_sequence.nextval,'Alex','Martin');
SQL> insert into emp (emp_id,fname,lname) values(emp_sequence.nextval,'Jon','paul');
SQL> insert into emp (emp_id,fname,lname) values(emp_sequence.nextval,'Yatin','Bones');
in emp_sequence.nextvalwhere emp_sequenceis the name of sequence we created above and nextvalis a function that is used to assign the next number from emp_sequence to emp_idcolumn in emp table.
in emp_sequence.nextvalwhereemp_sequence是我们上面创建的序列的名称，nextval是一个函数，用于分配next number from emp_sequence to emp_idemp 表中的列。
SQL> select * from emp;
EMP_ID FNAME LNAME
---------- ------------------------- -------------------------
1 Darvin Johnson
2 Mig Andrews
3 Alex Martin
4 Jon paul
5 Yatin Bones
回答by Vinod
Try this:
尝试这个：
create sequence seq_EmpID start with 1 increment by 1
insert into Emp_Table values(seq_EmpID.nextval,'Ram')
回答by gourav agrawal
Very good question!! Probably sequence can be used in this way - also, I am not sure if there really is a difference :
很好的问题！！可能可以以这种方式使用序列 - 另外，我不确定是否真的有区别：
CREATE SEQUENCE emp_id_seq MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;
回答by M.Chakir
If you use a sequence for several tables, because the value of the sequence is inconsistent example: we have two tables emp and depeartement: If I use the sequence on emp I would have: ID_dept = 6 because the 5 is already used in the other table.
如果对多个表使用一个序列，因为序列的值不一致 示例：我们有两个表 emp 和 depeartement：如果我在 emp 上使用序列，我会得到： ID_dept = 6 因为 5 已经在另一个表中使用了桌子。
example :
例子 ：
SQL> insert into emp values(masequence.nextval,'aaa');
1 ligne crúúe.
1 法郎。
SQL> insert into departement values(masequence.nextval,'aaa');
1 ligne crúúe.
1 法郎。
SQL> select * from emp;
ID_EMP NOM_EMP
---------- -------------------------
5 aaa
SQL> select * from departement;
ID_DEPT NOM_DEPT
---------- ----------
6 aaa
SQL>
