通過DB2 TPC-C基準實現探索SQL(2)

PAYMENT事務

PAYMENT事務有兩種版本。對於那些提供了客戶id 的客戶，使用第一種版本。對於不記得客戶ID，而只提供了姓氏的客戶，使用第二種版本。這裡只討論第二種版本，因為其中提出了第一種版本中所沒有的挑戰。

在支付事務（按姓氏）中，必鬚髮生以下步驟：

檢索地區的名稱和地址。

根據姓氏發現客戶的客戶 id。如果在該地區有多個同姓的客戶，則正確的客戶應該是根據客戶的名字得來的“中間”客戶。

檢索客戶的個人資訊。

增加該地區至今為止的收入。

增加倉庫至今為止的收入。

增加客戶的支付額，如果客戶信用不佳，則還應包括額外的資料。

將這次的支付記錄到曆史中。

與前面的事務一樣，這裡的大部分邏輯被封裝到一個名為 PAY_C_LAST() 的表函數中。

清單 13. 表函數 PAY_C_LAST

1
CREATE FUNCTION PAY_C_LAST(　W_ID INTEGER
2　　　　　　　　　　　　　　, D_ID SMALLINT
3　　　　　　　　　　　　　　, C_W_ID INTEGER
4　　　　　　　　　　　　　　, C_D_ID SMALLINT
5　　　　　　　　　　　　　　, C_LAST VARCHAR(16)
6　　　　　　　　　　　　　　, H_DATE BIGINT
7　　　　　　　　　　　　　　, H_AMOUNT BIGINT
8　　　　　　　　　　　　　　, BAD_CREDIT_PREFIX VARCHAR(34)
9　　　　　　　　　　　　　 )
10
RETURNS TABLE(　 W_STREET_1 CHAR(20)
11　　　　　　　　, W_STREET_2 CHAR(20)
12　　　　　　　　, W_CITY CHAR(20)
13　　　　　　　　, W_STATE CHAR(2)
14　　　　　　　　, W_ZIP CHAR(9)
15　　　　　　　　, D_STREET_1 CHAR(20)
16　　　　　　　　, D_STREET_2 CHAR(20)
17　　　　　　　　, D_CITY CHAR(20)
11　　　　　　　　, D_STATE CHAR(2)
19　　　　　　　　, D_ZIP CHAR(9)
20　　　　　　　　, C_ID INTEGER
21　　　　　　　　, C_FIRST VARCHAR(16)
22　　　　　　　　, C_MIDDLE CHAR(2)
23　　　　　　　　, C_STREET_1 VARCHAR(20)
24　　　　　　　　, C_STREET_2 VARCHAR(20)
25　　　　　　　　, C_CITY VARCHAR(20)
26　　　　　　　　, C_STATE CHAR(2)
27　　　　　　　　, C_ZIP CHAR(9)
28　　　　　　　　, C_PHONE CHAR(16)
29　　　　　　　　, C_SINCE BIGINT
30　　　　　　　　, C_CREDIT CHAR(2)
31　　　　　　　　, C_CREDIT_LIM BIGINT
32　　　　　　　　, C_DISCOUNT INTEGER
33　　　　　　　　, C_BALANCE BIGINT
34　　　　　　　　, C_DATA CHAR(200)
35　　　　　　　)
36
SPECIFIC PAY_C_Id
INHERIT ISOLATION LEVEL WITH LOCK REQUEST
37
MODIFIES SQL DATA DETERMINISTIC NO EXTERNAL ACTION LANGUAGE SQL
38 VAR:
BEGIN ATOMIC
39
DECLARE　W_NAME CHAR(10) ;
40
DECLARE　D_NAME CHAR(10) ;
41
DECLARE　W_STREET_1 CHAR(20) ;
42
DECLARE　W_STREET_2 CHAR(20) ;
43
DECLARE　W_CITY CHAR(20) ;
44
DECLARE　W_STATE CHAR(2) ;
45
DECLARE　W_ZIP CHAR(9) ;
46
DECLARE　D_STREET_1 CHAR(20) ;
47
DECLARE　D_STREET_2 CHAR(20) ;
48
DECLARE　D_CITY CHAR(20) ;
49
DECLARE　D_STATE CHAR(2) ;
50
DECLARE　D_ZIP CHAR(9) ;
51
DECLARE　C_ID INTEGER ;
52
DECLARE　C_FIRST VARCHAR(16) ;
53
DECLARE　C_MIDDLE CHAR(2) ;
54
DECLARE　C_STREET_1 VARCHAR(20) ;
55
DECLARE　C_STREET_2 VARCHAR(20) ;
56
DECLARE　C_CITY VARCHAR(20) ;
57
DECLARE　C_STATE CHAR(2) ;
58
DECLARE　C_ZIP CHAR(9) ;
59
DECLARE　C_PHONE CHAR(16) ;
60
DECLARE　C_SINCE BIGINT ;
61
DECLARE　C_CREDIT CHAR(2) ;
62
DECLARE　C_CREDIT_LIM BIGINT ;
63
DECLARE　C_DISCOUNT INTEGER ;
64
DECLARE　C_BALANCE BIGINT ;
65
DECLARE　C_DATA CHAR(200) ;
66
67　 /* Update District and retrieve its data */
68
SET ( D_NAME, D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP )
69　　 =　(
SELECT　 D_NAME, D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP
70
FROM OLD TABLE (
UPDATE DISTRICT
71
SET D_YTD = D_YTD + PAY_C_ID.H_AMOUNT
72
WHERE D_W_ID = PAY_C_ID.W_Id
73
AND D_ID　 = PAY_C_ID.D_Id
74　　　　　　　　　　　　　 )
AS U
75　　　　)
76　 ;
77　 /* Determine the C_ID */
78
SET ( C_ID )
79　　 = (
SELECT C_Id
80
FROM (
SELECT　 C_Id
81　　　　　　　　　　　　 , COUNT(*) OVER()
AS COUNT
82　　　　　　　　　　　　 , ROWNUMBER() OVER (
ORDER BY C_FIRST)
AS NUM
83
FROM CUSTOMER
84
WHERE C_LAST = PAY_C_LAST.C_LAST
85
AND C_W_ID = PAY_C_LAST.C_W_Id
86
AND C_D_ID = PAY_C_LAST.C_D_Id
87　　　　　　　　)
AS T
88
WHERE NUM = (COUNT + 1) / 2
89　　　 )
90　 ;
91　 /* Update the customer */
92
SET (　 C_FIRST, C_MIDDLE, C_STREET_1, C_STREET_2
93　　　　 , C_CITY, C_STATE, C_ZIP, C_PHONE, C_SINCE, C_CREDIT, C_CREDIT_LIM
94　　　　 , C_DISCOUNT, C_BALANCE, C_DATA )
95　　 = (
SELECT　 C_FIRST, C_MIDDLE, C_STREET_1, C_STREET_2
96　　　　　　　　, C_CITY, C_STATE, C_ZIP, C_PHONE, C_SINCE, C_CREDIT
97　　　　　　　　, C_CREDIT_LIM , C_DISCOUNT, C_BALANCE
98　　　　　　　　,
CASE WHEN C_CREDIT = 'BC'
99
THEN SUBSTR(C_DATA, 1, 200)
END AS C_DATA
100
FROM NEW TABLE (
UPDATE CUSTOMER
101
SET　 C_BALANCE　　 = C_BALANCE - PAY_C_ID.H_AMOUNT
102　　　　　　　　　　　　　　　　　 , C_YTD_PAYMENT = C_YTD_PAYMENT + PAY_C_ID.H_AMOUNT
103　　　　　　　　　　　　　　　　　 , C_PAYMENT_CNT = C_PAYMENT_CNT + 1
104　　　　　　　　　　　　　　　　　 , C_DATA =
CASE WHEN C_CREDIT = 'BC'
105
THEN　　BAD_CREDIT_PREFIX
106　　　　　　　　　　　　　　　　　　　　　　　　　　　　|| SUBSTR( C_DATA, 1, 466 )
107
ELSE C_DATA
108
ENd
109
WHERE C_W_ID = PAY_C_ID.C_W_Id
110
AND C_D_ID = PAY_C_ID.C_D_Id
111
AND C_ID　 = PAY_C_ID.C_Id
112　　　　　　　　　　　　　)
AS U
113　　　 )
114　 ;
115　 /* Update the warehouse */
116
SET ( W_NAME, W_STREET_1, W_STREET_2, W_CITY, W_STATE,　W_ZIP )
117　　 = (
SELECT W_NAME, W_STREET_1, W_STREET_2, W_CITY, W_STATE,　W_ZIP
118
FROM OLD TABLE (
UPDATE WAREHOUSE
119
SET W_YTD = W_YTD + PAY_C_ID.H_AMOUNT
120
WHERE W_ID = PAY_C_ID.W_Id
121　　　　　　　　　　　　　)
AS U
122　　　 )
123　 ;
124　 /* Finally insert into the history */
125
INSERT
126
INTO HISTORY ( H_C_ID, H_C_D_ID, H_C_W_ID, H_D_Id
127　　　　　　　　　, H_W_ID, H_DATA, H_DATE, H_AMOUNT )
128
VALUES (　 PAY_C_ID.C_Id
129　　　　　　　, PAY_C_ID.C_D_Id
130　　　　　　　, PAY_C_ID.C_W_Id
131　　　　　　　, PAY_C_ID.D_Id
132　　　　　　　, PAY_C_ID.W_Id
133　　　　　　　, VAR.W_NAME || CHAR( '　　', 4 ) || VAR.D_NAME
134　　　　　　　, PAY_C_ID.H_DATE
135　　　　　　　, PAY_C_ID.H_AMOUNT
136　　　　　　)
137　 ;
138　 /* Done - return the collected data */
139
RETURN VALUES (　 W_STREET_1, W_STREET_2, W_CITY, W_STATE,　W_ZIP
140　　　　　　　　　 , D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP
141　　　　　　　　　 , C_ID , C_FIRST, C_MIDDLE, C_STREET_1, C_STREET_2
142　　　　　　　　　 , C_CITY, C_STATE, C_ZIP, C_PHONE, C_SINCE, C_CREDIT
143　　　　　　　　　 , C_CREDIT_LIM , C_DISCOUNT, C_BALANCE, C_DATA
144　　　　　　　　 )
145　 ;
146
END