使用示例，我們的 SQL 專家向您展示了標(biāo)識列和最近引入的序列對象的典型用法，以自動生成數(shù)值。
簡介
IBM? DB2? Universal Database?(UDB) for Linux、UNIX? 和 Windows? V8.1 和最新發(fā)布的 DB2 Universal Database for z/OS? and OS/390? V8 都支持一組豐富的功能，這組功能可以自動生成數(shù)值序列。這些最新的 DB2 UDB 發(fā)行版在以前引入了標(biāo)識列的基礎(chǔ)上，又引入了數(shù)據(jù)庫 序列對象。在本文中，我們簡要討論標(biāo)識列和序列的典型用法，然后提供一些示例，對它們進(jìn)行演示并比較它們的功能。
| |
DB2 UDB for Linux、UNIX 和 Windows |
DB2 UDB for z/OS and OS/390 |
DB2 UDB for iSeries? |
| 標(biāo)識列 |
V7 |
V6 |
V5R2 |
| 序列對象 |
V7.2 |
V8（在撰寫本文時(shí)還是 Beta 版） |
計(jì)劃將來推出發(fā)行版 |
| 國際 SQL 標(biāo)準(zhǔn)：已計(jì)劃將標(biāo)識列和序列對象列入國際 SQL 標(biāo)準(zhǔn)的下一版本。DB2 中實(shí)現(xiàn)的支持與所提議的內(nèi)容緊密匹配，而且該支持已列入國際 SQL 標(biāo)準(zhǔn)的當(dāng)前草案版本。 |
生成數(shù)字序列的兩種方法
在 DB2 中可以用兩種方法自動生成一個(gè)數(shù)字序列：
IDENTITY 列
當(dāng)用 IDENTITY 屬性定義表的列時(shí)，每當(dāng)將一行插入表時(shí)，就會自動為該列生成一個(gè)數(shù)值。
SEQUENCE 對象
讓 DB2 自動生成數(shù)字序列的第二種方法是創(chuàng)建 SEQUENCE 對象?？梢允褂?序列表達(dá)式（sequence expression）來引用序列對象。序列表達(dá)式可以出現(xiàn)在表達(dá)式能夠出現(xiàn)的大多數(shù)位置。序列表達(dá)式可以指定要返回的值是新生成的值還是以前生成的值。
如果序列引用是針對 下一值（next value），就會為序列自動生成一個(gè)數(shù)值，并將該數(shù)值作為序列表達(dá)式的結(jié)果返回。例如，如果我們假設(shè)已經(jīng)創(chuàng)建了名為 orders_seq 的序列，該序列表達(dá)式就返回為序列生成的下一個(gè)值：
NEXT VALUE FOR orders_seq
|
如果序列引用是針對 前一值（previous value），那么會將前一個(gè) SQL 語句中為序列生成的數(shù)值作為序列表達(dá)式的結(jié)果返回。該序列表達(dá)式返回由序列生成的前一個(gè)值：
PREVIOUS VALUE FOR orders_seq
|
注：當(dāng) DB2 UDB 引入序列時(shí)，曾支持非 SQL 標(biāo)準(zhǔn)語法 NEXTVAL 代替 NEXT VALUE ，以及 PREVVAL 代替 PREVIOUS VALUE 。這些變體繼續(xù)受到支持。
在標(biāo)識和序列之間做出選擇
雖然標(biāo)識和序列都用于生成數(shù)值，但您也許要根據(jù)特殊情況選擇使用一種來代替另一種。
在以下情況下，標(biāo)識列很有用：
- 表中只有一列需要自動生成的值
- 每一行都需要獨(dú)立的值
- 用自動生成器來產(chǎn)生表的主鍵
- 生成新值的進(jìn)程與對表的插入操作緊密聯(lián)系，無論插入操作是如何發(fā)生的
在以下情況下，序列對象很有用：
- 要將從一個(gè)序列生成的值存儲到多個(gè)表中
- 每個(gè)表有多個(gè)列需要自動生成的值（可能通過使用同一個(gè)序列或多個(gè)序列為每一行生成多個(gè)值）
- 生成新值的進(jìn)程與任何對表的引用無關(guān)
與序列對象不同，標(biāo)識列是在表上定義的，因此需要某些限制。每個(gè)表最多只能有一個(gè)標(biāo)識列。當(dāng)創(chuàng)建一個(gè)列作為標(biāo)識列時(shí)，對于該列必須使用確切的數(shù)字?jǐn)?shù)據(jù)類型。因?yàn)闃?biāo)識屬性為列生成一個(gè)值，這類似于 DEFAULT 子句所做的，所以在定義標(biāo)識列時(shí)不能指定 DEFAULT 子句。標(biāo)識列被隱式地定義成 NOT NULL 。
示例 1. 組合客戶和供應(yīng)商表
要說明標(biāo)識列的典型用法，請考慮這樣一個(gè)表：它包含了虛構(gòu)的 Widget 公司的數(shù)據(jù)庫的客戶訂單信息。該公司希望為插入表中的每一行（訂單）自動生成訂單號。
用于我們的示例的 DDL
如 清單 1 所示，我們使用了用于訂單號的標(biāo)識列，他們將訂單號列定義成主鍵的一部分。注：該列中的以及它本身的 IDENTITY 屬性并沒有保證所生成的序列值是唯一的。但是， PRIMARY KEY 約束保證了表中行的唯一性。為了確保只將自動生成的值插入標(biāo)識列，他們指定了 GENERATED ALWAYS 子句。在每個(gè)季度結(jié)束時(shí)，Widget 公司使用最后一個(gè)生成的 order_id 來確定這個(gè)季度接了多少訂單。選項(xiàng) NO CACHE 和 ORDER 確保了在系統(tǒng)故障的情況下，不廢棄未使用的標(biāo)識值。Widget 公司計(jì)劃通過使用 ALTER TABLE 語句，又從 1 重新開始訂單號列，以開始一個(gè)新的季度。
清單 1中顯式地顯示了標(biāo)識列的所有屬性，即使所設(shè)定的值是未指定值時(shí)的缺省值。因?yàn)槿笔≈禃蚋鞴?yīng)商實(shí)現(xiàn)而不同，所以指定所有選項(xiàng)是一個(gè)很好的編碼習(xí)慣。
清單 1. 使用 IDENTITY 列創(chuàng)建訂單表
CREATE TABLE customer_orders_t (
order_id
INT NOT NULL
GENERATED ALWAYS
AS IDENTITY
(START WITH 1
INCREMENT BY 1
MINVALUE 1
NO MAXVALUE
NO CYCLE
NO CACHE
ORDER),
order_date
DATE NOT NULL,
cust_id
INT NOT NULL,
product_id
INT NOT NULL,
quantity
INT NOT NULL,
price
DECIMAL(10,2)
NOT NULL,
status
CHAR(9)
NOT NULL,
PRIMARY KEY (order_date, order_id))
|
以下是將行插入表的 INSERT 語句的示例。
INSERT INTO customer_orders_t
VALUES
(
DEFAULT, CURRENT DATE,
:cid, :pid, :qty, :cost, 'PENDING')
|
Widget 公司不僅管理客戶訂單，而且還管理供應(yīng)商訂單。供應(yīng)商訂單在一個(gè)獨(dú)立的供應(yīng)商訂單表中，這個(gè)表的定義方式與定義客戶訂單表的方式非常相似。
CREATE TABLE supplier_orders_t (
order_id
INT NOT NULL
GENERATED ALWAYS
AS IDENTITY
(
START WITH 1
INCREMENT BY 1
MINVALUE 1
NO MAXVALUE
NO CYCLE
NO CACHE
ORDER),
order_date
DATE NOT NULL,
supp_id
INT NOT NULL,
product_id
INT NOT NULL,
quantity
INT NOT NULL,
price
DECIMAL(10,2)
NOT NULL,
status
CHAR(9)
NOT NULL,
PRIMARY KEY (order_date, order_id))
|
以下是將行插入 supplier_orders_t 表的 INSERT 語句的示例：
INSERT INTO supplier_orders_t
VALUES
(
DEFAULT, CURRENT DATE,
:sid, :pid, :qty, :cost, 'PENDING')
|
組合表
該公司意識到通過將客戶訂單表和供應(yīng)商訂單表組合成用于客戶和供應(yīng)商的一個(gè)總訂單表，可以獲得更大的效率和協(xié)同?？蛻粲唵位蚬?yīng)商訂單之間唯一的區(qū)別是訂單是進(jìn)來還是出去，這反映在 STATUS 字段中。為了組合這些表，并造成最小中斷，他們計(jì)劃逐漸引入這種更改。他們的計(jì)劃中的步驟包括：
- 使訂單號同步，這些訂單號是為每個(gè)表生成的，這樣在表之間它們就是唯一的。
- 等待，直到完成所有非同步訂單。（或者，他們可以等到一個(gè)季度開始，那時(shí)將復(fù)位訂單號。）
- 逐步停止使用供應(yīng)商訂單表，使用客戶訂單表來管理來自客戶和供應(yīng)商的所有訂單。
- 清除。
第 1 步：使訂單號同步
要使在這兩個(gè)表中使用的生成的 order_id 號碼同步，則更改這兩個(gè)表，這樣可以由用于標(biāo)識列的 SEQUENCE 對象提供值，而不總是生成值。由單個(gè)序列 orders_seq 為這兩個(gè)表中的標(biāo)識列生成值。通過引用 NEXT VALUE 表達(dá)式中的 orders_seq ，修改每個(gè)表的 INSERT 語句以顯式地向標(biāo)識列提供值。 orders_seq 序列的定義方式如下：
CREATE SEQUENCE orders_seq
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
NO MAXVALUE
NO CYCLE
NO CACHE
ORDER
|
用 SET GENERATED BY DEFAULT子句將客戶訂單表和供應(yīng)商訂單表改成允許插入操作顯式地向標(biāo)識列提供值。
ALTER TABLE customer_orders_t
ALTER COLUMN order_id
SET GENERATED BY DEFAULT
ALTER TABLE supplier_orders_t
ALTER COLUMN order_id
SET GENERATED BY DEFAULT
|
在將兩個(gè)訂單表的全部 INSERT 語句修改為向 order_id 列提供顯式值且 orders_seq 序列從適當(dāng)?shù)闹甸_始的時(shí)候，發(fā)出 LOCK TABLE 語句來限制對這兩個(gè)表的插入操作。
以下是如何更改用于供應(yīng)商和客戶訂單表的 INSERT 語句：
INSERT INTO customer_orders_t
VALUES
(
NEXT VALUE FOR orders_seq,
CURRENT DATE,
:cid, :pid, :qty, :cost, 'PENDING')
INSERT INTO supplier_orders_t
VALUES
(
NEXT VALUE FOR orders_seq,
CURRENT DATE,
:sid, :pid, :qty, :cost, 'PENDING')
|
那么以下是如何修改 orders_seq 序列，使它從客戶訂單和供應(yīng)商訂單表的 order_id 標(biāo)識列所生成的最大值的后一個(gè)值開始。首先，使用 SELECT 語句確定該值：
SELECT MAX(c.order_id),
MAX(s.order_id)
FROM customer_orders_t c, supplier_orders_t s
|
例如，假設(shè)以上查詢返回兩個(gè)值：42331 和 57231。那么可以按以下方式改變 orders_seq 序列：
ALTER SEQUENCE orders_seq
RESTART WITH 57232
|
COMMIT 語句釋放表上的鎖，那么又可以對這兩個(gè)訂單表執(zhí)行插入操作了。所插入的值是從單個(gè)序列 orders_seq 生成的，并不是每個(gè) order_id 列擁有由標(biāo)識屬性各自獨(dú)立地生成的值，因此這些值在兩個(gè)表中將是唯一的。
請參閱 圖 1以獲取 第 1 步的圖釋。
圖 1. order_id 序列值覆蓋標(biāo)識列
第 2 步：等待，直到同步了訂單為止
Widget 公司并不想等到季度開始（那時(shí)訂單被復(fù)位）；而是決定監(jiān)控訂單的狀態(tài)。當(dāng)以下查詢的結(jié)果是空表時(shí)，他們轉(zhuǎn)到 第 3 步：
SELECT order_id
FROM (
SELECT order_id
FROM customer_orders_t
WHERE status <> 'COMPLETED'
AND order_id < 57232)
AS x
UNION ALL (
SELECT order_id
FROM supplier_orders_t
WHERE status <> 'COMPLETED'
AND order_id < 57232)
|
請參閱 圖 2以獲取 第 2 步的圖釋。
圖 2. 消除了所有潛在的重復(fù) order_id 號碼
第 3 步：逐步停止使用供應(yīng)商訂單
要逐步停止使用 supplier_orders_t 表，通過按以下方式重命名該表，就可以使它暫時(shí)不可用：
RENAME TABLE supplier_orders_t
TO supplier_orders_t_old
|
然后，會創(chuàng)建一個(gè)視圖來允許對 supplier_orders_t 的現(xiàn)有引用繼續(xù)訪問底層數(shù)據(jù)：
CREATE VIEW supplier_orders_t
(order_id, order_date, supp_id, product_id, quantity,
price, status)
AS SELECT
order_id, order_date, cust_id, product_id, quantity,
price, status
FROM customer_orders_t
|
現(xiàn)在傳統(tǒng)客戶和供應(yīng)商的所有活動訂單都在 customer_orders_t 表中進(jìn)行管理。要使將來更方便、更直觀地維護(hù)這些表，還需要在 第 4 步中執(zhí)行一些清除工作。
請參閱 圖 3以獲取 第 3 步的圖釋。
圖 3. 所有新訂單都進(jìn)入 customer_orders_t
第 4 步：清除
因?yàn)楝F(xiàn)在必須只為一個(gè)列（ order_id ）生成訂單號，所以可以由標(biāo)識列生成值，而不是使用單獨(dú)的序列對象。同樣，表暫時(shí)不可用，而且標(biāo)識值被復(fù)位成由序列生成的下一個(gè)值。
LOCK TABLE customer_orders_t
IN EXCLUSIVE MODE
VALUES NEXT VALUE FROM orders_seq
INTO :nextorder
|
例如，假設(shè)上述查詢返回值：64243。那么，可以按以下方式改變 customer_orders_t 表：
ALTER TABLE customer_orders_t
ALTER COLUMN order_id
SET GENERATED ALWAYS
RESTART WITH 64243
|
每個(gè)實(shí)例（在這些實(shí)例中， INSERT 語句使用 orders_seq 序列）同樣需要更改，回到使用 DEFAULT ，如以前在 第 1 步中所顯示的?，F(xiàn)在可以刪除該序列：
DROP SEQUENCE orders_seq
RESTRICT
|
在歸檔了舊的供應(yīng)商訂單表中的數(shù)據(jù)之后，也可以刪除該表。
請參閱 圖 4以獲取 第 4 步的圖釋。
圖 4. 再次使用標(biāo)識列生成值，從 64243 開始
調(diào)優(yōu)性能
既然使用 customer_orders_t 表的次數(shù)幾乎是原來的兩倍，Widget 公司決定將該表放在并行環(huán)境中。為了利用現(xiàn)在可以執(zhí)行的并行插入操作，他們決定通過高速緩存值來調(diào)優(yōu)該表的標(biāo)識列的性能。他們確定了高速緩存大小 50 適合于每小時(shí)創(chuàng)建的訂單數(shù)量以及出于任何原因而重新啟動數(shù)據(jù)庫系統(tǒng)的頻率。他們還更改了如何計(jì)算一個(gè)季度中實(shí)際創(chuàng)建的訂單數(shù)，因此沒有理由強(qiáng)制按順序生成序列值。仍然繼續(xù)要滿足在一個(gè)季度中要生成唯一值的主要需求，因此做出了以下調(diào)整來增強(qiáng)用于標(biāo)識列的序列生成的性能：
ALTER TABLE customer_orders_t
ALTER COLUMN order_id
SET CACHE 50
SET NO ORDER
|
示例 2. 收集衛(wèi)星讀數(shù)
如 示例 1中所示，序列允許跨多個(gè)表生成唯一值。序列還允許為一個(gè)表中的多個(gè)列自動生成它們的值。
對于該示例，請考慮一個(gè)圍繞行星或月亮的軌道衛(wèi)星。這個(gè)特定衛(wèi)星 SAT1 旨在獲取它的軌道上的 16 個(gè)不同點(diǎn)的數(shù)據(jù)讀數(shù)。收集該數(shù)據(jù)的表有三個(gè)列，在這些列中會自動生成值：一列使用標(biāo)識屬性生成讀數(shù)標(biāo)識，另兩列則從序列中獲取它們的值。請參閱 圖 5獲取說明。
圖 5. 衛(wèi)星和它的軌道上的 16 個(gè)數(shù)據(jù)讀取點(diǎn)
用于我們的示例的 DDL
因?yàn)樽x數(shù)的數(shù)字可能非常大，標(biāo)識列使用 DECIMAL(31) 數(shù)據(jù)類型。
CREATE TABLE SAT1_readings (
reading_id
DECIMAL(31)
NOT NULL PRIMARY KEY
GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 1
MINVALUE 1
NO MAXVALUE
NO CYCLE
NO CACHE
ORDER),
orbit_location
SMALLINT NOT NULL,
horizon_adjustment
SMALLINT NOT NULL,
planet_image
BLOB(100
M))
|
orbit_location 有一個(gè)值，該值從 0 到 15，代表軌道上獲取讀數(shù)的 16 個(gè)點(diǎn)。創(chuàng)建以下序列來生成這 16 個(gè)值的循環(huán)序列：
CREATE SEQUENCE orbit_location_seq
AS SMALLINT
START WITH 0
INCREMENT BY 1
MINVALUE 0
MAXVALUE 15
CYCLE
NO CACHE
ORDER
|
horizon_adjustment 值表示衛(wèi)星與水平面相關(guān)的位置在哪里。0 值表示它在水平面上，值 +4 表示它位于水平面上的最高點(diǎn)，值 -4 表示它位于水平面下的最低點(diǎn)。這些序列從值 -4 開始，這既不是最小值，也不是最大值，因?yàn)樗鼘⒂糜诜匠淌街?，以便?jì)算水平調(diào)整值。序列對于每個(gè)軌道周期性循環(huán)。計(jì)算 horizon_adjustment 值時(shí)將使用以下序列：
CREATE SEQUENCE horizon_adjustment_seq
AS SMALLINT
START WITH -4
INCREMENT BY 1
MINVALUE -7
MAXVALUE 8
CYCLE
NO CACHE
ORDER
|
填充讀數(shù)表
每次產(chǎn)生讀數(shù)時(shí)，以下 INSERT 語句就會填充表：
INSERT INTO SAT1_readings
VALUES
(
DEFAULT, NEXT VALUE FOR orbit_location_seq,
ABS(NEXT VALUE FOR horizon_adjustment_seq) - 4,
:planet_image)
|
在前 17 次讀取和相應(yīng)的 INSERT 語句之后，為列 reading_id (r_i)、orbit_location (o_l) 和 horizon_adjustment (h_a) 自動生成的值是：
| r_i |
1. |
2. |
3. |
4. |
5. |
6. |
7. |
8. |
9. |
10. |
11. |
12. |
13. |
14. |
15. |
16. |
17. |
| o_l |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
15 |
0 |
| h_a |
0 |
-1 |
-2 |
-3 |
-4 |
-3 |
-2 |
-1 |
0 |
1 |
2 |
3 |
4 |
3 |
2 |
1 |
0 |
捕捉附加讀數(shù)
因?yàn)閮x器是足夠敏感的，在衛(wèi)星實(shí)際到達(dá)軌道之前，可以對行星進(jìn)行讀數(shù)。儀器的范圍允許在衛(wèi)星進(jìn)入其軌道之前，當(dāng)它接近行星時(shí)，可以進(jìn)行 10 次讀數(shù)。要捕捉這些在正常軌道圈之外的附加讀數(shù)，序列值從定義循環(huán)極限的 MINVALUE 和 MAXVALUE 值的范圍外開始。
以下語句改變標(biāo)識值和序列值以允許讀取軌道上的 16 個(gè)點(diǎn)之前先獲取附加的 10 個(gè)讀數(shù)。RESTART 選項(xiàng)從指定值重新開始標(biāo)識列或序列，或者當(dāng)沒有指定值時(shí)，在創(chuàng)建標(biāo)識列或序列時(shí)從用 START WITH 值指定的值重新開始。
ALTER TABLE SAT1_readings
ALTER COLUMN reading_id
RESTART
ALTER SEQUENCE orbit_location_seq
RESTART WITH -10
ALTER SEQUENCE horizon_adjustment_seq
RESTART WITH -14
|
以下的表顯示了：如果在相應(yīng)的 INSERT 語句之前執(zhí)行了以上三個(gè) ALTER 語句，那么前 17 個(gè)讀數(shù)將會是什么。這 17 個(gè)讀數(shù)包括在軌道之前獲得的 10 個(gè)讀數(shù)和在軌道上獲得的前七個(gè)讀數(shù)：
| r_i |
1. |
2. |
3. |
4. |
5. |
6. |
7. |
8. |
9. |
10. |
11. |
12. |
13. |
14. |
15. |
16. |
17. |
| o_l |
-10 |
-9 |
-8 |
-7 |
-6 |
-5 |
-4 |
-3 |
-2 |
-1 |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
| h_a |
10 |
9 |
8 |
7 |
6 |
5 |
4 |
3 |
2 |
1 |
0 |
-1 |
-2 |
-3 |
-4 |
-3 |
-2 |
結(jié)束語
DB2 支持兩種靈活的自動生成數(shù)值的方式：標(biāo)識列（與一個(gè)表緊密相連）和序列對象（獨(dú)立于任何表引用來生成值）。上面的示例說明了標(biāo)識列和序列對象用于自動生成數(shù)值的功能是多么強(qiáng)大以及有多么靈活。這些示例具體演示了標(biāo)識列和序列的以下功能：
- 使用主鍵中的標(biāo)識列
- 在兩個(gè)表的情況下使用序列對象
- 為標(biāo)識列顯式地指定一個(gè)值，而不是生成值
- 從預(yù)先確定的值開始值的序列
- 從指定值重新開始標(biāo)識列值
- 高速緩存標(biāo)識值以提高性能
- 定義一個(gè)序列或標(biāo)識列，以允許值循環(huán)
- 為序列指定一個(gè)開始值，該值大于為該序列定義的最小值
- 為序列指定一個(gè)開始值，該值是負(fù)值
- 定義一個(gè)序列，以生成先增加再減少的值
- 確保按生成值的順序返回這些值
我們希望您發(fā)現(xiàn)序列和標(biāo)識會對您的應(yīng)用程序有用。
|
