存储过程--首次尝试

• 内容简介

  上班期间接到一个需要用存储过程完成的需求，由于之前没有接触过存储过程，数据库也仅限于简单使用，所以在此分享sql语句以及一些简单使用，顺便纪念一下第一次写的存储过程语句。有写的不好的地方欢迎大家指正

---

• 存储过程

  1.具体需求

  线路对应的费率中的底价改为原来底价的0.9折，四舍五入保留两位小数。

  2.分析

  分析：相关的表有operation_route – 线路表 , operation_route_revision – 版本记录表 , route_rate – 费率表 ， operation_route_revision作为线路和费率的桥表
  首先不能改原来定义的数据和他们的关联关系， 所以 实际操作就是要在原来的基础上 将原来线路对应的 版本–费率 重新生成一份，并且创建新的关联关系

  3.思路

  遍历op_route 通过该线路关联的 revision_id 对应 op_route_revision 在 对应 route_rate 复制这两条记录，并且做出修改，更新操作.需要修改的字段有：
  当前线路的版本号revision_id改为 该线路对应的 版本复制以后的ID
  该线路对应的版本中的 费率ID 修改为 对应的 费率更新后的 费率ID
  对应费率的 MinPrice 改为 ListPrice*0.9 取小数点后两位

  ---

• 具体代码

  DELIMITER $$
  	drop procedure if exists `route_rate_update`$$
  CREATE
  
      PROCEDURE `route_rate_update`()
  
      BEGIN
  
      /* 异常值 */
      DECLARE Done INT DEFAULT 0;
  
      /* 线路ID */
      DECLARE routeId INT;
  
      /* 版本ID */
      DECLARE revisionId INT;
  
      /* 费率ID */
      DECLARE rateId INT;
  
      /* 复制后的版本ID */
      DECLARE newRevisionId INT;
  
      /* 复制后的费率ID */
      DECLARE newRateId INT;
      /* 声明op_route表的游标 并查询线路ID，版本ID两个字段值待用 */
      DECLARE opRoute CURSOR FOR SELECT op_route_id, revision_id FROM `operation_route` ort where ort.DELETED = 0 and ort.op_route_id in (8,9);
      /* 异常处理 */
          DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
      /* 打开游标 */
      OPEN opRoute;
  
          /* 遍历线路表 */
          REPEAT
  
          /* 逐个取出游标中查询的字段 */
          FETCH opRoute INTO routeId, revisionId;
  
          /* 如果无异常 */
          IF NOT Done THEN
  
              /* 逻辑处理 */
              -- SELECT  routeId;
              -- SELECT  revisionId;
  
              /* 复制一条 线路ID对应的版本记录 */
              insert into `operation_route_revision` (`op_route_id`, `op_route_name`, ......)
              select `op_route_id`, `op_route_name`, ......;
  
              /* 获取自增版本记录的ID并赋值 */
              select last_insert_id() into newRevisionId;
  
              /* 查询线路对应的版本同时关联的费率记录ID,并赋值给rateId */
              select ortv.RATE_ID from `operation_route_revision` ortv where ortv.REVISION_ID = revisionId into rateId;
  
              /* 复制一条 线路对应的版本同时关联的费率记录,对相应的字段进行修改 */
              INSERT INTO `route_rate` (`ROUTE_ID`, ......
              FROM `route_rate` WHERE `RATE_ID` = rateId ;
  
              /* 获取自增费率记录的ID并赋值 */
              SELECT LAST_INSERT_ID() INTO newRateId;
  
              /* 更新新增的版本记录中的费率ID */
              update `operation_route_revision` ortv set ortv.RATE_ID = newRateId where ortv.REVISION_ID = newRevisionId;
  
              /* 更新线路中关联的版本记录ID */
              UPDATE `operation_route` ort SET ort.revision_id = newRevisionId where ort.op_route_id = routeId;
  
              -- select newRevisionId;
  
          END IF;
  
      /* 循环终止条件 */
      UNTIL Done END REPEAT;
  
      /* 关闭游标 */
          CLOSE opRoute;
      END$$
  DELIMITER ;
  

---

结语

写的时候，结合网上前辈们的记录，直到最后完成，还是有一点成就感的，或许这就是代码的魅力了……

---