什么是存儲過程
簡單的說，就是一組SQL語句集，功能強大，可以實現一些比較復雜的邏輯功能，類似于JAVA語言中的方法；
ps:存儲過程跟觸發器有點類似，都是一組SQL集，但是存儲過程是主動調用的，且功能比觸發器更加強大，觸發器是某件事觸發后自動調用；
有哪些特性
有輸入輸出參數，可以聲明變量，有if/else, case,while等控制語句，通過編寫存儲過程，可以實現復雜的邏輯功能；
函數的普遍特性：模塊化，封裝，代碼復用；
速度快，只有首次執行需經過編譯和優化步驟，后續被調用可以直接執行，省去以上步驟；
MySQL存儲過程的創建
語法
CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body
CREATE PROCEDURE 過程名([[IN|OUT|INOUT] 參數名 數據類型[,[IN|OUT|INOUT] 參數名 數據類型…]]) [特性 ...] 過程體
DELIMITER // CREATE PROCEDURE myproc(OUT s int) BEGIN SELECT COUNT(*) INTO s FROM students; END //DELIMITER ;
分隔符
MySQL默認以";"為分隔符，如果沒有聲明分割符，則編譯器會把存儲過程當成SQL語句進行處理，因此編譯過程會報錯，所以要事先用“DELIMITER //”聲明當前段分隔符，讓編譯器把兩個"//"之間的內容當做存儲過程的代碼，不會執行這些代碼；“DELIMITER ;”的意為把分隔符還原。
參數
存儲過程根據需要可能會有輸入、輸出、輸入輸出參數，如果有多個參數用","分割開。MySQL存儲過程的參數用在存儲過程的定義，共有三種參數類型,IN,OUT,INOUT:
其中，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'：注釋信息。
技巧：創建存儲過程時，系統默認指定CONTAINS SQL，表示存儲過程中使用了SQL語句。但是，如果存儲過程中沒有使用SQL語句，最好設置為NO SQL。而且，存儲過程中最好在COMMENT部分對存儲過程進行簡單的注釋，以便以后在閱讀存儲過程的代碼時更加方便。
【示例1】 下面創建一個名為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
上述代碼中，存儲過程名稱為num_from_employee；輸入變量為emp_id；輸出變量為count_num。SELECT語句從employee表查詢d_id值等于emp_id的記錄，并用COUNT(*)計算d_id值相同的記錄的條數，最后將計算結果存入count_num中。代碼的執行結果如下：
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 ;
代碼執行完畢后，沒有報出任何出錯信息就表示存儲函數已經創建成功。以后就可以調用這個存儲過程，數據庫中會執行存儲過程中的SQL語句。
說明：MySQL中默認的語句結束符為分號（;）。存儲過程中的SQL語句需要分號來 結束。為了避免沖突，首先用"DELIMITER &&"將MySQL的結束符設置為&&。最后再用"DELIMITER ;"來將結束符恢復成分號。這與創建觸發器時是一樣的。
函數
在MySQL中，創建存儲函數的基本形式如下：
CREATE FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body
其中，sp_name參數是存儲函數的名稱；func_parameter表示存儲函數的參數列表；RETURNS type指定返回值的類型；characteristic參數指定存儲函數的特性，該參數的取值與存儲過程中的取值是一樣的，請讀者參照14.1.1小節的內容；routine_body參數是SQL代碼的內容，可以用BEGIN…END來標志SQL代碼的開始和結束。
func_parameter可以由多個參數組成，其中每個參數由參數名稱和參數類型組成，其形式如下：param_name type
其中，param_name參數是存儲函數的參數名稱；type參數指定存儲函數的參數類型，該類型可以是MySQL數據庫的任意數據類型。
【示例2】 下面創建一個名為name_from_employee的存儲函數。代碼如下：
CREATE FUNCTION name_from_employee (emp_id INT ) RETURNS VARCHAR(20) BEGIN RETURN (SELECT name FROM employee WHERE num=emp_id ); END
上述代碼中，存儲函數的名稱為name_from_employee；該函數的參數為emp_id；返回值是VARCHAR類型。SELECT語句從employee表查詢num值等于emp_id的記錄，并將該記錄的name字段的值返回。代碼的執行結果如下：
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 ;
結果顯示，存儲函數已經創建成功。該函數的使用和MySQL內部函數的使用方法一樣。
變量的使用
在存儲過程和函數中，可以定義和使用變量。用戶可以使用DECLARE關鍵字來定義變量。然后可以為變量賦值。這些變量的作用范圍是BEGIN…END程序段中。本小節將講解如何定義變量和為變量賦值。
1．定義變量
MySQL中可以使用DECLARE關鍵字來定義變量。定義變量的基本語法如下：
DECLARE var_name[,...] type [DEFAULT value]
其中， DECLARE關鍵字是用來聲明變量的；var_name參數是變量的名稱，這里可以同時定義多個變量；type參數用來指定變量的類型；DEFAULT value子句將變量默認值設置為value，沒有使用DEFAULT子句時，默認值為NULL。
【示例3】 下面定義變量my_sql，數據類型為INT型，默認值為10。代碼如下：
DECLARE my_sql INT DEFAULT 10 ;
2．為變量賦值
MySQL中可以使用SET關鍵字來為變量賦值。SET語句的基本語法如下：
SET var_name = expr [, var_name = expr] ...
其中，SET關鍵字是用來為變量賦值的；var_name參數是變量的名稱；expr參數是賦值表達式。一個SET語句可以同時為多個變量賦值，各個變量的賦值語句之間用逗號隔開。
【示例4】 下面為變量my_sql賦值為30。代碼如下：
SET my_sql = 30 ;
MySQL中還可以使用SELECT…INTO語句為變量賦值。其基本語法如下：
SELECT col_name[,…] INTO var_name[,…] FROM table_name WEHRE condition
其中，col_name參數表示查詢的字段名稱；var_name參數是變量的名稱；table_name參數指表的名稱；condition參數指查詢條件。
【示例5】 下面從employee表中查詢id為2的記錄，將該記錄的d_id值賦給變量my_sql。代碼如下：
SELECT d_id INTO my_sql FROM employee WEHRE id=2 ;
定義條件和處理程序
定義條件和處理程序是事先定義程序執行過程中可能遇到的問題。并且可以在處理程序中定義解決這些問題的辦法。這種方式可以提前預測可能出現的問題，并提出解決辦法。這樣可以增強程序處理問題的能力，避免程序異常停止。MySQL中都是通過DECLARE關鍵字來定義條件和處理程序。本小節中將詳細講解如何定義條件和處理程序。
1．定義條件
MySQL中可以使用DECLARE關鍵字來定義條件。其基本語法如下：
DECLARE condition_name CONDITION FOR condition_value condition_value: SQLSTATE [VALUE] sqlstate_value | mysql_error_code
其中，condition_name參數表示條件的名稱；condition_value參數表示條件的類型；sqlstate_value參數和mysql_error_code參數都可以表示MySQL的錯誤。例如ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。
【示例6】 下面定義"ERROR 1146 (42S02)"這個錯誤，名稱為can_not_find?？梢杂脙煞N不同的方法來定義，代碼如下：
//方法一：使用sqlstate_value DECLARE can_not_find CONDITION FOR SQLSTATE '42S02' ; //方法二：使用mysql_error_code DECLARE can_not_find CONDITION FOR 1146 ;
2．定義處理程序
MySQL中可以使用DECLARE關鍵字來定義處理程序。其基本語法如下：
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
其中，handler_type參數指明錯誤的處理方式，該參數有3個取值。這3個取值分別是CONTINUE、EXIT和UNDO。CONTINUE表示遇到錯誤不進行處理，繼續向下執行；EXIT表示遇到錯誤后馬上退出；UNDO表示遇到錯誤后撤回之前的操作，MySQL中暫時還不支持這種處理方式。
注意：通常情況下，執行過程中遇到錯誤應該立刻停止執行下面的語句，并且撤回前面的操作。但是，MySQL中現在還不能支持UNDO操作。因此，遇到錯誤時最好執行EXIT操作。如果事先能夠預測錯誤類型，并且進行相應的處理，那么可以執行CONTINUE操作。
condition_value參數指明錯誤類型，該參數有6個取值。sqlstate_value和mysql_error_code與條件定義中的是同一個意思。condition_name是DECLARE定義的條件名稱。SQLWARNING表示所有以01開頭的sqlstate_value值。NOT FOUND表示所有以02開頭的sqlstate_value值。SQLEXCEPTION表示所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值。sp_statement表示一些存儲過程或函數的執行語句。
【示例7】 下面是定義處理程序的幾種方式。代碼如下：
//方法一：捕獲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'; //方法三：先定義條件，然后調用 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';
上述代碼是6種定義處理程序的方法。
第一種方法是捕獲sqlstate_value值。如果遇到sqlstate_value值為42S02，執行CONTINUE操作，并且輸出"CAN NOT FIND"信息。
第二種方法是捕獲mysql_error_code值。如果遇到mysql_error_code值為1146，執行CONTINUE操作，并且輸出"CAN NOT FIND"信息。
第三種方法是先定義條件，然后再調用條件。這里先定義can_not_find條件，遇到1146錯誤就執行CONTINUE操作。
第四種方法是使用SQLWARNING。SQLWARNING捕獲所有以01開頭的sqlstate_value值，然后執行EXIT操作，并且輸出"ERROR"信息。
第五種方法是使用NOT FOUND。NOT FOUND捕獲所有以02開頭的sqlstate_value值，然后執行EXIT操作，并且輸出"CAN NOT FIND"信息。
第六種方法是使用SQLEXCEPTION。SQLEXCEPTION捕獲所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值，然后執行EXIT操作，并且輸出"ERROR"信息。
MySQL存儲過程寫法總結
1、創建無參存儲過程。
create procedure product()begin select * from user;end;
一條簡單的存儲過程創建語句，此時調用的語句為：
call procedure();
##注意，如果是在命令行下編寫的話，這樣的寫法會出現語法錯誤，即再select 那一句結束
mysql就會進行解釋了，此時應該先把結尾符換一下：
delimiter //create procedure product()begin select * from user;end //
最后再換回來
delimiter ;
2、創建有參存儲過程
有參的存儲包括兩種參數，
一個是傳入參數;
一個是傳出參數;
例如一個存儲過程：
create procedure procedure2(out p1 decimal(8,2),out p2 decimal(8,2),in p3 int)beginselect sum(uid) into p1 from user where order_name = p3;select avg(uid) into p2 from user ;end ;
從上面sql語句可以看出，p1和p2是用來檢索并且傳出去的值，而p3則是必須有調用這傳入的具體值。
看具體調用過程：
call product(); //無參
call procedure2(@userSum,@userAvg,201708); //有參
當用完后，可以直接查詢userSum和userAvg的值：
select @userSum, @userAvg;
結果如下：
+----------+----------+
| @userSum | @userAvg |
+----------+----------+
| 67.00 | 6.09 |
+----------+----------+
1 row in set (0.00 sec)
3、刪除存儲過程
一條語句： drop procedure product; //沒有括號后面
4、一段完整的存儲過程實例：
-- 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;
上面存儲過程類似于高級語言的業務處理，看懂還是不難的，注意寫法細節
commit關鍵字：它不是必需的，但如果給出，將在show procedure status的結果中給出。
if語句：這個例子給出了mysqlif語句的基本用法，if語句還支持elseif和else子句。
通過show procedure status可以列出所有的存儲過程的詳細列表，并且可以在后面加一個
like+指定過濾模式來進行過濾。
新聞熱點
疑難解答
