DB2序列簡介及使用 一、創建序列 序列是按照一定的規則生產的數值，序列的作用非常的大，比如銀行交易中的流水號，就是記錄每筆交易的關鍵字段。 通過create sequence語句創建序列，具體語法如下： >>-CREATE--+------------+--SEQUENCE--sequence-name--·-----------> '-OR REPLACE-' .-AS INTEGER--------. >--+-------------------+--·--+------------------------------+---> '-AS--| data-type |-' '-START WITH--numeric-constant-' .-INCREMENT BY 1-----------------. >--·--+--------------------------------+--·---------------------> '-INCREMENT BY--numeric-constant-' .-NO MINVALUE----------------. >--+----------------------------+--·----------------------------> '-MINVALUE--numeric-constant-' .-NO MAXVALUE----------------. .-NO CYCLE-. >--+----------------------------+--·--+----------+--·-----------> '-MAXVALUE--numeric-constant-' '-CYCLE----' .-CACHE 20----------------. .-NO ORDER-. >--+-------------------------+--·--+----------+--·------------->< +-CACHE--integer-constant-+ '-ORDER----' '-NO CACHE----------------' 簡化寫如下： create sequence seq_name start with a increment by b minvalue c maxvalue d no cycle cache e order 參數簡介如下： or replace：若有創建序列重名的序列，那麼將會被替代（先刪除，再創建） as datatype：指定數據類型，這裡說明下，序列的數據類型只能為數值型， 如，smallint，integer，bigint，不帶小數點的decimal類型。 start with：指定序列的起始值，默認情況下，對於升序的序列是當前指定數據類型的最小值， 對於降序序列的是當前指定類型的最大值。 increment by：指定增長的值，默認值為1，正數表示此序列為增長升序的，負數表示此序列為降序的。 minvalue：最小值，若降序序列，no cycle的話，到此值的時候就不再生成序列值了； no minvalue：對於升序序列來說，此值為start with的值，如果start with值未指定的話就是1. 對於降序序列來說，此值就是指定數據類型的最小值。 maxvalue：指定生產序列的最大值， no maxvalue：對於升序序列來說，此值為指定數據類型的最大值； 對於降序序列來說，此值為start with值，若未指定start with值的話就是-1. cycle：循環使用數據值，對於升序序列來說，當達到了最大值之後，下一個值將會是其最小值； 對於降序序列來說，達到最小之後，下一個序列值為其最大值。 no cycle：當達到序列的邊界值之後，就不再產生序列值，默認選項。 cache：緩存序列值，表示每次應用此序列的時候，預先生產並存放在內存中的序列值。 其作用是有效的降低了寫日志的I/O操作。 若在使用的過程中，出現系統錯誤的話，那麼所有這些緩存值將會丟失。 最小值為2，默認為20 no cache：當指定此選項的時候，內存中不會存儲任何序列值，無論出現什麼異常現象都不會影響到此序列， 每次生存新的序列值，都會導致寫日志的I/O操作。 order：按照請求的順序生成值。 no order：不會按照請求的順序生成值，默認情況。 下面創建一個序列seq_001: create sequence seq_001 start with 1 increment by 1 no maxvalue no cycle cache 21 DB20000I SQL 命令成功完成。 查看編目表中存放的序列seq_001的信息： SELECT SEQNAME,START,INCREMENT,MINVALUE,MAXVALUE,CYCLE,CACHE,ORDER FROM SYSCAT.SEQUENCES WHERE SEQNAME='SEQ_001' SEQNAME START INCREMENT MINVALUE MAXVALUE CYCLE CACHE ORDER --------- ---------- ----------- ---------- ------------ ------ ------- -------- SEQ_001 1. 1. 1. 2147483647. N 21 N 1 條記錄已選擇。 注意事項： 1、常量序列，也就是一個不會改變其生成值的序列。 創建的時候，指定增長值為0，即increment by 0，不過start with值不能超過其數據類型的最大最小值； 另一種方法，指定start with值，minvalue值，和maxvalue值相等。 2、當序列定義為no cycle，可以通過alter sequence改變其屬性，讓其達到邊界值之後還能繼續產生序列值， 即從no cycle修改為cycle 3、當定義序列時指定cycle，除了increment by 1或者-1之外，序列生成的最大值將不是指定數據類型的最大值； 如，一個序列定義為start with=1，increment=2，maxvalue=10，則能夠生成的最大值將會是9，而不是10. 4、序列的定義者擁有序列的alter和usage特權（with grant option，可將其授予其他用戶）， 序列的擁有者可以刪除序列。 5、下面2點使用與所有DB2數據庫版本，非標准信息： 1）.A comma can be used to separate multiple sequence options 2）.novinvalue,nomaxvalue,nocycle,nocache,noorder可以替代 no minvalue,no maxvalue,no cycle,no cache,no order。 二、修改序列 語法如下： >>-ALTER SEQUENCE--sequence-name--------------------------------> .-----------------------------------------------. V (1) | >----------+-RESTART--+------------------------+-+-+----------->< | '-WITH--numeric-constant-' | +-INCREMENT BY--numeric-constant------+ +-+-MINVALUE--numeric-constant-+------+ | '-NO MINVALUE----------------' | +-+-MAXVALUE--numeric-constant-+------+ | '-NO MAXVALUE----------------' | +-+-CYCLE----+------------------------+ | '-NO CYCLE-' | +-+-CACHE--integer-constant-+---------+ | '-NO CACHE----------------' | '-+-ORDER----+------------------------' '-NO ORDER-' 參數簡介： restart：重置序列，如果沒有指定with n時，序列將按照create sequence時指定參數去產生值。 with：重置序列，並按照重新指定的值去生成序列值，可以使任意值。 其他參數與create sequence時一樣的。 從語法中可以看出，可以修改的屬性如下： 1、序列其實值（或重置） 2、increment值 3、最大最小值 4、cache值 5、當達到邊界值的時候，是否循環產生序列值 6、是否按照請求順序產生序列值 注意： 1、序列的數據類型不能修改，若要修改，只能刪除當前序列，重建時指定想要的數據類型。 2、當修改的時候，所有的緩存值將會丟失。 3、當將序列修改為cycle之後，序列將會產生重復的值。 下面是一個實例： alter sequence seq_001 increment by 2 maxvalue 20 cycle no cache DB20000I SQL 命令成功完成。 SELECT SEQNAME,START,INCREMENT,MINVALUE,MAXVALUE,CYCLE,CACHE,ORDER FROM SYSCAT.SEQUENCES WHERE SEQNAME='SEQ_001' SEQNAME START INCREMENT MINVALUE MAXVALUE CYCLE CACHE ORDER --------- ---------- ----------- ---------- ------------ ------ ------- -------- SEQ_001 1. 2. 1. 20. Y 1 N 1 條記錄已選擇。 我們可以看出no cache其實是cache 1。 三、序列的使用 可以通過兩個表達式來獲取序列的值，next value獲取下一個值，previous value獲取當前值； 為了保持DB2向後的兼容，也可以使用nextval和prevval獲取下一個序列值和當前序列值。 當首次引用序列的時候，只能使用next value獲取其第一個值；否則將會得到下面的錯誤信息： db2 => insert into test1(cid,cname,sex,age) values(previous value for seq_001,'scott','M',30) DB21034E 該命令被當作 SQL 語句來處理，因為它是無效的“命令行處理器”命令。在 SQL 處理期間，它返回： SQL0845N 在 NEXT VALUE 表達式在序列 "SEQID = 7" 的當前會話中生成值之前，不能使用 PREVIOUS VALUE 表達式。 SQLSTATE=51035 db2 => insert into test1(cid,cname,sex,age) values(next value for seq_001,'scott','M',30) DB20000I SQL 命令成功完成。 db2 => select * from test1 CID CNAME SEX AGE ---------- -------------------- --- ----------- 1 scott M 30 1 條記錄已選擇。 next value和previous value可以使用在select，values，insert，和update語句中，不能使用在where語句中。 --the end--
