共計 8926 個字符，預計需要花費 23 分鐘才能閱讀完成。

這篇文章將為大家詳細講解有關 MYSQL 中存儲過程和函數怎么寫，丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

什么是存儲過程

簡單的說，就是一組 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:

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
begin
select 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+ 指定過濾模式來進行過濾。

關于“MYSQL 中存儲過程和函數怎么寫”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。