存储过程

#删除存储过程
-- 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()