存储过程简单案例
CREATE PROCEDURE `relation_update`()
BEGIN
DECLARE var_count int DEFAULT 0;
DECLARE var_count1 int DEFAULT 0;
DECLARE var_report_name VARCHAR (100) DEFAULT NULL;
DECLARE var_report_code VARCHAR (1000) DEFAULT NULL;
DECLARE cur CURSOR FOR
select report_name,report_code from gaea_report where report_name in (
SELECT distinct(report_code) FROM `gaea_report_dashboard_widget` where tenant_id = 1493439788426678274) ;
SET var_count = 0;
SELECT count(*) into var_count1 from ( select report_name,report_code from gaea_report where report_name in (
SELECT distinct(report_code) FROM `gaea_report_dashboard_widget` where tenant_id = 1493439788426678274) ) t;
OPEN cur;
WHILE var_count < var_count1 DO
FETCH cur INTO var_report_name, var_report_code;
UPDATE `sc_report_pre`.`gaea_report_dashboard` SET `report_code` = var_report_code WHERE `report_code` = var_report_name;
UPDATE `sc_report_pre`.`gaea_report_dashboard_widget` SET `report_code` = var_report_code WHERE `report_code` = var_report_name;
set var_count = var_count + 1;
END WHILE;
CLOSE cur;
END
call relation_update();