Oracle Sequence
一,创建
1.简单创建
Create sequence myseq;
简单创建的默认设置如下:
CREATE SEQUENCE GZDQMP.MYSEQ
START WITH 21
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
increment by 1
CACHE 20
NOORDER;
2.详细则是针对每个值进行设置
CREATE SEQUENCE GZDQMP.MYSEQ --所有者GZDQMP
START WITH 21 --开始于21
MAXVALUE 99999999999999999 --最大值
MINVALUE 1 --最小值
NOCYCLE --不循环(确保唯一)
increment by 1 --增量为1
CACHE 20 --高速缓存大小20(Oracle一次放20个序列号到内存,提高存取速度)
NOORDER;
二、获取序列号
Select myseq.nextval from dual;--获取下一个值
Select myseq.currval from dual;--获取当前值
三、删除序列
drop sequence myseq;
四、序列与自增触发器的应用
create sequence table_name_seq;
CREATE OR REPLACE TRIGGER table_name_tri
BEFORE INSERT
ON table_name
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
IF :new.id IS NULL
THEN
SELECT table_name_seq.NEXTVAL INTO :NEW.ID FROM DUAL;
END IF;
END;
一,创建
1.简单创建
Create sequence myseq;
简单创建的默认设置如下:
CREATE SEQUENCE GZDQMP.MYSEQ
START WITH 21
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
increment by 1
CACHE 20
NOORDER;
2.详细则是针对每个值进行设置
CREATE SEQUENCE GZDQMP.MYSEQ --所有者GZDQMP
START WITH 21 --开始于21
MAXVALUE 99999999999999999 --最大值
MINVALUE 1 --最小值
NOCYCLE --不循环(确保唯一)
increment by 1 --增量为1
CACHE 20 --高速缓存大小20(Oracle一次放20个序列号到内存,提高存取速度)
NOORDER;
二、获取序列号
Select myseq.nextval from dual;--获取下一个值
Select myseq.currval from dual;--获取当前值
三、删除序列
drop sequence myseq;
四、序列与自增触发器的应用
create sequence table_name_seq;
CREATE OR REPLACE TRIGGER table_name_tri
BEFORE INSERT
ON table_name
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
IF :new.id IS NULL
THEN
SELECT table_name_seq.NEXTVAL INTO :NEW.ID FROM DUAL;
END IF;
END;