tscli.c

Mysql for Windows最新源码

"	FOR i IN 0 .. n_customers_d / 15 LOOP"
"		INSERT INTO SUPPLIER VALUES (i,"
"					CONCAT('Supplier', TO_CHAR(i)),"
"					RND_STR(RND(20, 30)),"
"					RND(0, 24),"
"					RND_STR(15),"
"					RND(1, 1000),"
"					RND_STR(RND(40, 80)));"
"	END LOOP;"
""
"	COMMIT WORK;"
""
"	FOR i IN 0 .. n_customers_d - 1 LOOP"
"		IF ((i / 100) * 100 = i) THEN"
"			COMMIT WORK;"
"			PRINTF('Populating customer ', i);"
"		END IF;"
""
"		INSERT INTO CUSTOMER_D VALUES (i,"
"					CONCAT('Customer', TO_CHAR(i)),"
"					RND_STR(RND(20, 30)),"
"					RND(0, 24),"
"					RND_STR(15),"
"					RND(1, 1000),"
"					RND_STR(10),"
"					RND_STR(RND(50, 100)));"
""
"		FOR j IN i * 10 .. i * 10 + 9 LOOP"
""
"			rnd := (j * 2400) / (10 * n_customers_d);"
""
"			INSERT INTO ORDERS_D VALUES (j,"
"					3 * RND(0, (n_customers_d / 3) - 1)"
"					+ RND(1, 2),"
"					'F', 1000,"
"					rnd,"
"					RND_STR(10),"
"				     CONCAT('Clerk', TO_CHAR(RND(0, 1000))),"
"				     	0, RND_STR(RND(3, 7)));"
""
"			FOR k IN 0 .. RND(0, 6) LOOP"
"				INSERT INTO LINEITEM VALUES (j,"
"					RND(1, 1000),"
"					RND(0, n_customers_d / 15),"
"					k,"
"					RND(1, 50),"
"					100,"
"					5,"
"					RND(0, 8),"
"					'N',"
"					'F',"
"					rnd + RND(1, 100),"
"					rnd + RND(1, 100),"
"					rnd + RND(1, 100),"
"					RND_STR(1),"
"					RND_STR(1),"
"					RND_STR(RND(1, 3)));"
"			END LOOP;"
"		END LOOP;"
""
"	END LOOP;"
""
"	COMMIT WORK;"
"	PRINTF('TPC-D tables populated');"
""
"	PRINTF('Populating join test tables');"
"	FOR i IN 1 .. 1 LOOP"
"		INSERT INTO JTEST1 VALUES (i, i);"
"		INSERT INTO JTEST2 VALUES (i, i);"
"	END LOOP;"
"	PRINTF('Join test tables populated');"
""
"	COMMIT WORK;"
"	END;"
	;

	str4 = ut_str_catenate(str1, str2);
	populate_str = ut_str_catenate(str4, str3);

	/*-----------------------------------------------------------*/
	str = 

"	PROCEDURE PRINT_TABLES () IS"
"	i INT;"	
"	BEGIN"
""
"	/* PRINTF('Printing ITEM table:');"
""
"	ROW_PRINTF"
"		SELECT *"
"			FROM ITEM;"
""
"	PRINTF('Printing WAREHOUSE table:');"
""
"	ROW_PRINTF"
"		SELECT *"
"			FROM WAREHOUSE;"
""
"	PRINTF('Printing STOCK table:');"
""
"	ROW_PRINTF"
"		SELECT *"
"			FROM STOCK;"
""
"	PRINTF('Printing DISTRICT table:');"
""
"	ROW_PRINTF"
"		SELECT *"
"			FROM DISTRICT;"
""
"	PRINTF('Printing CUSTOMER table:');"
""
"	ROW_PRINTF"
"		SELECT *"
"			FROM CUSTOMER;"
""
"	PRINTF('Printing HISTORY table:');"
""
"	ROW_PRINTF"
"		SELECT *"
"			FROM HISTORY;"
""
"	PRINTF('Printing ORDERS table:');"
""
"	ROW_PRINTF"
"		SELECT *"
"			FROM ORDERS;"
""
"	PRINTF('Printing ORDER_LINE table:');"
""
"	ROW_PRINTF"
"		SELECT *"
"			FROM ORDER_LINE"
"			WHERE OL_O_ID >= 3000; */"
""
"	PRINTF('Printing NEW_ORDER table:');"
""
"	ROW_PRINTF"
"		SELECT *"
"			FROM NEW_ORDER;"
""
"	COMMIT WORK;"
"	END;"
	;

	print_str = str;
	/*-----------------------------------------------------------*/
	commit_str =

"	PROCEDURE COMMIT_TEST () IS"
"	"
"	BEGIN"
"	COMMIT WORK;"
"	END;"
	;

	/*-----------------------------------------------------------*/

	str1 =
	
"	PROCEDURE NEW_ORDER (c_w_id IN CHAR,"
"				c_d_id IN CHAR,"
"				c_id IN CHAR,"
"				ol_supply_w_ids IN CHAR,"
"				ol_i_ids IN CHAR,"
"				ol_quantities IN CHAR,"
"				c_last OUT CHAR,"
"				c_credit OUT CHAR,"
"				c_discount OUT INT,"
"				w_tax OUT INT,"
"				d_tax OUT INT,"
"				o_ol_count OUT INT,"
"				o_id OUT INT,"
"				o_entry_d OUT INT,"
"				total OUT INT,"
"				i_names OUT CHAR,"
"				s_quantities OUT CHAR,"
"				bg OUT CHAR,"
"				i_prices OUT CHAR,"
"				ol_amounts OUT CHAR) IS"
""
" 	i INT;"
" 	j INT;"
" 	o_all_local CHAR;"
" 	i_price INT;"
" 	i_name CHAR;"
" 	i_data CHAR;"
" 	s_quantity INT;"
" 	s_data CHAR;"
" 	s_dist_01 CHAR;"
" 	s_dist_02 CHAR;"
" 	s_dist_03 CHAR;"
" 	s_dist_04 CHAR;"
" 	s_dist_05 CHAR;"
" 	s_dist_06 CHAR;"
" 	s_dist_07 CHAR;"
" 	s_dist_08 CHAR;"
" 	s_dist_09 CHAR;"
" 	s_dist_10 CHAR;"
" 	ol_i_id CHAR;"
" 	ol_quantity INT;"
" 	ol_amount INT;"
" 	ol_supply_w_id CHAR;"
" 	ol_dist_info CHAR;"
""
" 	DECLARE CURSOR district_cursor IS"
" 		SELECT D_NEXT_O_ID, D_TAX"
" 			FROM DISTRICT"
" 			WHERE D_ID = c_d_id AND D_W_ID = c_w_id"
" 			FOR UPDATE;"
""
" 	DECLARE CURSOR stock_cursor IS"
" 		SELECT S_QUANTITY, S_DATA,"
"				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"
"			FROM STOCK"
"			WHERE S_W_ID = ol_supply_w_id AND S_I_ID = ol_i_id"
"			FOR UPDATE;"
	;
	str2 =
	
" 	BEGIN"
"	FOR j IN 1 .. 1 LOOP"
""
"	/* PRINTF('Warehouse ', BINARY_TO_NUMBER(c_w_id)); */"
" 	o_all_local := '1';"
"	i_names := '12345678901234567890123456789012345678901234567890"
			"12345678901234567890123456789012345678901234567890"
			"12345678901234567890123456789012345678901234567890"
			"12345678901234567890123456789012345678901234567890"
			"12345678901234567890123456789012345678901234567890"
			"12345678901234567890123456789012345678901234567890"
			"12345678901234567890123456789012345678901234567890"
			"1234567890';"
"	s_quantities := '12345678901234567890123456789012345678901234567890"
			"1234567890';"
"	i_prices := '12345678901234567890123456789012345678901234567890"
			"1234567890';"
"	ol_amounts := '12345678901234567890123456789012345678901234567890"
			"1234567890';"
" 	bg := 'GGGGGGGGGGGGGGG';"
"	total := 0;"
""
"	SELECT C_DISCOUNT, C_LAST, C_CREDIT INTO c_discount, c_last, c_credit"
"		FROM CUSTOMER"
"		WHERE C_W_ID = c_w_id AND C_D_ID = c_d_id AND C_ID = c_id;"
""
"	OPEN district_cursor;"
""
"	FETCH district_cursor INTO o_id, d_tax;"
""
"	UPDATE DISTRICT SET D_NEXT_O_ID = o_id + 1"
"		WHERE CURRENT OF district_cursor;"
""
"	CLOSE district_cursor;"
""
""
	;
	str3 =

"	o_ol_count := LENGTH(ol_quantities);"
""
"	/* PRINTF('C-WAREHOUSE id ', BINARY_TO_NUMBER(c_w_id),"
"		' C-district id ', c_d_id,"
"		' order id ', o_id, ' linecount ', o_ol_count); */"
""
" 	FOR i IN 0 .. (o_ol_count - 1) LOOP"
""
"		ol_i_id := SUBSTR(ol_i_ids, 3 * i, 3);"
"		ol_supply_w_id := SUBSTR(ol_supply_w_ids, 2 * i, 2);"
"		ol_quantity := BINARY_TO_NUMBER(SUBSTR(ol_quantities, i, 1));"
""
"		/* PRINTF('ol_i_id ', BINARY_TO_NUMBER(ol_i_id),"
"			' ol_supply_w_id ', BINARY_TO_NUMBER(ol_supply_w_id),"
"			' ol_quantity ', ol_quantity); */"
"" 
"		SELECT I_PRICE, I_NAME, I_DATA INTO i_price, i_name, i_data"
"			FROM ITEM"
"			WHERE I_ID = ol_i_id"
"			CONSISTENT READ;"
""
"		IF (SQL % NOTFOUND) THEN"
"			/* PRINTF('Rolling back; item not found: ',"
"					BINARY_TO_NUMBER(ol_i_id)); */"
"			ROLLBACK WORK;"
"			o_ol_count := 0;"
""
"			RETURN;"
"		END IF;"
""
"		OPEN stock_cursor;"
""
"		FETCH stock_cursor INTO s_quantity, s_data,"
"					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;"
""
"		/* PRINTF('Stock quantity ', s_quantity); */"
""
"		IF (s_quantity >= ol_quantity + 10) THEN"
"			s_quantity := s_quantity - ol_quantity;"
"		ELSE"
"			s_quantity := (s_quantity + 91) - ol_quantity;"
"		END IF;"
""
"		UPDATE STOCK SET S_QUANTITY = s_quantity,"
"				S_YTD = S_YTD + ol_quantity,"
"				S_ORDER_CNT = S_ORDER_CNT + 1"
 "			WHERE CURRENT OF stock_cursor;"
""
"		IF (ol_supply_w_id <> c_w_id) THEN"
""
"			o_all_local := '0';"
"			PRINTF('Remote order ',"
"				BINARY_TO_NUMBER(ol_supply_w_id), ' ',"
"				BINARY_TO_NUMBER(c_w_id));"
""
"			UPDATE STOCK SET S_REMOTE_CNT = S_REMOTE_CNT + 1"
"				WHERE CURRENT OF stock_cursor;"
"		END IF;"
""
"		CLOSE stock_cursor;"
""
"		IF ((INSTR(i_data, 'ORIGINAL') > 0)"
"				OR (INSTR(s_data, 'ORIGINAL') > 0)) THEN"
"			REPLSTR(bg, 'B', i, 1);"
"		END IF;"
""
"		ol_amount := ol_quantity * i_price;"
""
"		total := total + ol_amount;"
	;
	str4 =
"		IF (c_d_id = '0') THEN"
"			ol_dist_info := s_dist_01;"
"		ELSIF (c_d_id = '1') THEN"
"			ol_dist_info := s_dist_02;"
"		ELSIF (c_d_id = '2') THEN"
"			ol_dist_info := s_dist_03;"
"		ELSIF (c_d_id = '3') THEN"
"			ol_dist_info := s_dist_04;"
"		ELSIF (c_d_id = '4') THEN"
"			ol_dist_info := s_dist_05;"
"		ELSIF (c_d_id = '5') THEN"
"			ol_dist_info := s_dist_06;"
"		ELSIF (c_d_id = '6') THEN"
"			ol_dist_info := s_dist_07;"
"		ELSIF (c_d_id = '7') THEN"
"			ol_dist_info := s_dist_08;"
"		ELSIF (c_d_id = '8') THEN"
"			ol_dist_info := s_dist_09;"
"		ELSIF (c_d_id = '9') THEN"
"			ol_dist_info := s_dist_10;"
"		END IF;"
""
"		INSERT INTO ORDER_LINE VALUES (o_id, c_d_id, c_w_id,"
"					TO_BINARY(i + 1, 1), ol_i_id,"
"					ol_supply_w_id, NULL, ol_quantity,"
"					ol_amount, ol_dist_info);"
""
"		REPLSTR(i_names, i_name, i * 24, LENGTH(i_name));"
"		REPLSTR(s_quantities, TO_BINARY(s_quantity, 4), i * 4, 4);"
"		REPLSTR(i_prices, TO_BINARY(i_price, 4), i * 4, 4);"
"		REPLSTR(ol_amounts, TO_BINARY(ol_amount, 4), i * 4, 4);"
""
"		/* PRINTF('i_name ', i_name, ' s_quantity ', s_quantity,"
"			' i_price ', i_price, ' ol_amount ', ol_amount); */"
"	END LOOP;"
""
"	SELECT W_TAX INTO w_tax"
"		FROM WAREHOUSE"
"		WHERE W_ID = c_w_id;"
""
"	total := (((total * (10000 + w_tax + d_tax)) / 10000)"
"			  * (10000 - c_discount)) / 10000;"
""
"	o_entry_d := SYSDATE();"
""
"	INSERT INTO ORDERS VALUES (o_id, c_d_id, c_w_id, c_id, o_entry_d,"
"					NULL, o_ol_count, o_all_local);"
"	INSERT INTO NEW_ORDER VALUES (o_id, c_d_id, c_w_id);"
""
"	/* PRINTF('Inserted order lines:');"
"	ROW_PRINTF"
"		SELECT * FROM ORDER_LINE WHERE OL_O_ID = o_id AND"
"						OL_D_ID = c_d_id"
"						AND OL_W_ID = c_w_id; */"
" 	COMMIT WORK;"
"	END LOOP;"
" 	END;"
	;

	str5 = ut_str_catenate(str1, str2);
	str6 = ut_str_catenate(str3, str4);

	new_order_str = ut_str_catenate(str5, str6);

	/*-----------------------------------------------------------*/

	str1 =
	
"	PROCEDURE PAYMENT (c_w_id IN CHAR) IS"
""
" 	i INT;"
" 	n_items INT;"
" 	n_warehouses INT;"
" 	n_districts INT;"
"	n_customers INT;"
"	w_id CHAR;"
"	w_street_1 CHAR;"
"	w_street_2 CHAR;"
"	w_city CHAR;"
"	w_state CHAR;"
"	w_zip CHAR;"
"	w_name CHAR;"
"	d_id CHAR;"
"	d_street_1 CHAR;"
"	d_street_2 CHAR;"
"	d_city CHAR;"
"	d_state CHAR;"
"	d_zip CHAR;"
"	d_name CHAR;"
"	c_d_id CHAR;"
"	c_street_1 CHAR;"
"	c_street_2 CHAR;"
"	c_city CHAR;"
"	c_state CHAR;"
"	c_zip CHAR;"
"	c_id CHAR;"
"	c_last CHAR;"
"	c_first CHAR;"
"	c_middle CHAR;"
"	c_phone CHAR;"
"	c_credit CHAR;"
"	c_credit_lim INT;"
"	c_discount INT;"
"	c_balance INT;"
"	c_since INT;"
"	c_data CHAR;"
"	byname INT;"
"	namecnt INT;"
"	amount INT;"
"	h_data CHAR;"
"	h_date INT;"
"	c_more_data CHAR;"
"	more_len INT;"
"	data_len INT;"
""
"	DECLARE CURSOR warehouse_cursor IS"
"		SELECT W_STREET_1, W_STREET_2, W_CITY, W_STATE, W_ZIP, W_NAME"
"			FROM WAREHOUSE"
"			WHERE W_ID = w_id"
"			FOR UPDATE;"
""
"	DECLARE CURSOR district_cursor IS"
"		SELECT D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP, D_NAME"
"			FROM DISTRICT"
"			WHERE D_W_ID = w_id AND D_ID = d_id"
"			FOR UPDATE;"
""
"	DECLARE CURSOR customer_by_name_cursor IS"
"		SELECT C_ID"
"			FROM CUSTOMER"
"			WHERE C_W_ID = c_w_id AND C_D_ID = c_d_id"
"				AND C_LAST = c_last"
"			ORDER BY C_FIRST ASC;"
""
"	DECLARE CURSOR customer_cursor IS"
"		SELECT C_FIRST, C_MIDDLE, C_LAST, C_STREET_1, C_STREET_2,"
"				C_CITY, C_STATE, C_ZIP, C_PHONE, C_CREDIT,"
"				C_CREDIT_LIM, C_DISCOUNT, C_BALANCE,"
"				C_SINCE"
"			FROM CUSTOMER"
"			WHERE C_W_ID = c_w_id AND C_D_ID = c_d_id"
"							AND C_ID = c_id"
"			FOR UPDATE;"
	;

	str2 =
	
"	BEGIN"
""
" 	n_items := 200;"
" 	n_warehouses := 1;"
" 	n_districts := 10;"
" 	n_customers := 20;"
""
"	byname := RND(1, 100);"
"	amount := RND(1, 1000);"
"	h_date := SYSDATE();"
"	w_id := c_w_id;"
"	d_id := TO_BINARY(47 + RND(1, n_districts), 1);"
"	c_d_id := TO_BINARY(47 + RND(1, n_districts), 1);"
""
"	IF (byname <= 60) THEN"
"		c_last := CONCAT('NAME', TO_CHAR(RND(1, n_customers) / 3));"
""
"		SELECT COUNT(*) INTO namecnt"
"			FROM CUSTOMER"
"			WHERE C_W_ID = c_w_id AND C_D_ID = c_d_id"
"							AND C_LAST = c_last;"
"		/* PRINTF('Payment trx: Customer name ', c_last,"
"						' namecount ', namecnt); */"
"		OPEN customer_by_name_cursor;"
""
"		FOR i IN 1 .. (namecnt + 1) / 2 LOOP"
"			FETCH customer_by_name_cursor INTO c_id;"
"		END LOOP;"
"		/* ASSERT(NOT (customer_by_name_cursor % NOTFOUND)); */"
"		"
"		CLOSE customer_by_name_cursor;"
"	ELSE"
"		c_id := TO_BINARY(RND(1, n_customers), 3);"
"	END IF;"

	;
	str3 =
""
"	/* PRINTF('Payment for customer ', BINARY_TO_NUMBER(c_w_id), ' ',"
"				c_d_id, ' ', BINARY_TO_NUMBER(c_id)); */"
"	OPEN customer_cursor;"
""
"	FETCH customer_cursor INTO c_first, c_middle, c_last, c_street_1,"
"					c_street_2, c_city, c_state, c_zip,"
"					c_phone, c_credit, c_credit_lim,"
"					c_discount, c_balance, c_since;"
"	c_balance := c_balance - amount;"
""
"	OPEN district_cursor;"
""
"	FETCH district_cursor INTO d_street_1, d_street_2, d_city, d_state,"
"							d_zip, d_name;"
"	UPDATE DISTRICT SET D_YTD = D_YTD + amount"
"		WHERE CURRENT OF district_cursor;"
""
"	CLOSE district_cursor;"
""
"	OPEN warehouse_cursor;"
""
"	FETCH warehouse_cursor INTO w_street_1, w_street_2, w_city, w_state,"
"							w_zip, w_name;"
"	UPDATE WAREHOUSE SET W_YTD = W_YTD + amount"
"		WHERE CURRENT OF warehouse_cursor;"
""
"	CLOSE warehouse_cursor;"
""
"	h_data := CONCAT(w_name, '    ', d_name);"
"	"
"	IF (c_credit = 'BC') THEN"
"		/* PRINTF('Bad customer pays'); */"
""
"		SELECT C_DATA INTO c_data"
"			FROM CUSTOMER"