C# UI界面操作数据库核心代码

卡信息处理流程卡信息的查询按钮调用startCARDTotal总函数！总函数中分别循环调用三个子函数card1_temp，card2_temp，card3_temp

CREATE DEFINER=`root`@`%` PROCEDURE `startCARDTotal`(in produc VARCHAR(30),in stime datetime,in etime datetime)
BEGIN
	DECLARE i_1 INT DEFAULT 0;
	DECLARE i_2 INT DEFAULT 0;
	DECLARE i_3 INT DEFAULT 0;
	DECLARE card1num INT;
	DECLARE card2num INT;
	DECLARE card3num INT;
	DECLARE card VARCHAR(30);
	
-- 如果存在临时表则删除
	DROP TABLE IF EXISTS card1_temp;
	DROP TABLE IF EXISTS card2_temp;
	DROP TABLE IF EXISTS card3_temp;
-- 	复制整张表
	CREATE TABLE card1_temp LIKE result_total;
	CREATE TABLE card2_temp LIKE result_total;
	CREATE TABLE card3_temp LIKE result_total;
-- 删除多余列
	ALTER TABLE card1_temp DROP SERIAL,DROP Production,DROP MODEL,DROP CARD_2,DROP CARD_3;
	ALTER TABLE card2_temp DROP SERIAL,DROP Production,DROP MODEL,DROP CARD_1,DROP CARD_3;
	ALTER TABLE card3_temp DROP SERIAL,DROP Production,DROP MODEL,DROP CARD_1,DROP CARD_2;
-- 统计多少张不同的卡
	SELECT COUNT(CARD_1) INTO card1num FROM(SELECT DISTINCT CARD_1 FROM result_total) AS tmp1;
	SELECT COUNT(CARD_2) INTO card2num FROM(SELECT DISTINCT CARD_2 FROM result_total) AS tmp2;
	SELECT COUNT(CARD_3) INTO card3num FROM(SELECT DISTINCT CARD_3 FROM result_total) AS tmp3;
	
	-- 	三张卡分别进行循环统计并插入
	WHILE i_1<card1num
	DO
	-- 	查询result_total得到卡号
		SELECT CARD_1 INTO card FROM
			(SELECT DISTINCT CARD_1 FROM result_total WHERE NewsTime BETWEEN stime AND etime) 
			AS card1 LIMIT i_1,1;
	-- 	调用card_temp插入统计结果
		CALL card1_temp(card,produc,stime,etime);
		SET i_1=i_1+1;
	END WHILE;
	
	-- 	三张卡分别进行循环统计并插入
	WHILE i_2<card2num
	DO
	-- 	查询result_total得到卡号
		SELECT CARD_2 INTO card FROM
			(SELECT DISTINCT CARD_2 FROM result_total WHERE NewsTime BETWEEN stime AND etime) 
			AS card2 LIMIT i_2,1;
	-- 	调用card_temp插入统计结果
		CALL card2_temp(card,produc,stime,etime);
		SET i_2=i_2+1;
	END WHILE;
	
	-- 	三张卡分别进行循环统计并插入
	WHILE i_3<card3num
	DO
	-- 	查询result_total得到卡号
		SELECT CARD_3 INTO card FROM
			(SELECT DISTINCT CARD_3 FROM result_total WHERE NewsTime BETWEEN stime AND etime) 
			AS card3 LIMIT i_3,1;
	-- 	调用card_temp插入统计结果
		CALL card3_temp(card,produc,stime,etime);
		SET i_3=i_3+1;
	END WHILE;

END