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