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

1人收藏此文章,


发表于3小时前(2013-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.