mysql 游标查询
引用:http://blog.csdn.net/a600423444/article/details/6424360
- 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