MYSQL 存储过程遍历修改每行数据

CREATE DEFINER=`root`@`%` PROCEDURE `inits`()
BEGIN
	#Routine body goes here...

DECLARE s int DEFAULT 0;
DECLARE pid int(11);
DECLARE businesCode VARCHAR(255);
-- 定义游标，并将sql结果集赋值到游标中
DECLARE report CURSOR FOR select P_ID,BUSINESS_CODE from BUSINESS_INFO;
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
-- 打开游标
open report;
-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
fetch report into pid,businesCode;
-- 当s不等于1，也就是未遍历完时，会一直循环
while s<>1 do
-- 执行业务逻辑
update BUSINESS_INFO set USER_ID =(
SELECT u.P_ID FROM USER_INFO u WHERE u.USER_NAME= (
SELECT t.USER_NAME FROM TEMP t WHERE t.BUSINESS_CODE=businesCode
)
),
PRODUCT_ID = (
SELECT p.P_ID FROM PRODUCT_CLASS p WHERE p.PRODUCT_NAME=(
SELECT t.PRODUCT_NAME FROM TEMP t WHERE t.BUSINESS_CODE=businesCode
)
),
FLOOR_ID=(
SELECT f.P_ID FROM FLOOR f WHERE f.FLOOR_NAME=(
SELECT t.FLOOR_NAME FROM TEMP t WHERE t.BUSINESS_CODE=businesCode
)
) where BUSINESS_CODE=businesCode;
-- 将游标中的值再赋值给变量，供下次循环使用
fetch report into pid,businesCode;
-- 当s等于1时表明遍历以完成，退出循环
end while;
-- 关闭游标
close report;

END

最后

以上就是傻傻台灯为你收集整理的MYSQL 存储过程遍历修改每行数据的全部内容，希望文章能够帮你解决MYSQL 存储过程遍历修改每行数据所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。