DB2 sequence 测试

主要测试DB2中sequence的用法

db2 "CREATE SEQUENCE seq_2 INCREMENT BY 1 START WITH 1   MAXVALUE 30 MINVALUE 0  NOCYCLE CACHE 10 ORDER"
db2 "CREATE TABLE tab1 (col1 int, col2 int)"
db2 "INSERT INTO tab1 VALUES (0, 0)"

1.) 在同一个SQL里如果NEXTVAL出现两次及以上，也只增加一次：

db2 "INSERT INTO tab1 (col1, col2) VALUES (seq_2.NEXTVAL, seq_2.NEXTVAL)"

db2 "select * from tab1"

COL1        COL2
----------- -----------
          0           0
          1           1

  2 record(s) selected.
 
db2 "select seq_2.CURRVAL from sysibm.sysdummy1"

1
-----------
          1

  1 record(s) selected.

2.) 若一个SQL中同时出现NEXTVAL和CURRVAL，那么NEXTVAL显示的是下一个值，CURRVAL显示的是当前值，这与它俩出现的顺序无关

db2 "SELECT seq_2.CURRVAL, seq_2.NEXTVAL FROM sysibm.sysdummy1"

1           2
----------- -----------
          1           2

  1 record(s) selected.


db2 "SELECT seq_2.CURRVAL FROM sysibm.sysdummy1"

1
-----------
          2

  1 record(s) selected.
  
db2 "SELECT seq_2.NEXTVAL, seq_2.CURRVAL FROM sysibm.sysdummy1"

1           2
----------- -----------
          3           2

  1 record(s) selected.