Mysql變量、游標及存儲過程的應用

mysql自5.0起就支持存儲過程,存儲過程通俗的講就是在一段封裝過的sql,但不僅僅只有sql那么簡單,通常還會有變量、條件判斷、循環體,游標等。

存儲過程的作用

在很多場景中,需要將多個表的數據處理,來產生新的我們需要的數據。這些多個表的數據并不能通過連接等查詢方式給出,只能通過判斷和循環才能產生。這個時候,就可以利用存儲過程來實現。

此外,存儲過程還有一些好處,比如性能比較高,還有能減少網絡請求。如果不用存儲過程來實現的話,使用php來實現就需要調用多次mysql,產生多次請求。

當然,存儲過程也不是沒有缺點的,它比較哪調式,另外不支持集群。

創建存儲過程

創建存儲過程語法如下:

CREATE?PROCEDURE?過程名(參數)? BEGIN ?過程體 END

關于參數,設置參數語法為

[IN|OUT|INOUT] 參數名 類型

  • IN 表示該變量只能在過程體內使用

  • OUT 表示該變量只能在過程體外使用

  • INOUT 表示在過程體內和體外都能使用

下面,我們來創建一個最簡單的存儲過程。

CREATE?PROCEDURE?p1(IN?x?INT)? BEGIN ??SELECT?x; END;

變量

在MySQL中變量分為全局變量局部變量

全局變量以@開頭,無需聲明,直接使用即可,如

SET?@name='gwx';

局部變量需要先聲明,局部變量的初始化方法如下:

DECLARE?x?int?DEFAULT?0;

下面我們來完成一個存儲過程:根據路程計算車費,3公里內按6遠計算,超過的距離按每公里1.2元計算.

--?distance?路程 CREATE?PROCEDURE?p1(in?distance?FLOAT) BEGIN ??DECLARE?d_money?FLOAT?DEFAULT?0;? ????IF?distance>3?THEN? ???????SET?d_money=6+(distance-3)*1.2; ????ELSE ??????SET?d_money=6; ????END?IF; ????SELECT?d_money; END;

游標

拿php做比較,游標有點想foreach,每次循環獲取一條記錄。

定義一個游標:

declare?游標名?CURSOR?FOR?SELECT?語句

開啟關閉游標:

  • open 游標名

  • close 游標名

取游標數據:

FETCH?游標名?INTO?變量名

可以這么簡單的介紹,大家會有疑惑,不清楚應該如何去使用。下面,來看一個實例,從實例中學習如何使用游標。

用游標完成一個非常簡單的功能,將test_cursor表中數字全部累加起來。

CREATE?TABLE?IF?NOT?EXISTS?test_cursor( ??num1?INT(10)?UNSIGNED?NOT?NULL?DEFAULT?0, ??num2?INT(10)?UNSIGNED?NOT?NULL?DEFAULT?0 );???? INSERT?INTO?test_cursor(num1,num2)?VALUES(1,1),(2,2),(3,3); ? CREATE?PROCEDURE?`test_cursor`() BEGIN ????DECLARE?sum?INT(10)?DEFAULT?0; ????DECLARE?n1,n2?INT(10); ????DECLARE?done?INT?DEFAULT?0; ???? ????DECLARE?cur?CURSOR?FOR?SELECT?num1,num2?FROM?test_cursor;? ????DECLARE?continue?HANDLER?FOR?SQLSTATE?'02000'?SET?done=1;? ???? ????OPEN?cur;?--?打開游標 ????WHILE?done=0?DO?????? ????????FETCH?cur?INTO?n1,n2; ???????? ????????IF?done=0?THEN?--?注意這里為什么加IF條件,不加的話,最后一個值會被多加一遍????????? ??????SET?sum=sum+n1+n2; ????????END?IF; ????END?WHILE; ????CLOSE?cur;??--?關閉游標 ???? ????SELECT?sum; END

這里有幾點需要注意,首先局部變量的定義必須要在聲明游標前聲明。

另外,這里DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=1; 表示游標結束后將done設置為1,以結束循環。

? 版權聲明
THE END
喜歡就支持一下吧
點贊13 分享