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內部函數的使用方法一樣。