#删除存储过程 -- drop procedure if exists add_test; CREATE PROCEDURE add_test() begin #定义变量 declare client_id int; declare shop_id int; #定义游标位置变量 declare local_status int; #创建游标 declare local_message cursor for SELECT id, f_foreign_client_id from (SELECT ts.id as id,ts.f_tel as sf_tel,tc.f_tel as cf_tel,tc.f_foreign_client_id from t_shop as ts LEFT JOIN t_contact as tc ON ts.f_tel = tc.f_tel where ts.f_foreign_client_id = 0 and ts.f_tel <> 0) as a where f_foreign_client_id <> ''; #定义游标默认值 DECLARE CONTINUE HANDLER FOR NOT FOUND SET local_status=0; #再次设置有女表默认值 set local_status=0; #打开游标 open local_message; #获取总数量 set local_status=(SELECT count(*) from (SELECT ts.id as id,ts.f_tel as sf_tel,tc.f_tel as cf_tel,tc.f_foreign_client_id from t_shop as ts LEFT JOIN t_contact as tc ON ts.f_tel = tc.f_tel where ts.f_foreign_client_id = 0 and ts.f_tel <> 0) as a where f_foreign_client_id <> ''); #while循环 while local_status>0 do #给游标赋值 fetch local_message into shop_id,client_id; #将sql查询出的值赋值给上面的变量 #判断数据是否处理完 if(local_status>0) then #要处理的sql语句 update t_shop set f_foreign_client_id = client_id where id = shop_id; #处理完每条数据之后,需要给游标为值减一 set local_status=(local_status-1); end if; #循环结束 end while; #结束游标 close local_message; end #调用存储函数add_test -- CALL add_test()