第十一章 存儲過程和函數
第一節：存儲過程和函數的引入
存儲過程和函數是在數據庫中定義一些 SQL 語句的集合，然後直接調用這些存儲過程和函數來執行已經定義好
的 SQL 語句。 存儲過程和函數可以避免開發人員重復的編寫相同的 SQL 語句。 而且， 存儲過程和函數是在
MySQL
服務器中存儲和執行的，可以減少客戶端和服務器端的數據傳輸；
第二節：創建存儲過程和函數
2.1 創建存儲過程
CREATE PROCEDURE sp_name([proc_parameter[,...]])
[characteristic...] routine_body
sp_name 參數是存儲過程的名稱；
proc_parameter 表示存儲過程的參數列表；
characteristic 參數指定存儲過程的特性；
routine_body 參數是 SQL 代碼的內容，可以用 BEGIN...END 來標志 SQL 代碼的開始和結束。
proc_parameter 中的每個參數由 3 部分組成。這 3 部分分別是輸入輸出類型、參數名稱和參數類型。
[ IN | OUT | INOUT ] param_name type
其中，IN 表示輸入參數；OUT 表示輸出參數；INOUT 表示既可以是輸入，也可以是輸出；param_name 參數是
存儲過程的參數名稱；type 參數指定存儲過程的參數類型，該類型可以是 MySQL 數據庫的任意數據類型；
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 表示調用者可以執行。默認情況下，系統指定的權限是 DEFINER。
COMMENT ‘string’ ：注釋信息；
2.2 創建存儲函數
CREATE FUNCTION sp_name ( [func_parameter[,...]] )
RETURNS type
[ characteristic... ] routine_body
sp_name 參數是存儲函數的名稱；
func_parameter 表示存儲函數的參數列表；RETURNS type 指定返回值的類型；
characteristic 參數指定存儲過程的特性，該參數的取值與存儲過程中的取值是一樣的；
routine_body 參數是 SQL 代碼的內容，可以用 BEGIN...END 來標志 SQL 代碼的開始和結束；
func_parameter 可以由多個參數組成，其中每個參數由參數名稱和參數類型組成，其形式如下：
param_name type 其中，param_name 參數是存儲函數的參數名稱；
type 參數指定存儲函數的參數類型，該類型可以是 MySQL 數據庫的任意數據類型；
2.3 變量的使用
1，定義變量
DECLARE var_name [,...] type [ DEFAULT value ]
2，為變量賦值
SET var_name = expr [,var_name=expr] ...
SELECT col_name[,...] INTO var_name[,...]
FROM table_name WHERE condition
2.4 游標的使用
查詢語句可能查詢出多條記錄，在存儲過程和函數中使用游標來逐條讀取查詢結果集中的記錄。游標的使
用包括聲明游標、打開游標、使用游標和關閉游標。游標必須聲明在處理程序之前，並且聲明在變量和條
件之後。
1，聲明游標
DECLARE cursor_name CURSOR FOR select_statement ;
2，打開游標
OPEN cursor_name;
3，使用游標
FETCH cursor_name INTO var_name [,var_name ... ]；
4，關閉游標
CLOSE cursor_name；
2.5 流程控制的使用
存儲過程和函數中可以使用流程控制來控制語句的執行。MySQL 中可以使用 IF 語句、CASE 語句、LOOP
語句、LEAVE 語句、ITERATE 語句、REPEAT 語句和 WHILE 語句來進行流程控制。
1，IF 語句
IF search_condition THEN statement_list
[ ELSEIF search_condition THEN statement_list ]...
[ ELSE statement_list ]
END IF
2，CASE 語句
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]...
[ELSE statement_list ]
END CASE
3，LOOP，LEAVE 語句
LOOP 語句可以使某些特定的語句重復執行，實現一個簡單的循環。但是 LOOP 語句本身沒有停止循環
的語句，必須是遇到 LEAVE 語句等才能停止循環。LOOP 語句的語法的基本形式如下：
[begin_label：]LOOP
Statement_list
END LOOP [ end_label ]
LEAVE 語句主要用於跳出循環控制。語法形式如下：
LEAVE label
4，ITERATE 語句
ITERATE 語句也是用來跳出循環的語句。但是，ITERATE 語句是跳出本次循環，然後直接進入下一次
循環。基本語法：
ITERATE label ;
5，REPEAT 語句
REPEAT 語句是有條件控制的循環語句。當滿足特定條件時，就會跳出循環語句。REPEAT 語句的基本
語法形式如下：
[ begin_label : ] REPEAT
Statement_list
UNTIL search_condition
END REPEAT [ end_label ]
6，WHILE 語句
[ begin_label : ] WHILE search_condition DO
Statement_list
END WHILE [ end_label ]
第三節：調用存儲過程和函數
3.1 調用存儲過程
CALL sp_name( [parameter[,...]] )
3.2 調用存儲函數
fun_name( [parameter[,...]] )
第四節：查看存儲過程和函數
4.1 SHOW STATUS 語句查看存儲過程和函數的狀態
SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE ‘pattern’ ] ;
4.2 SHOW CREATE 語句查看存儲過程的函數的定義
SHOW CREATE { PROCEDURE | FUNCTION } sp_name ;
4.3 從 information_schema.Routines 表中查看存儲過程和函數的信息
第五節：修改存儲過程和函數
ALTER { PROCEDURE | FUNCTION } sp_name [ characteristic ... ]
characteristic :
{ CONTAINS 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 | INVODER } 指明誰有權限來執行。
DEFINER 表示只有定義者自己才能夠執行；
INVODER 表示調用者可以執行。
COMMENT ‘string’ 是注釋信息。
第六節：刪除存儲過程和函數
DROP {PROCEDURE | FUNCTION } sp_name ;
