原文地址：https://dwz.cn/6Ysx1KXs
作者：best.lei
存儲過程和函數(shù)
- 簡單的說，存儲過程就是一條或者多條SQL語句的集合?？梢砸暈榕募?，但是其作用不僅僅局限于批處理。本文主要介紹如何創(chuàng)建存儲過程和存儲函數(shù)，以及如何調(diào)用、查看、修改、刪除存儲過程和存儲函數(shù)等。
創(chuàng)建存儲過程和函數(shù)
存儲程序分為存儲過程和存儲函數(shù)。Mysql創(chuàng)建存儲過程和存儲函數(shù)的語句分別為CREATE PROCEDURE和CREATE FUNCTION。使用CALL語句來調(diào)用存儲過程，只能用輸出變量返回值。存儲函數(shù)可以從語句外調(diào)用（通過引用函數(shù)名），也能返回標(biāo)量值。存儲過程也可以調(diào)用其他存儲過程。廢話少說，如下步入正文。
創(chuàng)建存儲過程的基本語法格式為：CREATE PROCEDURE sp_name([proc_parameter]) [characteristics] routine_body
其中CREATE PROCEDURE為創(chuàng)建存儲過程的關(guān)鍵字，sp_name為存儲過程的名稱，pro_parameter為指定存儲過程的參數(shù)列表，其中參數(shù)列表如下：
- [IN|OUT|INOUT] param_name type 其中，IN表述輸入?yún)?shù)，OUT表示輸出參數(shù)，INOUT表示即可輸入也可輸出；param_name表述參數(shù)名稱；type表示參數(shù)類型，該類型可以是MySQL數(shù)據(jù)庫中的任意類型。
- characteristics指定存儲過程的特性，有以下取值：
- LANGUAGE SQL:說明routine_body部分是由SQL語句組成的，當(dāng)前系統(tǒng)支持的語言為SQL，SQL是LANGUAGE特性的唯一值。
- [NOT] DETERMINISTIC:指明存儲過程執(zhí)行的結(jié)果是否確定。DETERMINISTIC表示結(jié)果是確定的，每次執(zhí)行存儲過程時，相同的輸入會得到相同的輸出，NOT DETERMINISTIC表示結(jié)果是不確定的，相同的輸入可能得到不同的輸出，如果沒有指定任意一個值，默認為NOT DETERMINISTIC。
- [CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA]：指明子程序使用SQL語句的限制。CONTAINS SQL表明子程序包含SQL語句，但不包含讀寫數(shù)據(jù)語句；NO SQL表明子程序不包含SQL語句；READS SQL DATA說明子程序包含讀數(shù)據(jù)的語句；MODIFIES SQL DATA表名子程序包含寫數(shù)據(jù)的語句。默認情況下，系統(tǒng)會指定為CONTAINS SQL。
- SQL SECURITY[DEFINER|INVOKER]：指明誰有權(quán)限來執(zhí)行。DEFINER表示只有定義著才能執(zhí)行。INVOKER表示用友權(quán)限的調(diào)用者可以執(zhí)行。默認情況下，系統(tǒng)指定為DEFINER。
- COMMENT 'string'：注釋信息，用來描述存儲過程或函數(shù)。
- routine_body是SQL代碼的內(nèi)容，可以用BEGIN...END來表示SQL代碼的開始和結(jié)束。
我們從最簡單的存儲過程開始說起，如下是不包含任何參數(shù)的存儲過程，代碼為：CREATE PROCEDURE Proc() BEGIN SELECT * FROM tb_score; END; 我們定義了一個名稱為Proc的存儲過程，該過程是用來查詢tb_score（該表接上篇博客，已存在）數(shù)據(jù)庫表中的所有數(shù)據(jù)。
第一張圖是通過執(zhí)行sql語句查詢到tb_score表中的數(shù)據(jù)。第二張圖是創(chuàng)建存儲過程，其中第一句DELIMITER //是將MySQL的結(jié)束符設(shè)置為//，因為MySQL默認的結(jié)束符為分號，為了避免與存儲過程中SQL語句結(jié)束符相沖突，需要DELIMITER改變存儲過程的結(jié)束符，并以“END //”結(jié)束存儲過程。第三張圖是調(diào)用存儲過程，在調(diào)用存儲過程之前先將MySQL結(jié)束符恢復(fù)為默認的分號（DELIMITER ;）然后通過CALL Proc()調(diào)用。
接下來我們講解MySQL存儲過程中的參數(shù)IN、OUT、INOUT，IN作為輸入，將輸入作為參數(shù)傳輸?shù)酱鎯^程的執(zhí)行當(dāng)中去；OUT作為輸出，將存儲過程的輸出通過參數(shù)傳出來，而INOUT參數(shù)可以同時作為輸入和輸出。
還是通過存儲過程查詢tb_score表，不過這次我們要查詢課程號為1（cID=1）的所有學(xué)生的成績，存儲過程定義為：CREATE PROCEDURE Proc_cID(IN classID INT) BEGIN SELECT * FROM tb_score WHERE cID=classID; END;
如我們需要查詢課程號為1的學(xué)生的人數(shù)和平均成績，則存儲過程定義如下：CREATE PROCEDURE Proc_AVG(IN classID INT,OUT total INT,OUT a_s FLOAT) BEGIN SELECT COUNT(*),AVG(grade) INTO total,a_s FROM tb_score WHERE cID=classID; END;
創(chuàng)建存儲函數(shù)需要使用CREATE FUNCTION語句，基本語法格式為：CREATE FUNCTION func_name([func_parameter]) returns type [characteristic] routine_body CREATE FUNCTION為用來創(chuàng)建存儲函數(shù)的關(guān)鍵字，func_name表示存儲函數(shù)的名稱，func_parameter為存儲過程的參數(shù)列表如下：
- [IN|OUT|INOUT] param_name type 其參數(shù)含義同存儲過程（PROCEDURE）相同，其默認為IN參數(shù)。
RETURNS type語句表示函數(shù)返回數(shù)據(jù)的類型，characteristic指定存儲函數(shù)的特性，取值與創(chuàng)建存儲過程時相同。
查詢某個學(xué)生某門課程的成績函數(shù)代碼為：CREATE FUNCTION Query_score(classID INT,studentID INT) RETURNS INT RETURN (SELECT grade FROM tb_score WHERE cID=classID AND sID=studentID); 通過SELECT Query_score(1,1)查詢1號學(xué)生1號課程的成績。
讀者可能會發(fā)現(xiàn)存儲過程的查詢結(jié)果可能為多個值，而存儲函數(shù)的查詢結(jié)果是某一類型的單值。而且存儲過程在調(diào)用時用CALL而存儲函數(shù)是SELECT。那么存儲過程和函數(shù)具體的區(qū)別又是什么呢？
- 存儲過程的功能更加復(fù)雜，而函數(shù)的功能針對性更強；
- 存儲過程可以返回參數(shù)（通過OUT|INOUT），而函數(shù)只能返回單一值或者表對象；
- 存儲過程作為一個獨立的部分來執(zhí)行，而函數(shù)可以作為查詢語句的一部分來調(diào)用，由于函數(shù)可以返回一個表對象，因此它可以在查詢語句中位于FROM關(guān)鍵字之后；
- 存儲過程是通過關(guān)鍵字CALL來調(diào)用，作為一個獨立的執(zhí)行部分。而存儲函數(shù)則可作為SELECT語句的一部分調(diào)用，嵌入到SQL語句中；
- 當(dāng)存儲過程和函數(shù)被執(zhí)行的時候，SQLManager會到procedure cache中去取相應(yīng)的查詢語句，如果在procedure cache里沒有相應(yīng)的查詢語句，SQLManager就會對存儲過程和函數(shù)進行編譯。
變量可以在子程序中聲明并使用，作用范圍是在BEGIN...END程序中，如下將主要介紹如何定義變量和為變量賦值。
- 定義變量。語法格式為：DECLARE var_name[,var_name]...data_type[DEFAULT value]; var_name為局部變量名稱，DEFAULT value給變量提供一個默認值。值除了可以被聲明為一個常數(shù)之外，還可以被指定為一個表達式。如果缺少DEFAULT子句，初始值為NULL。
- 為變量賦值。MySQL中使用SET語句為變量賦值，語法格式為：SET var_name=expr[,var_name=expr]...
流程控制與用來根據(jù)條件控制語句的執(zhí)行。MySQL中的用來構(gòu)造控制流程的語句有：IF語句、CASE語句、LOOP語句、LEAVE語句、ITERATE語句、REPEAT語句和WHILE語句。各語句介紹如下：
- IF語句。包含多個條件判斷，根據(jù)判斷的結(jié)果為TRUE或FALSE執(zhí)行相應(yīng)的語句，語法格式為 IF expr_condition THEN statement_list ESLEIF expr_condition THEN statement_list ESLE statement_list END IF 如下我們定義一個學(xué)生成績等級評定函數(shù)，將學(xué)生成績以參數(shù)的形式傳輸函數(shù)，輸出學(xué)生成績等級A(90~100)、B(75~90)、C(60~75)、D(60以下)。代碼編寫中需要注意，IF中如果有多個限制條件，限制條件間用AND連接，DECLARE的變量聲明必須在BEGIN內(nèi)，以及字符串之間拼接用CONCAT。該功能代碼如下圖所示：
- CASE語句。另外一個進行條件判斷的語句，該語句有2種語句格式。
- 第一種格式為：CASE case_expr WHEN value THEN statement_list [WHEN value THEN statement_list] [ELSE statement_list] END CASE 其中case_expr參數(shù)表示條件判斷的表達式，決定哪個WHEN子句會被執(zhí)行，value表示表達式可能的值，如果case_expr等于某個value，則執(zhí)行相應(yīng)value后的statement_list。
- 第二種格式為：CASE WHEN expr_condition THEN statement_list [WHEN expr_condition THEN statement_list] [ELSE statement_list] END CASE 其中expr_condition參數(shù)表示條件判斷語句，該格式下，WHEN語句將被逐個執(zhí)行，直到某個expr_condition表達式為真，則這行對應(yīng)THEN關(guān)鍵字后面的statement_list語句。如果沒有匹配，ELSE子句里的語句被執(zhí)行。
- LOOP語句。循環(huán)語句用來重復(fù)執(zhí)行某些語句，與IF和CASE相比，LOOP只是創(chuàng)建一個循環(huán)操作的過程，并不進行條件判斷。LOOP內(nèi)的語句一直重復(fù)執(zhí)行直到循環(huán)被退出。跳出循環(huán)過程使用LEAVE子句，LOOP語句基本格式為：[label] LOOP statement_list END LOOP[label] label表示LOOP語句的標(biāo)注名稱，該參數(shù)可以省略，statement_list表示需要執(zhí)行的語句。
- LEAVE語句。從LOOP語句的例子中可知LEAVE語句用來退出任何被標(biāo)注的流程控制構(gòu)造，LEAVE語句基本格式為：LEAVE label
- ITERATE語句。將執(zhí)行順序轉(zhuǎn)到語句段開頭處，語句基本格式為：ITERATE lable ITERATE只可以出現(xiàn)在LOOP、REPEAT和WHILE語句內(nèi)。ITERATE的意思為再次循環(huán)，label參數(shù)表示循環(huán)的標(biāo)志。ITERATE語句必須跟在循環(huán)標(biāo)志前面。例子中p1=0，如果p1的值小于10時，重復(fù)執(zhí)行p1加1操作；p1大于等于10并且小于20時，打印消息'p1 is between 10 and 20';p1大于20時，退出循環(huán)。
- REPEAT語句。創(chuàng)建一個帶條件判斷的循環(huán)過程，每次語句執(zhí)行完畢后，會對條件表達式進行判斷，表達式為真循環(huán)結(jié)束，否則重復(fù)執(zhí)行循環(huán)中的語句。語句基本格式：[label]:REPEAT statement_list UNTIL expr_condition END REPEAT [label]
- WHILE語句。WHILE語句創(chuàng)建一個帶條件判斷的循環(huán)過程，與REPEAT不同，WHILE在執(zhí)行語句時先對指定的表達式進行判斷，為真則執(zhí)行循環(huán)內(nèi)的語句，否則退出循環(huán)。語句基本格式：[label] WHILE expr_condition DO statement_list END WHILE [label]
查看存儲過程和函數(shù)
MySQL中，用戶可以使用SHOW STATUS語句或SHOW CREATE語句來查看存儲過程和函數(shù)，也可以直接從系統(tǒng)的information_schema數(shù)據(jù)庫中查詢。本節(jié)將通過實例來介紹這3種方法。
- SHOW STATUS語句查看存儲過程和函數(shù)的狀態(tài)，其基本語法為：SHOW{PROCEDURE|FUNCTION} STATUS [LIKE 'pattern'] 這個語句返回子程序的特征，如數(shù)據(jù)庫、名字、類型、創(chuàng)建者及創(chuàng)建和修改日期。如果沒有指定樣式，根據(jù)使用的語句，所有存儲程序或存儲函數(shù)的信息都被列出。PROCEDURE和FUNCTION分別表示查看存儲過程和函數(shù)，LIKE語句表示匹配存儲過程或函數(shù)的名稱。
- SHOW CREATE查看存儲過程和函數(shù)語句格式為：SHOW CREATE {PROCEDURE|FUNCTION} sp_name 它返回一個可以來重新創(chuàng)建已命名子程序的確切字符串。PROCEDURE和FUNCTION分別表示查看存儲過程和函數(shù)，同樣也可是使用LIKE匹配。
- 從information_schema.Routines表中查看存儲過程和函數(shù)的信息。MySQL中存儲過程和函數(shù)的信息存儲在information_schema數(shù)據(jù)庫的Routines表中。通過查詢該表的記錄查詢存儲過程和函數(shù)的信息?；菊Z法格式為：SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='sp_name' 其中ROUTINE_NAME字段存儲的是存儲過程和函數(shù)的名稱，sp_name參數(shù)表述存儲過程或函數(shù)的名稱。
修改存儲過程和函數(shù)
使用ALTER語句可以修改存儲過程或函數(shù)的特性，本節(jié)將介紹如何通過ALTER語句修改存儲過程和函數(shù)。語法格式為：ALTER {PROCEDURE|FUNCTION} sp_name [characteristic ...] 其中，sp_name參數(shù)表示存儲過程或函數(shù)的名稱，characteristic參數(shù)指定存儲函數(shù)的特性，可能的取值有：
- CONTAINS SQL表示子程序包含SQL語句，但是不包含讀或?qū)憯?shù)據(jù)的語句；
- NO SQL表示子程序中不包含SQL語句；
- READES SQL DATA表示子程序中包含讀數(shù)據(jù)的語句；
- MODIFIES SQL DATA表示子程序中包含寫數(shù)據(jù)的語句；
- SQL SECURITY{DEFINER|INVOKER}指明誰有權(quán)限來執(zhí)行；
- DEFINER表示只有定義著自己才能夠執(zhí)行；
- INVOKER表示調(diào)用者可以執(zhí)行；
- COMMENT 'string'表示注釋信息。
修改存儲過程使用ALTER PROCEDURE語句，修改存儲函數(shù)使用ALTER FUNCTION語句。
刪除存儲過程
刪除存儲過程和函數(shù)可以使用DROP語句，語法格式為：DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name 這個語句被用來移除一個存儲過程或函數(shù)，sp_name為要移除的存儲過程或函數(shù)的名稱。IF EXISTS子句是一個MySQL的擴展，如果存儲過程或函數(shù)不存在，以防發(fā)生錯誤，產(chǎn)生一個用SHOW WARNINGS查看的警告。
最后再說幾點值得大家注意的吧：
- 目前MySQL不支持對已存在的存儲過程代碼進行修改，如果必須修改，則先使用DROP語句刪除該存儲過程，再重新創(chuàng)建新的存儲過程；
- 存儲過程中包含用戶定義的SQL語句集合，也可是使用CALL語句調(diào)用存儲過程，但不能使用DROP刪除其他存儲過程；
- 在定義存儲過程參數(shù)列表時，應(yīng)注意把參數(shù)名與數(shù)據(jù)庫表中的字段名區(qū)別開，否則會報錯。
- 如果存儲過程中需要傳入中文參數(shù)，這時需要在定義存儲過程的時候，在參數(shù)后加上character set gbk，不然調(diào)用存儲過程使用中文參數(shù)會出錯。如CREATE PROCEDURE userInfo(IN u_name VARCHAR(50) character set gbk, OUT u_age INT)。
