达梦_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)ISV_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;BEGINFOR I IN 1..10 LOOPV_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 THENROLLBACK;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分钟，得到结果