在MYSQL 5中,終于引入了存儲過程這一新特性,這將大大增強(qiáng)MYSQL 的數(shù)據(jù)庫處理能力,在本文中,將指導(dǎo)讀者快速掌握MYSQL 5的存儲過程的基本知識,帶領(lǐng)用戶入門。

存儲過程介紹

存儲過程是一組為了完成特定功能的SQL語句集，經(jīng)編譯后存儲在數(shù)據(jù)庫中。用戶通過指定存儲過程的名字并給出參數(shù)(如果該存儲過程帶有參數(shù))來執(zhí)行它。存儲過程可由應(yīng)用程序通過一個調(diào)用來執(zhí)行，而且允許用戶聲明變量 。同時，存儲過程可以接收和輸出參數(shù)、返回執(zhí)行存儲過程的狀態(tài)值，也可以嵌套調(diào)用。

存儲過程的特點

作為存儲過程，有以下這些優(yōu)點：

(1)減少網(wǎng)絡(luò)通信量。調(diào)用一個行數(shù)不多的存儲過程與直接調(diào)用SQL語句的網(wǎng)絡(luò)通信量可能不會有很大的差別，可是如果存儲過程包含上百行SQL語句，那么其性能絕對比一條一條的調(diào)用SQL語句要高得多。

(2)執(zhí)行速度更快。存儲過程創(chuàng)建的時候，數(shù)據(jù)庫已經(jīng)對其進(jìn)行了一次解析和優(yōu)化。其次，存儲過程一旦執(zhí)行，在內(nèi)存中就會保留一份這個存儲過程，這樣下次再執(zhí)行同樣的存儲過程時，可以從內(nèi)存中直接中讀取。

(3)更強(qiáng)的安全性。存儲過程是通過向用戶授予權(quán)限(而不是基于表)，它們可以提供對特定數(shù)據(jù)的訪問，提高代碼安全，比如防止 SQL注入。

(4) 業(yè)務(wù)邏輯可以封裝存儲過程中，這樣不僅容易維護(hù)，而且執(zhí)行效率也高

當(dāng)然存儲過程也有一些缺點，比如：

1 可移植性方面：當(dāng)從一種數(shù)據(jù)庫遷移到另外一種數(shù)據(jù)庫時，不少的存儲過程的編寫要進(jìn)行部分修改。

2 存儲過程需要花費(fèi)一定的學(xué)習(xí)時間去學(xué)習(xí),比如學(xué)習(xí)其語法等。

在MYSQL中，推薦使用MYSQL Query Browswer(http://dev.mysql.com/doc/query-browser/en/)這個工具去進(jìn)行存儲過程的開發(fā)和管理。

下面分步驟來學(xué)習(xí)MYSQL中的存儲過程。

1 定義存儲過程的結(jié)束符

在存儲過程中，通常要輸入很多SQL語句，而SQL語句中每個語句以分號來結(jié)束，因此要告訴存儲過程，什么位置是意味著整個存儲過程結(jié)束，所以我們在編寫存儲過程前，先定義分隔符，我們這里定義“//”為分隔符，我們使用DELIMITER //這樣的語法，就可以定義結(jié)束符了，當(dāng)然你可以自己定義其他喜歡的符號。

2 如何創(chuàng)建存儲過程

下面先看下一個簡單的例子，代碼如下：

DELIMITER //  
CREATE PROCEDURE `p2` ()  
LANGUAGE SQL  
DETERMINISTIC  
SQL SECURITY DEFINER  
COMMENT 'A procedure' 
BEGIN 
    SELECT 'Hello World !';  
END// 

下面講解下存儲過程的組成部分：

　　1)首先在定義好終結(jié)符后，使用CREATE PROCEDURE+存儲過程名的方法創(chuàng)建存儲過程，LANGUAGE選項指定了使用的語言，這里默認(rèn)是使用SQL。

　　2)DETERMINISTIC關(guān)鍵詞的作用是，當(dāng)確定每次的存儲過程的輸入和輸出都是相同的內(nèi)容時，可以使用該關(guān)鍵詞，否則默認(rèn)為NOT DETERMINISTIC。

　　3) SQL SECURITY關(guān)鍵詞，是表示調(diào)用時檢查用戶的權(quán)限。當(dāng)值為INVOKER時，表示是用戶調(diào)用該存儲過程時檢查，默認(rèn)為DEFINER,即創(chuàng)建存儲過程時檢查。

　　4) COMMENT部分是存儲過程的注釋說明部分。

　　5)在BEGIN END部分中，是存儲過程的主體部分。

3 調(diào)用存儲過程的方法

調(diào)用存儲過程的方法很簡單，只需要使用call命令即可，后面跟要調(diào)用存儲過程的名稱及輸入的變量列表，比如：

CALL stored_procedure_name (param1, param2, ....)  
CALL procedure1(10 , 'string parameter' , @parameter_var); 

4 修改和刪除存儲過程

可以用ALTER的語法去修改存儲過程的主要特征和參數(shù)，要修改其存儲過程的主體部分的話，必須要先刪除然后再重建。比如下面修改存儲過程num_from_employee的定義。將讀寫權(quán)限改為MODIFIES SQL DATA，并指明調(diào)用者可以執(zhí)行。代碼執(zhí)行如下：

ALTER PROCEDURE num_from_employee   
MODIFIES SQL DATA SQL SECURITY INVOKER ; 

而刪除存儲過程的語法為使用DROP關(guān)鍵詞即可。如下

DROP PROCEDURE IF EXISTS p2; 

#p#

5 存儲過程的參數(shù)

下面來學(xué)習(xí)下存儲過程中的參數(shù)，先看下存儲過程中的參數(shù)形式，如下：

CREATE PROCEDURE proc1 ()-----這個存儲過程中是空的參數(shù)列表  
 
CREATE PROCEDURE proc1 (IN varname DATA-TYPE)-----這個存儲過程中有一個輸出參數(shù)，名稱為varname，后面是跟數(shù)據(jù)類型DATA-TYPE,IN參數(shù)是默認(rèn)的，因此可以省略不寫  
 
CREATE PROCEDURE proc1 (OUT varname DATA-TYPE)-----這個存儲過程中varname為輸出參數(shù)  
 
CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE)-----這個存儲過程中，varname既是輸入?yún)?shù)也是輸出參數(shù) 

下面具體看個例子，首先是IN輸入?yún)?shù)的例子，如下：

　　DELIMITER //  
　　CREATE PROCEDURE `proc_IN` (IN var1 INT)  
　　BEGIN 
　　SELECT var1 + 2 AS result;  
　　END// 

輸出OUT參數(shù)例子如下：

　　DELIMITER //  
　　CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))  
　　BEGIN 
　　SET var1 = 'This is a test';  
　　END // 

IN-OUT的例子：

　　DELIMITER //  
　　CREATE PROCEDURE `proc_INOUT` (OUT var1 INT)  
　　BEGIN 
　　SET var1 = var1 * 2;  
　　END // 

6 如何定義變量

下面講解下MYSQL 5存儲過程中，如何定義變量。

必須顯式地在存儲過程的一開始聲明變量，并指出它們的數(shù)據(jù)類型，一但聲明了變量后，就可以在存儲過程中使用，定義變量的語法如下：

DECLARE varname DATA-TYPE DEFAULT defaultvalue 

舉例說明：

　　DECLARE a, b INT DEFAULT 5;  
　　DECLARE str VARCHAR(50);  
　　DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;  
　　DECLARE v1, v2, v3 TINYINT; 

一旦定義好變量，就可以在存儲過程中對其進(jìn)行賦初值,并進(jìn)行各類相關(guān)的操作，比如：

　　DELIMITER //  
　　CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20))  
　　BEGIN 
　　DECLARE a, b INT DEFAULT 5;  
　　DECLARE str VARCHAR(50);  
　　DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;  
　　DECLARE v1, v2, v3 TINYINT;  
　　INSERT INTO table1 VALUES (a);  
　　SET str = 'I am a string';  
　　SELECT CONCAT(str,paramstr), today FROM table2 WHERE b>=5;  
　　END // 

#p#

7 MYSQL存儲過程的語法結(jié)構(gòu)

MYSQL存儲過程中支持IF，CASE，ITERATE，LEAVE LOOP，WHILE和REPEAT等語法結(jié)構(gòu)和語句，在本文中，著重介紹IF,CASE和WHILE語法，因為它們使用的最為廣泛。

IF 語句

if語句使用的是if…then end if的語法結(jié)構(gòu)，例子如下：

　　DELIMITER //  
　　CREATE PROCEDURE `proc_IF` (IN param1 INT)  
　　BEGIN 
　　DECLARE variable1 INT;  
　　SET variable1 = param1 + 1;  
　　IF variable1 = 0 THEN 
　　SELECT variable1;  
　　END IF;  
　　IF param1 = 0 THEN 
　　SELECT 'Parameter value = 0';  
　　ELSE 
　　SELECT 'Parameter value <= 0';  
　　END IF;  
　　END // 

CASE語句

當(dāng)有很多IF語句時，就應(yīng)該考慮使用CASE語句了，它是多分支選擇語句，有兩種寫法：

第一種寫法：

　　DELIMITER //  
　　CREATE PROCEDURE `proc_CASE` (IN param1 INT)  
　　BEGIN 
　　DECLARE variable1 INT;  
　　SET variable1 = param1 + 1;  
　　CASE variable1  
　　WHEN 0 THEN 
　　INSERT INTO table1 VALUES (param1);  
　　WHEN 1 THEN 
　　INSERT INTO table1 VALUES (variable1);  
　　ELSE 
　　INSERT INTO table1 VALUES (99);  
　　END CASE;  
　　END // 

另外一種寫法：

　　DELIMITER //  
　　CREATE PROCEDURE `proc_CASE` (IN param1 INT)  
　　BEGIN 
　　DECLARE variable1 INT;  
　　SET variable1 = param1 + 1;  
　　CASE 
　　WHEN variable1 = 0 THEN 
　　INSERT INTO table1 VALUES (param1);  
　　WHEN variable1 = 1 THEN 
　　INSERT INTO table1 VALUES (variable1);  
　　ELSE 
　　INSERT INTO table1 VALUES (99);  
　　END CASE;  
　　END // 

WHILE語句

WHILE語句跟普通編程語言中的while語句差不多，例子如下：

　　DELIMITER //  
　　CREATE PROCEDURE `proc_WHILE` (IN param1 INT)  
　　BEGIN 
　　DECLARE variable1, variable2 INT;  
　　SET variable1 = 0;  
　　WHILE variable1   
　　INSERT INTO table1 VALUES (param1);  
　　SELECT COUNT(*) INTO variable2 FROM table1;  
　　SET variable1 = variable1 + 1;  
　　END WHILE;  
　　END // 

8 MYSQL存儲過程中的游標(biāo)

MYSQL中的游標(biāo)是一個十分重要的概念。游標(biāo)提供了一種對從表中檢索出的數(shù)據(jù)進(jìn)行操作的靈活手段，就本質(zhì)而言，游標(biāo)實際上是一種能從包括多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機(jī)制。MYSQL中的游標(biāo)的語法如下：

　　DECLARE cursor-name CURSOR FOR SELECT ...; /* 聲明一個游標(biāo)，名稱為cursor-name,并用CURSOR FOR SELECT*/  
　　DECLARE CONTINUE HANDLER FOR NOT FOUND /*指定當(dāng)遍歷完結(jié)果集后，游標(biāo)如何繼續(xù)處理*/  
　　OPEN cursor-name; /*打開游標(biāo) */  
　　FETCH cursor-name INTO variable [, variable]; /* 將變量賦值給游標(biāo)*/  
　　CLOSE cursor-name; /*使用后關(guān)閉游標(biāo)*/ 

一個具體的例子如下：

　　DELIMITER //  
　　CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT)  
　　BEGIN 
　　DECLARE a, b, c INT;  
　　DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;  
　　DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;  
　　OPEN cur1;  
　　SET b = 0;  
　　SET c = 0;  
　　WHILE b = 0 DO  
　　FETCH cur1 INTO a;  
　　IF b = 0 THEN 
　　SET c = c + a;  
　　END IF;  
　　END WHILE;  
　　CLOSE cur1;  
　　SET param1 = c;  
　　END // 

其中，DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;

表示將從table1表中選取col1列的內(nèi)容放到游標(biāo)curl中，即每次游標(biāo)遍歷的結(jié)果都放在curl中，要注意游標(biāo)只能向前遍歷，而不能向后，并且注意，游標(biāo)不能更新，最后關(guān)閉游標(biāo)。

【編輯推薦】

1. MySQL中創(chuàng)建及優(yōu)化索引組織結(jié)構(gòu)的思路
2. 微博 請問你是怎么優(yōu)化數(shù)據(jù)庫的？
3. MySQL技巧:結(jié)合相關(guān)參數(shù) 做好Limit優(yōu)化
4. MySQL數(shù)據(jù)庫的優(yōu)化（下）MySQL數(shù)據(jù)庫的高可用架構(gòu)方案
5. MySQL數(shù)據(jù)庫的優(yōu)化（上）單機(jī)MySQL數(shù)據(jù)庫的優(yōu)化