存储过程游标遍历loop和while

系统管理员 2022-03-31 11:24 274阅读 0赞

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