mySQL 游标CURSOR使用例子

BEGIN
     DECLARE Done INT DEFAULT 0;
    
     DECLARE CurrentLingQi INT;
    
     DECLARE ShizuName VARCHAR(30);
     /* 声明游标 */
     DECLARE rs CURSOR FOR SELECT NodeName, LingQi FROM socialrelation;
     /* 异常处理 */
     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
    
     /* 打开游标 */
     OPEN rs;  
    
     /* 逐个取出当前记录LingQi字段的值，需要进行最大值的判断 */
     FETCH NEXT FROM rs INTO ShizuName, CurrentLingQi;     
     /* 遍历数据表 */
     REPEAT
           IF NOT Done THEN
              SET CurrentLingQi = CurrentLingQi + 60;
              /* 如果更新后灵气值大于允许的最大值，则就设置为最大值 */
              IF CurrentLingQi >= 1800 THEN
                 UPDATE socialrelation SET LingQi = 1800 WHERE NodeName = ShizuName;
              ELSE
              /* 否则，正常更新 */
                 UPDATE socialrelation SET LingQi = LingQi + 60 WHERE NodeName = ShizuName;
              END IF;
           END IF;
          
     FETCH NEXT FROM rs INTO ShizuName, CurrentLingQi;

     UNTIL Done END REPEAT;
    
     /* 关闭游标 */
     CLOSE rs;
END