Oracle與DB2中序列處理

1、序列定義    
   ORACLE:   

             CREATE SEQUENCE <sequence_name>

          INCREMENT BY <integer>

              START WITH <integer>

           MAXVALUE <integer> / NOMAXVALUE

           MINVALUE <integer> / NOMINVALUE

              CYCLE / NOCYCLE

              CACHE <#> / NOCACHE

              ORDER / NOORDER;   
    
   DB2:    

             CREATE SEQUENCE <sequence-name> 

                 AS data-type                           預設 As Integer
              START WITH <numeric-constant>   
          INCREMENT BY <numeric-constant>               預設 INCREMENT BY 1
           MINVALUE <numeric-constant> | NO MINVALUE    預設 NO MINVALUE
           MAXVALUE <numeric-constant> | NO MAXVALUE    預設 NO MAXVALUE
           NO CYCLE | CYCLE                             預設 NO CYCLE
           CACHE <numeric-constant> | NO CACHE          預設 CACHE 20
           NO ORDER | ORDER                             預設 NO ORDER

2、序列取值
   取下一個值：
      ORACLE: 序列.NEXTVAL
      DB2:    NEXTVAL FOR 序列 或 NEXT VALUE FOR 序列
   取當前值：
      ORACLE: 序列.CURRVAL
      DB2:    PREVIOUS VALUE FOR 序列  或 PREVVAL FOR 序列

3、序列設定開始值
     ORACLE：取得下一個值；先設定步長（下一個值 與 設定的開始值之間差）；取得下一個值；再把步長修改成原來值
     DB2：ALTER SEQUENCE 序列名  RESTART WITH 下一值

3、修改
     ORACLE：
 修改最大值    ALTER SEQUENCE <sequence_name> MAX VALUE <integer>
 修改最小值：  ALTER SEQUENCE <sequence_name> MIN VALUE <integer> （此值需要比當前值小）
 修改步長：    ALTER SEQUENCE <sequence_name> INCREMENT BY <integer>;
 修改CACHE值： ALTER SEQUENCE <sequence_name> CACHE <integer> | NOCACHE
 修改迴圈屬性: ALTER SEQUENCE <sequence_name> <CYCLE | NOCYCLE>
 修改排序屬性：ALTER SEQUENCE <sequence_name> <ORDER | NOORDER>

     DB2： 
 修改最大值    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>

3、DROP 
    ORACLE： DROP SEQUENCE <sequence_name>; 
    DB2：    DROP SEQUENCE <sequence_name>;