mysql 存储过程递归查询语句 mysql存储过程循环查询结果

-- 创建存储过程
CREATE PROCEDURE plan_use_office_to_project_use_office()
BEGIN
	-- 定义变量
	DECLARE s int DEFAULT 0;
	DECLARE pid VARCHAR(100);
	-- 定义游标，并将sql结果集赋值到游标中
	DECLARE proids CURSOR FOR SELECT project_id FROM bid_project_base;
	-- 声明当游标遍历完后将标志变量置成某个值
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
	-- 打开游标
	open proids;
		-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
		fetch proids into pid;
		-- 当s不等于1，也就是未遍历完时，会一直循环
		while s<>1 do
			-- 执行业务逻辑
			update bid_project_base set use_office_id = 
				(select pi.use_office_id from plan_info pi where pi.plan_id = (select ppr.plan_id from plan_project_relation ppr where ppr.project_id = pid))
			where project_id = pid;			
			-- 将游标中的值再赋值给变量，供下次循环使用
			fetch proids into pid;
		-- 当s等于1时表明遍历以完成，退出循环
		end while;
	-- 关闭游标
	close proids;
END;

call plan_use_office_to_project_use_office();

DROP PROCEDURE IF EXISTS plan_use_office_to_project_use_office;