mysql实战技巧（七）mysql存贮过程实战

      BEGIN
          #两个游标相隔需要 begin end 隔开
     		 begin
     						declare temp_id int;
     						declare sum1 int;
     						declare done INT DEFAULT 0;
     						#插入管理者主表游标
     						declare cursor_three CURSOR FOR
                 #跟sql语句指定游标包括的值
     						select id from org_department_info;
                 #指定跳出游标的条件
     						declare continue handler for not found set done =1;
     					  #打开游标
      						open cursor_three;
                 #开始循环游标
      						myLoop:LOOP
                 #通过游标向变量中赋值
      						fetch cursor_three into temp_id;
                 #跳出循环的条件
      						if done=1 then
      								leave myLoop;
     						end if;
                 #查询是或否已有
                 select count(1) as sum1 into sum1 from org_manager where  departId = temp_id;
                 #判断
                  if sum1 = 0 THEN
                          INSERT INTO org_manager
      										 ( `departId`, `departManagerId`, `applicationId`, `status`, `approvalSign`, `maintenancePeriod`, `remark`, `addPeople`, `addTimes`, `operatorId`, `operateTimes`)
     											VALUES ( temp_id, 1, 1, '1', '1', '1', 1, 1, SYSDATE(), 1,  SYSDATE());
                 end if;
     						end loop myLoop;
      						CLOSE cursor_three ;
     		end;
         BEGIN
                declare doneTwo INT DEFAULT 0;
                declare manage_id int;
                declare sum2 int;
     						#插入管理者主表游标
     					 declare cursor_two CURSOR FOR
     					 select id from org_manager;
                declare continue handler for not found set doneTwo =1;
                 open cursor_two;
                 l2: LOOP
      								fetch cursor_two into manage_id;
      														if doneTwo = 1 then
      																leave l2;
     														end if;
                 #查询是或否已有
                 select count(1) as sum2 into sum2 from org_manager_records where orgManagerInfoId = manage_id;
                  if sum2 = 0 THEN
                     INSERT INTO org_manager_records
                      ( `orgManagerInfoId`, `departName`, `departCode`, `departManagerId`, `managerName`, `status`, `applicationId`, `approvalSign`, `maintenancePeriod`, `remark`, `addPeople`, `addTimes`, `operatorId`, `operateTimes`)
                      VALUES
                       ( manage_id, '', '', '1', '', '1', '1', '1', '1', '111', '1', SYSDATE(), '1', SYSDATE());
                 end if;
                end loop l2;
                 CLOSE cursor_two ;
         end ;
      END
  
 

文章来源: baocl.blog.csdn.net，作者：小黄鸡1992，版权归原作者所有，如需转载，请联系作者。

原文链接：baocl.blog.csdn.net/article/details/98939740