Last active
August 29, 2015 14:06
-
-
Save jesseincn/d993b4b9ebfc174a4144 to your computer and use it in GitHub Desktop.
Oracle自增列创建方法
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Oracle没有自增字段这样的功能,但是通过触发器(trigger)和序列(sequence)可以实现。 | |
先建一个测试表了: | |
create table userlogin | |
( | |
id number(6) not null, | |
name varchar2(30) not null primary key | |
) | |
tablespace users | |
/ | |
第一步:创建SEQUENCE | |
create sequence userlogin_seq increment by 1 start with 1 minvalue 1 maxvalue 9999999999999 nocache order; | |
第二步:创建一个基于该表的before insert 触发器,在触发器中使用刚创建的SEQUENCE | |
create or replace trigger userlogin_trigger | |
before insert on userlogin | |
for each row | |
begin | |
select userlogin_seq.nextval into:new.id from sys.dual ; | |
end; | |
/ | |
第三步:在userlogin表中测试 | |
写个insert语句,插入一条记录,看ID字段自增了没,自增则OK啦。 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment