今天寫(xiě)一個(gè)mysql存儲(chǔ)過(guò)程，根據(jù)自己的需求要遍歷一個(gè)數(shù)據(jù)表，因?yàn)閷?duì)存儲(chǔ)過(guò)程用的不多，語(yǔ)法不甚熟悉，加之存儲(chǔ)過(guò)程沒(méi)有調(diào)試環(huán)境，花了不少時(shí)間才慢慢弄好，故留個(gè)痕跡。
1 BEGIN
2 DECLARE Done INT DEFAULT 0;
3
4 DECLARE CurrentLingQi INT;
5
6 DECLARE ShizuName VARCHAR(30);
7 /* 聲明游標(biāo) */
8 DECLARE rs CURSOR FOR SELECT NodeName, LingQi FROM socialrelation;
9 /* 異常處理 */
10 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
11
12 /* 打開(kāi)游標(biāo) */
13 OPEN rs;
14
15 /* 逐個(gè)取出當(dāng)前記錄LingQi字段的值，需要進(jìn)行最大值的判斷 */
16 FETCH NEXT FROM rs INTO ShizuName, CurrentLingQi;
17 /* 遍歷數(shù)據(jù)表 */
18 REPEAT
19 IF NOT Done THEN
20 SET CurrentLingQi = CurrentLingQi + 60;
21 /* 如果更新后靈氣值大于允許的最大值，則就設(shè)置為最大值 */
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 /* 關(guān)閉游標(biāo) */
35 CLOSE rs;
36 END