存储过程 游标遍历loop和while

1、***********loop方式
DELIMITER $$
DROP PROCEDURE IF EXISTS`realtime_income_procedure0` $$
CREATE
    PROCEDURE realtime_income_procedure0(IN daytimeParm VARCHAR(16),IN code_c VARCHAR(20),IN pro_id BIGINT,IN dim_type INT,IN time_type VARCHAR(5))
    BEGIN
        DECLARE appkey VARCHAR(100);
	DECLARE platform VARCHAR(32);
	DECLARE server_id VARCHAR(100);
	DECLARE money DOUBLE;
	 -- 遍历数据结束标志
	DECLARE done BOOLEAN DEFAULT FALSE;
	-- 游标
	DECLARE cur CURSOR FOR (SELECT a.appkey,a.platform,a.server_id,SUM(a.pay_sum) AS money FROM tl_washer_charge_basic AS a WHERE  a.daytime='2019-01-02' GROUP BY a.appkey,a.platform,a.server_id);
	-- 将结束标志绑定到游标
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	OPEN cur;
	-- 遍历
	read_loop:LOOP
		FETCH cur INTO appkey,platform,server_id,money;
		IF done THEN
			LEAVE read_loop;
		END IF;
		-- 开始操作
		INSERT INTO tl_sas_data_info(appkey,CODE,pro_id,server_id,show_time,create_time,dim_type,time_type,update_time)VALUES(appkey,pro_id,server_id,daytimeParm,NOW(),dim_type,time_type,NOW());
		COMMIT;
	END LOOP read_loop;
	CLOSE cur;
    END$$
DELIMITER ;

调用CALL realtime_income_procedure0('2019-01-02','1_2_1_1',16,2,'0m');

2、**************while方式
DELIMITER $$
DROP PROCEDURE IF EXISTS`realtime_income_procedure1` $$
CREATE
    PROCEDURE realtime_income_procedure1(IN in_daytimeParm VARCHAR(16),IN in_code_c VARCHAR(20),IN in_pro_id BIGINT,IN in_dim_type INT,IN in_time_type VARCHAR(5))
    BEGIN
        DECLARE _appkey VARCHAR(100);
	DECLARE _platform VARCHAR(32);
	DECLARE _server_id VARCHAR(100);
	DECLARE _money DOUBLE;
	DECLARE _num INT;
	 -- -- 这个变量用于处理游标到最后一行的情况
	DECLARE done INT DEFAULT 0;
	-- 游标 不加别名查不出结果（坑爹啊）
	DECLARE cur CURSOR FOR (SELECT a.appkey,a.platform,a.server_id,SUM(a.pay_sum) AS money FROM tl_washer_charge_basic AS a WHERE  a.daytime='2019-01-02' GROUP BY a.appkey,a.platform,a.server_id);
	-- 设置一个终止标记，SQLSTATE '02000'是一个未找到条件
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	OPEN cur;
	-- 遍历
	FETCH cur INTO _appkey,_platform,_server_id,_money;
	WHILE done <> 1 DO
		-- 开始操作
		SELECT COUNT(1) INTO _num FROM tl_sas_data_info b WHERE b.appkey=_appkey AND b.platform=_platform AND b.server_id=_server_id AND b.show_time=in_daytimeParm AND b.time_type= in_time_type AND b.dim_type=in_dim_type;
		INSERT INTO debug_tmp (remark)VALUES(CONCAT(_num,'__',_appkey,'__',_platform,'__',_server_id,'__',in_daytimeParm,'__',in_time_type,'__',in_dim_type));
			INSERT INTO tl_sas_data_info(appkey,CODE,pro_id,server_id,show_time,create_time,dim_type,time_type,update_time,data_value)VALUES(_appkey,in_code_c,in_pro_id,_server_id,in_daytimeParm,NOW(),in_dim_type,in_time_type,NOW(),_money);
		FETCH NEXT FROM cur INTO _appkey,_platform,_server_id,_money;
	END WHILE;
	CLOSE cur;
    END$$
DELIMITER ;

调用CALL realtime_income_procedure1('2019-01-02','1_2_1_1',16,2,'0m');