mysql 存储过程循环

2021-07-21 04:28:17

网摘<br>DELIMITER $$
 

CREATE DEFINER=`root`@`%` PROCEDURE 
`sp_pament_TrainFee`()
BEGIN
/*局部变量的定义 declare*/

declare strYear int;

declare strEnrollID int;

declare feesum int;

declare stop int default 
      0;

declare 
cur cursor 
for(

select  
year ,EnrollID ,sum(Fee) from 
trainmanage  where 
ReturnStatus !=1 group 
by Year,EnrollID );

 /*这把 游标 异常后 捕捉

         *        并设置 循环使用 变量 stop 为 null 跳出循环。
*/

declare 
CONTINUE HANDLER FOR 
SQLSTATE ‘02000‘ 
SET stop = null;
/*开游标*/

OPEN cur;
/*游标向下走一步，将查询出来的值付给定义的变量*/

    FETCH 
cur INTO strYear,strEnrollID,feesum;

    WHILE ( stop is 
not null) DO

    update 
payment  set  
ExpectTrain=feesum   where 
EnrollID = strEnrollID and 
right(ID,2) =right(strYear,2) ;

    FETCH 
cur INTO strYear,strEnrollID,feesum;

    END 
WHILE;
/*游标向下走一步*/

CLOSE cur;
END

mysql 存储过程循环,布布扣,bubuko.com

mysql 存储过程循环

码农公寓

相关文章