通过DB2 TPC-C基准实现探索SQL
2009-12-23 15:00:09 来源:WEB开发网如果没有看 NEW_OL_LOCAL 表函数,那么对 NEW ORDER 事务的探索就不算完:
清单 5. NEW_OL_LOCAL 表函数
1
CREATE FUNCTION NEW_OL_LOCAL( I_ID INTEGER
2 , I_QTY SMALLINT
3 , W_ID INTEGER
4 , O_ID INTEGER
5 , D_ID SMALLINT
6 , OL_NUMBER SMALLINT
7 )
8
RETURNS TABLE( I_PRICE INTEGER
9 , I_NAME CHAR(24)
0 , I_DATA VARCHAR(50)
11 , OL_DIST_INFO CHAR(24)
12 , S_DATA VARCHAR(50)
13 , S_QUANTITY SMALLINT
14 )
15
SPECIFIC NEW_OL_LOCAL
16
MODIFIES SQL DATA DETERMINISTIC NO EXTERNAL ACTION LANGUAGE SQL
17
18 VAR:
BEGIN ATOMIC
19
DECLARE I_PRICE INTEGER ;
20
DECLARE I_NAME CHAR(24) ;
21
DECLARE I_DATA VARCHAR(50) ;
22
DECLARE OL_DIST_INFO CHAR(24) ;
23
DECLARE S_DATA VARCHAR(50) ;
24
DECLARE S_QUANTITY SMALLINT ;
25
26
SET ( I_PRICE , I_NAME , I_DATA )
27 = (
SELECT
28 I_PRICE
29 , I_NAME
30 , I_DATA
31
32
FROM ITEM
33
WHERE ITEM.I_ID = NEW_OL_LOCAL.I_ID
34 )
35 ;
36
SET ( OL_DIST_INFO , S_DATA , S_QUANTITY )
37 = (
SELECT OL_DIST_INFO
38 , S_DATA
39 , S_QUANTITY
40
FROM NEW TABLE (
UPDATE STOCK
41
INCLUDE ( OL_DIST_INFO CHAR( 24 ) )
42
SET S_QUANTITY =
CASE WHEN S_QUANTITY - NEW_OL_LOCAL.I_QTY >= 10
43
THEN S_QUANTITY - NEW_OL_LOCAL.I_QTY
44
ELSE S_QUANTITY - NEW_OL_LOCAL.I_QTY + 91
45
END
46 , S_ORDER_CNT = S_ORDER_CNT + 1
47 , S_YTD = S_YTD + NEW_OL_LOCAL.I_QTY
48 , OL_DIST_INFO =
CASE D_ID
WHEN 1
THEN S_DIST_01
49
WHEN 2
THEN S_DIST_02
50
WHEN 3
THEN S_DIST_03
51
WHEN 4
THEN S_DIST_04
52
WHEN 5
THEN S_DIST_05
53
WHEN 6
THEN S_DIST_06
54
WHEN 7
THEN S_DIST_07
55
WHEN 8
THEN S_DIST_08
56
WHEN 9
THEN S_DIST_09
57
WHEN 10
THEN S_DIST_10
58
END
59
WHERE S_I_ID = NEW_OL_LOCAL.I_ID
60
AND S_W_ID = NEW_OL_LOCAL.W_ID
61 )
AS U
62 )
63 ;
64
RETURN VALUES( VAR.I_PRICE
65 , VAR.I_NAME
66 , VAR.I_DATA
67 , VAR.OL_DIST_INFO
68 , VAR.S_DATA
69 , VAR.S_QUANTITY
70 )
71 ;
72
END
更多精彩
赞助商链接