MySQL 存儲過程

MySQL 5.0 版本開始支持存儲過程。

存儲過程（Stored Procedure）是一種在數據庫中存儲復雜程序，以便外部程序調用的一種數據庫對象。

存儲過程是為了完成特定功能的SQL語句集，經編譯創建并保存在數據庫中，用戶可通過指定存儲過程的名字并給定參數(需要時)來調用執行。

存儲過程思想上很簡單，就是數據庫 SQL 語言層面的代碼封裝與重用。

優點

• 存儲過程可封裝，并隱藏復雜的商業邏輯。
• 存儲過程可以回傳值，并可以接受參數。
• 存儲過程無法使用 SELECT 指令來運行，因為它是子程序，與查看表，數據表或用戶定義函數不同。
• 存儲過程可以用在數據檢驗，強制實行商業邏輯等。

缺點

• 存儲過程，往往定制化于特定的數據庫上，因為支持的編程語言不同。當切換到其他廠商的數據庫系統時，需要重寫原有的存儲過程。
• 存儲過程的性能調校與撰寫，受限于各種數據庫系統。

一、存儲過程的創建和調用

• 存儲過程就是具有名字的一段代碼，用來完成一個特定的功能。
• 創建的存儲過程保存在數據庫的數據字典中。

創建存儲過程

MYSQL 存儲過程中的關鍵語法

聲明語句結束符，可以自定義:

DELIMITER $$  或  DELIMITER //

聲明存儲過程:

CREATE PROCEDURE demo_in_parameter(IN p_in int)       

存儲過程開始和結束符號:

BEGIN .... END    

變量賦值:

SET @p_in=1  

變量定義:

DECLARE l_int int unsigned default 4000000; 

創建mysql存儲過程、存儲函數:

create procedure 存儲過程名(參數)

存儲過程體:

create function 存儲函數名(參數)

實例

創建數據庫，備份數據表用于示例操作：

下面是存儲過程的例子，刪除給定球員參加的所有比賽：

解析：默認情況下，存儲過程和默認數據庫相關聯，如果想指定存儲過程創建在某個特定的數據庫下，那么在過程名前面加數據庫名做前綴。 在定義過程時，使用 DELIMITER $$ 命令將語句的結束符號從分號 ; 臨時改為兩個 $$，使得過程體中使用的分號被直接傳遞到服務器，而不會被客戶端（如mysql）解釋。

調用存儲過程：

call sp_name[(傳參)];

解析：在存儲過程中設置了需要傳參的變量p_playerno，調用存儲過程的時候，通過傳參將57賦值給p_playerno，然后進行存儲過程里的SQL操作。

存儲過程體

• 存儲過程體包含了在過程調用時必須執行的語句，例如：dml、ddl語句，if-then-else和while-do語句、聲明變量的declare語句等
• 過程體格式：以begin開始，以end結束(可嵌套)

BEGIN  　　BEGIN  　　　　BEGIN  　　　　　　statements;   　　　　END  　　END  END

注意：每個嵌套塊及其中的每條語句，必須以分號結束，表示過程體結束的begin-end塊(又叫做復合語句compound statement)，則不需要分號。

為語句塊貼標簽:

[begin_label:] BEGIN  　　[statement_list]  END [end_label]

例如：

標簽有兩個作用：

• 1、增強代碼的可讀性
• 2、在某些語句(例如:leave和iterate語句)，需要用到標簽

二、存儲過程的參數

MySQL存儲過程的參數用在存儲過程的定義，共有三種參數類型,IN,OUT,INOUT,形式如：

CREATEPROCEDURE 存儲過程名([[IN |OUT |INOUT ] 參數名 數據類形...])

• IN 輸入參數：表示調用者向過程傳入值（傳入值可以是字面量或變量）
• OUT 輸出參數：表示過程向調用者傳出值(可以返回多個值)（傳出值只能是變量）
• INOUT 輸入輸出參數：既表示調用者向過程傳入值，又表示過程向調用者傳出值（值只能是變量）

1、in 輸入參數

以上可以看出，p_in 在存儲過程中被修改，但并不影響 @p_id 的值，因為前者為局部變量、后者為全局變量。

2、out輸出參數

3、inout輸入參數

注意：

1、如果過程沒有參數，也必須在過程名后面寫上小括號例：

CREATE PROCEDURE sp_name ([proc_parameter[,...]]) ……

2、確保參數的名字不等于列的名字，否則在過程體中，參數名被當做列名來處理

建議：

• 輸入值使用in參數。
• 返回值使用out參數。
• inout參數就盡量的少用。

三、變量

1. 變量定義

局部變量聲明一定要放在存儲過程體的開始：

DECLAREvariable_name [,variable_name...] datatype [DEFAULT value];

其中，datatype 為 MySQL 的數據類型，如: int, float, date,varchar(length)

例如:

2. 變量賦值

  SET 變量名 = 表達式值 [,variable_name = expression ...]

3. 用戶變量

在MySQL客戶端使用用戶變量:

在存儲過程中使用用戶變量

在存儲過程間傳遞全局范圍的用戶變量

注意:

• 1、用戶變量名一般以@開頭
• 2、濫用用戶變量會導致程序難以理解及管理

四、注釋

MySQL 存儲過程可使用兩種風格的注釋

兩個橫桿—：該風格一般用于單行注釋。

c 風格： 一般用于多行注釋。

例如：

MySQL存儲過程的調用

用call和你過程名以及一個括號，括號里面根據需要，加入參數，參數包括輸入參數、輸出參數、輸入輸出參數。具體的調用方法可以參看上面的例子。

MySQL存儲過程的查詢

我們像知道一個數據庫下面有那些表，我們一般采用 showtables; 進行查看。那么我們要查看某個數據庫下面的存儲過程，是否也可以采用呢？答案是，我們可以查看某個數據庫下面的存儲過程，但是是另一鐘方式。

我們可以用以下語句進行查詢：

selectname from mysql.proc where db='數據庫名';    或者    selectroutine_name from information_schema.routines where routine_schema='數據庫名';    或者    showprocedure status where db='數據庫名';

如果我們想知道，某個存儲過程的詳細，那我們又該怎么做呢？是不是也可以像操作表一樣用describe 表名進行查看呢？

答案是：我們可以查看存儲過程的詳細，但是需要用另一種方法：

SHOWCREATE PROCEDURE 數據庫.存儲過程名;

就可以查看當前存儲過程的詳細。

MySQL存儲過程的修改

ALTER PROCEDURE

更改用 CREATE PROCEDURE 建立的預先指定的存儲過程，其不會影響相關存儲過程或存儲功能。

MySQL存儲過程的刪除

刪除一個存儲過程比較簡單，和刪除表一樣：

DROPPROCEDURE

從 MySQL 的表格中刪除一個或多個存儲過程。

MySQL存儲過程的控制語句

(1). 變量作用域

內部的變量在其作用域范圍內享有更高的優先權，當執行到 end。變量時，內部變量消失，此時已經在其作用域外，變量不再可見了，應為在存儲過程外再也不能找到這個申明的變量，但是你可以通過 out 參數或者將其值指派給會話變量來保存其值。

(2). 條件語句

1. if-then-else 語句

2. case語句：

(3). 循環語句

1. while ···· end while

while 條件 do      --循環體  endwhile

2. repeat···· end repea

它在執行操作后檢查結果，而 while 則是執行前進行檢查。

repeat      --循環體  until 循環條件    end repeat;

3. loop ·····endloop

loop 循環不需要初始條件，這點和 while 循環相似，同時和 repeat 循環一樣不需要結束條件, leave 語句的意義是離開循環。

4. LABLES 標號：

標號可以用在 begin repeat while 或者 loop 語句前，語句標號只能在合法的語句前面使用。可以跳出循環，使運行指令達到復合語句的最后一步。

(4). ITERATE迭代

ITERATE 通過引用復合語句的標號,來從新開始復合語句:

參考文章：

https://www.cnblogs.com/geaozhang/p/6797357.html

http://blog.sina.com.cn/s/blog_86fe5b440100wdyt.html