4.TPC-C测试工具——导入TPCC数据
1. 创建目录
使用SYSTEM用户
创建测试工具的输出目录如下：
CREATE OR REPLACE DIRECTORY tpcc_log AS '/tmp';
GRANT READ,WRITE ON DIRECTORY tpcc_log TO tpcc;
然后使用TPCC用户修改会话语言和地域。
alter session set NLS_LANGUAGE = AMERICAN;
alter session set NLS_TERRITORY = AMERICA;
2. 列含义
W_ID：仓库号
D_W_ID：地区号
C_W_ID：客户号
订货种类数OL_cnt
1%新订单模拟用户输出数据错误而回滚
OL_I_ID:货物号,由函数NURand(8191,1,100000)生成ol_cnt个OL_I_ID。
OL_SUPPLY_W_ID:送货仓库号，x=random(1..100),x>1本地供货，x=1远程供货。
QL_QUANTITY:每种货物订购数量
O_ENTRY_D：订单日期
1. 相关包创建包
CREATE OR REPLACE PACKAGE tpcc_server_side
AUTHID CURRENT_USER
IS
PROCEDURELoadSchema(count_ware NUMBER);
FUNCTIONRandomNumber (p_min NUMBER, p_max NUMBER) RETURN NUMBER;
FUNCTIONNURand (p_const NUMBER, p_x NUMBER, p_y NUMBER, p_c NUMBER)
RETURNNUMBER;
FUNCTIONLastname (num NUMBER) RETURN VARCHAR;
FUNCTIONMakeAlphaString (p_x NUMBER, p_y NUMBER) RETURN VARCHAR;
FUNCTIONMakeZip RETURN VARCHAR;
FUNCTIONMakeNumberString RETURN VARCHAR;
END;
/
2. 包体如下
CREATE OR REPLACE PACKAGE BODY tpcc_server_side
IS
c_maxitemsCONSTANT NUMBER := 100000;
c_customers_per_district CONSTANT NUMBER := 3000;
c_districts_per_warehouse CONSTANT NUMBER := 10;
c_orders_per_district CONSTANT NUMBER := 3000;
trace_fileUTL_FILE.FILE_TYPE;
trace_directoryVARCHAR2(30) := 'TPCC_LOG';
TYPEnamearray IS TABLE OF VARCHAR2(10)
INDEX BYBINARY_INTEGER;
namearrnamearray;
TYPEglobarray IS TABLE OF CHAR
INDEX BYBINARY_INTEGER;
listglobarray;
TYPE numarrayIS TABLE OF BINARY_INTEGER
INDEX BYBINARY_INTEGER;
TYPE addressIS RECORD
(
street_1VARCHAR2(20),
street_2VARCHAR2(20),
city VARCHAR2(20),
state CHAR(2),
zip CHAR(9)
);
PROCEDUREOpenTrace
(
directory_name VARCHAR2,
file_nameVARCHAR2
)
IS
BEGIN
trace_file:= utl_file.fopen (directory_name,file_name,'a');
END;
PROCEDUREWriteTrace (s VARCHAR2)
IS
BEGIN
utl_file.put (trace_file,TO_CHAR (SYSDATE,'HH24:MI:SS '));
utl_file.put_line (trace_file,s);
utl_file.fflush (trace_file);
END;
PROCEDURECloseTrace
IS
BEGIN
utl_file.fclose (trace_file);
END;
FUNCTIONRandomNumber (p_min NUMBER, p_max NUMBER)
RETURN NUMBER
IS
BEGIN
RETURNTRUNC (ABS (dbms_random.value (p_min,p_max)));
END;
FUNCTIONNURand (p_const NUMBER, p_x NUMBER, p_y NUMBER, p_c NUMBER)
RETURN NUMBER
IS
l_rand_numNUMBER;
l_ran1NUMBER;
l_ran2NUMBER;
BEGIN
l_ran1 :=RandomNumber (0,p_Const);
l_ran2 :=RandomNumber (p_x,p_y);
l_rand_num:= MOD (l_ran1+l_ran2-BITAND(l_ran1,l_ran2)+p_c, p_y-p_x+1) + p_x;
RETURNl_rand_num;
END;
FUNCTIONLastname (num NUMBER) RETURN VARCHAR
IS
nameVARCHAR2(20);
BEGIN
name :=namearr (TRUNC (MOD ((num / 100),10)))||
namearr(TRUNC (MOD ((num / 10),10)))||
namearr(TRUNC (MOD ((num / 1),10)));
RETURNname;
END;
FUNCTIONMakeAlphaString (p_x NUMBER, p_y NUMBER)
RETURNVARCHAR
IS
l_lenNUMBER := RandomNumber (p_x,p_y);
l_stringVARCHAR2(4000) := '';
l_ch CHAR;
BEGIN
FOR i IN0..l_len - 1 LOOP
l_ch :=list (TRUNC (ABS (dbms_random.value (0,list.COUNT -1))));
l_string:= l_string || l_ch;
END LOOP;
RETURNl_string;
END;
FUNCTIONMakeZip
RETURNVARCHAR
IS
l_zipVARCHAR2(10) := '000011111';
l_ranzNUMBER := RandomNumber (0,9999);
l_lenNUMBER := LENGTH (TO_CHAR (l_ranz));
BEGIN
l_zip :=TO_CHAR (l_ranz) || SUBSTR (l_zip,l_len + 1,9);
returnl_zip;
END;
FUNCTION MakeAddress RETURN address
IS
addaddress;
BEGIN
add.street_1 := MakeAlphaString (10,20);
add.street_2 := MakeAlphaString (10,20);
add.city := MakeAlphaString(10,20);
add.state := MakeAlphaString(2,2);
add.zip := MakeZip;
return add;
END;
FUNCTIONMakeNumberString
RETURNVARCHAR
IS
l_zeroedVARCHAR2(8);
l_a NUMBER;
l_b NUMBER;
l_lenaNUMBER;
l_lenbNUMBER;
l_c_paVARCHAR2(8);
l_c_pb VARCHAR2(8);
BEGIN
l_zeroed :='00000000';
l_a :=RandomNumber (0,99999999);
l_b :=RandomNumber (0,99999999);
l_lena :=LENGTH (TO_CHAR (l_a));
l_lenb :=LENGTH (TO_CHAR (l_b));
l_c_pa :=TO_CHAR (l_a)||SUBSTR (l_zeroed,l_lena + 1);
l_c_pb :=TO_CHAR (l_b)||SUBSTR (l_zeroed,l_lenb + 1);
RETURNl_c_pa||l_c_pb;
END;
PROCEDURECustomer
(
p_d_idNUMBER,
p_w_idNUMBER,
p_customers_per_district NUMBER
)
IS
indxPLS_INTEGER := 0;
lst_indxPLS_INTEGER := 0;
end_custPLS_INTEGER := 1;
l_nrndNUMBER;
l_c_addaddress;
TYPEl_c_id_aat IS TABLE OF NUMBER(5) INDEX BY PLS_INTEGER;
TYPEl_c_d_id_aat IS TABLE OF NUMBER(2) INDEX BY PLS_INTEGER;
TYPEl_c_w_id_aat IS TABLE OF NUMBER(4) INDEX BY PLS_INTEGER;
TYPEl_c_first_aat IS TABLE OF VARCHAR(16) INDEX BY PLS_INTEGER;
TYPEl_c_middle_aat IS TABLE OF CHAR(2) INDEX BY PLS_INTEGER;
TYPEl_c_last_aat IS TABLE OF VARCHAR2(16) INDEX BY PLS_INTEGER;
TYPEl_c_street_1_aat IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
TYPEl_c_street_2_aat IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
TYPEl_c_city_aat IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
TYPEl_c_state_aat IS TABLE OF CHAR(2) INDEX BY PLS_INTEGER;
TYPEl_c_zip_aat IS TABLE OF CHAR(9) INDEX BY PLS_INTEGER;
TYPEl_c_phone_aat IS TABLE OF CHAR(16) INDEX BY PLS_INTEGER;
TYPEl_c_credit_aat IS TABLE OF CHAR(2) INDEX BY PLS_INTEGER;
TYPEl_c_credit_lim_aat IS TABLE OF NUMBER(12,2) INDEX BY PLS_INTEGER;
TYPEl_c_discount_aat IS TABLE OF NUMBER(4,4) INDEX BY PLS_INTEGER;
TYPEl_c_balance_aat IS TABLE OF NUMBER(12,2) INDEX BY PLS_INTEGER;
TYPEl_c_data_aat IS TABLE OF VARCHAR2(500) INDEX BY PLS_INTEGER;
TYPEl_h_amount_aat IS TABLE OF HISTORY.H_AMOUNT%TYPE INDEX BY PLS_INTEGER;
TYPEl_h_data_aat IS TABLE OF HISTORY.H_DATA%TYPE INDEX BY PLS_INTEGER;
l_c_id l_c_id_aat;
l_c_d_id l_c_d_id_aat;
l_c_w_id l_c_w_id_aat;
l_c_first l_c_first_aat;
l_c_middle l_c_middle_aat;
l_c_last l_c_last_aat;
l_c_street_1 l_c_street_1_aat;
l_c_street_2 l_c_street_2_aat;
l_c_city l_c_city_aat;
l_c_state l_c_state_aat;
l_c_zip l_c_zip_aat;
l_c_phone l_c_phone_aat;
l_c_credit l_c_credit_aat;
l_c_credit_lim l_c_credit_lim_aat;
l_c_discount l_c_discount_aat;
l_c_balance l_c_balance_aat;
l_c_data l_c_data_aat;
l_h_amount l_h_amount_aat;
l_h_data l_h_data_aat;
BEGIN
WriteTrace('Loading Customer for D='||p_d_id||' W='||p_w_id);
FOR i IN 1..p_customers_per_district LOOP
l_c_id(i):= i;
l_c_d_id(i) := p_d_id;
l_c_w_id(i) := p_w_id;
l_c_first(i) := MakeAlphaString (8,16);
l_c_middle(i) := 'OE';
IFl_c_id(i) <= 1000 THEN
l_c_last(i) := LastName (l_c_id(i) - 1);
ELSE
l_nrnd := NURand (255,0,999,123);
l_c_last(i) := LastName (l_nrnd);
END IF;
l_c_add:= MakeAddress;
l_c_street_1(i) := l_c_add.street_1;
l_c_street_2(i) := l_c_add.street_2;
l_c_city(i) := l_c_add.city;
l_c_state(i) := l_c_add.state;
l_c_zip(i) := l_c_add.zip;
l_c_phone(i) := MakeNumberString;
IFRandomNumber (0,1) = 1 THEN
l_c_credit(i) := 'GC';
ELSE
l_c_credit(i) := 'BC';
END IF;
l_c_credit_lim(i) := 50000;
l_c_discount(i) := RandomNumber (0,50) / 100.0;
l_c_balance(i) := -10;
l_c_data(i) := MakeAlphaString (300,500);
l_h_amount(i) := 10;
l_h_data(i) := MakeAlphastring (12,24);
IF MOD(l_c_id(i) ,1000) = 0 THEN
IF
l_c_id(i) = p_customers_per_district
THEN
end_cust := 0;
END IF;
FORALLindx IN l_c_id.FIRST .. l_c_id.LAST - end_cust
INSERT INTOcustomer
(
c_id,
c_d_id,
c_w_id,
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,
c_data,
c_ytd_payment,
c_payment_cnt,
c_delivery_cnt
)
VALUES
(
l_c_id(indx),
l_c_d_id(indx),
l_c_w_id(indx),
l_c_first(indx),
l_c_middle(indx),
l_c_last(indx),
l_c_street_1(indx),
l_c_street_2(indx),
l_c_city(indx),
l_c_state(indx),
l_c_zip(indx),
l_c_phone(indx),
SYSDATE,
l_c_credit(indx),
l_c_credit_lim(indx),
l_c_discount(indx),
l_c_balance(indx),
l_c_data(indx),
10.0,
1,
0
);
COMMIT;
FORALL indx IN l_c_id.FIRST .. l_c_id.LAST - end_cust
INSERTINTO history
(
h_c_id,
h_c_d_id,
h_c_w_id,
h_w_id,
h_d_id,
h_date,
h_amount,
h_data
)
VALUES
(
l_c_id(indx),
l_c_d_id(indx),
l_c_w_id(indx),
l_c_w_id(indx),
l_c_d_id(indx),
SYSDATE,
l_h_amount(indx),
l_h_data(indx)
);
COMMIT;
l_c_id.delete(lst_indx,i-1);
l_c_d_id.delete(lst_indx,i-1);
l_c_w_id.delete(lst_indx,i-1);
l_c_first.delete(lst_indx,i-1);
l_c_middle.delete(lst_indx,i-1);
l_c_last.delete(lst_indx,i-1);
l_c_street_1.delete(lst_indx,i-1);
l_c_street_2.delete(lst_indx,i-1);
l_c_city.delete(lst_indx,i-1);
l_c_state.delete(lst_indx,i-1);
l_c_zip.delete(lst_indx,i-1);
l_c_phone.delete(lst_indx,i-1);
l_c_credit.delete(lst_indx,i-1);
l_c_credit_lim.delete(lst_indx,i-1);
l_c_discount.delete(lst_indx,i-1);
l_c_balance.delete(lst_indx,i-1);
l_c_data.delete(lst_indx,i-1);
l_h_amount.delete(lst_indx,i-1);
l_h_data.delete(lst_indx,i-1);
lst_indx:=i-1;
END IF;
IF MOD(l_c_id(i) ,1000) = 0 THEN
WriteTrace('Loading Customer '||l_c_id(i));
END IF;
END LOOP;
WriteTrace('Customer Done');
END;
PROCEDUREOrders
(
p_d_idNUMBER,
p_w_idNUMBER,
p_maxitemsNUMBER,
p_orders_per_district NUMBER
)
IS
indxPLS_INTEGER := 0;
jndxPLS_INTEGER := 0;
lst_indxPLS_INTEGER := 0;
lst_jndxPLS_INTEGER := 0;
ol_totalPLS_INTEGER := 0;
end_orderPLS_INTEGER := 1;
end_olPLS_INTEGER := 1;
l_custnumarray;
l_r NUMBER;
l_t NUMBER;
TYPE l_o_d_id_aat IS TABLE OF NUMBER INDEX BYPLS_INTEGER;
TYPE l_o_w_id_aat IS TABLE OF NUMBER INDEX BYPLS_INTEGER;
TYPE l_o_id_aat IS TABLE OF NUMBER INDEX BYPLS_INTEGER;
TYPE l_o_c_id_aat IS TABLE OF NUMBER INDEX BYPLS_INTEGER;
TYPE l_o_carrier_id_aat IS TABLE OF NUMBER INDEX BYPLS_INTEGER;
TYPE l_o_ol_cnt_aat IS TABLE OF NUMBER INDEX BYPLS_INTEGER;
TYPE l_ol_aat IS TABLE OF NUMBER INDEX BYPLS_INTEGER;
TYPE l_ol_i_id_aat IS TABLE OF NUMBER INDEX BYPLS_INTEGER;
TYPE l_ol_supply_w_id_aat IS TABLE OF NUMBER INDEX BYPLS_INTEGER;
TYPE l_ol_quantity_aat IS TABLE OF NUMBER INDEX BYPLS_INTEGER;
TYPE l_ol_amount_aat IS TABLE OF NUMBER INDEX BYPLS_INTEGER;
TYPE l_ol_dist_info_aat IS TABLE OF CHAR(24) INDEX BYPLS_INTEGER;
TYPE l_ol_delivery_d_aat IS TABLE OF DATE INDEX BYPLS_INTEGER;
l_o_d_idl_o_d_id_aat;
l_o_w_idl_o_w_id_aat;
l_o_idl_o_id_aat;
no_l_o_d_idl_o_d_id_aat;
no_l_o_w_idl_o_w_id_aat;
no_l_o_idl_o_id_aat;
oli_l_o_idl_o_id_aat;
oli_l_o_d_id l_o_d_id_aat;
oli_l_o_w_id l_o_w_id_aat;
l_o_c_idl_o_c_id_aat;
l_o_carrier_id l_o_carrier_id_aat;
l_o_ol_cntl_o_ol_cnt_aat;
l_oll_ol_aat;
l_ol_i_idl_ol_i_id_aat;
l_ol_supply_w_id l_ol_supply_w_id_aat;
l_ol_quantity l_ol_quantity_aat;
l_ol_amountl_ol_amount_aat;
l_ol_dist_info l_ol_dist_info_aat;
l_ol_delivery_d l_ol_delivery_d_aat;
BEGIN
WriteTrace('Loading Orders for D='||p_d_id||' W='||p_w_id);
FOR i IN0..p_orders_per_district LOOP
l_cust(i) := 1;
END LOOP;
FOR i IN0..p_orders_per_district LOOP
l_r :=RandomNumber (i,p_orders_per_district);
l_t :=l_cust(i);
l_cust(i) := l_cust (l_r);
l_cust(l_r) := l_t;
END LOOP;
FOR i IN1..p_orders_per_district LOOP
l_o_d_id(i) := p_d_id;
l_o_w_id(i) := p_w_id;
l_o_id(i):= i;
l_o_c_id(i) := l_cust (l_o_id(i));
IF l_o_id(i) > 2100 THEN
no_l_o_d_id(i):= l_o_d_id(i);
no_l_o_w_id(i):= l_o_w_id(i);
no_l_o_id(i):= l_o_id(i);
l_o_carrier_id(i) := NULL;
ELSE
l_o_carrier_id(i) := RandomNumber (1,10);
END IF;
l_o_ol_cnt(i) := RandomNumber (5,15);
FOR j IN 1 ..l_o_ol_cnt(i) LOOP
ol_total := ol_total + 1;
IF l_o_id(i) > 2100 THEN
l_ol_amount(ol_total) := 0;
l_ol_delivery_d(ol_total) := NULL;
ELSE
l_ol_amount(ol_total) := RandomNumber (10,10000) /100;
l_ol_delivery_d(ol_total) := SYSDATE;
END IF;
oli_l_o_id(ol_total) := l_o_id(i);
oli_l_o_d_id(ol_total) := l_o_d_id(i);
oli_l_o_w_id(ol_total) := l_o_w_id(i);
l_ol(ol_total) := j;
l_ol_i_id(ol_total) := RandomNumber (1,p_maxitems);
l_ol_supply_w_id(ol_total) := l_o_w_id(i);
l_ol_quantity(ol_total) := 5;
l_ol_dist_info(ol_total) := MakeAlphaString (24,24);
END LOOP;
IF MOD(l_o_id(i),1000) = 0 THEN
WriteTrace ('...'||l_o_id(i));
IF
l_o_id(i) = p_orders_per_district
THEN
end_order := 0;
END IF;
FORALL indx IN l_o_id.FIRST .. l_o_id.LAST -end_order
INSERTINTO orders
(
o_id,
o_c_id,
o_d_id,
o_w_id,
o_entry_d,
o_carrier_id,
o_ol_cnt,
o_all_local
)
VALUES
(
l_o_id(indx),
l_o_c_id(indx),
l_o_d_id(indx),
l_o_w_id(indx),
SYSDATE,
l_o_carrier_id(indx),
l_o_ol_cnt(indx),
1
);
COMMIT;
FORALL indx IN no_l_o_id.FIRST .. no_l_o_id.LAST -end_order
INSERTINTO new_order
(
no_o_id,
no_d_id,
no_w_id
)
VALUES
(
no_l_o_id(indx),
no_l_o_d_id(indx),
no_l_o_w_id(indx)
);
COMMIT;
FORALL jndx IN oli_l_o_id.FIRST .. oli_l_o_id.LAST -end_order
INSERTINTO 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
(
oli_l_o_id(jndx),
oli_l_o_d_id(jndx),
oli_l_o_w_id(jndx),
l_ol(jndx),
l_ol_i_id(jndx),
l_ol_supply_w_id(jndx),
l_ol_quantity(jndx),
l_ol_amount(jndx),
l_ol_dist_info(jndx),
l_ol_delivery_d(jndx)
);
COMMIT;
oli_l_o_id.delete(lst_jndx,ol_total-1);
oli_l_o_d_id.delete(lst_jndx,ol_total-1);
oli_l_o_w_id.delete(lst_jndx,ol_total-1);
l_ol.delete(lst_jndx,ol_total-1);
l_ol_i_id.delete(lst_jndx,ol_total-1);
l_ol_supply_w_id.delete(lst_jndx,ol_total-1);
l_ol_quantity.delete(lst_jndx,ol_total-1);
l_ol_amount.delete(lst_jndx,ol_total-1);
l_ol_dist_info.delete(lst_jndx,ol_total-1);
l_ol_delivery_d.delete(lst_jndx,ol_total-1);
lst_jndx:= ol_total-1;
l_o_d_id.delete(lst_indx,i-1);
l_o_w_id.delete(lst_indx,i-1);
l_o_id.delete(lst_indx,i-1);
l_o_c_id.delete(lst_indx,i-1);
l_o_carrier_id.delete(lst_indx,i-1);
l_o_ol_cnt.delete(lst_indx,i-1);
lst_indx:= i-1;
END IF;
END LOOP;
COMMIT;
WriteTrace('Orders Done');
END;
PROCEDURE LoadItems (p_maxitems NUMBER)
IS
indxPLS_INTEGER := 0;
lst_indxPLS_INTEGER := 0;
end_itemPLS_INTEGER := 1;
l_orignumarray;
l_firstNUMBER;
TYPEl_i_id_aat IS TABLE OF ITEM.I_ID%TYPE
INDEX BYPLS_INTEGER;
TYPEl_i_im_id_aat IS TABLE OF ITEM.I_IM_ID%TYPE
INDEX BYPLS_INTEGER;
TYPEl_i_name_aat IS TABLE OF ITEM.I_NAME%TYPE
INDEX BYPLS_INTEGER;
TYPEl_i_price_aat IS TABLE OF ITEM.I_PRICE%TYPE
INDEX BYPLS_INTEGER;
TYPEl_i_data_aat IS TABLE OF ITEM.I_DATA%TYPE
INDEX BYPLS_INTEGER;
l_i_id l_i_id_aat;
l_i_im_id l_i_im_id_aat;
l_i_name l_i_name_aat;
l_i_price l_i_price_aat;
l_i_data l_i_data_aat;
BEGIN
WriteTrace('Loading Items');
FOR i IN0..p_maxitems - 1 LOOP
l_orig(i):= 0;
END LOOP;
FOR i IN0..(p_maxitems / 10) - 1 LOOP
l_orig(RandomNumber (0,p_maxitems - 1)) := 1;
END LOOP;
FOR i IN1..p_maxitems LOOP
l_i_id(i) := i;
l_i_im_id(i) := RandomNumber (1,10000);
l_i_name(i) := MakeAlphaString(14,24);
l_i_price(i) := TRUNC (RandomNumber (100,10000) / 100,2);
l_i_data(i) := MakeAlphaString(26,50);
IF l_orig(i - 1) = 1 THEN
l_first:= RandomNumber (0,LENGTH (l_i_data(i)) - 8);
l_i_data(i) := SUBSTR (l_i_data(i),0,l_first)||
'original'||SUBSTR (l_i_data(i),l_first + 8);
END IF;
IF MOD (l_i_id(i),10000) = 0
THEN
IF
l_i_id(i) = p_maxitems
THEN
end_item := 0;
END IF;
FORALLindx IN l_i_id.FIRST .. l_i_id.LAST - end_item
INSERTINTO item
(i_id,i_im_id, i_name, i_price, i_data) VALUES (l_i_id(indx), l_i_im_id(indx),l_i_name(indx), l_i_price(indx), l_i_data(indx));
COMMIT;
l_i_id.delete(lst_indx,i-1);
l_i_im_id.delete(lst_indx,i-1);
l_i_name.delete(lst_indx,i-1);
l_i_price.delete(lst_indx,i-1);
l_i_data.delete(lst_indx,i-1);
lst_indx := i-1;
END IF;
IF MOD(l_i_id(i),20000) = 0
THEN
WriteTrace ('Loading Items - '||l_i_id(i));
END IF;
END LOOP;
WriteTrace('Items Done');
END;
PROCEDURELoadStock (p_w_id NUMBER,p_maxitems NUMBER)
IS
indxPLS_INTEGER := 0;
lst_indxPLS_INTEGER := 0;
end_stockPLS_INTEGER := 1;
l_orignumarray;
l_firstNUMBER;
TYPEl_s_w_id_aat IS TABLE OF STOCK.S_W_ID%TYPE INDEX BY PLS_INTEGER;
TYPEl_s_i_id_aat IS TABLE OF STOCK.S_I_ID%TYPE INDEX BY PLS_INTEGER;
TYPEl_s_quantity_aat IS TABLE OF STOCK.S_QUANTITY%TYPE INDEX BY PLS_INTEGER;
TYPEl_s_dist_01_aat IS TABLE OF STOCK.S_DIST_01%TYPE INDEX BY PLS_INTEGER;
TYPEl_s_dist_02_aat IS TABLE OF STOCK.S_DIST_02%TYPE INDEX BY PLS_INTEGER;
TYPEl_s_dist_03_aat IS TABLE OF STOCK.S_DIST_03%TYPE INDEX BY PLS_INTEGER;
TYPEl_s_dist_04_aat IS TABLE OF STOCK.S_DIST_04%TYPE INDEX BY PLS_INTEGER;
TYPEl_s_dist_05_aat IS TABLE OF STOCK.S_DIST_05%TYPE INDEX BY PLS_INTEGER;
TYPEl_s_dist_06_aat IS TABLE OF STOCK.S_DIST_06%TYPE INDEX BY PLS_INTEGER;
TYPE l_s_dist_07_aat IS TABLE OFSTOCK.S_DIST_07%TYPE INDEX BY PLS_INTEGER;
TYPEl_s_dist_08_aat IS TABLE OF STOCK.S_DIST_08%TYPE INDEX BY PLS_INTEGER;
TYPEl_s_dist_09_aat IS TABLE OF STOCK.S_DIST_09%TYPE INDEX BY PLS_INTEGER;
TYPEl_s_dist_10_aat IS TABLE OF STOCK.S_DIST_10%TYPE INDEX BY PLS_INTEGER;
TYPEl_s_data_aat IS TABLE OF STOCK.S_DATA%TYPE INDEX BY PLS_INTEGER;
l_s_w_idl_s_w_id_aat;
l_s_i_idl_s_i_id_aat;
l_s_quantity l_s_quantity_aat;
l_s_dist_01l_s_dist_01_aat;
l_s_dist_02l_s_dist_02_aat;
l_s_dist_03l_s_dist_03_aat;
l_s_dist_04l_s_dist_04_aat;
l_s_dist_05l_s_dist_05_aat;
l_s_dist_06l_s_dist_06_aat;
l_s_dist_07l_s_dist_07_aat;
l_s_dist_08l_s_dist_08_aat;
l_s_dist_09l_s_dist_09_aat;
l_s_dist_10l_s_dist_10_aat;
l_s_datal_s_data_aat;
BEGIN
WriteTrace('Loading Stock W='||p_w_id);
FOR i IN0..p_maxitems - 1 LOOP
l_orig(i):= 0;
END LOOP;
FOR i IN0..(p_maxitems / 10) - 1 LOOP
l_orig(RandomNumber (0,p_maxitems - 1)) := 1;
END LOOP;
FOR i IN1..p_maxitems LOOP
l_s_w_id(i) := p_w_id;
l_s_i_id(i) := i;
l_s_quantity(i) := RandomNumber (10,100);
l_s_dist_01(i) := MakeAlphaString (24,24);
l_s_dist_02(i) := MakeAlphaString (24,24);
l_s_dist_03(i) := MakeAlphaString (24,24);
l_s_dist_04(i) := MakeAlphaString (24,24);
l_s_dist_05(i) := MakeAlphaString (24,24);
l_s_dist_06(i) := MakeAlphaString (24,24);
l_s_dist_07(i) := MakeAlphaString (24,24);
l_s_dist_08(i) := MakeAlphaString (24,24);
l_s_dist_09(i) := MakeAlphaString (24,24);
l_s_dist_10(i) := MakeAlphaString (24,24);
l_s_data(i) := MakeAlphaString (26,50);
IF l_orig(i - 1) = 1 THEN
l_first:= RandomNumber (0,LENGTH (l_s_data(i)) - 8);
l_s_data(i) := SUBSTR (l_s_data(i),0,l_first) || 'original' || SUBSTR(l_s_data(i),l_first + 8);
END IF;
IF MOD(l_s_i_id(i),10000) = 0
THEN
IF
l_s_i_id(i) = p_maxitems
THEN
end_stock := 0;
END IF;
FORALLindx IN l_s_i_id.FIRST .. l_s_i_id.LAST - end_stock
INSERTINTO STOCK
(
s_i_id,
s_w_id,
s_quantity,
s_dist_01,
s_dist_02,
s_dist_03,
s_dist_04,
s_dist_05,
s_dist_06,
s_dist_07,
s_dist_08,
s_dist_09,
s_dist_10,
s_data,
s_ytd,
s_order_cnt,
s_remote_cnt
)
VALUES
(
l_s_i_id(indx),
l_s_w_id(indx),
l_s_quantity(indx),
l_s_dist_01(indx),
l_s_dist_02(indx),
l_s_dist_03(indx),
l_s_dist_04(indx),
l_s_dist_05(indx),
l_s_dist_06(indx),
l_s_dist_07(indx),
l_s_dist_08(indx),
l_s_dist_09(indx),
l_s_dist_10(indx),
l_s_data(indx),
0,
0,
0
);
COMMIT;
l_s_i_id.delete(lst_indx,i-1);
l_s_w_id.delete(lst_indx,i-1);
l_s_quantity.delete(lst_indx,i-1);
l_s_dist_01.delete(lst_indx,i-1);
l_s_dist_02.delete(lst_indx,i-1);
l_s_dist_03.delete(lst_indx,i-1);
l_s_dist_04.delete(lst_indx,i-1);
l_s_dist_05.delete(lst_indx,i-1);
l_s_dist_06.delete(lst_indx,i-1);
l_s_dist_07.delete(lst_indx,i-1);
l_s_dist_08.delete(lst_indx,i-1);
l_s_dist_09.delete(lst_indx,i-1);
l_s_dist_10.delete(lst_indx,i-1);
l_s_data.delete(lst_indx,i-1);
lst_indx:= i-1;
END IF;
IF MOD(l_s_i_id(i),20000) = 0
THEN
WriteTrace ('Loading Stock '|| l_s_i_id(i));
END IF;
END LOOP;
COMMIT;
WriteTrace('Stock Done');
END;
PROCEDURELoadDistrict (p_w_id NUMBER,p_districts_per_warehouse NUMBER)
IS
l_d_w_idNUMBER;
l_d_ytdNUMBER;
l_d_next_o_id NUMBER;
l_d_idNUMBER;
l_d_nameVARCHAR2(10);
l_d_addaddress;
l_d_taxNUMBER;
BEGIN
WriteTrace('Loading District');
l_d_w_id :=p_w_id;
l_d_ytd :=30000;
l_d_next_o_id := 3001;
FOR i IN 1.. p_districts_per_warehouse LOOP
l_d_id :=i;
l_d_name:= MakeAlphaString (6,10);
l_d_add := MakeAddress;
l_d_tax:= TRUNC (RandomNumber (10,20) / 100.0,2);
INSERTINTO DISTRICT
(
d_id,
d_w_id,
d_name,
d_street_1,
d_street_2,
d_city,
d_state,
d_zip,
d_tax,
d_ytd,
d_next_o_id
)
VALUES
(
l_d_id,
l_d_w_id,
l_d_name,
l_d_add.street_1,
l_d_add.street_2,
l_d_add.city,
l_d_add.state,
l_d_add.zip,
l_d_tax,
l_d_ytd,
l_d_next_o_id
);
END LOOP;
COMMIT;
WriteTrace('District done');
END;
PROCEDURELoadWarehouses
(
p_count_ware NUMBER,
p_maxitemsNUMBER,
p_districts_per_warehouse NUMBER
)
IS
w_id NUMBER;
w_nameVARCHAR2(10);
w_taxNUMBER;
w_ytdNUMBER;
w_addADDRESS;
BEGIN
WriteTrace('Loading Warehouses');
FOR i IN1..p_count_ware LOOP
w_id :=i;
w_name :=MakeAlphaString (6,10);
w_add := MakeAddress;
w_tax :=TRUNC (RandomNumber (10,20) / 100.0,2);
w_ytd :=3000000;
INSERTINTO WAREHOUSE
(
w_id,
w_name,
w_street_1,
w_street_2,
w_city,
w_state,
w_zip,
w_tax,
w_ytd
)
VALUES
(
w_id,
w_name,
w_add.street_1,
w_add.street_2,
w_add.city,
w_add.state,
w_add.zip,
w_tax,
w_ytd
);
LoadStock(w_id,c_maxitems);
LoadDistrict (w_id,c_districts_per_warehouse);
COMMIT;
END LOOP;
WriteTrace('Warehouses done');
END;
PROCEDURELoadCustomers
(
p_count_ware NUMBER,
p_customers_per_district NUMBER,
p_districts_per_warehouse NUMBER
)
IS
l_w_idNUMBER;
l_d_idNUMBER;
BEGIN
WriteTrace('Loading Customers');
FOR i IN1..p_count_ware LOOP
l_w_id :=i;
FOR j IN1..p_districts_per_warehouse LOOP
l_d_id:= j;
Customer (l_d_id,l_w_id,p_customers_per_district);
END LOOP;
END LOOP;
COMMIT;
WriteTrace('Customers done');
END;
PROCEDURELoadOrders
(
p_count_ware NUMBER,
p_maxitemsNUMBER,
p_orders_per_district NUMBER,
p_districts_per_warehouseNUMBER
)
IS
l_w_idNUMBER;
l_d_idNUMBER;
BEGIN
WriteTrace('Loading Orders');
FOR i IN1..p_count_ware LOOP
l_w_id :=i;
FOR j IN1..p_districts_per_warehouse LOOP
l_d_id:= j;
Orders(l_d_id,l_w_id,p_maxitems,p_orders_per_district);
END LOOP;
END LOOP;
COMMIT;
WriteTrace('Orders done');
END;
PROCEDURELoadSchema(count_ware NUMBER) IS
BEGIN
OpenTrace('TPCC_LOG','tpcc_load.log');
WriteTrace('Loading Schema');
LoadItems(c_maxitems);
LoadWarehouses (count_ware,c_maxitems,c_districts_per_warehouse);
LoadCustomers(count_ware,c_customers_per_district,c_districts_per_warehouse);
LoadOrders(count_ware,c_maxitems,c_orders_per_district,c_districts_per_warehouse);
WriteTrace('Schema Load done');
CloseTrace;
END;
BEGIN
namearr(0) :='BAR';
namearr(1) :='OUGHT';
namearr(2) :='ABLE';
namearr(3) :='PRI';
namearr(4) :='PRES';
namearr(5) :='ESE';
namearr(6) :='ANTI';
namearr(7) :='CALLY';
namearr(8) :='ATION';
namearr(9) :='EING';
list(0) :='0';
list(1) :='1';
list(2) :='2';
list(3) :='3';
list(4) :='4';
list(5) :='5';
list(6) :='6';
list(7) :='7';
list(8) :='8';
list(9) :='9';
list(10) :='A';
list(11) :='B';
list(12) :='C';
list(13) :='D';
list(14) :='E';
list(15) :='F';
list(16) :='G';
list(17) :='H';
list(18) :='I';
list(19) :='J';
list(20) :='K';
list(21) :='L';
list(22) :='M';
list(23) :='N';
list(24) :='O';
list(25) :='P';
list(26) :='Q';
list(27) :='R';
list(28) :='S';
list(29) :='T';
list(30) :='U';
list(31) :='V';
list(32) :='W';
list(33) :='X';
list(34) :='Y';
list(35) :='Z';
list(36) :='a';
list(37) :='b';
list(38) :='c';
list(39) :='d';
list(40) :='e';
list(41) :='f';
list(42) :='g';
list(43) :='h';
list(44) :='i';
list(45) :='j';
list(46) :='k';
list(47) :='l';
list(48) :='m';
list(49) :='n';
list(50) :='o';
list(51) :='p';
list(52) :='q';
list(53) :='r';
list(54) :='s';
list(55) :='t';
list(56) :='u';
list(57) :='v';
list(58) :='w';
list(59) :='x';
list(60) :='y';
list(61) :='z';
END;
/
3. 调用包中函数进行导入
BEGIN tpcc_server_side.loadschema(2); END;
/
893
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
