mysql存储过程游标的运用，适合对游标刚学习者。

近来，因业务的需要写了一个存储，放上面晒晒。适合对游标刚学习者，大致业务是实现对多张表审核操作需要插入审核消息记录

--创建带有三个输入参数，一个输出参数的存储
create procedure prop_dealMessage  (in ids integer ,in status1 integer ,in op integer , out  ids1 varchar(20))


begin 

--声明业务变量
declare v_sql varchar(100); 
declare var1 varchar(20);
declare message text;
declare var2 varchar(20);
declare var3 integer;

--声明游标并标记游标是否可读；no_more_departments = 0 可读；no_more_departments = 1 不可读；这里大量声明了多个游标其实不可取，但用动态sql也不好处理，为了--完成任务暂时就这么写上去了。
declare no_more_departments integer default 0;
declare c_result   cursor   for select title,createuser from project_info where id = ids;
declare c_result1 cursor for select p.title,pd.userId from project_detail pd left join project_info p on p.id = pd.projectinfoId where pd.id = ids;
declare CONTINUE HANDLER FOR NOT FOUND  
        SET no_more_departments =1;  


set ids1 = ",";
--处理我的业务逻辑，读者不用关心细节

if (op = 1 || op =7) then
if     status1 = 22 then  set message = ( select  concat("恭喜!你的项目已通过审核成功发布!-"));
elseif status1 = 23 then  set message = "伤心!你的项目未通过审核,下次努力!-" ;
elseif status1 = 21 then  set message = "耐心! 后台正在复审!-" ;
end if;


open c_result;    --打开游标
  dept_loop:REPEAT  
  FETCH C_RESULT INTO var1 ,var3; --将游标每次迭代的数据赋值给变量var1,var3
IF no_more_departments= 0 THEN
                  insert into user_message(receiveid,sendid,content,type,enabled,isDeleted,Createdate) values( var3,-1, concat(message,"\"",var1,"\""),11,1,0,now());--满足条件执行插入数据
select last_insert_id() into var2;
set ids1 = concat(ids1 ,var2 ,",");
                 END IF;

UNTIL no_more_departments  END REPEAT dept_loop;
  close c_result; --关闭游标
elseif op =2 then 
if     status1 = 22 then  set message = ( select  concat("恭喜!你通过审核成功加入-"));
elseif status1 = 23 then  set message = "伤心!你未通过审核失败加入!-" ;
elseif status1 = 21 then  set message = "耐心! 后台正在复审!-" ;
end if;

open c_result1;    
  dept_loop:REPEAT  
  FETCH C_RESULT1 INTO var1 ,var3;
IF no_more_departments= 0 THEN
                  insert into user_message(receiveid,sendid,content,type,enabled,isDeleted,Createdate) values( var3,-1, concat(message,"\"",var1,"\""),11,1,0,now());
select last_insert_id() into var2;
set ids1 = concat(ids1 ,var2 ,",");
                 END IF;

UNTIL no_more_departments  END REPEAT dept_loop;
  close c_result1;
end if;
end

--调用带差存储

call prop_dealMessage(4, 22,8,@x);
select @x;

--总结：读者只要将游标的适用当做循环的适用方法，就很好学习游标的运用了。