mysql的嵌套循环语句_MYSQL游标嵌套循环，动态语句更新

DELIMITER $$

USE `mtapp_toms`$$

DROP PROCEDURE IF EXISTS `proc_dailyrpt_data_mix_preprocess`$$

CREATE DEFINER=`dtauser`@`%` PROCEDURE `proc_dailyrpt_data_mix_preprocess`()

BEGIN

-- CALL proc_weekrpt_data_mix_preprocess()

#declare v_sql varchar(1000);

DECLARE v_col VARCHAR(50);

DECLARE v_city VARCHAR(50);

DECLARE v_date VARCHAR(20);

DECLARE done INT DEFAULT FALSE;

DECLARE cur CURSOR FOR

SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_name = 'tb_dailyrpt_data_index_day'

AND COLUMN_NAME NOT IN ('时间','所属省份','所属地市','地方简称') ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

SET v_date='2020-07-12';#(SELECT DATE_ADD( CURRENT_DATE(),INTERVAL -1 DAY));

OPEN cur;

read_loop: LOOP

FETCH cur INTO v_col;

IF done THEN

LEAVE read_loop;

END IF;

BEGIN

DECLARE done2 INT DEFAULT FALSE;

DECLARE cur2 CURSOR FOR

SELECT 地市名称 FROM `tb_dailyrpt_config_city` ;#where 地市名称='全量';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = TRUE;

OPEN cur2;

read_loop2: LOOP

FETCH cur2 INTO v_city;

IF done2 THEN

LEAVE read_loop2;

END IF;

BEGIN

#declare v_val varchar(100);

SET @v_sql=CONCAT('set @v_val=(select ',v_col,' from tb_dailyrpt_data_index_day WHERE 所属地市=''',v_city,''' AND 时间=''',v_date,''' limit 1)');

PREPARE stmt FROM @v_sql;

EXECUTE stmt;

#select @v_val;

IF (@v_val IS NULL) THEN

SET @v_sql=CONCAT('set @v_val_last=(SELECT `',v_col,'` FROM `tb_dailyrpt_data_index_day`

WHERE 所属地市=''',v_city,''' AND 时间

IS NOT NULL ORDER BY `时间` DESC LIMIT 1)');

#set @sql=v_sql

#select @v_sql;

PREPARE stmt FROM @v_sql;

EXECUTE stmt;

IF(@v_val_last IS NOT NULL) THEN

SET @v_sql=CONCAT('update tb_dailyrpt_data_index_day set ',v_col,'=''',@v_val_last,''' WHERE 所属地市=''',v_city,''' AND 时间=''',v_date,''' ');

#SELECT @v_sql;

PREPARE stmt FROM @v_sql;

EXECUTE stmt;

END IF;

END IF;

END;

END LOOP;

CLOSE cur2;

#select v_col;

END;

END LOOP;

CLOSE cur;

END$$

DELIMITER ;