通过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