mysql 存儲(chǔ)過程遍歷查詢

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;#第一程基建燃油費(fèi)

DECLARE second_jj_ry int default 0;#第二程基建燃油費(fèi)

/* 定義光標(biāo) */

    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;#錯(cuò)誤定義，標(biāo)記循環(huán)結(jié)束

/* 打開光標(biāo) */

    OPEN _Cur;

/* 循環(huán)執(zhí)行 */

         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; #當(dāng)_done=1時(shí)退出被循

/*關(guān)閉光標(biāo)*/

    CLOSE _Cur;

    SET _done = 0;#只有定義為0，新的循環(huán)才能繼續(xù)。

    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