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 || C