mysql存储过程查询结果循环遍历 判断 赋值 游标等基本操作

一、首先说下本篇博客所实现功能的背景和功能是怎样的：

      背景：因为公司项目开始迁移新平台项目，所以以前的平台老数据以及订单信息需要拆分表，而且需要业务逻辑来分析以前的订单表，来拆分成另外的几个新表，

包括增加新的流水分析，以及更新其他用户或者商家的余额以及对账信息。

      功能：需要查询出某个日期节点后的几十万条订单信息，循环遍历每条订单，获取每条订单的交易额，从而根据订单中的商家id和用户id来更新用户表中的积分或

者余额信息，并且要在循环中为商家保存流水。

      讲解：其实这个存储过程一点都不难，主要是本人在之前没有写过存储过程的经验，而且这次是直接用在新旧项目中订单模块的迁移，所以说其实还是有点小小

的压力的。所以如果没有写过存储过程的同学们可以看看这一篇，然后思考一下。

二、存储过程技术点

    适用场景：因为存储过程是存在内存中的，直接跳过了用sql语言语法检查，编译等过程中（具体需要百度），所以存储过程的效率非常高。另外加上存储过程

非常适合有业务逻辑的多表操作，结果集操作等等，所以比我们写一个复杂的sql去完成一个功能，思路会更加清晰以及更加接近与编程语言的风格，比如循环，判断

等等。但是存储过程使用的场景还是比较少的，原因就是维护成本比较高，尤其是数据库有集群的时候，我还没有研究到那些深度。所以本人现在用存储过程的

场景就是某些特别耗时，而且改动不大的操作，列入统计，数据迁移等等。

    语法

1、创建存储过程      

1 2 3 4

​ ​create procedure sp_name()​ ​begin​ ​.........​ ​end​

sp_name() 为存储过程名称，（）里面可以设置带参数的，本列子不带参数。      

逻辑代码存在于begin 和 end 之中      

2、定义变量      

DECLARE a VARCHAR(32);相当于定义了一个全局的（作用于begin和end之中的变量，这个变量可以用来承接每次循环的某个值，相当于在while循环外设置值来接收的）      

注意：这里的变量必须设置到begin之后，不能定义在      

例如java      

5 6 7 8 9

​int​ ​ ​ ​a=0;​ ​while​ ​​ ​(a<10){​ ​a+1;​ ​}​ ​这里先定义几个变量待会要使用：​ ​DECLARE name VARCHAR(32);​ ​DECLARE phone VARCHAR(32);​ ​DECLARE password VARCHAR(32);​

3、游标的使用      

其实存储过程中的游标和java 中的iterator使用有点相似，都是处理循环遍历的，游标我现在是用来处理结果集遍历的      

首先设置一个游标的结束标志位，这里和java这些iterator.hasNext()相似      

10 11 12 13 14 15

​DECLARE s ​ ​int​ ​DEFAULT 0;／／如果是开发的话，直接这句话拷贝进去，具体原理不是很了解​ ​将结果集存入游标中，相当于 iterator  listiterator = list.iterator();  listiterator就相当于游标，list就是结果集​ ​DECLARE user CURSOR FOR SELECT a.name,a.phone,a.password ​ ​from​ ​user_info a ;​ ​//从用户表中查询出name，phone，password进入user这个游标中​ ​DECLARE CONTINUE HANDLER FOR SQLSTATE ​ ​'02000'​ ​SET s=1; ／／这句话是用在​ ​while​ ​循环前的，如果游标到了最后就会将之前定义的s设置为1 ，直接拷贝进入就行​ ​刚才演示的是把结果集存入游标，现在开始要使用游标了，就是java中的相当于要​ ​while​ ​(iterator.hasNext()){}这个步骤了​ ​存储过程的游标使用要使用,user是刚才的游标名​ ​OPEN user​ ​...​ ​CLOSE user​ ​将游标中的值用变量来接收需要使用刚才在begin后定义的变量  如 name phone password​ ​FETCH user ​ ​into​ ​name,phone,password;​ ​//将游标中的值赋值给变量，要注意顺序​

4、while循环      

一般在游标的处理过程中进行while循环，这里的while条件要使用刚才定义的游标结束标志 s 的值      

过程如下（结合游标）      

​OPEN user​ ​FETCH user ​ ​into​ ​name,phone,password;／／先将游标中的数据存入到变量中，这里和java的iterator有点不一样​ ​while​ ​a<>1 ​ ​do​ ​  ​ ​//当a不等于1的时候执行内容操作​ ​...／／进行逻辑操作​ ​FETCH user ​ ​into​ ​name,phone,password; ​ ​//再在循环中将游标中的值传入到变量中​ ​end ​ ​while​ ​CLOSE user​

5、if判断      

在刚才的逻辑操作中，可以对变量的值进行逻辑操作，就像和java之类的编程语言一样，最常用的不过if判断，语法如下      

​if​ ​(a > 0) then ​ ​select​ ​'> 0'​ ​; ​ ​elseif (a = 0) then ​ ​select​ ​'= 0'​ ​; ​ ​else​ ​  ​select​ ​'< 0'​ ​; ​ ​end ​ ​if​ ​;​

除此之外，还可以坐很多其他表的增删改查的操作，完全可以在存储过程中完成业务逻辑的修改，但是由于维护的难度以及测试的难度，这种运用场景还是不多的。      

BEGIN 
  DECLARE stationId VARCHAR(32);
  DECLARE consumeId VARCHAR(32);
  DECLARE openMoney DECIMAL(11,2);

  DECLARE balance DECIMAL(11,4);

  DECLARE payRate DECIMAL(11,4);

  DECLARE s int DEFAULT 0;

  DECLARE consume CURSOR FOR SELECT a.id_ AS consumeId,ROUND( a.consume_money - a.station_save - a.station_discount_save, 2 ) AS openMoney,a.station_id AS stationId FROM upim_user_consume a WHERE a.order_status = 1 AND a.status_ = '0' AND a.consume_time > '2017-08-01 00:00:00'  ORDER BY a.consume_time DESC;

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;


  OPEN consume;

    FETCH consume into consumeId,openMoney,stationId;

    while s <> 1 DO

    SELECT a.balance_ as balance,a.pay_rate AS payRate INTO balance,payRate from station_detail a where a.id=stationId and a.status_<>'-2';

    INSERT INTO upim_station_money_flow(id_,flow_type,source_id,before_change,change_,after_change,station_id) VALUES(REPLACE(UUID(),'-',''),0,consumeId,balance,-openMoney,balance-openMoney,stationId);

    UPDATE station_detail a set a.balance_=balance-openMoney where id=stationId ;           

    SELECT a.balance_ as balance,a.pay_rate AS payRate INTO balance,payRate from station_detail a where a.id=stationId and a.status_<>'-2';

    INSERT INTO upim_station_money_flow(id_,flow_type,source_id,before_change,change_,after_change,station_id) VALUES(REPLACE(UUID(),'-',''),3,consumeId,balance,openMoney*payRate,balance+(openMoney*payRate),stationId);

    UPDATE station_detail a set a.balance_=balance+(openMoney*payRate) where id=stationId ;


    FETCH consume INTO consumeId,openMoney,stationId;
    end WHILE;

  CLOSE consume;

END