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');