實(shí)戰(zhàn)MySql存儲(chǔ)過(guò)程

一、存儲(chǔ)過(guò)程基本用法

1、創(chuàng)建存儲(chǔ)過(guò)程

MySQL中，創(chuàng)建存儲(chǔ)過(guò)程的基本形式如下：

CREATE?PROCEDURE??存儲(chǔ)過(guò)程名 (參數(shù)列表)
BEGIN
????SQL語(yǔ)句代碼塊
END

其中參數(shù)列表的形式如下：

[IN|OUT|INOUT]?param_name type

其中in表示輸入?yún)?shù)，out表示輸出參數(shù)，inout表示既可以輸入也可以輸出；param_name表示參數(shù)名稱；type表示參數(shù)的類型，該類型可以是MYSQL數(shù)據(jù)庫(kù)中的任意類型。

例子：下面的語(yǔ)句創(chuàng)建一個(gè)查詢tb_user表全部數(shù)據(jù)的存儲(chǔ)過(guò)程

DROP?PROCEDURE?IF?EXISTS?sp_test;

DELIMITER //
CREATE?PROCEDURE?sp_test()
BEGIN
????SELECT?* FROM?tb_user;
END?//
DELIMITER ;

注意：

（1）由括號(hào)包圍的參數(shù)列必須總是存在。如果沒(méi)有參數(shù)，也該使用一個(gè)空參數(shù)列()。每個(gè)參數(shù)默認(rèn)都是一個(gè)IN參數(shù)。要指定為其它參數(shù)，可在參數(shù)名之前使用關(guān)鍵詞 OUT或INOUT

（2）"DELIMITER //" 語(yǔ)句的作用是將MYSQL的結(jié)束符設(shè)置為//，因?yàn)镸YSQL默認(rèn)的語(yǔ)句結(jié)束符為分號(hào); ，存儲(chǔ)過(guò)程中的SQL語(yǔ)句需要分號(hào)來(lái)結(jié)束，為了避免與存儲(chǔ)過(guò)程中SQL語(yǔ)句結(jié)束符相沖突，需要使用DELIMITER 改變存儲(chǔ)過(guò)程的結(jié)束符，并以"END //"結(jié)束存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程定義完畢之后再使用DELIMITER ;恢復(fù)默認(rèn)結(jié)束符。DELIMITER 也可以指定其他符號(hào)為結(jié)束符。注意：當(dāng)使用DELIMITER命令時(shí)，應(yīng)該避免使用反斜杠（\）字符，因?yàn)榉葱备苁荕YSQL的轉(zhuǎn)義字符！??！

DELIMITER 是分割符的意思，其實(shí)就是定義了一個(gè)語(yǔ)句執(zhí)行的結(jié)束符，類似函數(shù)or存儲(chǔ)過(guò)程這樣的create語(yǔ)句由于其中包含了很多的";"，而默認(rèn)MySQL的結(jié)束符就是";"，那么當(dāng)我們創(chuàng)建的時(shí)候就會(huì)報(bào)錯(cuò)，有了DELIMITER 就可以告訴mysql解釋器，該段命令是否已經(jīng)結(jié)束了，mysql是否可以執(zhí)行了。?

默認(rèn)情況下，delimiter是分號(hào)";"。在命令行客戶端中，如果有一行命令以分號(hào)結(jié)束，?那么回車后，mysql將會(huì)執(zhí)行該命令。如輸入下面的語(yǔ)句?

mysql> select?* from?stu;

然后回車，那么MySQL將立即執(zhí)行該語(yǔ)句。但有時(shí)候，不希望MySQL這么做。因?yàn)榭赡茌斎胼^多的語(yǔ)句，且語(yǔ)句中包含有分號(hào)。?默認(rèn)情況下，不可能等到用戶把這些語(yǔ)句全部輸入完之后，再執(zhí)行整段語(yǔ)句。因?yàn)閙ysql一遇到分號(hào)，它就要自動(dòng)執(zhí)行。?即，在語(yǔ)句之后為";"時(shí)，mysql解釋器就要執(zhí)行了。?這種情況下，就需要事先把delimiter換成其它符號(hào)，如//或$$等其他符號(hào)。?這樣只有當(dāng)$$出現(xiàn)之后，mysql解釋器才會(huì)執(zhí)行這段語(yǔ)句?。記得最后一個(gè)要將結(jié)束符修改回";"。

2、刪除存儲(chǔ)過(guò)程

語(yǔ)法：

DROP?PROCEDURE??IF??EXISTS??存儲(chǔ)過(guò)程名;

eg

DROP?PROCEDURE?IF?EXISTS?proc_employee;

這個(gè)語(yǔ)句被用來(lái)移除一個(gè)存儲(chǔ)程序。不能在一個(gè)存儲(chǔ)過(guò)程中刪除另一個(gè)存儲(chǔ)過(guò)程，只能調(diào)用另一個(gè)存儲(chǔ)過(guò)程。

3、調(diào)用存儲(chǔ)過(guò)程

語(yǔ)法：

CALL 存儲(chǔ)過(guò)程名(參數(shù)列表);

注：

（1）CALL語(yǔ)句是用來(lái)調(diào)用一個(gè)先前用CREATE PROCEDURE創(chuàng)建的存儲(chǔ)過(guò)程。
（2）CALL語(yǔ)句可以用聲明為OUT或INOUT參數(shù)的參數(shù)給它的調(diào)用者傳回值。
（3）存儲(chǔ)過(guò)程名稱后面必須加括號(hào)，哪怕該存儲(chǔ)過(guò)程沒(méi)有參數(shù)傳遞。

二、存儲(chǔ)過(guò)程體

存儲(chǔ)過(guò)程體中可以使用各種sql語(yǔ)句和過(guò)程式語(yǔ)句的組合，來(lái)封裝數(shù)據(jù)庫(kù)應(yīng)用中復(fù)雜的業(yè)務(wù)邏輯和處理規(guī)則，以實(shí)現(xiàn)數(shù)據(jù)庫(kù)應(yīng)用的靈活編程。下面主要介紹幾個(gè)用于構(gòu)造存儲(chǔ)過(guò)程體的常用語(yǔ)法元素。

1、變量的使用

在存儲(chǔ)過(guò)程和函數(shù)中，可以定義和使用變量。用戶可以使用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參數(shù)是變量的名稱，這里可以同時(shí)定義多個(gè)變量；type參數(shù)用來(lái)指定變量的類型；DEFAULT value子句將變量默認(rèn)值設(shè)置為value，沒(méi)有使用DEFAULT子句時(shí)，默認(rèn)值為NULL。

【示例】?下面定義變量my_sql，數(shù)據(jù)類型為INT型，默認(rèn)值為10。代碼如下：

DECLARE ?my_sql ?INT ?DEFAULT 10 ;

使用說(shuō)明：

局部變量只能在存儲(chǔ)過(guò)程體的begin…end語(yǔ)句塊中聲明。

局部變量必須在存儲(chǔ)過(guò)程體的開(kāi)頭處聲明。

局部變量的作用范圍僅限于聲明它的begin..end語(yǔ)句塊，其他語(yǔ)句塊中的語(yǔ)句不可以使用它。

局部變量不同于用戶變量，兩者區(qū)別：局部變量聲明時(shí)，在其前面沒(méi)有使用@符號(hào)，并且它只能在begin..end語(yǔ)句塊中使用；而用戶變量在聲明時(shí)，會(huì)在其名稱前面使用@符號(hào)，同時(shí)已聲明的用戶變量存在于整個(gè)會(huì)話之中。

（2）為變量賦值

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

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

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

【示例】下面為變量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參數(shù)表示查詢的字段名稱；var_name參數(shù)是變量的名稱；table_name參數(shù)指表的名稱；condition參數(shù)指查詢條件。

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

SELECT??d_id INTO??my_sql FROM??employee WEHRE id=2?;

?2、定義條件和處理程序

特定條件需要特定處理。這些條件可以聯(lián)系到錯(cuò)誤，以及子程序中的一般流程控制。定義條件是事先定義程序執(zhí)行過(guò)程中遇到的問(wèn)題。處理程序定義了在遇到這些問(wèn)題時(shí)候應(yīng)當(dāng)采取的處理方式，并且保證存儲(chǔ)過(guò)程或函數(shù)在遇到警告或錯(cuò)誤時(shí)能繼續(xù)執(zhí)行。這樣可以增強(qiáng)存儲(chǔ)程序處理問(wèn)題的能力，避免程序異常停止運(yùn)行。

（1）定義條件

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

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

condition_name：表示條件名稱

condition_type：表示條件的類型

sqlstate_value和mysql_error_code都可以表示mysql錯(cuò)誤，sqlstate_value為長(zhǎng)度5的字符串錯(cuò)誤代碼，mysql_error_code為數(shù)值類型錯(cuò)誤代碼，例如：ERROR1142(42000)中，sqlstate_value的值是42000，mysql_error_code的值是1142。

這個(gè)語(yǔ)句指定需要特殊處理?xiàng)l件。他將一個(gè)名字和指定的錯(cuò)誤條件關(guān)聯(lián)起來(lái)。這個(gè)名字隨后被用在定義處理程序的DECLARE HANDLER語(yǔ)句中。

【示例】定義ERROR1148(42000)錯(cuò)誤，名稱為command_not_allowed。

可以用兩種方法定義

//方法一：使用sqlstate_value
DECLARE?command_not_allowed CONDITION FOR?SQLSTATE?'42000'

//方法二：使用mysql_error_code
DECLARE?command_not_allowed CONDITION FOR?SQLSTATE?1148

（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參數(shù)指明錯(cuò)誤的處理方式，該參數(shù)有3個(gè)取值。這3個(gè)取值分別是CONTINUE、EXIT和UNDO。

CONTINUE表示遇到錯(cuò)誤不進(jìn)行處理，繼續(xù)向下執(zhí)行；

EXIT表示遇到錯(cuò)誤后馬上退出；

UNDO表示遇到錯(cuò)誤后撤回之前的操作，MySQL中暫時(shí)還不支持這種處理方式。

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

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

【示例】下面是定義處理程序的幾種方式。代碼如下：

//方法一：捕獲sqlstate_value
DECLARE?CONTINUE HANDLER?FOR?SQLSTATE?'42000'
SET?@info='CAN NOT FIND';
//方法二：捕獲mysql_error_code
DECLARE?CONTINUE HANDLER?FOR?1148SET?@info='CAN NOT FIND';
//方法三：先定義條件，然后調(diào)用
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值為42000，執(zhí)行CONTINUE操作，并且輸出"CAN NOT FIND"信息。

第二種方法是捕獲mysql_error_code值。如果遇到mysql_error_code值為1148，執(zhí)行CONTINUE操作，并且輸出"CAN NOT FIND"信息。

第三種方法是先定義條件，然后再調(diào)用條件。這里先定義can_not_find條件，遇到1148錯(cuò)誤就執(zhí)行CONTINUE操作。

第四種方法是使用SQLWARNING。SQLWARNING捕獲所有以01開(kāi)頭的sqlstate_value值，然后執(zhí)行EXIT操作，并且輸出"ERROR"信息。

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

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

【示例】定義條件和處理程序

CREATE?TABLE?t8(s1 INT,PRIMARY KEY(s1))

DELIMITER //
CREATE?PROCEDURE?handlerdemo()
BEGIN
DECLARE?CONTINUE HANDLER?FOR?SQLSTATE?'23000'?SET?@X2=1;
SET?@X=1;
INSERT?INTO?t8 VALUES(1);
SET?@X=2;
INSERT?INTO?t8 VALUES(1);
SET?@X=3;
END;
//
DELIMITER ;

/* 調(diào)用存儲(chǔ)過(guò)程*/
CALL?handlerdemo();

/* 查看調(diào)用存儲(chǔ)過(guò)程結(jié)果*/
SELECT?@X

@X是一個(gè)用戶變量，執(zhí)行結(jié)果@X等于3，這表明MYSQL執(zhí)行到程序的末尾。

如果DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @X2=1;，這一行不存在

第二個(gè)INSERT因PRIMARY KEY約束而失敗之后，MYSQL可能已經(jīng)采取EXIT策略，并且SELECT @X可能已經(jīng)返回2。

注意：@X表示用戶變量，使用SET語(yǔ)句為其賦值，用戶變量與連接有關(guān)，一個(gè)客戶端定義的變量不能被其他客戶端所使用，即有作用域的，該客戶端退出時(shí)，客戶端連接的所有變量將自動(dòng)釋放。

3、光標(biāo)

MYSQL里叫光標(biāo)，SQLSERVER里叫游標(biāo)，實(shí)際上一樣的。

查詢語(yǔ)句可能查詢出多條記錄，在存儲(chǔ)過(guò)程和函數(shù)中使用光標(biāo)來(lái)逐條讀取查詢結(jié)果集中的記錄。

光標(biāo)的使用包括聲明光標(biāo)、打開(kāi)光標(biāo)、使用光標(biāo)和關(guān)閉光標(biāo)。光標(biāo)必須聲明在處理程序之前，并且聲明在變量和條件之后。

（1）聲明光標(biāo)

MySQL中使用DECLARE關(guān)鍵字來(lái)聲明光標(biāo)。其語(yǔ)法的基本形式如下：

DECLARE?cursor_name CURSOR?FOR?select_statement ;

其中，cursor_name參數(shù)表示光標(biāo)的名稱；select_statement參數(shù)表示SELECT語(yǔ)句的內(nèi)容，返回一個(gè)用于創(chuàng)建光標(biāo)的結(jié)果集。

【示例】下面聲明一個(gè)名為cur_employee的光標(biāo)。代碼如下：

DECLARE?cur_employee CURSOR?FOR?SELECT?name, age FROM?employee ;

上面的示例中，光標(biāo)的名稱為cur_employee；SELECT語(yǔ)句部分是從employee表中查詢出name和age字段的值。

（2）打開(kāi)光標(biāo)

MySQL中使用OPEN關(guān)鍵字來(lái)打開(kāi)光標(biāo)。其語(yǔ)法的基本形式如下：

OPEN??cursor_name ;

其中，cursor_name參數(shù)表示光標(biāo)的名稱。

【示例】下面打開(kāi)一個(gè)名為cur_employee的光標(biāo)，代碼如下：

OPEN??cur_employee ;

（3）使用光標(biāo)

MySQL中使用FETCH關(guān)鍵字來(lái)使用光標(biāo)。其語(yǔ)法的基本形式如下：

FETCH cursor_name INTO var_name[,var_name…]?;

其中，cursor_name參數(shù)表示光標(biāo)的名稱；var_name參數(shù)表示將光標(biāo)中的SELECT語(yǔ)句查詢出來(lái)的信息存入該參數(shù)中。var_name必須在聲明光標(biāo)之前就定義好。

【示例】下面使用一個(gè)名為cur_employee的光標(biāo)。將查詢出來(lái)的數(shù)據(jù)存入emp_name和emp_age這兩個(gè)變量中，代碼如下：

FETCH??cur_employee INTO emp_name, emp_age ;

上面的示例中，將光標(biāo)cur_employee中SELECT語(yǔ)句查詢出來(lái)的信息存入emp_name和emp_age中。emp_name和emp_age必須在前面已經(jīng)定義。

（4）關(guān)閉光標(biāo)

MySQL中使用CLOSE關(guān)鍵字來(lái)關(guān)閉光標(biāo)。其語(yǔ)法的基本形式如下：

CLOSE??cursor_name ;

其中，cursor_name參數(shù)表示光標(biāo)的名稱。

【示例】 下面關(guān)閉一個(gè)名為cur_employee的光標(biāo)。代碼如下：

CLOSE??cur_employee ;

上面的示例中，關(guān)閉了這個(gè)名稱為cur_employee的光標(biāo)。關(guān)閉之后就不能使用FETCH來(lái)使用光標(biāo)了。

注意：MYSQL中，光標(biāo)只能在存儲(chǔ)過(guò)程和函數(shù)中使用?。?

4、流程控制的使用

存儲(chǔ)過(guò)程和函數(shù)中可以使用流程控制來(lái)控制語(yǔ)句的執(zhí)行。

MySQL中可以使用IF語(yǔ)句、CASE語(yǔ)句、LOOP語(yǔ)句、LEAVE語(yǔ)句、ITERATE語(yǔ)句、REPEAT語(yǔ)句和WHILE語(yǔ)句來(lái)進(jìn)行流程控制。

每個(gè)流程中可能包含一個(gè)單獨(dú)語(yǔ)句，或者是使用BEGIN...END構(gòu)造的復(fù)合語(yǔ)句，構(gòu)造可以被嵌套。

（1）IF語(yǔ)句

IF語(yǔ)句用來(lái)進(jìn)行條件判斷。根據(jù)是否滿足條件，將執(zhí)行不同的語(yǔ)句。其語(yǔ)法的基本形式如下：

IF?search_condition THEN statement_list
[ELSEIF?search_condition THEN statement_list] ...
[ELSE?statement_list]
END IF

其中，search_condition參數(shù)表示條件判斷語(yǔ)句；statement_list參數(shù)表示不同條件的執(zhí)行語(yǔ)句。

注意：MYSQL還有一個(gè)IF()函數(shù)，他不同于這里描述的IF語(yǔ)句

下面是一個(gè)IF語(yǔ)句的示例。代碼如下：

IF age>20?THEN SET @count1=@count1+1;
ELSEIF age=20?THEN SET @count2=@count2+1;
ELSE SET @count3=@count3+1;
END?IF;

該示例根據(jù)age與20的大小關(guān)系來(lái)執(zhí)行不同的SET語(yǔ)句。如果age值大于20，那么將count1的值加1；如果age值等于20，那么將count2的值加1；其他情況將count3的值加1。IF語(yǔ)句都需要使用END IF來(lái)結(jié)束。

（2）CASE語(yǔ)句

CASE語(yǔ)句也用來(lái)進(jìn)行條件判斷，其可以實(shí)現(xiàn)比IF語(yǔ)句更復(fù)雜的條件判斷。CASE語(yǔ)句的基本形式如下：

CASE?case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE?statement_list]
END CASE

其中，case_value參數(shù)表示條件判斷的變量；

when_value參數(shù)表示變量的取值；

statement_list參數(shù)表示不同when_value值的執(zhí)行語(yǔ)句。

CASE語(yǔ)句還有另一種形式。該形式的語(yǔ)法如下：

CASE?
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE?statement_list]
END CASE

其中，search_condition參數(shù)表示條件判斷語(yǔ)句；

statement_list參數(shù)表示不同條件的執(zhí)行語(yǔ)句。

下面是一個(gè)CASE語(yǔ)句的示例。代碼如下：

CASE?age?
WHEN?20?THEN SET @count1=@count1+1;
ELSE SET @count2=@count2+1;
END CASE ;

代碼也可以是下面的形式：

CASE?
WHEN?age=20?THEN SET @count1=@count1+1;
ELSE SET @count2=@count2+1;
END CASE ;

本示例中，如果age值為20，count1的值加1；否則count2的值加1。CASE語(yǔ)句都要使用END CASE結(jié)束。

注意：這里的CASE語(yǔ)句和“控制流程函數(shù)”里描述的SQL CASE表達(dá)式的CASE語(yǔ)句有輕微不同。這里的CASE語(yǔ)句不能有ELSE NULL子句 并且用END CASE替代END來(lái)終止?。?

（3）LOOP語(yǔ)句

LOOP語(yǔ)句可以使某些特定的語(yǔ)句重復(fù)執(zhí)行，實(shí)現(xiàn)一個(gè)簡(jiǎn)單的循環(huán)。但是LOOP語(yǔ)句本身沒(méi)有停止循環(huán)的語(yǔ)句，必須是遇到LEAVE語(yǔ)句等才能停止循環(huán)。

LOOP語(yǔ)句的語(yǔ)法的基本形式如下：

[begin_label:]?LOOP
statement_list
END LOOP [end_label]

其中，begin_label參數(shù)和end_label參數(shù)分別表示循環(huán)開(kāi)始和結(jié)束的標(biāo)志，這兩個(gè)標(biāo)志必須相同，而且都可以省略；

statement_list參數(shù)表示需要循環(huán)執(zhí)行的語(yǔ)句。

下面是一個(gè)LOOP語(yǔ)句的示例。代碼如下：

add_num: LOOP
SET?@count=@count+1;
END?LOOP?add_num ;

該示例循環(huán)執(zhí)行count加1的操作。因?yàn)闆](méi)有跳出循環(huán)的語(yǔ)句，這個(gè)循環(huán)成了一個(gè)死循環(huán)。LOOP循環(huán)都以END LOOP結(jié)束。

（4）LEAVE語(yǔ)句

LEAVE語(yǔ)句主要用于跳出循環(huán)控制。其語(yǔ)法形式如下：

LEAVE label

其中，label參數(shù)表示循環(huán)的標(biāo)志。

下面是一個(gè)LEAVE語(yǔ)句的示例。代碼如下：

add_num: LOOP
SET?@count=@count+1;
IF @count=100 THEN
LEAVE add_num ;
END?LOOP?add_num ;

該示例循環(huán)執(zhí)行count加1的操作。當(dāng)count的值等于100時(shí)，則LEAVE語(yǔ)句跳出循環(huán)。

（5）ITERATE語(yǔ)句

ITERATE語(yǔ)句也是用來(lái)跳出循環(huán)的語(yǔ)句。但是，ITERATE語(yǔ)句是跳出本次循環(huán)，然后直接進(jìn)入下一次循環(huán)。

ITERATE語(yǔ)句只可以出現(xiàn)在LOOP、REPEAT、WHILE語(yǔ)句內(nèi)。

ITERATE語(yǔ)句的基本語(yǔ)法形式如下：

ITERATE?label

下面是一個(gè)ITERATE語(yǔ)句的示例。代碼如下：

add_num: LOOP?
SET?@count=@count+1;
IF?@count=100?THEN
LEAVE add_num ;
ELSE?IF?MOD(@count,3)=0?THEN
ITERATE add_num;
SELECT?* FROM?employee?;
END?LOOP?add_num?;

該示例循環(huán)執(zhí)行count加1的操作，count值為100時(shí)結(jié)束循環(huán)。如果count的值能夠整除3，則跳出本次循環(huán)，不再執(zhí)行下面的SELECT語(yǔ)句。

說(shuō)明：LEAVE語(yǔ)句和ITERATE語(yǔ)句都用來(lái)跳出循環(huán)語(yǔ)句，但兩者的功能是不一樣的。

LEAVE語(yǔ)句是跳出整個(gè)循環(huán)，然后執(zhí)行循環(huán)后面的程序。而ITERATE語(yǔ)句是跳出本次循環(huán)，然后進(jìn)入下一次循環(huán)。

使用這兩個(gè)語(yǔ)句時(shí)一定要區(qū)分清楚。

（6）REPEAT語(yǔ)句

REPEAT語(yǔ)句是有條件控制的循環(huán)語(yǔ)句。當(dāng)滿足特定條件時(shí)，就會(huì)跳出循環(huán)語(yǔ)句。REPEAT語(yǔ)句的基本語(yǔ)法形式如下：

[begin_label:]?REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]

其中，statement_list參數(shù)表示循環(huán)的執(zhí)行語(yǔ)句；search_condition參數(shù)表示結(jié)束循環(huán)的條件，滿足該條件時(shí)循環(huán)結(jié)束。

下面是一個(gè)REPEAT語(yǔ)句的示例。代碼如下：

REPEAT
SET?@count=@count+1;
UNTIL @count=100
END?REPEAT?;

該示例循環(huán)執(zhí)行count加1的操作，count值為100時(shí)結(jié)束循環(huán)。REPEAT循環(huán)都用END REPEAT結(jié)束。

（7）WHILE語(yǔ)句

WHILE語(yǔ)句也是有條件控制的循環(huán)語(yǔ)句。但WHILE語(yǔ)句和REPEAT語(yǔ)句是不一樣的。WHILE語(yǔ)句是當(dāng)滿足條件時(shí)，執(zhí)行循環(huán)內(nèi)的語(yǔ)句。

WHILE語(yǔ)句的基本語(yǔ)法形式如下：

[begin_label:] WHILE search_condition DO?
statement_list
END?WHILE?[end_label]

其中，search_condition參數(shù)表示循環(huán)執(zhí)行的條件，滿足該條件時(shí)循環(huán)執(zhí)行；

statement_list參數(shù)表示循環(huán)的執(zhí)行語(yǔ)句。

下面是一個(gè)WHILE語(yǔ)句的示例。代碼如下：

WHILE?@count<100?DO
SET @count=@count+1;
END?WHILE ;

該示例循環(huán)執(zhí)行count加1的操作，count值小于100時(shí)執(zhí)行循環(huán)。

如果count值等于100了，則跳出循環(huán)。WHILE循環(huán)需要使用END WHILE來(lái)結(jié)束。

三、實(shí)例

1、mysql通用分頁(yè)存儲(chǔ)過(guò)程

DELIMITER //
DROP?PROCEDURE?IF?EXISTS?pr_pager;

CREATE?PROCEDURE?pr_pager(
????IN?p_table_name VARCHAR(100), -- 表名稱
????IN?p_fields VARCHAR(500), -- 要顯示的字段
????IN?pagecurrent INT, -- 當(dāng)前頁(yè)
????IN?pagesize INT, -- 每頁(yè)顯示的記錄數(shù)
????IN?p_where VARCHAR(500) CHARSET?utf8, -- 查詢條件
????IN?p_order VARCHAR(100), -- 排序
????OUT?totalcount INT????????????????????????-- 總記錄數(shù)
)
BEGIN
IF?pagesize <= 1?THEN
????????SET?pagesize = 20;
END?IF;
IF pagecurrent THEN
????SET?pagecurrent = 1;
END?IF;

SET?@startIndex = (pagecurrent-1)*pagesize;
SET?@endIndex = pagesize;

SET?@strsql = CONCAT('select ',p_fields,' from ',p_table_name,
CASE?IFNULL(p_where,'') WHEN?''?THEN?''?ELSE?CONCAT(' where ',p_where) END,
CASE?IFNULL(p_order,'') WHEN?''?THEN?''?ELSE?CONCAT(' order by ',p_order) END,
' limit ',@startIndex,',',@endIndex);
-- 預(yù)定義一個(gè)語(yǔ)句，并將它賦給stmtsql
PREPARE?stmtsql FROM?@strsql;
EXECUTE?stmtsql;
-- 釋放一個(gè)預(yù)定義語(yǔ)句的資源
DEALLOCATE?PREPARE?stmtsql;

SET?@strsqlcount = CONCAT('select count(*) into @Rows_Total from ',p_table_name,
CASE?IFNULL(p_where,'') WHEN?''?THEN?''?ELSE?CONCAT(' where ',p_where) END);

PREPARE?stmtsqlcount FROM?@strsqlcount;
EXECUTE?stmtsqlcount;
DEALLOCATE?PREPARE?stmtsqlcount;

SET?totalcount = @Rows_Total;
-- 計(jì)算總數(shù)也可以是下面這種方法
-- SELECT COUNT(*) INTO totalcount FROM tb_user;
END?//

DELIMITER ;

2、存儲(chǔ)過(guò)程調(diào)用

（1）不帶查詢條件和排序

CALL?pr_pager('t_user','id,username,birthday,sex,address',1,5,
NULL,NULL,@totalcount);

SELECT?@totalcount;

（2）帶查詢條件和排序

CALL?pr_pager('t_user','id,username,birthday,sex,address',1,5,
'username like \'小%\'','id asc',@totalcount);

SELECT?@totalcount;

鏈接：cnblogs.com/xiaoxi/p/6398347.html