第14章 存儲過程和函數
存儲過程和函數是在資料庫中定義一些SQL語句的集合，然後直接調用這些存儲過程和函數來執行已經定義好的SQL語句。
存儲過程和函數可以避免開發人員重複的編寫相同的SQL語句。
而且，存儲過程和函數是在MySQL伺服器中存儲和執行的，可以減少用戶端和伺服器端的資料傳輸。
14.1 建立存儲過程和函數
是指将經常使用的一組SQL語句組合在一起，并将這些SQL語句當做一個整體存儲在MySQL伺服器中。
14.1.1 建立存儲過程
文法：
CREATE PROCEDURE sp_name([proc_parameter[,...]])
[characteristic...] routine_body
其中：
sp_name是存儲過程的名稱；
pro_parameter表示存儲過程的參數清單。
pro_parameter中的每個參數由3部分組成，分别是：
輸入輸出類型、參數名稱、參數類型，文法：
[ IN | OUT | INOUT ] param_name type
其中：
IN表示輸入參數；
OUT表示輸出參數；
INOUT表示既可以是輸入，也可以是輸出；
param_name參數是存儲過程的參數名稱；
type參數指定存儲過程的參數類型，可以是MySQL資料庫的任意資料類型；
characteristic參數指定存儲過程的特性；
注意：
characteristic參數有多個取值。
說明：
□ LANGUAGE SQL：說明routine_body部分是由SQL語言的語句組成，這也是資料庫系統預設的語言；
□ [NOT] DETERMINISTIC：說明存儲過程的執行結果是否是确定的；DETERMINISTIC表示結果是确定的，每次執行存儲過程時，相同的輸入會得到相同的輸出；NOT DETERMINISTIC表示結果是非确定的，相同的輸入可能得到不同的輸出；
□ { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程式使用SQL語句的限制；CONTAINS SQL表示子程式包含SQL語句，但不包含讀或寫資料的語句；NO SQL表示子程式中不包含SQL語句；READS SQL DATA表示子程式中包含讀資料的語句；MODIFIES SQL DATA表示子程式中包含謝書記的語句；預設情況下，系統會指定為CONTAINS SQL。
□ SQL SECURITY { DEFINER | INVOKER }：指明誰有權限來執行。DEFINER表示隻有定義者自己才能夠執行；INVOKER表示調用者可以執行；預設情況下，系統會指定為CONTAINS SQL。
□ COMMENT 'string'：注釋資訊。
例如：
mysql> delimiter &&
mysql> create procedure num_from_employee(in emp_id int,out count_num int)
-> reads sql data
-> begin
-> select count(*) into count_num
-> from employee
-> where d_id=emp_id;
-> end &&
Query OK, rows affected ( sec)
注意：
MySQL中預設的語句結束符為分号（;），存儲過程中的SQL語句需要分号來結束。為了避免沖突，首先用“DELIMITER &&”将MySQL的結束符設定為&&。最後再用“DELIMITER;”來将結束符恢複成分号。這與建立觸發器時是一樣的。
14.1.2 建立存儲函數
在MySQL中，文法：
CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic...] routine_body
說明：
sp_name參數是存儲函數的名稱；
func_parameter表示存儲函數的參數清單，可以由多個參數組成，其中每個參數由參數名稱和參數類型組成。
形式：
param_name type
說明：
param_name參數是存儲函數的參數名稱；
type參數指定存儲函數的參數類型，該類型可以是MySQL資料庫的任意資料類型；
RETURN type指定傳回值的類型；
characteristic參數指定存儲函數的特性，該參數的取值與存儲過程的取值是一樣的；
routine_body參數是SQL代碼的内容，可以用BEGIN...END來标志SQL代碼的開始和結束。
14.1.3 變量的使用
在存儲過程和函數中，可以定義和使用變量。
使用者可以使用DECLARE關鍵字來定義變量，然後可以為變量指派。
這些變量的作用範圍是BEGIN...END程式段中。
1. 定義變量
文法：
DECLARE var_name[,...] type [DEFAULT value]
說明：
DECLARE關鍵字時用來聲明變量的；
var_name參數是變量的名稱，這裡可以同時定義多個變量；
type參數用來指定變量的類型；
DEFAULT value子句将變量預設值設定為value，沒有使用DEFAULT子句時，預設值為NULL。
2. 為變量指派
文法：
SET var_name=expr[,var_name=expr]...
說明：
SET關鍵字是用來為變量指派的；
var_name參數是變量的名稱；
expr參數是複制表達式。
例如：
SET my_sql = 30;
還可以使用SELECT…INTO語句為變量指派。
文法：
SELECT col_name[,...] INTO var_name[,...]
FROM table_name WHERE condition
說明：
col_name參數表示查詢的字段的名稱；
var_name參數是變量的名稱；
table_name參數名額的名稱；
condition參數指查詢條件。
例如：
SELECT d_id INTO my_sql
FROM employee WHERE id=2;
14.1.4 定義條件和處理程式
定義條件和處理程式是事先定義程式執行過程中可能遇到的問題。并且可以在處理程式中定義解決這些問題的辦法。這種方式可以提前預測可能出現的問題，并提出解決辦法。這樣可以增強程式處理問題的能力，避免程式異常停止。MySQL中都是通過DECLARE關鍵字來定義條件和處理程式。
1. 定義條件
文法：
DECLARE condition_name CONDITION FOR condition_value
condition_value:
SQLSTATE [VALUE] sqlstate_value | mysql_error_code
說明：
condition_name參數表示條件的名稱；
condition_value參數表示條件的類型；
sqlstate_value參數和mysql_error_code參數都可以表示MySQL的錯誤。
2. 定義處理程式
14.1.5 光标的使用
查詢語句可能查詢出多條記錄，在存儲過程和函數中使用光标來逐條讀取查詢結果集中的記錄。
光标的使用包括：
聲明光标；
打開光标；
使用光标；
關閉光标。
1. 聲明光标
MySQL中使用DECLARE關鍵字來聲明光标。
文法：
DECLARE cursor_name CURSOR FOR select_statement;
說明：
cursor_name參數表示光标的名稱；
select_statement參數表示SELECT語句的内容；
例如：
DECLARE cur_employee CURSOR FOR SELECT name, age FROM employee;
說明：
光标的名稱為cur_employee；SELECT語句部分是從employee表中查詢出name和age字段的值。
2. 打開光标
MySQL中，使用OPEN關鍵字來打開光标。
文法：
OPEN cursor_name;
3. 使用光标
MySQL中，使用FETCH關鍵字來使用光标。
文法：
FETCH cur_name INTO var_name[,var_name...];
說明：
cursor_name參數表示光标的名稱；
var_name參數表示将光标中的SELECT語句查詢出來的資訊存入該參數中。
var_name必須在聲明光标之前就定義好。
例如：
FETCH cur_employee INTO emp_name, emp_age;
說明：
說明的例子中，将光标cur_employee中SELECT語句查詢出來的資訊存入emp_name和emp_age中。emp_name和emp_age必須在前面已經定義。
4. 關閉光标
MySQL中，使用CLOSE關鍵字來關閉光标。
文法：
CLOSE cursor_name;
14.1.6 流程控制的使用
存儲過程和函數中，可以使用流程控制來控制語句的執行。
包括：
IF語句；
CASE語句；
LOOP語句；
LEAVE語句；
ITERATE語句；
REPEAT語句；
WHILE語句。
1. IF語句
14.2 調用存儲過程和函數
存儲過程和存儲函數都是存儲在伺服器端的SQL語句的集合。要使用這些已經定義好的存儲過程和存儲函數就必須要通過調用的方式來實作。
存儲過程時通過CALL語句來調用的。
存儲函數的使用方法與MySQL内部函數的使用方法是一樣的。
執行存儲過程和存儲函數需要擁有EXECUTE權限。EXECUTE權限的資訊存儲在information_schema資料庫下面的USER_PRIVILEGES表中。
14.2.1 調用存儲過程
MySQL中，使用CALL語句來調用存儲過程。調用存儲過程後，資料庫系統将執行存儲過程中的語句。然後，将結果傳回給輸出值。
文法：
CALL sp_name([parameter[,...]]);
說明：
sp_name是存儲過程的名稱；
parameter是指存儲過程的參數。
例子見書上。
14.2.2 調用存儲函數
14.3 檢視存儲過程和函數
存儲過程和函數建立以後，使用者可以檢視存儲過程和函數的狀态和定義。使用者可以通過SHOW STATUS語句來檢視存儲過程和函數的狀态，也可以通過SHOW CREATE語句來檢視存儲過程和函數的定義。使用者也可以通過查詢information_schema資料庫下的Routines表來檢視存儲過程和函數的資訊。
14.3.1 SHOW STATUS 語句檢視存儲過程和函數的狀态
文法：
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern'];
說明：
PROCEDURE參數表示查詢存儲過程；
FUNCTION參數表示查詢存儲函數；
LIKE 'pattern'參數用來比對存儲過程或函數的名稱。
例如：
mysql> SHOW PROCEDURE STATUS LIKE 'num_from_employee'\G
14.3.2 SHOW CREATE 語句檢視存儲過程和函數定義
文法：
SHOW CREATE {PROCEDURE | FUNCTION}sp_name;
說明：
PROCEDURE參數表示查詢存儲過程；
FUNCTION參數表示查詢存儲函數；
sp_name參數表示存儲過程或函數的名稱。
例如：
show create procedure num_from_employee \G
14.3.3 從information_schema.Routines表中檢視存儲過程和函數的資訊
存儲過程和函數的資訊存儲在information_schema資料庫下的Rountines表中。可以通過查詢該表的記錄來查詢存儲過程和函數的資訊。
文法：
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='sp_name';
說明：
ROUTINE_NAME字段中存儲的是存儲過程和函數的名稱；
sp_name參數表示存儲過程或函數的名稱。
14.4 修改存儲過程和函數
修改存儲過程和函數是指修改已經定義好的存儲過程和函數。
MySQL中，通過ALTER PROCEDURE語句來修改存儲過程。通過ALTER FUNCTION語句來修改存儲函數。
文法：
ALTER{PROCEDURE | FUNCTION}sp_name[characteristic ...]
characteristic:
{CONSTRAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
| SQL SECURITY{DEFINER | INVOKER}
| COMMENT 'string'
說明：
sp_name參數表示存儲過程或函數的名稱；
characteristic參數指定存儲函數的特性；
CONTAINS SQL表示子程式包含SQL語句，但不包含讀或寫資料的語句；
NO SQL表示子程式中不包含SQL語句；
READS SQL DATA表示子程式中包含讀資料的語句；
MODIFIES SQL DATA表示子程式中包含寫資料的語句；
SQL SECURITY { DEFINER | INVOKER }指明誰有權限來執行；
DEFINER表示隻有定義者自己才能夠執行；
INVOKER表示調用者可以執行；
COMMENT 'string'是注釋資訊。
修改存儲過程使用ALTER PROCEDURE語句；
修改存儲函數使用ALTER FUNCTION語句。
14.5 删除存儲過程和函數
MySQL中，使用DROP PROCEDURE語句來删除存儲過程。通過DROP FUNCTION語句來删除存儲函數。
文法：
DROP { PROCEDURE | FUNCTION } sp_name;
說明：
sp_name參數表示存儲過程或函數的名稱。
14.8 常見問題及解答
1.一個存儲過程中可以調用其他的存儲過程嗎？
2.存儲過程和存儲函數的差別是什麼？
3.存儲函數和MySQL内部函數有什麼差別？
參考文獻：
1.《MySQL入門很簡單》。