存储过程

 1 #删除存储过程
 2 -- drop procedure if exists add_test;  
 3 CREATE PROCEDURE add_test()  
 4   
 5     begin
 6     #定义变量
 7     declare client_id int;
 8     declare shop_id int;
 9 
10     #定义游标位置变量
11     declare local_status int; 
12     
13     #创建游标
14     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 <> '';
15     
16     #定义游标默认值
17     DECLARE CONTINUE HANDLER FOR NOT FOUND SET local_status=0;
18     #再次设置有女表默认值
19     set local_status=0;
20     
21     #打开游标
22     open local_message;
23     #获取总数量
24     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 <> '');
25     
26     #while循环
27     while local_status>0 do
28     #给游标赋值
29     fetch local_message into shop_id,client_id; #将sql查询出的值赋值给上面的变量
30     
31     #判断数据是否处理完
32     if(local_status>0) then
33     
34     #要处理的sql语句
35     update t_shop set f_foreign_client_id = client_id where id = shop_id;
36      
37     #处理完每条数据之后，需要给游标为值减一 
38     set local_status=(local_status-1);
39     end if;
40     #循环结束
41     end while;
42     
43     #结束游标
44     close local_message;
45     end
46       
47   #调用存储函数add_test  
48 --   CALL add_test()