簡單地說，存儲過程就是一條或者多條SQL語句的集合，可視為批文件，但是其作用不僅限于批處理。本篇主要介紹如何創(chuàng)建存儲過程和存儲函數(shù)以及變量的使用，如何調(diào)用、查看、修改、刪除存儲過程和存儲函數(shù)等。

存儲程序可以分為存儲過程和函數(shù)，MySQL 中創(chuàng)建存儲過程和函數(shù)使用的語句分別是：CREATE PROCEDURE和CREATE FUNCTION。使用CALL語句來調(diào)用存儲過程，只能用輸出變量返回值。函數(shù)可以從語句外調(diào)用（即通過引用函數(shù)名），也能返回標量值。存儲過程也可以調(diào)用其他存儲過程。

創(chuàng)建存儲過程

創(chuàng)建存儲過程，需要使用CREATE PROCEDURE語句，基本語法格式如下：

CREATE PROCEDURE sp name （ ［ proc parameter］）

［ characteristics…］ routine_ body

CREATE PROCEDURE為用來創(chuàng)建存儲函數(shù)的關(guān)鍵字： sp_ _name為存儲過程的名稱；proc_ parameter 為指定存儲過程的參數(shù)列表，列表形式如下：

［N1 OUT 1 INOUT］ param name type

其中，IN表示輸入?yún)?shù)，OUT表示輸出參數(shù)，INOUT表示既可以輸入也可以輸出；param_ name表示參數(shù)名稱； type 表示參數(shù)的類型，該類型可以是MySQL數(shù)據(jù)庫中的任意類型。

characteristics指定存儲過程的特性，有以下取值：

LANGUAGESQL：說明routine_body部分是由SQL語句組成的，當(dāng)前系統(tǒng)支持的語言為SQL，SQL是LANGUAGE特性的唯一值。

［NOT］ DETERMINISTIC：指明存儲過程執(zhí)行的結(jié)果是否確定。DETERMINISTIC 表示。結(jié)果是確定的。每次執(zhí)行存儲過程時，相同的輸入會得到相同的輸出。NOTDETERMINISTIC表示結(jié)果是不確定的，相同的輸入可能得到不同的輸出。如果沒有指定任意一個值，默認為NOT DETERMINISTIC.

{ CONTAINS SQL |NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL語句的限制。CONTAINS SQL表明子程序包含SQL語句，但是不包含讀寫數(shù)據(jù)的語句； NO SQL表明子程序不包含SQL語句； READS SQL DATA說明子程序包含讀數(shù)據(jù)的語句； MODIFIES SQL DATA表明子程序包含寫數(shù)據(jù)的語句。默認情況下，系統(tǒng)會指定為CONTAINS SQL。

SQL SECURITY { DEFINER |INVOKER }：指明誰有權(quán)限來執(zhí)行。DEFINER表示只有定義者才能執(zhí)行。INVOKER表示擁有權(quán)限的調(diào)用者可以執(zhí)行。默認情況下，系統(tǒng)指定為DEFINER。

COMMENT ‘string’： 注釋信息，可以用來描述存儲過程或函數(shù)。

routine_ body是SQL代碼的內(nèi)容，可以用BEGIN.END來表示SQL代碼的開始和結(jié)束。編寫存儲過程并不是件簡單的事情，可能存儲過程中需要復(fù)雜的SQL語句，并且要有創(chuàng)建存儲過程的權(quán)限；但是使用存儲過程將簡化操作，減少冗余的操作步驟，同時，還可以減少操作過程中的失誤，提高效率，因此存儲過程是非常有用的，而且應(yīng)該盡可能地學(xué)會使用。

下面的代碼演示了存儲過程的內(nèi)容，名稱為AvgFruitPrice，返回所有水果的平均價格，輸入代碼如下：

CREATE PROCEDURE AvgFruitPrice （）

BEGIN

SELECT AVG（f price） AS avgprice

FROM fruits;

END；

上述代碼中，此存儲過程名為AvgFruitPrice， 使用CREATE PROCEDURE AvgFruitPrice 0語句定義。此存儲過程沒有參數(shù)，但是后面的0仍然需要。BEGIN和END語句用來限定存儲過程體，過程本身僅是一個簡單的SELECT語句（AVG為求字段平均值的函數(shù)）。

創(chuàng)建查看fruits 表的存儲過程，代碼如下：

CREATE PROCEDURE Proc（）

BEGIN

SELECT * FROM fruits；

END；

這行代碼創(chuàng)建了一個查看fruits表的存儲過程，每次調(diào)用這個存儲過程的時候都會執(zhí)行SELECT語句查看表的內(nèi)容，代碼的執(zhí)行過程如下：

這個存儲過程和使用SELECT語句查看表的效果得到的結(jié)果是一樣的，當(dāng)然存儲過程也可以是很多語句的復(fù)雜組合，其本身也可以調(diào)用其他的函數(shù)來組成更加復(fù)雜的操作。

“DELIMITER //”語句的作用是將MySQL的結(jié)束符設(shè)置為//，因為MySQL默認的語句結(jié)束符號為分號;’，為了避免與存儲過程中SQL語句結(jié)束符相沖突，需要使用DELIMITER改變存儲過程的結(jié)束符，并以“END//“ 結(jié)束存儲過程。存儲過程定義完畢之后再使用“DELIMITER;”” 恢復(fù)默認結(jié)束符。DELIMITER 也可以指定其他符號做為結(jié)束符。

創(chuàng)建名稱為CountProc的存儲過程，代碼如下：

CREATE PROCEDURE CountProc （OUT paraml INT）

BEGIN

SELECT COUNT（*） INTO paraml FROM fruits；

END；

上述代碼的作用是創(chuàng)建-一個獲取fruits 表記錄條數(shù)的存儲過程，名稱是CountProc，COUNT（*）計算后把結(jié)果放入?yún)?shù)paraml中。代碼的執(zhí)行結(jié)果如下：

當(dāng)使用DELIMITER命令時，應(yīng)該避免使用反斜杠（“‘ ）字符，因為反斜線是MySQL的轉(zhuǎn)義字符。

創(chuàng)建存儲函數(shù)

創(chuàng)建存儲函數(shù)，需要使用CREATE FUNCTION語句，基本語法格式如下：

CREATE FUNCTION func name （ ［func_ parameter］ ）

RETURNS type

［characteristic 。..］ routine_ body

CREATE FUNCTION為用來創(chuàng)建存儲函數(shù)的關(guān)鍵字; func_ _name 表示存儲函數(shù)的名稱;func_ parameter 為存儲過程的參數(shù)列表，參數(shù)列表形式如下：

［ INIOUT 1 INOUT ］ param name type

其中，IN表示輸入?yún)?shù)，OUT表示輸出參數(shù)，INOUT表示既可以輸入也可以輸出；param_ name表示參數(shù)名稱; type 表示參數(shù)的類型，該類型可以是MySQL數(shù)據(jù)庫中的任意類型。

RETURNS type 語句表示函數(shù)返回數(shù)據(jù)的類型; characteristic 指定存儲函數(shù)的特性，取值與創(chuàng)建存儲過程時相同，這里不再贅述。

創(chuàng)建存儲函數(shù)，名稱為NameByZip，該函數(shù)返回SELECT語句的查詢結(jié)果，數(shù)值類型為字符串型，代碼如下：

CREATE FUNCTION NameByZip （）

RETURNS CHAR（50）

RETURN （SELECT s_ name FROM suppliers WHERE S call= 148075’） ;

創(chuàng)建一個存儲函數(shù)， 參數(shù)定義為空，返回-一個INT類型的結(jié)果。代碼的執(zhí)行結(jié)果如下：

mysq1》 DELIMITER //

mysql》 CREATE FUNCTION NameByZip （）

-》 RETURNS CHAR（50）

-》 RETURN 設(shè)（SELECT s name FROM suppliers WHERE 8 call= 1480755）;

-》 //

Query OK，0 rows affected （0.12 sec）

mysq1》 DELIMITER ;

如果在存儲函數(shù)中的RETURN語句返回一個類型不同于函數(shù)的RETURNS子句中指定類型的值，返回值將被強制為恰當(dāng)?shù)念愋?。比如，如?一個函數(shù)返回-一個ENUM或SET值，但是RETURN語句返回一個整數(shù)，對于SET成員集的相應(yīng)的ENUM成員，從函數(shù)返回的值是字符串。

指定參數(shù)為IN、ouT或INOUT只對PROCEDURE是合法的。（FUNCTION 中總是默認為IN參數(shù)）。RETURNS子句只能對FUNCTION做指定，對函數(shù)而言這是強制的。它用來指定函數(shù)的返回類型，而且函數(shù)體必領(lǐng)包含一個RETURN value語句。

變量的使用

變量可以在子程序中聲明并使用，這些變量的作用范圍是在BEGIN..END程序中，本小節(jié)主要介紹如何定義變量和為變量賦值。

1、定義變量

在存儲過程中使用DECLARE語句定義變量，語法格式如下：

DECLARE var name ［， varname］。 date type ［ DEFAULT value］ ;

var_ name為局部變量的名稱。DEFAULT value子句給變量提供一個默認值。 值除了可以被聲明為一個常數(shù)之外，還可以被指定為一個表達式。如果沒有DEFAULT 子句，初始值為NULL.

定義名稱為myparam的變量，類型為INT類型，默認值為100，代碼如下：

DECLARE myparam INT DEFAULT 100;

2、為變量賦值

定義變量之后為變量賦值可以改變變量的默認值MySQL中使用SET語句為變量賦值，語法格式如下：

SET var_ name來expr［，var_ name = expr］ 。..。

在存儲程序中的SET語句是一-般SET語句的擴展版本。被參考變量可能是子程序內(nèi)聲明的變量，或者是全局服務(wù)器變量，如系統(tǒng)變量或者用戶變量。

在存儲程序中的SET語句作為預(yù)先存在的SET語法的一部分來實現(xiàn)。這允許SET a=x，b=y， 。這樣的擴展語法。其中不同的變量類型（局域聲明變量及全局變量）可以被混合起來。這也允許把局部變量和--些只對系統(tǒng)變量有意義的選項合并起來。

聲明3個變量，分別為varl、var2 和var3，數(shù)據(jù)類型為INT，使用SET為變量賦值，代碼如下：

DECLARE var1， var2， var3 INT;

SET var1 =10， var2 = 20;

SET var3 =varl+var2;

MySQL中還可以通過SELECT 。.. INTO為-一個或多個變量賦值，語法如下：

SELECT col name［，。..］ INTO var name［， 。..］ table_ expr;

這個SELECT語法把選定的列直接存儲到對應(yīng)位置的變量。col name 表示字段名稱;var_ name表示定義的變量名稱table_ _expr 表示查詢條件表達式包括表名稱和WHERE子句。

聲明變量fruitname 和fruitprice， 通過SELECT 。.. INtO語句查詢指定記錄并為變量賦值，代碼如下：

DECLARE fruitname CHAR（50） ;

DECLARE fruitprice DECIMAL（8，2） ;

SELECT f_name， f_price INTO fruitname， fruitprice

FROM fruits WHEREf id =‘a(chǎn)l’ ;

以上就是小編整理的MySQL的創(chuàng)建存儲過程，只是小編的個人理解，有哪里不準確的地方，還請大家多多指出，小編和大家一起共同進步~~~