WEB开发网
开发学院数据库DB2 通过DB2 TPC-C基准实现探索SQL 阅读

通过DB2 TPC-C基准实现探索SQL

 2009-12-23 15:00:09 来源:WEB开发网   
核心提示:如果没有看 NEW_OL_LOCAL 表函数,那么对 NEW ORDER 事务的探索就不算完:清单 5. NEW_OL_LOCAL 表函数1CREATE FUNCTION NEW_OL_LOCAL( I_IDINTEGER2 , I_QTY SMALLINT3 , W_IDINTEGER4 , O_IDINTEGER5

如果没有看 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

上一页  1 2 3 4 5 6 7 8 9 10  下一页

Tags:通过 DB TPC

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接