--创建
CREATE SEQUENCE MY_SEQ
INCREMENT BY 1
START WITH 100
NOMAXVALUE
NOCYCLE
CACHE 10;
--删除
DROP SEQUENCE MY_SEQ;
--使用
只有在NEXTVAL被前一个事务或者当前事务引用后,才可以使用CURRVAL。在程序中一上来就使用CURRVAL是错误的。SELECT MY_SEQ.NEXTVAL FROM DUAL;
SELECT MY_SEQ.CURRVAL FROM DUAL;
--修改
ALTER SEQUENCE MY_SEQ
INCREMENT BY 10
MAXVALUE 10000
CYCLE
CACHE 20;
CREATE SEQUENCE MY_SEQ
INCREMENT BY 1
START WITH 100
NOMAXVALUE
NOCYCLE
CACHE 10;
--删除
DROP SEQUENCE MY_SEQ;
--使用
只有在NEXTVAL被前一个事务或者当前事务引用后,才可以使用CURRVAL。在程序中一上来就使用CURRVAL是错误的。SELECT MY_SEQ.NEXTVAL FROM DUAL;
SELECT MY_SEQ.CURRVAL FROM DUAL;
--修改
ALTER SEQUENCE MY_SEQ
INCREMENT BY 10
MAXVALUE 10000
CYCLE
CACHE 20;
CREATE SEQUENCE checkup_no_seq
NOCYCLE
MAXVALUE 9999999999
START WITH 2;2、建立触发器:
CREATE OR REPLACE TRIGGER set_checkup_no
BEFORE INSERT ON checkup_history
FOR EACH ROW
DECLARE
next_checkup_no NUMBER;
BEGIN
--Get the next checkup number from the sequence
SELECT checkup_no_seq.NEXTVAL
INTO next_checkup_no
FROM dual;
--use the sequence number as the primary key
--for the record being inserted
:new.checkup_no := next_checkup_no;
END;