[转]MySQL游标的使用

打开游标

1. OPEN ordernumbers; 

循环所有的行

1. REPEAT  
2. -- Get order number  
3. FETCH ordernumbers INTO o;  
4. update account set allMoneyallMoney=allMoney+72,lastMonthConsumelastMonthConsume=lastMonthConsume-72 where NumTg=@o;  

循环结束

1. UNTIL no_more_departments  
2. END REPEAT; 

关闭游标

1. CLOSE ordernumbers; 

DELIMITER $$

USE `kubauser`$$

DROP PROCEDURE IF EXISTS `cursortest`$$

CREATE DEFINER=`coo8new`@`%` PROCEDURE `cursortest`(OUT a VARCHAR(50),OUT b VARCHAR(50))
    BEGIN
    DECLARE _outuserid VARCHAR(50);
    DECLARE _kubauserid VARCHAR(50);
    DECLARE flag INT;
    DECLARE update_cursor CURSOR 
    FOR 
    SELECT outuserid,kubauserid FROM ecuser_cooperationuser;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=1;
    SET flag=0;
    OPEN update_cursor;
    REPEAT  /*循环*/
    FETCH update_cursor INTO _outuserid,_kubauserid;
        SET a=_outuserid;
        SET b=_kubauserid;
    /*update set where*/
    UNTIL flag 
    END REPEAT;
    CLOSE update_cursor ;
            
    END$$

DELIMITER ;

[转]MySQL游标的使用

标签：