當(dāng)前位置：首頁(yè) > IT技術(shù) > 數(shù)據(jù)庫(kù) > 正文

mysql存儲(chǔ)過(guò)程查詢(xún)結(jié)果循環(huán)遍歷 判斷 賦值 游標(biāo)等基本操作
2021-10-22 16:52:18

一、首先說(shuō)下本篇博客所實(shí)現(xiàn)功能的背景和功能是怎樣的：

? ? ? 背景：因?yàn)楣卷?xiàng)目開(kāi)始遷移新平臺(tái)項(xiàng)目，所以以前的平臺(tái)老數(shù)據(jù)以及訂單信息需要拆分表，而且需要業(yè)務(wù)邏輯來(lái)分析以前的訂單表，來(lái)拆分成另外的幾個(gè)新表，

包括增加新的流水分析，以及更新其他用戶(hù)或者商家的余額以及對(duì)賬信息。

? ? ? 功能：需要查詢(xún)出某個(gè)日期節(jié)點(diǎn)后的幾十萬(wàn)條訂單信息，循環(huán)遍歷每條訂單，獲取每條訂單的交易額，從而根據(jù)訂單中的商家id和用戶(hù)id來(lái)更新用戶(hù)表中的積分或

者余額信息，并且要在循環(huán)中為商家保存流水。

? ? ? 講解：其實(shí)這個(gè)存儲(chǔ)過(guò)程一點(diǎn)都不難，主要是本人在之前沒(méi)有寫(xiě)過(guò)存儲(chǔ)過(guò)程的經(jīng)驗(yàn)，而且這次是直接用在新舊項(xiàng)目中訂單模塊的遷移，所以說(shuō)其實(shí)還是有點(diǎn)小小

的壓力的。所以如果沒(méi)有寫(xiě)過(guò)存儲(chǔ)過(guò)程的同學(xué)們可以看看這一篇，然后思考一下。

?

二、存儲(chǔ)過(guò)程技術(shù)點(diǎn)

? ? 適用場(chǎng)景：因?yàn)榇鎯?chǔ)過(guò)程是存在內(nèi)存中的，直接跳過(guò)了用sql語(yǔ)言語(yǔ)法檢查，編譯等過(guò)程中（具體需要百度），所以存儲(chǔ)過(guò)程的效率非常高。另外加上存儲(chǔ)過(guò)程

非常適合有業(yè)務(wù)邏輯的多表操作，結(jié)果集操作等等，所以比我們寫(xiě)一個(gè)復(fù)雜的sql去完成一個(gè)功能，思路會(huì)更加清晰以及更加接近與編程語(yǔ)言的風(fēng)格，比如循環(huán)，判斷

等等。但是存儲(chǔ)過(guò)程使用的場(chǎng)景還是比較少的，原因就是維護(hù)成本比較高，尤其是數(shù)據(jù)庫(kù)有集群的時(shí)候，我還沒(méi)有研究到那些深度。所以本人現(xiàn)在用存儲(chǔ)過(guò)程的

場(chǎng)景就是某些特別耗時(shí)，而且改動(dòng)不大的操作，列入統(tǒng)計(jì)，數(shù)據(jù)遷移等等。

? ? 語(yǔ)法

1、創(chuàng)建存儲(chǔ)過(guò)程
1
2
3
4
create procedure sp_name()
begin
.........
end
 
sp_name() 為存儲(chǔ)過(guò)程名稱(chēng)，（）里面可以設(shè)置帶參數(shù)的，本列子不帶參數(shù)。
邏輯代碼存在于begin 和 end 之中

2、定義變量
DECLARE a VARCHAR(32);相當(dāng)于定義了一個(gè)全局的（作用于begin和end之中的變量，這個(gè)變量可以用來(lái)承接每次循環(huán)的某個(gè)值，相當(dāng)于在while循環(huán)外設(shè)置值來(lái)接收的）
注意：這里的變量必須設(shè)置到begin之后，不能定義在
例如java
1
2
3
4
5
6
7
8
9
int?a=0;
while(a<10){
??a+1;
}
?
這里先定義幾個(gè)變量待會(huì)要使用：
DECLARE name VARCHAR(32);
DECLARE phone VARCHAR(32);
DECLARE password VARCHAR(32);
 
3、游標(biāo)的使用
其實(shí)存儲(chǔ)過(guò)程中的游標(biāo)和java 中的iterator使用有點(diǎn)相似，都是處理循環(huán)遍歷的，游標(biāo)我現(xiàn)在是用來(lái)處理結(jié)果集遍歷的
首先設(shè)置一個(gè)游標(biāo)的結(jié)束標(biāo)志位，這里和java這些iterator.hasNext()相似
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DECLARE s?int?DEFAULT 0;／／如果是開(kāi)發(fā)的話，直接這句話拷貝進(jìn)去，具體原理不是很了解
將結(jié)果集存入游標(biāo)中，相當(dāng)于 iterator? listiterator = list.iterator();? listiterator就相當(dāng)于游標(biāo)，list就是結(jié)果集
?
DECLARE user CURSOR FOR SELECT a.name,a.phone,a.password?from?user_info a ;//從用戶(hù)表中查詢(xún)出name，phone，password進(jìn)入user這個(gè)游標(biāo)中
DECLARE CONTINUE HANDLER FOR SQLSTATE?'02000'?SET s=1; ／／這句話是用在while循環(huán)前的，如果游標(biāo)到了最后就會(huì)將之前定義的s設(shè)置為1 ，直接拷貝進(jìn)入就行
?
剛才演示的是把結(jié)果集存入游標(biāo)，現(xiàn)在開(kāi)始要使用游標(biāo)了，就是java中的相當(dāng)于要while(iterator.hasNext()){}這個(gè)步驟了
?
存儲(chǔ)過(guò)程的游標(biāo)使用要使用,user是剛才的游標(biāo)名
??OPEN user
????...
??CLOSE user
?
將游標(biāo)中的值用變量來(lái)接收需要使用剛才在begin后定義的變量? 如 name phone password
FETCH user?into?name,phone,password;//將游標(biāo)中的值賦值給變量，要注意順序
 
4、while循環(huán)

一般在游標(biāo)的處理過(guò)程中進(jìn)行while循環(huán)，這里的while條件要使用剛才定義的游標(biāo)結(jié)束標(biāo)志 s 的值
過(guò)程如下（結(jié)合游標(biāo)）
1
2
3
4
5
6
7
OPEN user
??FETCH user?into?name,phone,password;／／先將游標(biāo)中的數(shù)據(jù)存入到變量中，這里和java的iterator有點(diǎn)不一樣
??while?a<>1?do??//當(dāng)a不等于1的時(shí)候執(zhí)行內(nèi)容操作
?????...／／進(jìn)行邏輯操作
???FETCH user?into?name,phone,password;?//再在循環(huán)中將游標(biāo)中的值傳入到變量中
??end?while
CLOSE user
 

5、if判斷
 在剛才的邏輯操作中，可以對(duì)變量的值進(jìn)行邏輯操作，就像和java之類(lèi)的編程語(yǔ)言一樣，最常用的不過(guò)if判斷，語(yǔ)法如下
1
2
3
4
5
6
7
if?(a > 0) then?
????select?'> 0';?
elseif (a = 0) then?
????select?'= 0';?
else?
????select?'< 0';?
end?if;
 
除此之外，還可以坐很多其他表的增刪改查的操作，完全可以在存儲(chǔ)過(guò)程中完成業(yè)務(wù)邏輯的修改，但是由于維護(hù)的難度以及測(cè)試的難度，這種運(yùn)用場(chǎng)景還是不多的。


復(fù)制代碼
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
復(fù)制代碼

?

本文摘自 ：https://www.cnblogs.com/

開(kāi)通會(huì)員，享受整站包年服務(wù)立即開(kāi)通 >
推薦內(nèi)容