Explore SQL via DB2 Tpc-c benchmark Implementation (2)

Payment Affairs

There are two versions of payment transactions. For customers who provide a customer ID, use the first version. For customers who do not remember the customer ID, but only the last name, use the second version. Only the second version is discussed here, because it presents the challenges that are not available in the first version.

In the payment transaction (by last name), the following steps must occur:

Retrieves the name and address of the region.

The customer ID of the customer is found based on the last name. If you have more than one customer with the same name in the area, the correct customer should be the "middle" customer based on the customer.

Retrieves the customer's personal information.

Increase the income so far in the region.

Increase the warehouse income so far.

Increase the customer's payment and include additional data if the customer's credit is poor.

Record this payment in history.

As with previous transactions, most of the logic here is encapsulated in a table function called Pay_c_last ().

Listing 13. Table function 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)
One, w_street_2 CHAR (20)
W_city CHAR (20)
W_state CHAR (2)
W_zip CHAR (9)
D_street_1 CHAR (20)
D_street_2 CHAR (20)
D_city CHAR (20)
One, D_state CHAR (2)
D_zip CHAR (9)
C_ID INTEGER
C_first VARCHAR (16)
C_middle CHAR (2)
C_street_1 VARCHAR (20)
C_street_2 VARCHAR (20)
C_city VARCHAR (20)
C_state CHAR (2)
C_zip CHAR (9)
C_phone CHAR (16)
C_since BIGINT
C_credit CHAR (2)
C_credit_lim BIGINT
C_discount INTEGER
C_balance BIGINT
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
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
/* 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;
* * Determine the C_ID * *
78
SET (C_ID)
79 = (
SELECT c_id
80
From (
SELECT c_id
Bayi, COUNT (*) over ()
As COUNT
The 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;
* * Update the customer * *
92
SET (C_first, C_middle, C_street_1, c_street_2
C_city, C_state, C_zip, C_phone, C_since, C_credit, C_credit_lim
C_discount, C_balance, C_data)
95 = (
SELECT C_first, C_middle, C_street_1, c_street_2
C_city, C_state, C_zip, C_phone, C_since, C_credit
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
c_payment_cnt = c_payment_cnt + 1
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;
+ * 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
The same, 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
C_ID, C_first, C_middle, C_street_1, c_street_2
MB, 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