mysql游标

    CREATE PROCEDURE update_pointer()  
    BEGIN  
        /* 定义变量一 */  
        DECLARE  total FLOAT;  
        DECLARE  uid INT;  
        DECLARE  _done INT DEFAULT 0;  
        DECLARE currentP INT DEFAULT 0;  
        DECLARE firstid INT;  
        DECLARE secondid INT;  
        DECLARE parentId VARCHAR(16);  
        DECLARE first_jj_yy INT DEFAULT 0;#第一程基建燃油费  
        DECLARE second_jj_ry INT DEFAULT 0;#第二程基建燃油费  
        /* 定义光标 */  
        DECLARE _Cur CURSOR FOR  
                SELECT aliTotal,ordUID,flightId,backFliId FROM fightorder WHERE ordState=4;#主订单查询  
        DECLARE _CurRex CURSOR FOR  
            SELECT oeactTotal,ordId FROM flight_orderex WHERE oeState=4;  
          
          
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;#错误定义，标记循环结束  
          
        /* 打开光标 */  
        OPEN _Cur;  
             /* 循环执行 */  
             REPEAT  
                FETCH _Cur INTO total, uid, firstid, secondid;  
                IF NOT _done THEN  
                    IF total>0 AND uid>0 THEN  
                        SELECT fcn+fyq INTO first_jj_yy FROM flightlist WHERE flightId=firstid LIMIT 1;  
                        IF secondid>0 THEN  
                            SELECT fcn+fyq INTO second_jj_ry FROM flightlist WHERE flightId=firstid LIMIT 1;  
                        END IF;  
                        UPDATE sysusers SET integral=(integral+total)-(second_jj_ry+first_jj_yy) WHERE sysusers.UID=uid;  
                    END IF;  
                END IF;  
             UNTIL _done END REPEAT; #当_done=1时退出被循  
        /*关闭光标*/  
        CLOSE _Cur;  
        SET _done = 0;#只有定义为0，新的循环才能继续。  
        OPEN _CurRex;  
        REPEAT  
            FETCH _CurRex INTO total,parentId;  
            IF NOT _done THEN  
                #IF total > 0 THEN  
                    SELECT ordUID INTO uid FROM fightorder WHERE fightorder.alipayNo=parentId LIMIT 1;#查询用户UID  
                    #INSERT INTO test values(total,uid);  
                    UPDATE sysusers SET integral=integral+total WHERE sysusers.UID=uid;#新增积分  
                #END IF;  
            END IF;  
        UNTIL _done END REPEAT;  
        CLOSE _CurRex;  

    END 

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