MySQL 存储过程-江辉's博客

1.存储过程

/*根据状态修改订单信息*/
DELIMITER $$
DROP PROCEDURE IF EXISTS PRODUCT_SUCCESS $$
CREATE PROCEDURE PRODUCT_SUCCESS(IN i_productId INT)
  BEGIN
    DECLARE o_userId, o_orderId, o_type, o_status DECIMAL DEFAULT 0;
    /*首先这里对游标进行定义*/
    DECLARE cur_order CURSOR FOR
      SELECT zpo.userId,zpo.id,zpo.type
      FROM zc_product_order zpo
      WHERE zpo.status = 2002 AND zpo.productId = i_productId;
    /*这个是个条件处理,针对NOT FOUND的条件*/
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET o_userId = -1;
    /*接着使用OPEN打开游标*/
    OPEN cur_order;
    /*把第一行数据写入变量中,游标也随之指向了记录的第一行*/
    FETCH cur_order
    INTO o_userId, o_orderId, o_type;
    /* 遍历数据表 */
    REPEAT
      CASE o_type
        WHEN 1
        THEN SET o_status = 2005;
        WHEN 2
        THEN SET o_status = 2005;
        WHEN 3
        THEN SET o_status = 2003;
        WHEN 4
        THEN SET o_status = 2003;
      END CASE;
      /*1.修改订单状态*/
      UPDATE zc_product_order
      SET status = o_status
      WHERE id = o_orderId;
      /*2.新增订单日志记录*/
      INSERT INTO zc_product_order_log (orderId, userId, status, createTime)
      VALUES (o_orderId, o_userId, o_status, now());
      /*退出*/
      FETCH cur_order
      INTO o_userId, o_orderId, o_type;
    UNTIL o_userId = -1
    END REPEAT;
    /*Finally: cursor need be closed 用完后记得用CLOSE把资源释放掉*/
    CLOSE cur_order;
  END $$
1.存储过程
1.存储过程

FEATURED TAGS
