1200字范文,内容丰富有趣,写作的好帮手!
1200字范文 > Mysql创建存储过程及遍历查询结果

Mysql创建存储过程及遍历查询结果

时间:2022-02-27 20:25:55

相关推荐

Mysql创建存储过程及遍历查询结果

1人收藏此文章,

发表于3小时前(-10-11 11:59) ,

已有18次阅读 ,共0个评论

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

No tags for this post.

本内容不代表本网观点和政治立场,如有侵犯你的权益请联系我们处理。
网友评论
网友评论仅供其表达个人看法,并不表明网站立场。