一、什么是存儲過程？

1.概念理解：

存儲過程是一組為了完成特定功能的SQL語句集。通過組成SQL語句和控制語句，提供一種封裝任務(wù)的方法。因此在創(chuàng)建編譯好某個存儲過程后，因為存儲過程中有可執(zhí)行操作的sql語句，用戶可以根據(jù)需求，調(diào)用該過程時輸入?yún)?shù)即可執(zhí)行。簡單一點理解，也就是相當(dāng)于我們在JAVA里面寫的代碼一樣，封裝好 某個類、方法，這樣在需要這個方法的時候就去調(diào)用其，就不用再重新又寫、反復(fù)寫。

2.存儲過程的優(yōu)點：

（1）模塊化的程序設(shè)計

（2）在服務(wù)器端運(yùn)行，具有高效率的執(zhí)行力

（3）減少網(wǎng)絡(luò)流量，存儲過程在編譯后，也就是要在執(zhí)行一次之后，它的執(zhí)行規(guī)劃就會保留在高速緩沖存儲器中，用戶在后期調(diào)用該存儲過程時，后臺便只需從高速緩沖存儲器中調(diào)用編譯好的二進(jìn)制代碼，提高了系統(tǒng)性能

（4）確保數(shù)據(jù)庫的安全，防止了用戶暴露數(shù)據(jù)庫表的細(xì)節(jié)，可以作為安全機(jī)制使用 ?

3.存儲過程的分類：

• 系統(tǒng)存儲過程
• 用戶自定義存儲過程

二、Mysql語句創(chuàng)建、執(zhí)行和刪除存儲過程

1.創(chuàng)建存儲過程

創(chuàng)建時需要事先確定存儲過程的三個組成部分：

（1）.所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。

（2）.被執(zhí)行的針對數(shù)據(jù)庫的操作語句，包括調(diào)用其他存儲過程的語句。

（3）.返回給調(diào)用者的狀態(tài)值以指明調(diào)用是成功還是失敗。

（1）?創(chuàng)建語法格式：

CREATE PROCEDURE 存儲過程名 ([參數(shù) ... ])
[特征 ...]  存儲過程體 

a.參數(shù)=：[ IN | OUT | INOUT ] 參數(shù)名 參數(shù)類型

• 參數(shù)的命名不要與所聯(lián)系的數(shù)據(jù)表的列名出現(xiàn)相同的
• 有多個參數(shù)的時候，要用逗號隔開

IN類型——輸入?yún)?shù)	可以使數(shù)據(jù)傳遞給存儲過程
OUT類型——輸出參數(shù)	當(dāng)需要返回一個結(jié)果時使用
INOUT類型——輸入/輸出參數(shù)	兩者都可以充當(dāng)

b.特征=：LANGUAGE SQL ?| [NOT] DETERMINISTIC? | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ?| SQL SECURITY { DEFINER | INVOKER } ?| COMMENT 'string'

特征	對應(yīng)內(nèi)容
LANGUAGE SQL	存儲過程的語言為SQL
[NOT] DETERMINISTIC	存儲過程是否確定性，即存儲過程對同樣的輸入?yún)?shù)產(chǎn)生的結(jié)果是否相同
CONTAINS SQL	[默認(rèn)選項] 存儲過程的子程序包含SQL語句，但是不包含讀、寫數(shù)據(jù)的語句
NO SQL	存儲過程中不包含SQL語句
READS SQL DATA	存儲過程只讀取SQL數(shù)據(jù)
MODIFIES SQL DATA	存儲過程只修改SQL數(shù)據(jù)
SQL SECURITY { DEFINER | INVOKER }	存儲過程執(zhí)行的身份者指定 DEFINER：默認(rèn)。創(chuàng)建該存儲過程的用戶許可 INVOKER：使用存儲過程的用戶許可
COMMENT 'string'	存儲過程的描述備注，string是描述的內(nèi)容。使用SHOW CREATE PROCEDURE 就可以顯示該信息

c.存儲過程體：存儲過程的主體部分，包含了調(diào)用存儲過程時必會執(zhí)行的SQL語句。

• 開始標(biāo)志是BEGIN，結(jié)束標(biāo)志是END，只有 一條SQL語句時可以省略開始和結(jié)束標(biāo)志；
• 還需要注意的一個點是，因為存儲過程里面的SQL語句是以分號結(jié)尾的，所以服務(wù)器在處理程序的時候遇到第一個分號就會以為要結(jié)束程序了，所以需要我們使用“Delimiter 結(jié)束符號”命令將Mysql語句的結(jié)束標(biāo)志更改一下，編譯后再恢復(fù)分號結(jié)束標(biāo)志。

2.修改存儲過程特征

MySQL只能通過ALTER語句修改存儲過程的特征，不能修改存儲過程體的內(nèi)容，如需修改存儲過程體的內(nèi)容，需要先刪除存儲過程再重新創(chuàng)建

3.查看存儲過程

show procedure status [like 'pattern']; 

其中，like 'pattern'為可選參數(shù)，用來匹配存儲過程的名稱，如果不指定該參數(shù)，則會查看所有的存儲過程。

4.調(diào)用執(zhí)行存儲過程

call sp_name[(傳參)]; ? ?

其中，sp_name為所執(zhí)行的存儲過程名稱，傳參表示根據(jù)存儲過程定義時的參數(shù)進(jìn)行傳參。

5.刪除存儲過程

drop procedure [if exists] 存儲過程名;

三、異常處理

(1)MySQL定義異常捕獲類型及處理方法的語法如下：

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

a.HANDLER? :異常關(guān)鍵詞

b.FOR：聲明

c.statement:表示出現(xiàn)某種條件、錯誤的時候需要執(zhí)行的語句

• 可以是簡單的一句SQL語句
• 可以是復(fù)雜的多行語句——這里就需要用起始標(biāo)簽Begin和結(jié)束標(biāo)簽End

d.hander_action：異常類型，表示執(zhí)行完statement后希望系統(tǒng)執(zhí)行什么動作

• CONTINUE | EXIT ?| UNDO ? ? ? ?
• continue：程序繼續(xù)——SQL WARNING和NO FOUND 的默認(rèn)處理方法
• exit：跳出程序——SQLEXCEPTION的默認(rèn)處理方法
• undo：程序回滾，撤銷

d.condition_value：表示一個異常處理可以定義成針對多種情況進(jìn)行相應(yīng)的操作

condition_value	內(nèi)容
mysql_error_code	MySQL錯誤碼，一個由mysql自定義的數(shù)字
SQLSTATE[VALUE] sqlstate_value	SQL狀態(tài)碼，一個由五個字符組成的字符串
condition_name	條件名稱，使用declare...condition語句定義
SQLWARNING	SQL警告，表示SQLSTATE中字符串以‘01’起始的錯誤
NOT FOUND	找不到，表示SQLSTATE中字符串以‘02’起始的錯誤
SQLEXCEPTION	SQL異常，表示SQLSTATE中字符串不以‘00’，‘01’，‘02’起始的錯誤 其中，‘00’是表示成功執(zhí)行。

四、例題演練

1.創(chuàng)建一個存儲過程p_yg1:

實現(xiàn)根據(jù)傳入?yún)?shù)部門名稱可以查詢各部門所有員工的員工編號，員工姓名和職務(wù)。并調(diào)用此存儲過程查詢“技術(shù)”部門員工的員工編號，員工姓名和職務(wù)

delimiter //
create? procedure p_yg1( IN?departmentName varchar(30))
begin
select? ygxx.ygbh,ygxx.name,ygxx.zw from ygxx 
inner join? bmxx on ygxx.ssbmbh=bmxx.bmbh? where bmxx.bmmc=departmentName;
end //
delimiter;

（1）創(chuàng)建存儲過程p_yg1?

?（2）調(diào)用存儲過程p_yg1

2.創(chuàng)建一存儲過程p_intsp1:

• 通過帶參數(shù)的存儲過程向表spxx中插入一條數(shù)據(jù)，傳入?yún)?shù)為spbh，spmc，sslb，jg，sl
• 如果插入主鍵重復(fù)數(shù)據(jù)（錯誤號1062），則將spbh和spmc插入錯誤記錄表splog中
• 數(shù)據(jù)插入時間賦為當(dāng)前日期，操作標(biāo)志位賦上'insert'。

DELIMITER //
CREATE PROCEDURE p_intsp1 (IN spbh VARCHAR(20), IN spmc VARCHAR(30), IN sslb VARCHAR(20), IN jg DOUBLE, IN sl INT)
BEGINDECLARE t_error INTEGER DEFAULT 0;DECLARE CONTINUE HANDLER FOR 1062 SET t_error = 1;INSERT INTO spxx (spbh, spmc,sslb, jg, sl) VALUES (spbh, spmc,sslb, jg, sl);IF t_error = 1 THENINSERT INTO splog (spbjlog, spmclog, sjlog, bz) VALUES (spbh, spmc, NOW(), 'insert');ELSE COMMIT;END IF;END //
DELIMITER ;

（1）創(chuàng)建存儲過程p_intsp1

（2）調(diào)用存儲過程

a.展示當(dāng)前的商品信息表和記錄表

b.插入一條數(shù)據(jù)，重復(fù)了主鍵id

c.此時商品信息表沒有新數(shù)據(jù)插入，記錄log表插入新數(shù)據(jù)