达梦_TPC-C测试
1、运行gentpccdata.bat，生成100个仓库的数据，对应文本数据文件的总大小约为8G左右。
也可以运行下面的命令生成需要的数据：
java -Djava.ext.dirs=./ com.tpcc.IIlIIllllIIIIlll 1 100 100
2、建表和存储过程，运行scripts目录下的对应建表、建存储过程脚本
3、关于软件包中提供的数据库脚本tpcc_dm7_script.txt
先创建表（九个）
然后创建存储过程（5个--manager中存储过程最后去掉/）
然后进行文本数据的导入，即执行
“ /opt/dmdbms/bin/dmfldr SYSDBA/loongson123 control=\'/home/loongson/tpc/wxq/warehouse[1-9].ctl\' ”
再执行创建索引的语句（两条语句，第一条缺少一个ON，需要加上）
4、上述修改后的脚本如下：
第一次安装无需执行前九条语句
DROP TABLE C_ORDER_LINE;
DROP TABLE C_HISTORY;
DROP TABLE C_ORDER;
DROP TABLE C_NEW_ORDER;
DROP TABLE C_CUSTOMER;
DROP TABLE C_STOCK;
DROP TABLE C_ITEM;
DROP TABLE C_DISTRICT;
DROP TABLE C_WAREHOUSE;
CREATE TABLE C_ITEM (
I_ID INT NOT NULL,
I_IM_ID INT NULL,
I_NAME VARCHAR (24) NULL,
I_PRICE DOUBLE NULL,
I_DATA VARCHAR (50) NULL,
CLUSTER PRIMARY KEY(I_ID))
;
CREATE TABLE C_WAREHOUSE (
W_ID INT NOT NULL,
W_NAME VARCHAR (10) NULL,
W_STREET_1 VARCHAR (20) NULL,
W_STREET_2 VARCHAR (20) NULL,
W_CITY VARCHAR (20) NULL,
W_STATE CHAR (2) NULL,
W_ZIP CHAR (9) NULL,
W_TAX DOUBLE NULL,
W_YTD DOUBLE NULL,
CLUSTER PRIMARY KEY(W_ID))
;
CREATE TABLE C_DISTRICT (
D_ID INT NOT NULL,
D_W_ID INT NOT NULL,
D_NAME VARCHAR (10) NULL,
D_STREET_1 VARCHAR (20) NULL,
D_STREET_2 VARCHAR (20) NULL,
D_CITY VARCHAR (20) NULL,
D_STATE CHAR (2) NULL,
D_ZIP CHAR (9) NULL,
D_TAX DOUBLE NULL,
D_YTD DOUBLE NULL,
D_NEXT_O_ID INT NULL,
CLUSTER PRIMARY KEY(D_W_ID,D_ID))
;
CREATE TABLE C_STOCK (
S_I_ID INT NOT NULL,
S_W_ID INT NOT NULL,
S_QUANTITY INT NULL,
S_DIST_01 CHAR (24) NULL,
S_DIST_02 CHAR (24) NULL,
S_DIST_03 CHAR (24) NULL,
S_DIST_04 CHAR (24) NULL,
S_DIST_05 CHAR (24) NULL,
S_DIST_06 CHAR (24) NULL,
S_DIST_07 CHAR (24) NULL,
S_DIST_08 CHAR (24) NULL,
S_DIST_09 CHAR (24) NULL,
S_DIST_10 CHAR (24) NULL,
S_DATA VARCHAR (50) NULL ,
S_YTD INT NULL,
S_ORDER_CNT INT NULL,
S_REMOTE_CNT INT NULL,
CLUSTER PRIMARY KEY(S_W_ID,S_I_ID))
;
CREATE TABLE C_CUSTOMER (
C_ID INT NOT NULL,
C_D_ID INT NOT NULL,
C_W_ID INT NOT NULL,
C_FIRST VARCHAR (16) NULL,
C_MIDDLE CHAR (2) NULL,
C_LAST VARCHAR (16) NULL,
C_STREET_1 VARCHAR (20) NULL,
C_STREET_2 VARCHAR (20) NULL,
C_CITY VARCHAR (20) NULL,
C_STATE CHAR (2) NULL,
C_ZIP CHAR (9) NULL,
C_PHONE VARCHAR (16) NULL,
C_SINCE DATETIME NULL,
C_CREDIT CHAR (2) NULL,
C_CREDIT_LIM DOUBLE NULL,
C_DISCOUNT DOUBLE NULL,
C_BALANCE DOUBLE NULL,
C_DATA VARCHAR (500) NULL,
C_YTD_PAYMENT DOUBLE NULL,
C_PAYMENT_CNT INT NULL,
C_DELIVERY_CNT INT NULL,
CLUSTER PRIMARY KEY(C_W_ID,C_D_ID,C_ID))
;
CREATE TABLE C_HISTORY (
H_C_ID INT NULL,
H_C_D_ID INT NULL,
H_C_W_ID INT NULL,
H_D_ID INT NULL,
H_W_ID INT NULL,
H_DATE DATETIME DEFAULT NOW,
H_AMOUNT DOUBLE NULL,
H_DATA VARCHAR (24) NULL )
;
CREATE TABLE C_ORDER (
O_ID INT NOT NULL,
O_C_ID INT NULL,
O_D_ID INT NOT NULL,
O_W_ID INT NOT NULL,
O_ENTRY_D DATETIME DEFAULT NOW,
O_CARRIER_ID INT NULL,
O_OL_CNT INT NULL,
O_ALL_LOCAL INT NULL,
CLUSTER PRIMARY KEY(O_W_ID,O_D_ID,O_ID))
;
CREATE TABLE C_ORDER_LINE (
OL_O_ID INT NOT NULL,
OL_D_ID INT NOT NULL,
OL_W_ID INT NOT NULL,
OL_NUMBER INT NOT NULL,
OL_I_ID INT NULL,
OL_SUPPLY_W_ID INT NULL,
OL_QUANTITY INT NULL,
OL_AMOUNT DOUBLE NULL,
OL_DIST_INFO CHAR (24) NULL,
OL_DELIVERY_D DATETIME NULL,
CLUSTER PRIMARY KEY(OL_W_ID,OL_D_ID,OL_O_ID,OL_NUMBER))
;
CREATE TABLE C_NEW_ORDER(
NO_O_ID INT NOT NULL,
NO_D_ID INT NOT NULL,
NO_W_ID INT NOT NULL,
CLUSTER PRIMARY KEY(NO_W_ID,NO_D_ID,NO_O_ID))
;
-------------------------------
manager工具中将END后的/去掉
CREATE OR REPLACE PROCEDURE TPCC_DELIVERY(V_W_ID IN INT,V_CARRIER_ID IN INT, V_RET OUT INT)
IS
V_D_ID INT;
V_NO_O_ID INT;
V_C_ID INT;
V_OL_TOTAL DOUBLE PRECISION;
CURSOR C_NO FAST IS SELECT NO_O_ID FROM C_NEW_ORDER WHERE NO_D_ID=V_D_ID AND NO_W_ID=V_W_ID ORDER BY NO_O_ID ASC;
BEGIN
FOR I IN 1..10 LOOP
V_D_ID := I;
OPEN C_NO;
FETCH C_NO INTO V_NO_O_ID;
CONTINUE WHEN C_NO%NOTFOUND;
CLOSE C_NO;
DELETE FROM C_NEW_ORDER WHERE NO_O_ID=V_NO_O_ID AND NO_D_ID=V_D_ID AND NO_W_ID=V_W_ID;
SELECT O_C_ID INTO V_C_ID FROM C_ORDER WHERE O_ID=V_NO_O_ID AND O_W_ID=V_W_ID AND O_D_ID=V_D_ID;
UPDATE C_ORDER SET O_CARRIER_ID=V_CARRIER_ID WHERE O_ID=V_NO_O_ID AND O_W_ID=V_W_ID AND O_D_ID=V_D_ID;
UPDATE C_ORDER_LINE SET OL_DELIVERY_D=CURRENT_DATE WHERE OL_O_ID=V_NO_O_ID AND OL_D_ID=V_D_ID AND OL_W_ID=V_W_ID;
SELECT SUM(OL_AMOUNT) INTO V_OL_TOTAL FROM C_ORDER_LINE WHERE OL_O_ID=V_NO_O_ID AND OL_D_ID=V_D_ID AND OL_W_ID=V_W_ID;
UPDATE C_CUSTOMER SET C_BALANCE=C_BALANCE+V_OL_TOTAL WHERE C_ID=V_C_ID AND C_D_ID=V_D_ID AND C_W_ID=V_W_ID;
END LOOP;
COMMIT;
V_RET:=1;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
V_RET:=0;
END;
/
CREATE OR REPLACE PROCEDURE TPCC_NEWORDER
(
V_W_ID IN INT,
V_D_ID IN INT,
V_C_ID IN INT,
V_O_OL_CNT IN INT,
V_I_ID1 IN INT,
V_I_ID2 IN INT,
V_I_ID3 IN INT,
V_I_ID4 IN INT,
V_I_ID5 IN INT,
V_I_ID6 IN INT,
V_I_ID7 IN INT,
V_I_ID8 IN INT,
V_I_ID9 IN INT,
V_I_ID10 IN INT,
V_I_ID11 IN INT,
V_I_ID12 IN INT,
V_I_ID13 IN INT,
V_I_ID14 IN INT,
V_I_ID15 IN INT,
V_S_W_ID1 IN INT,
V_S_W_ID2 IN INT,
V_S_W_ID3 IN INT,
V_S_W_ID4 IN INT,
V_S_W_ID5 IN INT,
V_S_W_ID6 IN INT,
V_S_W_ID7 IN INT,
V_S_W_ID8 IN INT,
V_S_W_ID9 IN INT,
V_S_W_ID10 IN INT,
V_S_W_ID11 IN INT,
V_S_W_ID12 IN INT,
V_S_W_ID13 IN INT,
V_S_W_ID14 IN INT,
V_S_W_ID15 IN INT,
V_OL_QTY1 IN INT,
V_OL_QTY2 IN INT,
V_OL_QTY3 IN INT,
V_OL_QTY4 IN INT,
V_OL_QTY5 IN INT,
V_OL_QTY6 IN INT,
V_OL_QTY7 IN INT,
V_OL_QTY8 IN INT,
V_OL_QTY9 IN INT,
V_OL_QTY10 IN INT,
V_OL_QTY11 IN INT,
V_OL_QTY12 IN INT,
V_OL_QTY13 IN INT,
V_OL_QTY14 IN INT,
V_OL_QTY15 IN INT,
V_O_ALL_LOCAL IN INT,
V_RET OUT INT
)
IS
TYPE REC IS RECORD(
INAME VARCHAR(24),
IPRICE DOUBLE,
OLAMOUNT DOUBLE,
BGFLAG CHAR(1),
SQUANTITY INT);
TYPE ALL_SEL IS TABLE OF REC INDEX BY BINARY_INTEGER
TYPE INTTABLE IS TABLE OF INT INDEX BY BINARY_INTEGER;
V_TMP_OID INT;
V_ARR_ALL ALL_SEL;
V_S_DATA VARCHAR(50);
V_I_DATA VARCHAR(50);
V_DIST_INFO VARCHAR(25);
V_C_DISCOUNT DOUBLE;
V_C_CREDIT CHAR(2);
V_W_TAX DOUBLE;
V_CUR_OID INT;
V_D_TAX DOUBLE;
V_TOTAL_AMOUNT DOUBLE;
V_COMMIT_FLAG INT;
V_O_ENTRY_D DATE;
V_ARR_I_ID INTTABLE ;
V_ARR_W_ID INTTABLE ;
V_ARR_OL_QTY INTTABLE ;
V_TMP_QUANTITY INT;
V_C_LAST VARCHAR(16);
BEGIN
V_ARR_I_ID(1) := V_I_ID1;
V_ARR_I_ID(2) := V_I_ID2;
V_ARR_I_ID(3) := V_I_ID3;
V_ARR_I_ID(4) := V_I_ID4;
V_ARR_I_ID(5) := V_I_ID5;
V_ARR_I_ID(6) := V_I_ID6;
V_ARR_I_ID(7) := V_I_ID7;
V_ARR_I_ID(8) := V_I_ID8;
V_ARR_I_ID(9) := V_I_ID9;
V_ARR_I_ID(10) := V_I_ID10;
V_ARR_I_ID(11) := V_I_ID11;
V_ARR_I_ID(12) := V_I_ID12;
V_ARR_I_ID(13) := V_I_ID13;
V_ARR_I_ID(14) := V_I_ID14;
V_ARR_I_ID(15) := V_I_ID15;
V_ARR_W_ID(1) := V_S_W_ID1;
V_ARR_W_ID(2) := V_S_W_ID2;
V_ARR_W_ID(3) := V_S_W_ID3;
V_ARR_W_ID(4) := V_S_W_ID4;
V_ARR_W_ID(5) := V_S_W_ID5;
V_ARR_W_ID(6) := V_S_W_ID6;
V_ARR_W_ID(7) := V_S_W_ID7;
V_ARR_W_ID(8) := V_S_W_ID8;
V_ARR_W_ID(9) := V_S_W_ID9;
V_ARR_W_ID(10) := V_S_W_ID10;
V_ARR_W_ID(11) := V_S_W_ID11;
V_ARR_W_ID(12) := V_S_W_ID12;
V_ARR_W_ID(13) := V_S_W_ID13;
V_ARR_W_ID(14) := V_S_W_ID14;
V_ARR_W_ID(15) := V_S_W_ID15;
V_ARR_OL_QTY(1) := V_OL_QTY1;
V_ARR_OL_QTY(2) := V_OL_QTY2;
V_ARR_OL_QTY(3) := V_OL_QTY3;
V_ARR_OL_QTY(4) := V_OL_QTY4;
V_ARR_OL_QTY(5) := V_OL_QTY5;
V_ARR_OL_QTY(6) := V_OL_QTY6;
V_ARR_OL_QTY(7) := V_OL_QTY7;
V_ARR_OL_QTY(8) := V_OL_QTY8;
V_ARR_OL_QTY(9) := V_OL_QTY9;
V_ARR_OL_QTY(10) := V_OL_QTY10;
V_ARR_OL_QTY(11) := V_OL_QTY11;
V_ARR_OL_QTY(12) := V_OL_QTY12;
V_ARR_OL_QTY(13) := V_OL_QTY13;
V_ARR_OL_QTY(14) := V_OL_QTY14;
V_ARR_OL_QTY(15) := V_OL_QTY15;
V_TOTAL_AMOUNT := 0;
V_O_ENTRY_D := CURRENT_DATE;
UPDATE C_DISTRICT
SET D_NEXT_O_ID = D_NEXT_O_ID + 1
WHERE D_ID = V_D_ID
AND D_W_ID =V_W_ID
RETURNING D_NEXT_O_ID,D_TAX
INTO V_TMP_OID, V_D_TAX;
V_CUR_OID := V_TMP_OID - 1;
INSERT INTO C_ORDER(O_ID, O_C_ID, O_D_ID, O_W_ID, O_ENTRY_D, O_CARRIER_ID, O_OL_CNT, O_ALL_LOCAL)
VALUES (V_CUR_OID, V_C_ID, V_D_ID, V_W_ID, V_O_ENTRY_D, 0, V_O_OL_CNT, V_O_ALL_LOCAL);
FOR I IN 1..V_O_OL_CNT LOOP
SELECT I_NAME, I_PRICE, I_DATA
INTO V_ARR_ALL(I).INAME, V_ARR_ALL(I).IPRICE, V_I_DATA
FROM C_ITEM
WHERE I_ID = V_ARR_I_ID(I);
UPDATE C_STOCK
SET S_YTD = S_YTD + V_ARR_OL_QTY(I),
S_QUANTITY = S_QUANTITY - V_ARR_OL_QTY(I) + CASE WHEN (S_QUANTITY - V_ARR_OL_QTY(I) < 10) THEN 91 ELSE 0 END,
S_ORDER_CNT = S_ORDER_CNT + 1,
S_REMOTE_CNT = S_REMOTE_CNT + CASE WHEN (V_W_ID = V_ARR_W_ID(I)) THEN 0 ELSE 1 END
WHERE S_I_ID = V_ARR_I_ID(I)
AND S_W_ID=V_ARR_W_ID(I)
RETURNING S_DATA, S_QUANTITY
INTO V_S_DATA, V_ARR_ALL(I).SQUANTITY;
IF((INSTR(V_I_DATA,'ORIGINAL') > 0) AND (INSTR(V_S_DATA,'ORIGINAL') > 0)) THEN
V_ARR_ALL(I).BGFLAG := 'B';
ELSE
V_ARR_ALL(I).BGFLAG := 'G';
END IF;
CASE V_D_ID
WHEN 1 THEN SELECT S_DIST_01 INTO V_DIST_INFO FROM C_STOCK WHERE S_I_ID=V_ARR_I_ID(I) AND S_W_ID=V_ARR_W_ID(I);
WHEN 2 THEN SELECT S_DIST_02 INTO V_DIST_INFO FROM C_STOCK WHERE S_I_ID=V_ARR_I_ID(I) AND S_W_ID=V_ARR_W_ID(I);
WHEN 3 THEN SELECT S_DIST_03 INTO V_DIST_INFO FROM C_STOCK WHERE S_I_ID=V_ARR_I_ID(I) AND S_W_ID=V_ARR_W_ID(I);
WHEN 4 THEN SELECT S_DIST_04 INTO V_DIST_INFO FROM C_STOCK WHERE S_I_ID=V_ARR_I_ID(I) AND S_W_ID=V_ARR_W_ID(I);
WHEN 5 THEN SELECT S_DIST_05 INTO V_DIST_INFO FROM C_STOCK WHERE S_I_ID=V_ARR_I_ID(I) AND S_W_ID=V_ARR_W_ID(I);
WHEN 6 THEN SELECT S_DIST_06 INTO V_DIST_INFO FROM C_STOCK WHERE S_I_ID=V_ARR_I_ID(I) AND S_W_ID=V_ARR_W_ID(I);
WHEN 7 THEN SELECT S_DIST_07 INTO V_DIST_INFO FROM C_STOCK WHERE S_I_ID=V_ARR_I_ID(I) AND S_W_ID=V_ARR_W_ID(I);
WHEN 8 THEN SELECT S_DIST_08 INTO V_DIST_INFO FROM C_STOCK WHERE S_I_ID=V_ARR_I_ID(I) AND S_W_ID=V_ARR_W_ID(I);
WHEN 9 THEN SELECT S_DIST_08 INTO V_DIST_INFO FROM C_STOCK WHERE S_I_ID=V_ARR_I_ID(I) AND S_W_ID=V_ARR_W_ID(I);
WHEN 10 THEN SELECT S_DIST_10 INTO V_DIST_INFO FROM C_STOCK WHERE S_I_ID=V_ARR_I_ID(I) AND S_W_ID=V_ARR_W_ID(I);
END;
V_ARR_ALL(I).OLAMOUNT := V_ARR_OL_QTY(I) * V_ARR_ALL(I).IPRICE;
V_TOTAL_AMOUNT := V_TOTAL_AMOUNT + V_ARR_ALL(I).OLAMOUNT;
INSERT INTO C_ORDER_LINE(OL_O_ID, OL_D_ID, OL_W_ID, OL_NUMBER, OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY, OL_AMOUNT, OL_DIST_INFO, OL_DELIVERY_D)
VALUES(V_CUR_OID, V_D_ID, V_W_ID, I, V_ARR_I_ID(I), V_ARR_W_ID(I), V_ARR_OL_QTY(I), V_ARR_ALL(I).OLAMOUNT, V_DIST_INFO ,DATE'1900-01-01');
END LOOP;
INSERT INTO C_NEW_ORDER VALUES(V_CUR_OID,V_D_ID,V_W_ID);
select c_last, c_discount, c_credit, w_tax INTO V_C_LAST, V_C_DISCOUNT, V_C_CREDIT, V_W_TAX from C_CUSTOMER, C_WAREHOUSE
where w_id=v_w_id and c_w_id=v_w_id and c_d_id=v_d_id and c_id=v_c_id;
COMMIT;
V_RET:=1;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
V_RET:=0;
END;
/
CREATE OR REPLACE PROCEDURE TPCC_ORDSTAT
(
V_C_ID IN OUT INT,
V_D_ID IN INT,
V_W_ID IN INT,
V_C_LAST IN OUT VARCHAR(16),
V_BYNAME IN INT,
V_RET OUT INT
)
IS
V_C_BALANCE DOUBLE;
V_C_FIRST VARCHAR(16);
V_C_MIDDLE VARCHAR(2);
V_O_ID,V_O_CARRIER_ID INT;
V_O_ENTRY_D DATE;
CNT INT;
CURSOR C3 IS SELECT C_BALANCE, C_FIRST, C_MIDDLE, C_ID
FROM C_CUSTOMER
WHERE C_LAST = V_C_LAST
AND C_D_ID = V_D_ID
AND C_W_ID = V_W_ID
ORDER BY C_FIRST;
CURSOR C_LINE IS SELECT OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY,OL_AMOUNT, OL_DELIVERY_D
FROM C_ORDER_LINE
WHERE OL_O_ID = V_O_ID
AND OL_D_ID = V_D_ID
AND OL_W_ID = V_W_ID;
TYPE INTTABLE IS TABLE OF INT INDEX BY BINARY_INTEGER;
TYPE AMOUNTTABLE IS TABLE OF DOUBLE PRECISION INDEX BY BINARY_INTEGER;
TYPE DATETABLE IS TABLE OF DATE INDEX BY BINARY_INTEGER;
OS_C_LINE C_LINE%ROWTYPE;
OS_OL_I_ID INTTABLE ;
OS_OL_SUPPLY_W_ID INTTABLE ;
OS_OL_QUANTITY INTTABLE ;
OS_OL_AMOUNT AMOUNTTABLE;
OS_OL_DELIVERY_D DATETABLE;
I INT;
BEGIN
IF V_BYNAME = 0 THEN
SELECT C_BALANCE, C_FIRST, C_MIDDLE, C_LAST
INTO V_C_BALANCE, V_C_FIRST, V_C_MIDDLE, V_C_LAST
FROM C_CUSTOMER
WHERE C_ID = V_C_ID
AND C_D_ID = V_D_ID
AND C_W_ID = V_W_ID;
ELSE
SELECT COUNT(C_ID)
INTO CNT
FROM C_CUSTOMER
WHERE C_LAST = V_C_LAST
AND C_D_ID = V_D_ID
AND C_W_ID = V_W_ID;
IF (MOD(CNT, 2) = 1) THEN
CNT := (CNT + 1);
END IF;
CNT := CNT / 2;
OPEN C3;
FOR I IN 1 .. CNT
LOOP
FETCH C3 INTO V_C_BALANCE, V_C_FIRST, V_C_MIDDLE, V_C_ID;
END LOOP;
CLOSE C3;
END IF;
SELECT O_ID, O_CARRIER_ID, O_ENTRY_D
INTO V_O_ID, V_O_CARRIER_ID, V_O_ENTRY_D
FROM (
SELECT TOP 1 O_ID, O_CARRIER_ID, O_ENTRY_D
FROM C_ORDER
WHERE O_C_ID = V_C_ID
AND O_D_ID = V_D_ID
AND O_W_ID = V_W_ID
ORDER BY O_ID DESC) ;
-- WHERE ROWNUM = 1;
I := 1;
FOR OS_C_LINE IN C_LINE
LOOP
OS_OL_I_ID(I) := OS_C_LINE.OL_I_ID;
OS_OL_SUPPLY_W_ID(I) := OS_C_LINE.OL_SUPPLY_W_ID;
OS_OL_QUANTITY(I) := OS_C_LINE.OL_QUANTITY;
OS_OL_AMOUNT(I) := OS_C_LINE.OL_AMOUNT;
OS_OL_DELIVERY_D(I) := OS_C_LINE.OL_DELIVERY_D;
I := I + 1;
END LOOP;
COMMIT;
V_RET:=1;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
V_RET:=0;
END;
/
CREATE OR REPLACE PROCEDURE TPCC_PAYMENT
(
V_W_ID IN INT,
V_D_ID IN INT,
V_C_ID IN OUT INT,
V_C_W_ID IN INT,
V_C_D_ID IN INT,
V_C_LAST IN OUT VARCHAR2,
V_A_AMOUNT IN DOUBLE,
V_BYNAME IN INT,
V_RET OUT INT
)
IS
V_W_STATE,V_D_STATE,V_C_STATE,V_C_MIDDLE,V_C_CREDIT VARCHAR(2);
V_C_CREDIT_LIM,V_C_DISCOUNT,V_C_BALANCE DOUBLE;
V_C_SINCE,V_H_DATE DATE;
V_W_STREET_1,V_W_STREET_2,V_W_CITY,V_D_STREET_1,V_D_STREET_2,V_D_CITY,V_C_STREET_1,V_C_STREET_2,V_C_CITY VARCHAR(20);
V_C_FIRST,V_C_PHONE VARCHAR(16);
V_W_NAME,V_D_NAME VARCHAR(10);
V_W_ZIP,V_D_ZIP,V_C_ZIP VARCHAR(9);
V_C_DATA VARCHAR(500);
C_DATA_TMP VARCHAR(500);
P_C_NEW_DATA VARCHAR(500);
CNT INT;
CURSOR C1 IS SELECT C_ID
FROM C_CUSTOMER
WHERE C_LAST = V_C_LAST
AND C_D_ID = V_D_ID
AND C_W_ID = V_C_W_ID
ORDER BY C_FIRST;
BEGIN
UPDATE C_WAREHOUSE
SET W_YTD = W_YTD + V_A_AMOUNT
WHERE W_ID = V_W_ID
RETURNING W_NAME, W_STREET_1, W_STREET_2, W_CITY, W_STATE, W_ZIP
INTO V_W_NAME, V_W_STREET_1, V_W_STREET_2, V_W_CITY, V_W_STATE, V_W_ZIP;
UPDATE C_DISTRICT
SET D_YTD = D_YTD + V_A_AMOUNT
WHERE D_W_ID = V_W_ID
AND D_ID = V_D_ID
RETURNING D_NAME, D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP
INTO V_D_NAME, V_D_STREET_1, V_D_STREET_2, V_D_CITY, V_D_STATE, V_D_ZIP;
IF(V_BYNAME = 1) THEN
SELECT COUNT(*)
INTO CNT
FROM C_CUSTOMER
WHERE C_LAST = V_C_LAST
AND C_D_ID = V_C_D_ID
AND C_W_ID = V_C_W_ID;
IF (MOD(CNT, 2) = 1) THEN
CNT := (CNT + 1);
END IF;
CNT := CNT / 2;
OPEN C1;
FOR I IN 1..CNT LOOP
FETCH C1 INTO V_C_ID;
END LOOP;
CLOSE C1;
END IF;
UPDATE C_CUSTOMER
SET C_BALANCE = C_BALANCE - V_A_AMOUNT,
C_YTD_PAYMENT = C_YTD_PAYMENT + V_A_AMOUNT,
C_PAYMENT_CNT = C_PAYMENT_CNT + 1
WHERE C_W_ID = V_C_W_ID
AND C_D_ID = V_C_D_ID
AND C_ID = V_C_ID
RETURNING C_FIRST, C_MIDDLE, C_LAST, 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
INTO V_C_FIRST, V_C_MIDDLE, V_C_LAST, V_C_STREET_1, V_C_STREET_2, V_C_CITY, V_C_STATE, V_C_ZIP,
V_C_PHONE, V_C_SINCE, V_C_CREDIT, V_C_CREDIT_LIM, V_C_DISCOUNT, V_C_BALANCE;
IF(V_C_CREDIT = 'BC') THEN
P_C_NEW_DATA := '' || V_C_ID || ' ' || V_C_D_ID || ' ' || V_C_W_ID ||
' ' || V_D_ID || ' ' || V_W_ID || ' ' || V_A_AMOUNT;
UPDATE C_CUSTOMER
SET C_DATA = SUBSTR(P_C_NEW_DATA || C_DATA,
1,
500 - LENGTH(P_C_NEW_DATA))
WHERE C_W_ID = V_C_W_ID
AND C_D_ID = V_C_D_ID
AND C_ID = V_C_ID
RETURNING C_DATA INTO C_DATA_TMP;
V_C_DATA := SUBSTR(C_DATA_TMP, 1, 200);
END IF;
V_H_DATE := CURRENT_DATE;
INSERT INTO C_HISTORY(H_C_D_ID, H_C_W_ID, H_C_ID, H_D_ID,H_W_ID, H_DATE, H_AMOUNT, H_DATA)
VALUES (V_C_D_ID, V_C_W_ID, V_C_ID, V_D_ID, V_W_ID, V_H_DATE, V_A_AMOUNT, V_W_NAME || ' ' || V_D_NAME);
COMMIT;
V_RET:=1;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
V_RET:=0;
END;
／
CREATE OR REPLACE PROCEDURE TPCC_STOCKLEVEL(V_W_ID IN INT,V_D_ID IN INT,V_A IN INT, O_RET OUT INT)
IS V_D_NEXT_O_ID INT;
V_DISTINCT_I_ID INT;
BEGIN
SELECT D_NEXT_O_ID INTO V_D_NEXT_O_ID FROM C_DISTRICT WHERE D_ID = V_D_ID AND D_W_ID = V_W_ID;
SELECT COUNT (DISTINCT S_I_ID) INTO V_DISTINCT_I_ID FROM C_ORDER_LINE, C_STOCK
WHERE OL_D_ID=V_D_ID AND OL_W_ID =V_W_ID
AND OL_I_ID = S_I_ID AND S_W_ID=V_W_ID AND S_QUANTITY < V_A
AND OL_O_ID BETWEEN (V_D_NEXT_O_ID - 20) AND (V_D_NEXT_O_ID - 1);
COMMIT;
O_RET:=1;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
O_RET:=0;
END;
/
------------------------
将文本文件导入后再执行：
CREATE INDEX ORDER01 ON C_ORDER("O_W_ID" ASC,"O_D_ID" ASC,"O_C_ID" ASC,"O_ID" DESC);
CREATE INDEX CUST01 ON C_CUSTOMER(C_W_ID,C_D_ID,C_LAST,C_FIRST);
/*按照TPC-C标准，所有存储过程都应该返回一些信息给客户端，但是这样极易引发网络瓶颈，因此所有的存储过程都只返回是否成功，本应该返回客户端的信息，改为保存到数据库的本地变量中*/
5、使用各数据库的文本数据装载工具，将文本文件中的数据装载到数据库中
[warehouse.ctl文件]
OPTIONS ( SKIP = 0 ROWS = 50000 DIRECT = TRUE INDEX_OPTION = 2 ) LOAD DATA INFILE '/home/loongson/tpc/tpcc_1_100/warehouse1/***.tbl' STR X '0D0A' BADFILE 'warehous.bad' INTO TABLE *** (tpcc_dm7_script.txt中创建的9个表) FIELDS '|'
(分别执行一次，共需执行九次)
/opt/dmdbms/bin/dmfldr SYSDBA/loongson123 control=\'/home/loongson/tpc/wxq/warehouse[1-9].ctl\'可以同时进行
6、运行测试：
需要在windows系统下运行tpcctest.bat程序，并将jdbc_url项中的localhost修改成测试机器的IP地址
右键，Run Benchmark测试进行10分钟，得到结果
