mysql存储过程

在t_equipment表中查询出全部设备列表，根据条件，插入到t_newmessage表中。

其中用到了游标，日期的比较等

 

下边就是具体代码，需要一个Int型day入参；

 

begin

#传入参数为提前 几个月提醒

#定义变量
 declare equipmnetIDMess int;
 declare equipmentNameCaptionMess varchar(50);
 declare register_dateMess datetime;
 declare warranty_date_Mess datetime;
 declare d datetime;

 declare warranty_dateMess  int;
 declare equipment_list  int;
 declare role_list int;

 declare roleUserID int;
 declare roleUserName varchar(50);
 declare mess_num int;

 declare stopFlag int;
 declare stop_role_mark int;

 

 #给游标赋值
 declare  mycur cursor for   select equipmentid,equipmentNameCaption,register_date,warranty_date  from t_equipment where status=1;
 declare  rolecur cursor for   select userID,userName  from t_userrole where roleID=1;

 

#给变量equipmentId 赋值
 select count(equipmentId) into equipment_list  from t_equipment where status=1;
 select count(userID) into role_list from t_userrole where roleID=1;

 open mycur;
 set stopFlag=1;
 repeat
  fetch  mycur into equipmnetIDMess,equipmentNameCaptionMess,register_dateMess,warranty_dateMess;
   select count(messageID) into mess_num  from t_newmessage where messType=1 and objectID=equipmnetIDMess;
   #已经存在的设备，不再插入
   if(mess_num=0) then
    set d = date_add(register_dateMess,interval +warranty_dateMess month);
    set warranty_date_Mess = date_add(register_dateMess,interval +warranty_dateMess-day month);

    if( DATEDIFF(warranty_date_Mess,now())<=0)  then
     set stop_role_mark=1;
     #循环用户、角色表，所有管理员对应一条信息
     open rolecur;
     repeat
      fetch rolecur into roleUserID,roleUserName;

      insert into t_newmessage (messType,objectID,objectName,title,content,status,treatmentDate,creatDate,treatmentIdea,creatUserID,creatUserName,description,revision)
      values (1,equipmnetIDMess,equipmentNameCaptionMess,'设备到期提醒','设备已经到期',1,null,d,null,roleUserID,roleUserName,null,null);

      set stop_role_mark = stop_role_mark+1;
      until stop_role_mark = role_list+1
     end repeat;
     close rolecur;
    end if;
   end if;
   set stopFlag=stopFlag+1;
   until  stopFlag = equipment_list+1
 end repeat;
 close mycur;
end