MySQL存儲過程，mysql存儲過程實例

MySQL存儲過程，mysql存儲過程實例

http://www.cnblogs.com/exmyth/p/3303470.html 

14.1.1 創建存儲過程

MySQL中，創建存儲過程的基本形式如下：

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

其中，sp_name參數是存儲過程的名稱；proc_parameter表示存儲過程的參數列表； characteristic參數指定存儲過程的特性；routine_body參數是SQL代碼的內容，可以用BEGIN…END來標志SQL代碼的開始和結束。

proc_parameter中的每個參數由3部分組成。這3部分分別是輸入輸出類型、參數名稱和參數類型。其形式如下：

1. [ 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部分對存儲過程進行簡單的注釋，以便以後在閱讀存儲過程的代碼時更加方便。

【示例14-1】 下面創建一個名為num_from_employee的存儲過程。代碼如下：

1. CREATE  PROCEDURE  num_from_employee (IN emp_id INT, OUT count_num INT )  
2.           READS SQL DATA  
3.           BEGIN  
4.               SELECT  COUNT(*)  INTO  count_num  
5.               FROM  employee  
6.               WHERE  d_id=emp_id ;  
7.           END 

上述代碼中，存儲過程名稱為num_from_employee；輸入變量為emp_id；輸出變量為count_num。SELECT語句從employee表查詢d_id值等於emp_id的記錄，並用COUNT(*)計算d_id值相同的記錄的條數，最後將計算結果存入count_num中。代碼的執行結果如下：

1. mysql> DELIMITER &&  
2. mysql> CREATE  PROCEDURE  num_from_employee
   (IN emp_id INT, OUT count_num INT )  
3.     -> READS SQL DATA  
4.     -> BEGIN  
5.     -> SELECT  COUNT(*)  INTO  count_num  
6.     -> FROM  employee  
7.     -> WHERE  d_id=emp_id ;  
8.     -> END &&  
9. Query OK, 0 rows affected (0.09 sec)  
10. mysql> DELIMITER ; 

代碼執行完畢後，沒有報出任何出錯信息就表示存儲函數已經創建成功。以後就可以調用這個存儲過程，數據庫中會執行存儲過程中的SQL語句。

說明：MySQL中默認的語句結束符為分號（;）。存儲過程中的SQL語句需要分號來    結束。為了避免沖突，首先用"DELIMITER &&"將MySQL的結束符設置為&&。最後再用"DELIMITER ;"來將結束符恢復成分號。這與創建觸發器時是一樣的。

 

 

 

14.1.2 創建存儲函數

在MySQL中，創建存儲函數的基本形式如下：

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

其中，sp_name參數是存儲函數的名稱；func_parameter表示存儲函數的參數列表；RETURNS type指定返回值的類型；characteristic參數指定存儲函數的特性，該參數的取值與存儲過程中的取值是一樣的，請讀者參照14.1.1小節的內容；routine_body參數是SQL代碼的內容，可以用BEGIN…END來標志SQL代碼的開始和結束。

func_parameter可以由多個參數組成，其中每個參數由參數名稱和參數類型組成，其形式如下：

1. param_name type 

其中，param_name參數是存儲函數的參數名稱；type參數指定存儲函數的參數類型，該類型可以是MySQL數據庫的任意數據類型。

【示例14-2】 下面創建一個名為name_from_employee的存儲函數。代碼如下：

1. CREATE  FUNCTION  name_from_employee (emp_id INT )  
2.           RETURNS VARCHAR(20)  
3.           BEGIN  
4.               RETURN  (SELECT  name  
5.               FROM  employee  
6.               WHERE  num=emp_id );  
7.           END 

上述代碼中，存儲函數的名稱為name_from_employee；該函數的參數為emp_id；返回值是VARCHAR類型。SELECT語句從employee表查詢num值等於emp_id的記錄，並將該記錄的name字段的值返回。代碼的執行結果如下：

1. mysql> DELIMITER &&  
2. mysql> CREATE  FUNCTION  name_from_employee (emp_id INT )  
3.     -> RETURNS VARCHAR(20)  
4.     -> BEGIN  
5.     -> RETURN  (SELECT  name  
6.     -> FROM  employee  
7.     -> WHERE  num=emp_id );  
8.     -> END&&  
9. Query OK, 0 rows affected (0.00 sec)  
10. mysql> DELIMITER ; 

結果顯示，存儲函數已經創建成功。該函數的使用和MySQL內部函數的使用方法一樣。

 

 

 

14.1.3  變量的使用

在存儲過程和函數中，可以定義和使用變量。用戶可以使用DECLARE關鍵字來定義變量。然後可以為變量賦值。這些變量的作用范圍是BEGIN…END程序段中。本小節將講解如何定義變量和為變量賦值。

1．定義變量

MySQL中可以使用DECLARE關鍵字來定義變量。定義變量的基本語法如下：

1. DECLARE  var_name[,...]  type  [DEFAULT value] 

其中， DECLARE關鍵字是用來聲明變量的；var_name參數是變量的名稱，這裡可以同時定義多個變量；type參數用來指定變量的類型；DEFAULT value子句將變量默認值設置為value，沒有使用DEFAULT子句時，默認值為NULL。

【示例14-3】 下面定義變量my_sql，數據類型為INT型，默認值為10。代碼如下：

1. DECLARE  my_sql  INT  DEFAULT 10 ; 

2．為變量賦值

MySQL中可以使用SET關鍵字來為變量賦值。SET語句的基本語法如下：

1. SET  var_name = expr [, var_name = expr] ... 

其中，SET關鍵字是用來為變量賦值的；var_name參數是變量的名稱；expr參數是賦值表達式。一個SET語句可以同時為多個變量賦值，各個變量的賦值語句之間用逗號隔開。

【示例14-4】 下面為變量my_sql賦值為30。代碼如下：

1. SET  my_sql = 30 ; 

MySQL中還可以使用SELECT…INTO語句為變量賦值。其基本語法如下：

1. SELECT  col_name[,…]  INTO  var_name[,…]  
2.     FROM  table_name  WEHRE  condition 

其中，col_name參數表示查詢的字段名稱；var_name參數是變量的名稱；table_name參數指表的名稱；condition參數指查詢條件。

【示例14-5】 下面從employee表中查詢id為2的記錄，將該記錄的d_id值賦給變量my_sql。代碼如下：

1. SELECT  d_id  INTO  my_sql  
2.         FROM  employee  WEHRE  id=2 ; 

 

 

14.1.4  定義條件和處理程序

定義條件和處理程序是事先定義程序執行過程中可能遇到的問題。並且可以在處理程序中定義解決這些問題的辦法。這種方式可以提前預測可能出現的問題，並提出解決辦法。這樣可以增強程序處理問題的能力，避免程序異常停止。MySQL中都是通過DECLARE關鍵字來定義條件和處理程序。本小節中將詳細講解如何定義條件和處理程序。

1．定義條件

MySQL中可以使用DECLARE關鍵字來定義條件。其基本語法如下：

1. DECLARE  condition_name  CONDITION  FOR  condition_value  
2. condition_value:  
3.       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。

【示例14-6】 下面定義"ERROR 1146 (42S02)"這個錯誤，名稱為can_not_find。可以用兩種不同的方法來定義，代碼如下：

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

2．定義處理程序

MySQL中可以使用DECLARE關鍵字來定義處理程序。其基本語法如下：

1. DECLARE handler_type HANDLER FOR 
   condition_value[,...] sp_statement  
2. handler_type:  
3.     CONTINUE | EXIT | UNDO  
4. condition_value:  
5.     SQLSTATE [VALUE] sqlstate_value |
   condition_name  | SQLWARNING  
6.        | 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表示一些存儲過程或函數的執行語句。

【示例14-7】 下面是定義處理程序的幾種方式。代碼如下：

1. //方法一：捕獲sqlstate_value  
2. DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
   SET @info='CAN NOT FIND';  
3. //方法二：捕獲mysql_error_code  
4. DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND';  
5. //方法三：先定義條件，然後調用  
6. DECLARE  can_not_find  CONDITION  FOR  1146 ;  
7. DECLARE CONTINUE HANDLER FOR can_not_find SET 
   @info='CAN NOT FIND';  
8. //方法四：使用SQLWARNING  
9. DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';  
10. //方法五：使用NOT FOUND  
11. DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';  
12. //方法六：使用SQLEXCEPTION  
13. 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"信息。

==================================================== http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html

mysql存儲過程詳解

1.      存儲過程簡介

 

我們常用的操作數據庫語言SQL語句在執行的時候需要要先編譯，然後執行，而存儲過程（Stored Procedure）是一組為了完成特定功能的SQL語句集，經編譯後存儲在數據庫中，用戶通過指定存儲過程的名字並給定參數（如果該存儲過程帶有參數）來調用執行它。

一個存儲過程是一個可編程的函數，它在數據庫中創建並保存。它可以有SQL語句和一些特殊的控制結構組成。當希望在不同的應用程序或平台上執行相同的函數，或者封裝特定功能時，存儲過程是非常有用的。數據庫中的存儲過程可以看做是對編程中面向對象方法的模擬。它允許控制數據的訪問方式。

存儲過程通常有以下優點：

(1).存儲過程增強了SQL語言的功能和靈活性。存儲過程可以用流控制語句編寫，有很強的靈活性，可以完成復雜的判斷和較復雜的運算。

(2).存儲過程允許標准組件是編程。存儲過程被創建後，可以在程序中被多次調用，而不必重新編寫該存儲過程的SQL語句。而且數據庫專業人員可以隨時對存儲過程進行修改，對應用程序源代碼毫無影響。

(3).存儲過程能實現較快的執行速度。如果某一操作包含大量的Transaction-SQL代碼或分別被多次執行，那麼存儲過程要比批處理的執行速度快很多。因為存儲過程是預編譯的。在首次運行一個存儲過程時查詢，優化器對其進行分析優化，並且給出最終被存儲在系統表中的執行計劃。而批處理的Transaction-SQL語句在每次運行時都要進行編譯和優化，速度相對要慢一些。

(4).存儲過程能過減少網絡流量。針對同一個數據庫對象的操作（如查詢、修改），如果這一操作所涉及的Transaction-SQL語句被組織程存儲過程，那麼當在客戶計算機上調用該存儲過程時，網絡中傳送的只是該調用語句，從而大大增加了網絡流量並降低了網絡負載。

(5).存儲過程可被作為一種安全機制來充分利用。系統管理員通過執行某一存儲過程的權限進行限制，能夠實現對相應的數據的訪問權限的限制，避免了非授權用戶對數據的訪問，保證了數據的安全。

 

2.      關於MySQL的存儲過程

存儲過程是數據庫存儲的一個重要的功能，但是MySQL在5.0以前並不支持存儲過程，這使得MySQL在應用上大打折扣。好在MySQL 5.0終於開始已經支持存儲過程，這樣即可以大大提高數據庫的處理速度，同時也可以提高數據庫編程的靈活性。

3.      MySQL存儲過程的創建

 

(1). 格式

MySQL存儲過程創建的格式：CREATE PROCEDURE 過程名 ([過程參數[,...]])
[特性 ...] 過程體

這裡先舉個例子：
   

注：

（1）這裡需要注意的是DELIMITER //和DELIMITER ;兩句，DELIMITER是分割符的意思，因為MySQL默認以";"為分隔符，如果我們沒有聲明分割符，那麼編譯器會把存儲過程當成SQL語句進行處理，則存儲過程的編譯過程會報錯，所以要事先用DELIMITER關鍵字申明當前段分隔符，這樣MySQL才會將";"當做存儲過程中的代碼，不會執行這些代碼，用完了之後要把分隔符還原。

（2）存儲過程根據需要可能會有輸入、輸出、輸入輸出參數，這裡有一個輸出參數s，類型是int型，如果有多個參數用","分割開。

（3）過程體的開始與結束使用BEGIN與END進行標識。

這樣，我們的一個MySQL存儲過程就完成了，是不是很容易呢?看不懂也沒關系，接下來，我們詳細的講解。