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