mysql存储过程之游标遍历数据表

出处：http://www.cnblogs.com/colder/p/3142067.html

1  BEGIN

 2      DECLARE Done INT DEFAULT 0;

 3    

 4      DECLARE CurrentLingQi INT;

 5    

 6      DECLARE ShizuName VARCHAR(30);

 7     /* 声明游标 */

 8      DECLARE rs CURSOR FOR SELECT NodeName, LingQi FROM socialrelation;

 9     /* 异常处理 */

10      DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;

11    

12     /* 打开游标 */

13      OPEN rs; 

14    

15     /* 逐个取出当前记录LingQi字段的值，需要进行最大值的判断 */

16      FETCH NEXT FROM rs INTO ShizuName, CurrentLingQi;    

17     /* 遍历数据表 */

18      REPEAT

19            IF NOT Done THEN

20               SET CurrentLingQi = CurrentLingQi + 60;

21              /* 如果更新后灵气值大于允许的最大值，则就设置为最大值 */

22               IF CurrentLingQi >= 1800 THEN

23                  UPDATE socialrelation SET LingQi = 1800 WHERE NodeName = ShizuName;

24               ELSE

25              /* 否则，正常更新 */

26                  UPDATE socialrelation SET LingQi = LingQi + 60 WHERE NodeName = ShizuName;

27               END IF;

28            END IF;

29          

30      FETCH NEXT FROM rs INTO ShizuName, CurrentLingQi;

31

32      UNTIL Done END REPEAT;

33    

34     /* 关闭游标 */

35      CLOSE rs;

36 END