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 THENIF total>0 AND uid>0 THENSELECT fcn+fyq into first_jj_yy FROM flightlist WHERE flightId=firstid limit 1;IF secondid>0 THENSELECT 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;REPEATFETCH _CurRex INTO total,parentId;IF NOT _done THEN#IF total > 0 THENSELECT 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