Mysql游标查询结果合并

DROP PROCEDURE IF EXISTS insert_extra_column;
DELIMITER //
CREATE PROCEDURE insert_extra_column() 
BEGIN
	##定义判断变量
	DECLARE _flag varchar(50);
	## 定义查询变量
	DECLARE _cur CURSOR FOR 
		SELECT `CODE` FROM	location l where l.DELETED = 0 AND l.LOCATION_TYPE='M';
	### 循环赋初始值
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET _flag=NULL;
	##创建临时表存储每次游标查询结果
	create temporary table result_temp(
			LOCATION_CODE VARCHAR(50),
			DIAGNOSIS_COUNT INT, 
			REMOTE_COUNT INT, 
			SERVICE_TYPE varchar(20) 
	)Engine=InnoDB default charset utf8;
	### 打开
	OPEN _cur;
	## 赋值
	FETCH _cur INTO _flag;
	###循环判断
	WHILE (_flag is not null ) DO		
		INSERT INTO result_temp 
		select 
			_flag as LOCATION_CODE,	
			(select COUNT(*) from visit v where v.DELETED = 0 and v.REPORT_DOCTOR is not null and v.LOCATION_CODE = _flag and v.HIGH_LOCATION_CODE is null) as DIAGNOSIS_COUNT,
			(select COUNT(*) from visit v where v.DELETED = 0 and v.REPORT_DOCTOR is not null and v.LOCATION_CODE = _flag and v.HIGH_LOCATION_CODE is not null) as REMOTE_COUNT,
			'RAD' as SERVICE_TYPE;		
		## 赋值下一个游标
		FETCH _cur INTO _flag;
	END WHILE;
	## 关闭
	CLOSE _cur; 
	SELECT * from result_temp;	
END//  
DELIMITER ;