DB2中的sequence定義和使用
--第一種方法(自己使用過的,綠色安全無污染): 創建sequence,名為SEQ_LDBG ,自增1, CREATE SEQUENCE SEQ_LDBG AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE NO CYCLE NO CACHE ORDER; --在sql中nextval for SEQ_LDBG這樣使用,codechange7為表名, select nextval for SEQ_LDBG, code,name from codechange7; ---第二種方法: CREATE SEQUENCE SEQ_LDBG START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 24; --獲取下一個值 select nextval for SEQ_LDBG from codechange7; --獲取當前值 select prevval for SEQ_LDBG from codechange7; --插入記錄 insert into codechange7(id) values ( NEXTVAL FOR id) --刪除sequence DROP SEQUENCE SEQ_LDBG ; --序列設置開始值 ALTER SEQUENCE 序列名 (SEQ_LDBG ) RESTART WITH 下一值(1) --修改 修改最大值: ALTER SEQUENCE <sequence_name> MAX VALUE <numeric-constant> | NO MAXVALUE 修改最小值: ALTER SEQUENCE <sequence_name> MIN VALUE <numeric-constant> | NO MINVALUE
(此值需要比當前值小)
修改步長: ALTER SEQUENCE <sequence_name> INCREMENT BY <numeric-constant>; 修改CACHE值: ALTER SEQUENCE <sequence_name> CACHE <numeric-constant> | NO CACHE 修改循環屬性: ALTER SEQUENCE <sequence_name> <CYCLE | NO CYCLE> 修改排序屬性:ALTER SEQUENCE <sequence_name> <ORDER | NO ORDER> 從新計數: ALTER SEQUENCE <sequence_name> RESTART | RESTART WITH <numeric-constant>