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