一. 什么是存儲過程

簡單的說竞川，就是一組SQL語句集棋嘲，功能強(qiáng)大酒唉，可以實(shí)現(xiàn)一些比較復(fù)雜的邏輯功能，類似于JAVA語言中的方法沸移；
ps:存儲過程跟觸發(fā)器有點(diǎn)類似痪伦，都是一組SQL集，但是存儲過程是主動(dòng)調(diào)用的雹锣，且功能比觸發(fā)器更加強(qiáng)大网沾，觸發(fā)器是某件事觸發(fā)后自動(dòng)調(diào)用；

二. 存儲過程特性

1. 有輸入輸出參數(shù)蕊爵，可以聲明變量辉哥，有if/else, case,while等控制語句，通過編寫存儲過程攒射，可以實(shí)現(xiàn)復(fù)雜的邏輯功能醋旦；

2. 函數(shù)的普遍特性：模塊化，封裝会放，代碼復(fù)用饲齐；

3. 速度快，只有首次執(zhí)行需經(jīng)過編譯和優(yōu)化步驟咧最，后續(xù)被調(diào)用可以直接執(zhí)行捂人，省去以上步驟甩骏；

三. MySQL存儲過程的創(chuàng)建

3.1 語法

1. CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body

2. CREATE PROCEDURE 過程名([[IN|OUT|INOUT] 參數(shù)名 數(shù)據(jù)類型[,[IN|OUT|INOUT] 參數(shù)名 數(shù)據(jù)類型…]]) [特性 ...] 過程體

DELIMITER //
 CREATE PROCEDURE myproc(OUT s int)
  BEGIN
   SELECT COUNT(*) INTO s FROM students;
  END
  //
DELIMITER ;

3.2 分隔符

MySQL默認(rèn)以";"為分隔符，如果沒有聲明分割符先慷，則編譯器會把存儲過程當(dāng)成SQL語句進(jìn)行處理，因此編譯過程會報(bào)錯(cuò)咨察，所以要事先用“DELIMITER //”聲明當(dāng)前段分隔符论熙，讓編譯器把兩個(gè)"http://"之間的內(nèi)容當(dāng)做存儲過程的代碼，不會執(zhí)行這些代碼摄狱；“DELIMITER ;”的意為把分隔符還原脓诡。

3.3 參數(shù)

1. 存儲過程根據(jù)需要可能會有輸入、輸出媒役、輸入輸出參數(shù)祝谚，如果有多個(gè)參數(shù)用","分割開。MySQL存儲過程的參數(shù)用在存儲過程的定義酣衷，共有三種參數(shù)類型,IN,OUT,INOUT:

IN參數(shù)的值必須在調(diào)用存儲過程時(shí)指定交惯，在存儲過程中修改該參數(shù)的值不能被返回，為默認(rèn)值
OUT:該值可在存儲過程內(nèi)部被改變穿仪，并可返回
INOUT:調(diào)用時(shí)指定席爽，并且可被改變和返回

2. 其中，sp_name參數(shù)是存儲過程的名稱啊片；proc_parameter表示存儲過程的參數(shù)列表只锻； characteristic參數(shù)指定存儲過程的特性；routine_body參數(shù)是SQL代碼的內(nèi)容紫谷，可以用BEGIN…END來標(biāo)志SQL代碼的開始和結(jié)束齐饮。

3. proc_parameter中的每個(gè)參數(shù)由3部分組成。這3部分分別是輸入輸出類型笤昨、參數(shù)名稱和參數(shù)類型祖驱。其形式如下：
   [ IN | OUT | INOUT ] param_name type
   其中，IN表示輸入?yún)?shù)瞒窒；OUT表示輸出參數(shù)羹膳； INOUT表示既可以是輸入，也可以是輸出根竿； param_name參數(shù)是存儲過程的參數(shù)名稱陵像；type參數(shù)指定存儲過程的參數(shù)類型，該類型可以是MySQL數(shù)據(jù)庫的任意數(shù)據(jù)類型寇壳。

4. characteristic參數(shù)有多個(gè)取值醒颖。其取值說明如下：
   （1）LANGUAGE SQL:
   ——說明routine_body部分是由SQL語言的語句組成，這也是數(shù)據(jù)庫系統(tǒng)默認(rèn)的語言壳炎。
   （2）[NOT] DETERMINISTIC：
   ——指明存儲過程的執(zhí)行結(jié)果是否是確定的泞歉。DETERMINISTIC表示結(jié)果是確定的逼侦。每次執(zhí)行存儲過程時(shí)，相同的輸入會得到相同的輸出腰耙。NOT DETERMINISTIC表示結(jié)果是非確定的榛丢，相同的輸入可能得到不同的輸出。默認(rèn)情況下挺庞，結(jié)果是非確定的晰赞。
   （3）{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：
   ——指明子程序使用SQL語句的限制。CONTAINS SQL表示子程序包含SQL語句选侨，但不包含讀或?qū)憯?shù)據(jù)的語句掖鱼；NO SQL表示子程序中不包含SQL語句；READS SQL DATA表示子程序中包含讀數(shù)據(jù)的語句援制；MODIFIES SQL DATA表示子程序中包含寫數(shù)據(jù)的語句戏挡。默認(rèn)情況下，系統(tǒng)會指定為CONTAINS SQL晨仑。
   （4）SQL SECURITY { DEFINER | INVOKER }：
   ——指明誰有權(quán)限來執(zhí)行褐墅。DEFINER表示只有定義者自己才能夠執(zhí)行铺董；INVOKER表示調(diào)用者可以執(zhí)行单旁。默認(rèn)情況下径荔，系統(tǒng)指定的權(quán)限是DEFINER漠秋。
   （5）COMMENT 'string'：
   ——注釋信息偿短。

技巧：創(chuàng)建存儲過程時(shí)赃份，系統(tǒng)默認(rèn)指定CONTAINS SQL饿这，表示存儲過程中使用了SQL語句抛丽。但是噪珊，如果存儲過程中沒有使用SQL語句晌缘，最好設(shè)置為NO SQL。而且痢站，存儲過程中最好在COMMENT部分對存儲過程進(jìn)行簡單的注釋磷箕，以便以后在閱讀存儲過程的代碼時(shí)更加方便。

1. 下面創(chuàng)建一個(gè)名為num_from_employee的存儲過程阵难。代碼如下：

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

2. 上述代碼中岳枷，存儲過程名稱為num_from_employee；輸入變量為emp_id呜叫；輸出變量為count_num空繁。
SELECT語句從employee表查詢d_id值等于emp_id的記錄，并用COUNT(*)計(jì)算d_id值相同的記錄的條數(shù)朱庆，
最后將計(jì)算結(jié)果存入count_num中盛泡。代碼的執(zhí)行結(jié)果如下：

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, 0 rows affected (0.09 sec) 
mysql> DELIMITER ;

3. 代碼執(zhí)行完畢后，沒有報(bào)出任何出錯(cuò)信息就表示存儲函數(shù)已經(jīng)創(chuàng)建成功娱颊。以后就可以調(diào)用這個(gè)存儲過程傲诵，
數(shù)據(jù)庫中會執(zhí)行存儲過程中的SQL語句凯砍。

4. 說明：MySQL中默認(rèn)的語句結(jié)束符為分號（;）。存儲過程中的SQL語句需要分號來 結(jié)束拴竹。為了避免沖突悟衩，
首先用"DELIMITER &&"將MySQL的結(jié)束符設(shè)置為&&。最后再用"DELIMITER ;"來將結(jié)束符恢復(fù)成分號栓拜。這
與創(chuàng)建觸發(fā)器時(shí)是一樣的座泳。

3.4 函數(shù)

1. 在MySQL中，創(chuàng)建存儲函數(shù)的基本形式如下：

CREATE FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body 

2. 其中菱属，sp_name參數(shù)是存儲函數(shù)的名稱；func_parameter表示存儲函數(shù)的參數(shù)列表舰罚；RETURNS type指定返回值的類型纽门；characteristic參數(shù)指定存儲函數(shù)的特性，該參數(shù)的取值與存儲過程中的取值是一樣的营罢，請讀者參照14.1.1小節(jié)的內(nèi)容赏陵；routine_body參數(shù)是SQL代碼的內(nèi)容，可以用BEGIN…END來標(biāo)志SQL代碼的開始和結(jié)束饲漾。

3. func_parameter可以由多個(gè)參數(shù)組成蝙搔，其中每個(gè)參數(shù)由參數(shù)名稱和參數(shù)類型組成，其形式如下：param_name type

4. 其中考传，param_name參數(shù)是存儲函數(shù)的參數(shù)名稱吃型；type參數(shù)指定存儲函數(shù)的參數(shù)類型，該類型可以是MySQL數(shù)據(jù)庫的任意數(shù)據(jù)類型僚楞。

1. 下面創(chuàng)建一個(gè)名為name_from_employee的存儲函數(shù)勤晚。代碼如下：

CREATE FUNCTION name_from_employee (emp_id INT ) 
     RETURNS VARCHAR(20) 
     BEGIN
       RETURN (SELECT name
       FROM employee 
       WHERE num=emp_id ); 
     END

2. 上述代碼中，存儲函數(shù)的名稱為name_from_employee泉褐；該函數(shù)的參數(shù)為emp_id赐写；返回值是VARCHAR
類型。SELECT語句從employee表查詢num值等于emp_id的記錄膜赃，并將該記錄的name字段的值返回挺邀。代碼
的執(zhí)行結(jié)果如下：

mysql> DELIMITER && 
mysql> CREATE FUNCTION name_from_employee (emp_id INT ) 
  -> RETURNS VARCHAR(20) 
  -> BEGIN
  -> RETURN (SELECT name
  -> FROM employee 
  -> WHERE num=emp_id ); 
  -> END&& 
Query OK, 0 rows affected (0.00 sec) 
mysql> DELIMITER ;

結(jié)果顯示，存儲函數(shù)已經(jīng)創(chuàng)建成功跳座。該函數(shù)的使用和MySQL內(nèi)部函數(shù)的使用方法一樣端铛。

3.5 變量的使用

在存儲過程和函數(shù)中，可以定義和使用變量疲眷。用戶可以使用DECLARE關(guān)鍵字來定義變量沦补。然后可以為變量賦值。這些變量的作用范圍是BEGIN…END程序段中咪橙。本小節(jié)將講解如何定義變量和為變量賦值夕膀。

3.5.1 定義變量

1. MySQL中可以使用DECLARE關(guān)鍵字來定義變量虚倒。定義變量的基本語法如下：
   DECLARE var_name[,...] type [DEFAULT value]
   其中， DECLARE關(guān)鍵字是用來聲明變量的产舞；var_name參數(shù)是變量的名稱魂奥，這里可以同時(shí)定義多個(gè)變量；type參數(shù)用來指定變量的類型易猫；DEFAULT value子句將變量默認(rèn)值設(shè)置為value耻煤，沒有使用DEFAULT子句時(shí)，默認(rèn)值為NULL准颓。

2. 下面定義變量my_sql哈蝇，數(shù)據(jù)類型為INT型，默認(rèn)值為10攘已。代碼如下：
   DECLARE my_sql INT DEFAULT 10 ;

3.5.2 為變量賦值

1. MySQL中可以使用SET關(guān)鍵字來為變量賦值炮赦。SET語句的基本語法如下：
   SET var_name = expr [, var_name = expr] ...
   其中，SET關(guān)鍵字是用來為變量賦值的样勃；var_name參數(shù)是變量的名稱吠勘；expr參數(shù)是賦值表達(dá)式。一個(gè)SET語句可以同時(shí)為多個(gè)變量賦值峡眶，各個(gè)變量的賦值語句之間用逗號隔開剧防。
   例：下面為變量my_sql賦值為30。代碼如下：
   SET my_sql = 30 ;

2. MySQL中還可以使用SELECT…INTO語句為變量賦值辫樱。其基本語法如下：
   SELECT col_name[,…] INTO var_name[,…] FROM table_name WEHRE condition
   　　其中峭拘，col_name參數(shù)表示查詢的字段名稱；var_name參數(shù)是變量的名稱狮暑；table_name參數(shù)指表的名稱棚唆；condition參數(shù)指查詢條件。
   下面從employee表中查詢id為2的記錄心例，將該記錄的d_id值賦給變量my_sql宵凌。代碼如下：
   SELECT d_id INTO my_sql FROM employee WEHRE id=2 ;

3.6 定義條件和處理程序

定義條件和處理程序是事先定義程序執(zhí)行過程中可能遇到的問題。并且可以在處理程序中定義解決這些問題的辦法止后。這種方式可以提前預(yù)測可能出現(xiàn)的問題瞎惫，并提出解決辦法。這樣可以增強(qiáng)程序處理問題的能力译株，避免程序異常停止瓜喇。MySQL中都是通過DECLARE關(guān)鍵字來定義條件和處理程序。本小節(jié)中將詳細(xì)講解如何定義條件和處理程序歉糜。

3.6.1 定義條件

1. MySQL中可以使用DECLARE關(guān)鍵字來定義條件乘寒。其基本語法如下：

DECLARE condition_name CONDITION FOR condition_value 
condition_value: 
   SQLSTATE [VALUE] sqlstate_value | mysql_error_code

2. 其中，condition_name參數(shù)表示條件的名稱匪补；condition_value參數(shù)表示條件的類型伞辛；
sqlstate_value參數(shù)和mysql_error_code參數(shù)都可以表示MySQL的錯(cuò)誤烂翰。例如ERROR 1146 (42S02)中，
sqlstate_value值是42S02蚤氏，mysql_error_code值是1146甘耿。

3. 下面定義"ERROR 1146 (42S02)"這個(gè)錯(cuò)誤，名稱為can_not_find竿滨〖烟瘢可以用兩種不同的方法來定義，
代碼如下：

//方法一：使用sqlstate_value 
DECLARE can_not_find CONDITION FOR SQLSTATE '42S02' ; 
//方法二：使用mysql_error_code 
DECLARE can_not_find CONDITION FOR 1146 ;

3.6.2 定義處理程序

1. 使用DECLARE關(guān)鍵字來定義處理程序：

1. MySQL中可以使用DECLARE關(guān)鍵字來定義處理程序于游。其基本語法如下：

DECLARE handler_type HANDLER FOR
condition_value[,...] sp_statement 
handler_type: 
  CONTINUE | EXIT | UNDO 
condition_value: 
  SQLSTATE [VALUE] sqlstate_value |
condition_name | SQLWARNING 
    | NOT FOUND | SQLEXCEPTION | mysql_error_code


2. 其中毁葱，handler_type參數(shù)指明錯(cuò)誤的處理方式，該參數(shù)有3個(gè)取值贰剥。這3個(gè)取值分別是CONTINUE倾剿、
EXIT和UNDO。CONTINUE表示遇到錯(cuò)誤不進(jìn)行處理鸠澈，繼續(xù)向下執(zhí)行柱告；EXIT表示遇到錯(cuò)誤后馬上退出截驮；
UNDO表示遇到錯(cuò)誤后撤回之前的操作笑陈，MySQL中暫時(shí)還不支持這種處理方式。

3. 注意：通常情況下葵袭，執(zhí)行過程中遇到錯(cuò)誤應(yīng)該立刻停止執(zhí)行下面的語句涵妥，并且撤回前面的操作。但是坡锡，
MySQL中現(xiàn)在還不能支持UNDO操作蓬网。因此，遇到錯(cuò)誤時(shí)最好執(zhí)行EXIT操作鹉勒。如果事先能夠預(yù)測錯(cuò)誤類型帆锋，
并且進(jìn)行相應(yīng)的處理，那么可以執(zhí)行CONTINUE操作禽额。

2. condition_value參數(shù)指明錯(cuò)誤類型的6種取值：

1. condition_value參數(shù)指明錯(cuò)誤類型锯厢，該參數(shù)有6個(gè)取值。sqlstate_value和mysql_error_code與
條件定義中的是同一個(gè)意思脯倒。condition_name是DECLARE定義的條件名稱实辑。SQLWARNING表示所有以01
開頭的sqlstate_value值。NOT FOUND表示所有以02開頭的sqlstate_value值藻丢。SQLEXCEPTION表示
所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值剪撬。sp_statement表示一些存儲過程或
函數(shù)的執(zhí)行語句。

2. 下面是定義處理程序的幾種方式悠反。代碼如下:

//方法一：捕獲sqlstate_value 
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SET @info='CAN NOT FIND'; 
//方法二：捕獲mysql_error_code 
DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND'; 
//方法三：先定義條件残黑，然后調(diào)用 
DECLARE can_not_find CONDITION FOR 1146 ; 
DECLARE CONTINUE HANDLER FOR can_not_find SET
@info='CAN NOT FIND'; 
//方法四：使用SQLWARNING 
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR'; 
//方法五：使用NOT FOUND 
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND'; 
//方法六：使用SQLEXCEPTION 
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

3. 上述代碼是6種定義處理程序的方法:

（1）第一種方法是捕獲sqlstate_value值馍佑。如果遇到sqlstate_value值為42S02，執(zhí)行CONTINUE操作萍摊，
并且輸出"CAN NOT FIND"信息挤茄。

（2）第二種方法是捕獲mysql_error_code值。如果遇到mysql_error_code值為1146冰木，執(zhí)行CONTINUE操作穷劈，
并且輸出"CAN NOT FIND"信息。

（3）第三種方法是先定義條件踊沸，然后再調(diào)用條件歇终。這里先定義can_not_find條件，遇到1146錯(cuò)誤就執(zhí)行
CONTINUE操作逼龟。

（4）第四種方法是使用SQLWARNING评凝。SQLWARNING捕獲所有以01開頭的sqlstate_value值，然后執(zhí)行
EXIT操作腺律，并且輸出"ERROR"信息奕短。

（5）第五種方法是使用NOT FOUND。NOT FOUND捕獲所有以02開頭的sqlstate_value值匀钧，然后執(zhí)行
EXIT操作翎碑，并且輸出"CAN NOT FIND"信息。

（6）第六種方法是使用SQLEXCEPTION之斯。SQLEXCEPTION捕獲所有沒有被SQLWARNING或NOT FOUND捕獲
的sqlstate_value值日杈，然后執(zhí)行EXIT操作，并且輸出"ERROR"信息佑刷。

四. MySQL存儲過程寫法總結(jié)

4.1 創(chuàng)建無參存儲過程

create procedure product()
begin
    select * from user;
end;

一條簡單的存儲過程創(chuàng)建語句莉擒，此時(shí)調(diào)用的語句為:
call procedure();

注意，如果是在命令行下編寫的話瘫絮，這樣的寫法會出現(xiàn)語法錯(cuò)誤涨冀，即再select 那一句結(jié)束
mysql就會進(jìn)行解釋了，此時(shí)應(yīng)該先把結(jié)尾符換一下:
delimiter //
create procedure product()
begin
    select * from user;
end //

最后再換回來:
delimiter ;

4.2 創(chuàng)建有參存儲過程

1. 有參的存儲包括兩種參數(shù)麦萤，
一個(gè)是傳入?yún)?shù);
一個(gè)是傳出參數(shù);

例如一個(gè)存儲過程：
create procedure procedure2(
out p1 decimal(8,2),
out p2 decimal(8,2),
in p3 int
)
begin
select sum(uid) into p1 from user where order_name = p3;
select avg(uid) into p2 from user ;
end ;

2. 從上面sql語句可以看出鹿鳖，p1和p2是用來檢索并且傳出去的值，而p3則是必須有調(diào)用這傳入的具體值频鉴。
看具體調(diào)用過程：
call product();    //無參
call procedure2(@userSum,@userAvg,201708);    //有參

3. 當(dāng)用完后栓辜，可以直接查詢userSum和userAvg的值：
select @userSum, @userAvg;

結(jié)果如下：
+----------+----------+
| @userSum | @userAvg |
+----------+----------+
|    67.00 |     6.09 |
+----------+----------+
1 row in set (0.00 sec)

4.3 刪除存儲過程

一條語句： drop procedure product;   //沒有括號后面

4.4 一段完整的存儲過程實(shí)例

-- Name: drdertotal 
-- Parameters : onumber = order number 
--       taxable = 0 if not taxable,1if taxable 
--       ototal = order total variable 
  
create procedure ordertotal( 
in onumber int, 
in taxable boolean, 
out ototal decimal(8,2) 
) commit 'Obtain order total, optionally adding tax' 
begin
  -- Declare variable for total 
  declare total decimal(8,2); 
  -- Declare tax percentage 
  declare taxrate int default 6; 
    
  --Get the order total 
  select Sum(item_price*quantity) 
  from orderitems 
  where order_num = onumber 
  into total; 
    
  --Is this taxable? 
  if taxable then
    --Yes, so add taxrate to the total 
    select total+(total/100*taxrate) into total; 
  end if; 
    
  --Add finally, save to out variable 
  select total into ototal; 
end;

上面存儲過程類似于高級語言的業(yè)務(wù)處理，看懂還是不難的垛孔，注意寫法細(xì)節(jié)
commit關(guān)鍵字：它不是必需的藕甩，但如果給出，將在show procedure status的結(jié)果中給出。
if語句：這個(gè)例子給出了mysqlif語句的基本用法狭莱，if語句還支持elseif和else子句僵娃。
通過show procedure status可以列出所有的存儲過程的詳細(xì)列表，并且可以在后面加一個(gè)
like+指定過濾模式來進(jìn)行過濾腋妙。