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

《mysql存储过程之游标遍历数据表.doc》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、mysql存储过程之游标遍历数据表1 BEGIN 2     DECLAREDoneINTDEFAULT0; 3     4     DECLARECurrentLingQiINT; 5     6     DECLAREShizuNameVARCHAR(30); 7     /*声明游标*/ 8     DECLARErsCURSORFORSELECTNodeName,LingQiFROMsocialrelation; 9     /*异常处理*/10     DECLARECONTINUEHANDLERFORSQLSTATE'02000' SE

2、TDone=1;11    12     /*打开游标*/13     OPENrs; 14    15     /*逐个取出当前记录LingQi字段的值，需要进行最大值的判断*/16     FETCHNEXTFROMrsINTOShizuName,CurrentLingQi;    17     /*遍历数据表*/18     REPEAT19           IFNOTDoneTHEN20              SETCurrentLingQi=CurrentLingQi+60;21              /*如果更新后灵气值大于

3、允许的最大值，则就设置为最大值*/22              IFCurrentLingQi>=1800THEN23                 UPDATEsocialrelationSETLingQi=1800WHERENodeName=ShizuName;24              ELSE25              /*否则，正常更新*/26                 UPDATEsocialrelationSETLingQi=LingQi+60WHERENodeName=ShizuName;27           

4、   ENDIF;28           ENDIF;29          30     FETCHNEXTFROMrsINTOShizuName,CurrentLingQi;3132     UNTILDoneENDREPEAT;33    34     /*关闭游标*/35     CLOSErs;36END