CREATE DEFINER=admin@% PROCEDURE update_account_info()
BEGIN-- 定义存储过程变量
DECLARE
rowIds BIGINT ( 20 );
DECLARE
lineNames VARCHAR ( 255 );
DECLARE
regions VARCHAR ( 255 );
DECLARE
stopCur INT DEFAULT 0;-- 定义游标(更新指定部分数据)
DECLARE
cur CURSOR FOR (
SELECT
t2.row_id,
t.ta_name_of_production_line AS line_name,
t.region
FROM
account_info t2
LEFT JOIN taiwan_district_manager t ON t2.pubtran_code = t.taiwan_District_number
WHERE
t2.STATUS = ‘0’
LIMIT 0,
50000
);-- 定义游标结束,当遍历完成时，将stopCur设置为null?,也可以写成 DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET stopCur = null;
DECLARE
CONTINUE HANDLER FOR NOT FOUND
SET stopCur = NULL;-- 开游标?
OPEN cur;-- 游标向下走一步，将查询出来的两个值赋给定义的两个变量
FETCH cur INTO rowIds,
lineNames,
regions;-- 循环体
WHILE
( stopCur IS NOT NULL ) DO-- 更新对应关系表数据
UPDATE account_info
SET line_name = lineNames,
region = regions,
STATUS = ‘1’
WHERE
row_id = rowIds;-- 游标向下走一步
FETCH cur INTO rowIds,
lineNames,
regions;
END WHILE;-- 关闭游标
CLOSE cur;
END
