DMSQL DM自增列的使用

DM自增列的使用
DM自增列定義
1．自增列功能定義
在表中建立一個自增列。該屬性與CREATE TABLE語句一起使用，一個表只能有一個自增列。
語法格式
IDENTITY [ (種子, 增量) ]
引數
1．種子 裝載到表中的第一個行所使用的值；
2．增量 增量值，該值被新增到前一個已裝載的行的標識值上。增量值可以為正數或負數，但不能為0。

使用說明
1．IDENTITY 適用於int(-2147483648～+2147483647)、bigint(-263～+263-2)型別的列；每個表只能建立一個自增列；
2．不能對自增列使用DEFAULT 約束；
3．必須同時指定種子和增量值，或者二者都不指定。如果二者都未指定，則取預設值(1,1)；若種子或增量為小數型別，報錯；
4．最大值和最小值為該列的資料型別的邊界；
5．建表種子和增量大於最大值或者種子和增量小於最小值時報錯；
6．自增列一旦生成，無法更新，不允許用Update語句進行修改；
7. 臨時表、列儲存表、水平分割槽表、垂直分割槽表不支援使用自增列。

2．自增列查詢函式
1) IDENT_SEED(函式)
語法格式：
IDENT_SEED ('tablename')
功能：返回種子值，該值是在帶有自增列的表中建立自增列時指定的。
引數：tablename：是帶有引號的字串常量，也可以是變數、函式或列名。tablename的資料型別為char或varchar。其含義是表名，可帶模式名字首。
返回型別：返回資料型別為int / NULL
2) IDENT_INCR(函式)
語法格式：
IDENT_INCR ('tablename')
功能：返回增量值，該值是在帶有自增列的表中建立自增列時指定的。
引數：tablename：是帶有引號的字串常量，也可以是變數、函式或列名。tablename的資料型別為char或varchar。其含義是表名，可帶模式名字首。
返回型別：返回資料型別為int / NULL
例如用自增列查詢函式獲得表PERSON_TYPE的自增列的種子和增量資訊。

SQL> SELECT IDENT_SEED('PERSON.PERSON_TYPE');
LINEID     IDENT_SEED('PERSON.PERSON_TYPE')
---------- --------------------------------
1          1
used time: 1.529(ms). Execute id is 46903.
SQL> SELECT IDENT_INCR('PERSON.PERSON_TYPE');
LINEID     IDENT_INCR('PERSON.PERSON_TYPE')
---------- --------------------------------
1          1
used time: 0.956(ms). Execute id is 46905.

SET IDENTITY_INSERT 屬性
設定是否允許將顯式值插入表的自增列中。
語法格式
SET IDENTITY_INSERT [< 模式名>.]< 表名> ON | OFF;
引數
1．< 模式名> 指明表所屬的模式，預設為當前模式；
2．< 表名> 指明含有自增列的表名。
使用說明
1．IDENTITY_INSERT屬性的預設值為OFF。SET IDENTITY_INSERT 的設定是在執行或執行時進行的。當一個連線結束，IDENTITY_INSERT屬性將被自動還原為OFF；
2．DM要求一個會話連線中只有一個表的IDENTITY_INSERT 屬性可以設定為ON，當設定一個新的表IDENTITY_INSERT 屬性設定為ON時，之前已經設定為ON的表會自動還原為OFF。當一個表的IDENTITY_INSERT 屬性被設定為ON時，該表中的自動增量列的值由使用者指定。如果插入值大於表的當前標識值(自增列當前值)，則DM自動將新插入值作為當前標識值使用，即改變該表的自增列當前值；否則，將不影響該自增列當前值；
3．當設定一個表的IDENTITY_INSERT 屬性為OFF時，新插入行中自增列的當前值由系統自動生成，使用者將無法指定；
4．自增列一經插入，無法修改；
5．手動插入自增列，除了將IDENTITY_INSERT設定為ON，還要求在插入列表中明確指定待插入的自增列列名。插入方式與非IDENTITY表是完全一樣的。如果插入時，既不指定自增列名也不給自增列賦值，則新插入行中自增列的當前值由系統自動生成。

舉例說明
例如SET IDENTITY_INSERT 的使用
1) PERSON_TYPE表中的PERSON_TYPEID列是自增列

SQL> select * from person.person_type;
LINEID     PERSON_TYPEID NAME
---------- ------------- --------
1          1             採購經理
2          2             採購代表
3          3             銷售經理
4          4             銷售代表
5          9             衛生員
6          10            保潔員
6 rows got
used time: 12.207(ms). Execute id is 46927.

2) 在該表中插入資料，自增列的值由系統自動生成。

SQL> INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('銷售總監');
affect rows 1
used time: 0.884(ms). Execute id is 46930.
SQL> INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('人力資源部經理');
affect rows 1
used time: 0.749(ms). Execute id is 46931.
SQL> commit;
executed successfully
used time: 21.221(ms). Execute id is 46933.
SQL> select * from person.person_type;
LINEID     PERSON_TYPEID NAME
---------- ------------- --------------
1          1             採購經理
2          2             採購代表
3          3             銷售經理
4          4             銷售代表
5          9             衛生員
6          10            保潔員
7          11            銷售總監
8          12            人力資源部經理
8 rows got
used time: 0.445(ms). Execute id is 46934.

3) 當插入資料並且要指定自增列的值時，必須要透過語句將IDENTITY_INSERT設定為ON 時，插入語句中必須指定PERSON_TYPEID 中要插入的列。例如：

SQL> SET IDENTITY_INSERT PERSON.PERSON_TYPE ON;
executed successfully
used time: 32.673(ms). Execute id is 46938.
SQL> INSERT INTO PERSON.PERSON_TYPE(PERSON_TYPEID, NAME) VALUES( 14, '廣告部經理');
affect rows 1
used time: 0.966(ms). Execute id is 46941.
SQL> INSERT INTO PERSON.PERSON_TYPE(PERSON_TYPEID, NAME) VALUES( 15, '財務部經理');
affect rows 1
used time: 0.788(ms). Execute id is 46943.
SQL> commit;
executed successfully
used time: 25.501(ms). Execute id is 46944.
SQL> select * from person.person_type;
LINEID     PERSON_TYPEID NAME
---------- ------------- --------------
1          1             採購經理
2          2             採購代表
3          3             銷售經理
4          4             銷售代表
5          9             衛生員
6          10            保潔員
7          11            銷售總監
8          12            人力資源部經理
9          14            廣告部經理
10         15            財務部經理
10 rows got
used time: 0.467(ms). Execute id is 46945.

4) 不允許使用者修改自增列的值。

SQL> UPDATE PERSON.PERSON_TYPE SET PERSON_TYPEID = 14 WHERE NAME = '廣告部經理';
UPDATE PERSON.PERSON_TYPE SET PERSON_TYPEID = 14 WHERE NAME = '廣告部經理';
[-2664]:Error in line: 1
Try to alter identity column [PERSON_TYPEID].
used time: 0.565(ms). Execute id is 0.

5) 還原IDENTITY_INSERT屬性。

SQL> SET IDENTITY_INSERT PERSON.PERSON_TYPE OFF;
executed successfully
used time: 0.597(ms). Execute id is 46951.

6) 插入後再次查詢。注意觀察自增列當前值的變化。

SQL> SET IDENTITY_INSERT PERSON.PERSON_TYPE OFF;
executed successfully
used time: 0.597(ms). Execute id is 46951.
SQL> INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('市場總監');
affect rows 1
used time: 1.013(ms). Execute id is 46954.
SQL> commit;
executed successfully
used time: 16.449(ms). Execute id is 46955.
SQL> select * from person.person_type;
LINEID     PERSON_TYPEID NAME
---------- ------------- --------------
1          1             採購經理
2          2             採購代表
3          3             銷售經理
4          4             銷售代表
5          9             衛生員
6          10            保潔員
7          11            銷售總監
8          12            人力資源部經理
9          14            廣告部經理
10         15            財務部經理
11         16            市場總監
11 rows got
used time: 1.000(ms). Execute id is 46956.