-- 序列操作 --
-- 创建序列
CREATE SEQUENCE u_sales_SEQ INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
-- 查出所有存在的序列
SELECT * FROM user_sequences
-- 删除序列
DROP SEQUENCE U_SALES_SEQ;
-- 查出下一个序列ID
SELECT U_SALES_SEQ.nextval FROM DUAL
-- 查出当前序列的ID
SELECT U_SALES_SEQ.currval FROM DUAL;
2、Alter Sequence
你或者是该sequence的owner,或者有ALTER ANY SEQUENCE 权限才能改动sequence. 可以alter除start至以外的所有
sequence参数.如果想要改变start值,必须 drop sequence 再 re-create .
Alter sequence 的例子
ALTER SEQUENCE emp_sequence
INCREMENT BY 10
MAXVALUE 10000
CYCLE -- 到10000后从头开始
NOCACHE ;
影响Sequence的初始化参数:
SEQUENCE_CACHE_ENTRIES =设置能同时被cache的sequence数目。
可以很简单的Drop Sequence
DROP SEQUENCE order_seq;
--建触发器
CREATE OR REPLACE TRIGGER table1_tg
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
SELECT table1_seq.NEXTVAL INTO :NEW.id FROM DUAL;
END;