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