MYSQL中存儲過(guò)程和函數怎么寫(xiě)

這篇文章將為大家詳細講解有關(guān)中存儲過(guò)程和函數怎么寫(xiě)，小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

什么是存儲過(guò)程

簡(jiǎn)單的說(shuō)，就是一組SQL語(yǔ)句集，功能強大，可以實(shí)現一些比較復雜的邏輯功能，類(lèi)似于JAVA語(yǔ)言中的方法；

ps:存儲過(guò)程跟觸發(fā)器有點(diǎn)類(lèi)似，都是一組SQL集，但是存儲過(guò)程是主動(dòng)調用的，且功能比觸發(fā)器更加強大，觸發(fā)器是某件事觸發(fā)后自動(dòng)調用；

有哪些特性

有輸入輸出參數，可以聲明變量，有if/else, case,while等控制語(yǔ)句，通過(guò)編寫(xiě)存儲過(guò)程，可以實(shí)現復雜的邏輯功能；

函數的普遍特性：模塊化，封裝，代碼復用；

速度快，只有首次執行需經(jīng)過(guò)編譯和優(yōu)化步驟，后續被調用可以直接執行，省去以上步驟；

MySQL存儲過(guò)程的創(chuàng )建

語(yǔ)法

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

CREATE PROCEDURE  過(guò)程名([[IN|OUT|INOUT] 參數名 數據類(lèi)型[,[IN|OUT|INOUT] 參數名 數據類(lèi)型…]]) [特性 ...] 過(guò)程體

DELIMITER //
 CREATE PROCEDURE myproc(OUT s int)
  BEGIN
   SELECT COUNT(*) INTO s FROM students;
  END
  //
DELIMITER ;

分隔符

MySQL默認以";"為分隔符，如果沒(méi)有聲明分割符，則編譯器會(huì )把存儲過(guò)程當成SQL語(yǔ)句進(jìn)行處理，因此編譯過(guò)程會(huì )報錯，所以要事先用“DELIMITER //”聲明當前段分隔符，讓編譯器把兩個(gè)"//"之間的內容當做存儲過(guò)程的代碼，不會(huì )執行這些代碼；“DELIMITER ;”的意為把分隔符還原。

參數

存儲過(guò)程根據需要可能會(huì )有輸入、輸出、輸入輸出參數，如果有多個(gè)參數用","分割開(kāi)。MySQL存儲過(guò)程的參數用在存儲過(guò)程的定義，共有三種參數類(lèi)型,IN,OUT,INOUT:

• IN參數的值必須在調用存儲過(guò)程時(shí)指定，在存儲過(guò)程中修改該參數的值不能被返回，為默認值

• OUT:該值可在存儲過(guò)程內部被改變，并可返回

• INOUT:調用時(shí)指定，并且可被改變和返回

其中，sp_name參數是存儲過(guò)程的名稱(chēng)；proc_parameter表示存儲過(guò)程的參數列表； characteristic參數指定存儲過(guò)程的特性；routine_body參數是SQL代碼的內容，可以用BEGIN…END來(lái)標志SQL代碼的開(kāi)始和結束。

　　proc_parameter中的每個(gè)參數由3部分組成。這3部分分別是輸入輸出類(lèi)型、參數名稱(chēng)和參數類(lèi)型。其形式如下：

　　[ IN | OUT | INOUT ] param_name type

　　其中，IN表示輸入參數；OUT表示輸出參數； INOUT表示既可以是輸入，也可以是輸出； param_name參數是存儲過(guò)程的參數名稱(chēng)；type參數指定存儲過(guò)程的參數類(lèi)型，該類(lèi)型可以是MySQL數據庫的任意數據類(lèi)型。

　　characteristic參數有多個(gè)取值。其取值說(shuō)明如下：

　　LANGUAGE SQL：說(shuō)明routine_body部分是由SQL語(yǔ)言的語(yǔ)句組成，這也是數據庫系統默認的語(yǔ)言。

　　[NOT] DETERMINISTIC：指明存儲過(guò)程的執行結果是否是確定的。DETERMINISTIC表示結果是確定的。每次執行存儲過(guò)程時(shí)，相同的輸入會(huì )得到相同的輸出。NOT DETERMINISTIC表示結果是非確定的，相同的輸入可能得到不同的輸出。默認情況下，結果是非確定的。

　　{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL語(yǔ)句的限制。CONTAINS SQL表示子程序包含SQL語(yǔ)句，但不包含讀或寫(xiě)數據的語(yǔ)句；NO SQL表示子程序中不包含SQL語(yǔ)句；READS SQL DATA表示子程序中包含讀數據的語(yǔ)句；MODIFIES SQL DATA表示子程序中包含寫(xiě)數據的語(yǔ)句。默認情況下，系統會(huì )指定為CONTAINS SQL。

　　SQL SECURITY { DEFINER | INVOKER }：指明誰(shuí)有權限來(lái)執行。DEFINER表示只有定義者自己才能夠執行；INVOKER表示調用者可以執行。默認情況下，系統指定的權限是DEFINER。

　　COMMENT 'string'：注釋信息。

　　技巧：創(chuàng )建存儲過(guò)程時(shí)，系統默認指定CONTAINS SQL，表示存儲過(guò)程中使用了SQL語(yǔ)句。但是，如果存儲過(guò)程中沒(méi)有使用SQL語(yǔ)句，最好設置為NO SQL。而且，存儲過(guò)程中最好在COMMENT部分對存儲過(guò)程進(jìn)行簡(jiǎn)單的注釋?zhuān)员阋院笤陂喿x存儲過(guò)程的代碼時(shí)更加方便。

【示例1】 下面創(chuàng )建一個(gè)名為num_from_employee的存儲過(guò)程。代碼如下：

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

上述代碼中，存儲過(guò)程名稱(chēng)為num_from_employee；輸入變量為emp_id；輸出變量為count_num。SELECT語(yǔ)句從employee表查詢(xún)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 ;

代碼執行完畢后，沒(méi)有報出任何出錯信息就表示存儲函數已經(jīng)創(chuàng )建成功。以后就可以調用這個(gè)存儲過(guò)程，數據庫中會(huì )執行存儲過(guò)程中的SQL語(yǔ)句。

　　說(shuō)明：MySQL中默認的語(yǔ)句結束符為分號（;）。存儲過(guò)程中的SQL語(yǔ)句需要分號來(lái) 結束。為了避免沖突，首先用"DELIMITER &&"將MySQL的結束符設置為&&。最后再用"DELIMITER ;"來(lái)將結束符恢復成分號。這與創(chuàng )建觸發(fā)器時(shí)是一樣的。

函數

　　在MySQL中，創(chuàng )建存儲函數的基本形式如下：

CREATE FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body
　　其中，sp_name參數是存儲函數的名稱(chēng)；func_parameter表示存儲函數的參數列表；RETURNS type指定返回值的類(lèi)型；characteristic參數指定存儲函數的特性，該參數的取值與存儲過(guò)程中的取值是一樣的，請讀者參照14.1.1小節的內容；routine_body參數是SQL代碼的內容，可以用BEGIN…END來(lái)標志SQL代碼的開(kāi)始和結束。

　　func_parameter可以由多個(gè)參數組成，其中每個(gè)參數由參數名稱(chēng)和參數類(lèi)型組成，其形式如下：param_name type

　　其中，param_name參數是存儲函數的參數名稱(chēng)；type參數指定存儲函數的參數類(lèi)型，該類(lèi)型可以是MySQL數據庫的任意數據類(lèi)型。

【示例2】 下面創(chuàng )建一個(gè)名為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

上述代碼中，存儲函數的名稱(chēng)為name_from_employee；該函數的參數為emp_id；返回值是VARCHAR類(lèi)型。SELECT語(yǔ)句從employee表查詢(xún)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 ;

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

變量的使用

在存儲過(guò)程和函數中，可以定義和使用變量。用戶(hù)可以使用DECLARE關(guān)鍵字來(lái)定義變量。然后可以為變量賦值。這些變量的作用范圍是BEGIN…END程序段中。本小節將講解如何定義變量和為變量賦值。

1．定義變量

　　MySQL中可以使用DECLARE關(guān)鍵字來(lái)定義變量。定義變量的基本語(yǔ)法如下：

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

　　其中， DECLARE關(guān)鍵字是用來(lái)聲明變量的；var_name參數是變量的名稱(chēng)，這里可以同時(shí)定義多個(gè)變量；type參數用來(lái)指定變量的類(lèi)型；DEFAULT value子句將變量默認值設置為value，沒(méi)有使用DEFAULT子句時(shí)，默認值為NULL。

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

DECLARE my_sql INT DEFAULT 10 ;

2．為變量賦值

　　MySQL中可以使用SET關(guān)鍵字來(lái)為變量賦值。SET語(yǔ)句的基本語(yǔ)法如下：

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

　　其中，SET關(guān)鍵字是用來(lái)為變量賦值的；var_name參數是變量的名稱(chēng)；expr參數是賦值表達式。一個(gè)SET語(yǔ)句可以同時(shí)為多個(gè)變量賦值，各個(gè)變量的賦值語(yǔ)句之間用逗號隔開(kāi)。

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

SET my_sql = 30 ;

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

SELECT col_name[,…] INTO var_name[,…] FROM table_name WEHRE condition
　　其中，col_name參數表示查詢(xún)的字段名稱(chēng)；var_name參數是變量的名稱(chēng)；table_name參數指表的名稱(chēng)；condition參數指查詢(xún)條件。

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

SELECT d_id INTO my_sql FROM employee WEHRE id=2 ;

定義條件和處理程序

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

1．定義條件

　　MySQL中可以使用DECLARE關(guān)鍵字來(lái)定義條件。其基本語(yǔ)法如下：

DECLARE condition_name CONDITION FOR condition_value 
condition_value: 
   SQLSTATE [VALUE] sqlstate_value | mysql_error_code

其中，condition_name參數表示條件的名稱(chēng)；condition_value參數表示條件的類(lèi)型；sqlstate_value參數和mysql_error_code參數都可以表示MySQL的錯誤。例如ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。

【示例6】 下面定義"ERROR 1146 (42S02)"這個(gè)錯誤，名稱(chēng)為can_not_find?？梢杂脙煞N不同的方法來(lái)定義，代碼如下：

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

2．定義處理程序

MySQL中可以使用DECLARE關(guān)鍵字來(lái)定義處理程序。其基本語(yǔ)法如下：

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個(gè)取值。這3個(gè)取值分別是CONTINUE、EXIT和UNDO。CONTINUE表示遇到錯誤不進(jìn)行處理，繼續向下執行；EXIT表示遇到錯誤后馬上退出；UNDO表示遇到錯誤后撤回之前的操作，MySQL中暫時(shí)還不支持這種處理方式。

　　注意：通常情況下，執行過(guò)程中遇到錯誤應該立刻停止執行下面的語(yǔ)句，并且撤回前面的操作。但是，MySQL中現在還不能支持UNDO操作。因此，遇到錯誤時(shí)最好執行EXIT操作。如果事先能夠預測錯誤類(lèi)型，并且進(jìn)行相應的處理，那么可以執行CONTINUE操作。

　　condition_value參數指明錯誤類(lèi)型，該參數有6個(gè)取值。sqlstate_value和mysql_error_code與條件定義中的是同一個(gè)意思。condition_name是DECLARE定義的條件名稱(chēng)。SQLWARNING表示所有以01開(kāi)頭的sqlstate_value值。NOT FOUND表示所有以02開(kāi)頭的sqlstate_value值。SQLEXCEPTION表示所有沒(méi)有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值。sp_statement表示一些存儲過(guò)程或函數的執行語(yǔ)句。

【示例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開(kāi)頭的sqlstate_value值，然后執行EXIT操作，并且輸出"ERROR"信息。

　　第五種方法是使用NOT FOUND。NOT FOUND捕獲所有以02開(kāi)頭的sqlstate_value值，然后執行EXIT操作，并且輸出"CAN NOT FIND"信息。

　　第六種方法是使用SQLEXCEPTION。SQLEXCEPTION捕獲所有沒(méi)有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值，然后執行EXIT操作，并且輸出"ERROR"信息。

MySQL存儲過(guò)程寫(xiě)法總結

1、創(chuàng )建無(wú)參存儲過(guò)程。

create procedure product()
begin
    select * from user;
end;

一條簡(jiǎn)單的存儲過(guò)程創(chuàng )建語(yǔ)句，此時(shí)調用的語(yǔ)句為：

call procedure();

##注意，如果是在命令行下編寫(xiě)的話(huà)，這樣的寫(xiě)法會(huì )出現語(yǔ)法錯誤，即再select 那一句結束
mysql就會(huì )進(jìn)行解釋了，此時(shí)應該先把結尾符換一下：

delimiter //
create procedure product()
begin
    select * from user;
end //

最后再換回來(lái)

delimiter ;

2、創(chuàng )建有參存儲過(guò)程

有參的存儲包括兩種參數，
一個(gè)是傳入參數;
一個(gè)是傳出參數;
例如一個(gè)存儲過(guò)程：

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語(yǔ)句可以看出，p1和p2是用來(lái)檢索并且傳出去的值，而p3則是必須有調用這傳入的具體值。
看具體調用過(guò)程：

call product();    //無(wú)參
call procedure2(@userSum,@userAvg,201708);    //有參

當用完后，可以直接查詢(xún)userSum和userAvg的值：

select @userSum, @userAvg;

結果如下：

+----------+----------+
| @userSum | @userAvg |
+----------+----------+
|    67.00 |     6.09 |
+----------+----------+
1 row in set (0.00 sec)

3、刪除存儲過(guò)程

一條語(yǔ)句： drop procedure product;   //沒(méi)有括號后面

4、一段完整的存儲過(guò)程實(shí)例：

-- 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;

上面存儲過(guò)程類(lèi)似于高級語(yǔ)言的業(yè)務(wù)處理，看懂還是不難的，注意寫(xiě)法細節

commit關(guān)鍵字：它不是必需的，但如果給出，將在show procedure status的結果中給出。

if語(yǔ)句：這個(gè)例子給出了mysqlif語(yǔ)句的基本用法，if語(yǔ)句還支持elseif和else子句。

通過(guò)show procedure status可以列出所有的存儲過(guò)程的詳細列表，并且可以在后面加一個(gè)

like+指定過(guò)濾模式來(lái)進(jìn)行過(guò)濾。