原文地址：https:///6Ysx1KXs

作者：best.lei

存儲(chǔ)過程和函數(shù)

• 簡單的說，存儲(chǔ)過程就是一條或者多條SQL語句的集合。可以視為批文件，但是其作用不僅僅局限于批處理。本文主要介紹如何創(chuàng)建存儲(chǔ)過程和存儲(chǔ)函數(shù)，以及如何調(diào)用、查看、修改、刪除存儲(chǔ)過程和存儲(chǔ)函數(shù)等。

創(chuàng)建存儲(chǔ)過程和函數(shù)

存儲(chǔ)程序分為存儲(chǔ)過程和存儲(chǔ)函數(shù)。Mysql創(chuàng)建存儲(chǔ)過程和存儲(chǔ)函數(shù)的語句分別為CREATE PROCEDURE和CREATE FUNCTION。使用CALL語句來調(diào)用存儲(chǔ)過程，只能用輸出變量返回值。存儲(chǔ)函數(shù)可以從語句外調(diào)用（通過引用函數(shù)名），也能返回標(biāo)量值。存儲(chǔ)過程也可以調(diào)用其他存儲(chǔ)過程。廢話少說，如下步入正文。

• 創(chuàng)建存儲(chǔ)過程

創(chuàng)建存儲(chǔ)過程的基本語法格式為：CREATE PROCEDURE sp_name([proc_parameter]) [characteristics] routine_body

其中CREATE PROCEDURE為創(chuàng)建存儲(chǔ)過程的關(guān)鍵字，sp_name為存儲(chǔ)過程的名稱，pro_parameter為指定存儲(chǔ)過程的參數(shù)列表，其中參數(shù)列表如下：

1. [IN|OUT|INOUT] param_name type 其中，IN表述輸入?yún)?shù)，OUT表示輸出參數(shù)，INOUT表示即可輸入也可輸出；param_name表述參數(shù)名稱；type表示參數(shù)類型，該類型可以是MySQL數(shù)據(jù)庫中的任意類型。
2. characteristics指定存儲(chǔ)過程的特性，有以下取值：

• LANGUAGE SQL:說明routine_body部分是由SQL語句組成的，當(dāng)前系統(tǒng)支持的語言為SQL，SQL是LANGUAGE特性的唯一值。
• [NOT] DETERMINISTIC:指明存儲(chǔ)過程執(zhí)行的結(jié)果是否確定。DETERMINISTIC表示結(jié)果是確定的，每次執(zhí)行存儲(chǔ)過程時(shí)，相同的輸入會(huì)得到相同的輸出，NOT DETERMINISTIC表示結(jié)果是不確定的，相同的輸入可能得到不同的輸出，如果沒有指定任意一個(gè)值，默認(rèn)為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ù)的語句。默認(rèn)情況下，系統(tǒng)會(huì)指定為CONTAINS SQL。
• SQL SECURITY[DEFINER|INVOKER]：指明誰有權(quán)限來執(zhí)行。DEFINER表示只有定義著才能執(zhí)行。INVOKER表示用友權(quán)限的調(diào)用者可以執(zhí)行。默認(rèn)情況下，系統(tǒng)指定為DEFINER。
• COMMENT 'string'：注釋信息，用來描述存儲(chǔ)過程或函數(shù)。

1. routine_body是SQL代碼的內(nèi)容，可以用BEGIN...END來表示SQL代碼的開始和結(jié)束。

我們從最簡單的存儲(chǔ)過程開始說起，如下是不包含任何參數(shù)的存儲(chǔ)過程，代碼為：CREATE PROCEDURE Proc() BEGIN SELECT * FROM tb_score; END; 我們定義了一個(gè)名稱為Proc的存儲(chǔ)過程，該過程是用來查詢tb_score（該表接上篇博客，已存在）數(shù)據(jù)庫表中的所有數(shù)據(jù)。

第一張圖是通過執(zhí)行sql語句查詢到tb_score表中的數(shù)據(jù)。第二張圖是創(chuàng)建存儲(chǔ)過程，其中第一句DELIMITER //是將MySQL的結(jié)束符設(shè)置為//，因?yàn)镸ySQL默認(rèn)的結(jié)束符為分號(hào)，為了避免與存儲(chǔ)過程中SQL語句結(jié)束符相沖突，需要DELIMITER改變存儲(chǔ)過程的結(jié)束符，并以“END //”結(jié)束存儲(chǔ)過程。第三張圖是調(diào)用存儲(chǔ)過程，在調(diào)用存儲(chǔ)過程之前先將MySQL結(jié)束符恢復(fù)為默認(rèn)的分號(hào)（DELIMITER ;）然后通過CALL Proc()調(diào)用。

接下來我們講解MySQL存儲(chǔ)過程中的參數(shù)IN、OUT、INOUT，IN作為輸入，將輸入作為參數(shù)傳輸?shù)酱鎯?chǔ)過程的執(zhí)行當(dāng)中去；OUT作為輸出，將存儲(chǔ)過程的輸出通過參數(shù)傳出來，而INOUT參數(shù)可以同時(shí)作為輸入和輸出。

還是通過存儲(chǔ)過程查詢tb_score表，不過這次我們要查詢課程號(hào)為1（cID=1）的所有學(xué)生的成績，存儲(chǔ)過程定義為：CREATE PROCEDURE Proc_cID(IN classID INT) BEGIN SELECT * FROM tb_score WHERE cID=classID; END;

如我們需要查詢課程號(hào)為1的學(xué)生的人數(shù)和平均成績，則存儲(chǔ)過程定義如下：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)建存儲(chǔ)函數(shù)

創(chuàng)建存儲(chǔ)函數(shù)需要使用CREATE FUNCTION語句，基本語法格式為：CREATE FUNCTION func_name([func_parameter]) returns type [characteristic] routine_body CREATE FUNCTION為用來創(chuàng)建存儲(chǔ)函數(shù)的關(guān)鍵字，func_name表示存儲(chǔ)函數(shù)的名稱，func_parameter為存儲(chǔ)過程的參數(shù)列表如下：

• [IN|OUT|INOUT] param_name type 其參數(shù)含義同存儲(chǔ)過程（PROCEDURE）相同，其默認(rèn)為IN參數(shù)。

RETURNS type語句表示函數(shù)返回?cái)?shù)據(jù)的類型，characteristic指定存儲(chǔ)函數(shù)的特性，取值與創(chuàng)建存儲(chǔ)過程時(shí)相同。

查詢某個(gè)學(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號(hào)學(xué)生1號(hào)課程的成績。

讀者可能會(huì)發(fā)現(xiàn)存儲(chǔ)過程的查詢結(jié)果可能為多個(gè)值，而存儲(chǔ)函數(shù)的查詢結(jié)果是某一類型的單值。而且存儲(chǔ)過程在調(diào)用時(shí)用CALL而存儲(chǔ)函數(shù)是SELECT。那么存儲(chǔ)過程和函數(shù)具體的區(qū)別又是什么呢？

1. 存儲(chǔ)過程的功能更加復(fù)雜，而函數(shù)的功能針對(duì)性更強(qiáng)；
2. 存儲(chǔ)過程可以返回參數(shù)（通過OUT|INOUT），而函數(shù)只能返回單一值或者表對(duì)象；
3. 存儲(chǔ)過程作為一個(gè)獨(dú)立的部分來執(zhí)行，而函數(shù)可以作為查詢語句的一部分來調(diào)用，由于函數(shù)可以返回一個(gè)表對(duì)象，因此它可以在查詢語句中位于FROM關(guān)鍵字之后；
4. 存儲(chǔ)過程是通過關(guān)鍵字CALL來調(diào)用，作為一個(gè)獨(dú)立的執(zhí)行部分。而存儲(chǔ)函數(shù)則可作為SELECT語句的一部分調(diào)用，嵌入到SQL語句中；
5. 當(dāng)存儲(chǔ)過程和函數(shù)被執(zhí)行的時(shí)候，SQLManager會(huì)到procedure cache中去取相應(yīng)的查詢語句，如果在procedure cache里沒有相應(yīng)的查詢語句，SQLManager就會(huì)對(duì)存儲(chǔ)過程和函數(shù)進(jìn)行編譯。

• 變量的使用

變量可以在子程序中聲明并使用，作用范圍是在BEGIN...END程序中，如下將主要介紹如何定義變量和為變量賦值。

1. 定義變量。語法格式為：DECLARE var_name[,var_name]...data_type[DEFAULT value]; var_name為局部變量名稱，DEFAULT value給變量提供一個(gè)默認(rèn)值。值除了可以被聲明為一個(gè)常數(shù)之外，還可以被指定為一個(gè)表達(dá)式。如果缺少DEFAULT子句，初始值為NULL。
2. 為變量賦值。MySQL中使用SET語句為變量賦值，語法格式為：SET var_name=expr[,var_name=expr]...

• 流程控制的使用

流程控制與用來根據(jù)條件控制語句的執(zhí)行。MySQL中的用來構(gòu)造控制流程的語句有：IF語句、CASE語句、LOOP語句、LEAVE語句、ITERATE語句、REPEAT語句和WHILE語句。各語句介紹如下：

1. IF語句。包含多個(gè)條件判斷，根據(jù)判斷的結(jié)果為TRUE或FALSE執(zhí)行相應(yīng)的語句，語法格式為 IF expr_condition THEN statement_list ESLEIF expr_condition THEN statement_list ESLE statement_list END IF 如下我們定義一個(gè)學(xué)生成績等級(jí)評(píng)定函數(shù)，將學(xué)生成績以參數(shù)的形式傳輸函數(shù)，輸出學(xué)生成績等級(jí)A(90~100)、B(75~90)、C(60~75)、D(60以下)。代碼編寫中需要注意，IF中如果有多個(gè)限制條件，限制條件間用AND連接，DECLARE的變量聲明必須在BEGIN內(nèi)，以及字符串之間拼接用CONCAT。該功能代碼如下圖所示：

1. 
   
2. CASE語句。另外一個(gè)進(jìn)行條件判斷的語句，該語句有2種語句格式。
3. 第一種格式為：CASE case_expr WHEN value THEN statement_list [WHEN value THEN statement_list] [ELSE statement_list] END CASE 其中case_expr參數(shù)表示條件判斷的表達(dá)式，決定哪個(gè)WHEN子句會(huì)被執(zhí)行，value表示表達(dá)式可能的值，如果case_expr等于某個(gè)value，則執(zhí)行相應(yīng)value后的statement_list。
4. 第二種格式為：CASE WHEN expr_condition THEN statement_list [WHEN expr_condition THEN statement_list] [ELSE statement_list] END CASE 其中expr_condition參數(shù)表示條件判斷語句，該格式下，WHEN語句將被逐個(gè)執(zhí)行，直到某個(gè)expr_condition表達(dá)式為真，則這行對(duì)應(yīng)THEN關(guān)鍵字后面的statement_list語句。如果沒有匹配，ELSE子句里的語句被執(zhí)行。
5. LOOP語句。循環(huán)語句用來重復(fù)執(zhí)行某些語句，與IF和CASE相比，LOOP只是創(chuàng)建一個(gè)循環(huán)操作的過程，并不進(jì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í)行的語句。

1. 
   
2. LEAVE語句。從LOOP語句的例子中可知LEAVE語句用來退出任何被標(biāo)注的流程控制構(gòu)造，LEAVE語句基本格式為：LEAVE label
3. 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時(shí)，重復(fù)執(zhí)行p1加1操作；p1大于等于10并且小于20時(shí)，打印消息'p1 is between 10 and 20';p1大于20時(shí)，退出循環(huán)。

1. 
   
2. REPEAT語句。創(chuàng)建一個(gè)帶條件判斷的循環(huán)過程，每次語句執(zhí)行完畢后，會(huì)對(duì)條件表達(dá)式進(jìn)行判斷，表達(dá)式為真循環(huán)結(jié)束，否則重復(fù)執(zhí)行循環(huán)中的語句。語句基本格式：[label]:REPEAT statement_list UNTIL expr_condition END REPEAT [label]
3. WHILE語句。WHILE語句創(chuàng)建一個(gè)帶條件判斷的循環(huán)過程，與REPEAT不同，WHILE在執(zhí)行語句時(shí)先對(duì)指定的表達(dá)式進(jìn)行判斷，為真則執(zhí)行循環(huán)內(nèi)的語句，否則退出循環(huán)。語句基本格式：[label] WHILE expr_condition DO statement_list END WHILE [label]

查看存儲(chǔ)過程和函數(shù)

MySQL中，用戶可以使用SHOW STATUS語句或SHOW CREATE語句來查看存儲(chǔ)過程和函數(shù)，也可以直接從系統(tǒng)的information_schema數(shù)據(jù)庫中查詢。本節(jié)將通過實(shí)例來介紹這3種方法。

• SHOW STATUS語句查看存儲(chǔ)過程和函數(shù)的狀態(tài)，其基本語法為：SHOW{PROCEDURE|FUNCTION} STATUS [LIKE 'pattern'] 這個(gè)語句返回子程序的特征，如數(shù)據(jù)庫、名字、類型、創(chuàng)建者及創(chuàng)建和修改日期。如果沒有指定樣式，根據(jù)使用的語句，所有存儲(chǔ)程序或存儲(chǔ)函數(shù)的信息都被列出。PROCEDURE和FUNCTION分別表示查看存儲(chǔ)過程和函數(shù)，LIKE語句表示匹配存儲(chǔ)過程或函數(shù)的名稱。

• 
  
• SHOW CREATE查看存儲(chǔ)過程和函數(shù)語句格式為：SHOW CREATE {PROCEDURE|FUNCTION} sp_name 它返回一個(gè)可以來重新創(chuàng)建已命名子程序的確切字符串。PROCEDURE和FUNCTION分別表示查看存儲(chǔ)過程和函數(shù)，同樣也可是使用LIKE匹配。

• 
  
• 從information_schema.Routines表中查看存儲(chǔ)過程和函數(shù)的信息。MySQL中存儲(chǔ)過程和函數(shù)的信息存儲(chǔ)在information_schema數(shù)據(jù)庫的Routines表中。通過查詢?cè)摫淼挠涗洸樵兇鎯?chǔ)過程和函數(shù)的信息?；菊Z法格式為：SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='sp_name' 其中ROUTINE_NAME字段存儲(chǔ)的是存儲(chǔ)過程和函數(shù)的名稱，sp_name參數(shù)表述存儲(chǔ)過程或函數(shù)的名稱。

修改存儲(chǔ)過程和函數(shù)

使用ALTER語句可以修改存儲(chǔ)過程或函數(shù)的特性，本節(jié)將介紹如何通過ALTER語句修改存儲(chǔ)過程和函數(shù)。語法格式為：ALTER {PROCEDURE|FUNCTION} sp_name [characteristic ...] 其中，sp_name參數(shù)表示存儲(chǔ)過程或函數(shù)的名稱，characteristic參數(shù)指定存儲(chǔ)函數(shù)的特性，可能的取值有：

1. CONTAINS SQL表示子程序包含SQL語句，但是不包含讀或?qū)憯?shù)據(jù)的語句；
2. NO SQL表示子程序中不包含SQL語句；
3. READES SQL DATA表示子程序中包含讀數(shù)據(jù)的語句；
4. MODIFIES SQL DATA表示子程序中包含寫數(shù)據(jù)的語句；
5. SQL SECURITY{DEFINER|INVOKER}指明誰有權(quán)限來執(zhí)行；
6. DEFINER表示只有定義著自己才能夠執(zhí)行；
7. INVOKER表示調(diào)用者可以執(zhí)行；
8. COMMENT 'string'表示注釋信息。

修改存儲(chǔ)過程使用ALTER PROCEDURE語句，修改存儲(chǔ)函數(shù)使用ALTER FUNCTION語句。

刪除存儲(chǔ)過程

刪除存儲(chǔ)過程和函數(shù)可以使用DROP語句，語法格式為：DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name 這個(gè)語句被用來移除一個(gè)存儲(chǔ)過程或函數(shù)，sp_name為要移除的存儲(chǔ)過程或函數(shù)的名稱。IF EXISTS子句是一個(gè)MySQL的擴(kuò)展，如果存儲(chǔ)過程或函數(shù)不存在，以防發(fā)生錯(cuò)誤，產(chǎn)生一個(gè)用SHOW WARNINGS查看的警告。

最后再說幾點(diǎn)值得大家注意的吧：

1. 目前MySQL不支持對(duì)已存在的存儲(chǔ)過程代碼進(jìn)行修改，如果必須修改，則先使用DROP語句刪除該存儲(chǔ)過程，再重新創(chuàng)建新的存儲(chǔ)過程；
2. 存儲(chǔ)過程中包含用戶定義的SQL語句集合，也可是使用CALL語句調(diào)用存儲(chǔ)過程，但不能使用DROP刪除其他存儲(chǔ)過程；
3. 在定義存儲(chǔ)過程參數(shù)列表時(shí)，應(yīng)注意把參數(shù)名與數(shù)據(jù)庫表中的字段名區(qū)別開，否則會(huì)報(bào)錯(cuò)。
4. 如果存儲(chǔ)過程中需要傳入中文參數(shù)，這時(shí)需要在定義存儲(chǔ)過程的時(shí)候，在參數(shù)后加上character set gbk，不然調(diào)用存儲(chǔ)過程使用中文參數(shù)會(huì)出錯(cuò)。如CREATE PROCEDURE userInfo(IN u_name VARCHAR(50) character set gbk, OUT u_age INT)。