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

1、創(chuàng)建存儲(chǔ)過程和函數(shù)

存儲(chǔ)程序可以分為存儲(chǔ)過程和函數(shù)。在MySQL中，創(chuàng)建存儲(chǔ)過程和函數(shù)使用的語句分別是CREATE PROCEDURE和CREATEFUNCTION。

使用CALL語句來調(diào)用存儲(chǔ)過程，只能用輸出變量返回值。函數(shù)可以從語句外調(diào)用（引用函數(shù)名）?，也能返回標(biāo)量值。存儲(chǔ)過程也可以調(diào)用其他存儲(chǔ)過程。

1.1、創(chuàng)建存儲(chǔ)過程

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

create procedure sp_name ([proc_parameter])
	[characteristics ...] routine_body

• CREATE PROCEDURE為用來創(chuàng)建存儲(chǔ)函數(shù)的關(guān)鍵字；
• sp_name為存儲(chǔ)過程的名稱；
• proc_parameter為指定存儲(chǔ)過程的參數(shù)列表，列表形式如下：[in | out | inout ] param_name type
  • IN表示輸入?yún)?shù)
  • OUT表示輸出參數(shù)
  • INOUT表示既可以輸入也可以輸出；
  • param_name表示參數(shù)名稱；
  • type表示參數(shù)的類型，該類型可以是MySQL數(shù)據(jù)庫中的任意類型;
• characteristics指定存儲(chǔ)過程的特性，有以下取值：
  • LANGUAGE SQL：說明routine_body部分是由SQL語句組成的，當(dāng)前系統(tǒng)支持的語言為SQL。SQL是LANGUAGE特性的唯一值。
  • [NOT] DETERMINISTIC：指明存儲(chǔ)過程執(zhí)行的結(jié)果是否正確。DETERMINISTIC表示結(jié)果是確定的。每次執(zhí)行存儲(chǔ)過程時(shí)，相同的輸入會(huì)得到相同的輸出。NOTDETERMINISTIC表示結(jié)果是不確定的，相同的輸入可能得到不同的輸出。如果沒有指定任意一個(gè)值，默認(rèn)為NOT DETERMINISTIC。
  • { CONTAINS SQL | NO SQL |READS SQL DATA | MODIFIESSQL DATA }：指明子程序使用SQL語句的限制。CONTAINS SQL表明 子程序包含SQL語句，但是不包含讀寫數(shù)據(jù)的語句；NO SQL表明子程序不包含SQL語句；READS SQLDATA說明子程序包含讀數(shù)據(jù)的語句；MODIFIES SQL DATA表明子程序包含寫數(shù)據(jù)的語句。默認(rèn)情況下，系統(tǒng)會(huì)指定為CONTAINSSQL。
  • SQL SECURITY { DEFINER |INVOKER }：指明誰有權(quán)限來執(zhí)行。DEFINER表示只有定義者才能執(zhí)行。INVOKER表示擁有權(quán)限的調(diào)用者可以執(zhí)行。默認(rèn)情況下，系統(tǒng) 指定為DEFINER。
  • COMMENT 'string'：注釋信息，可以用來描述存儲(chǔ)過程或函數(shù)。
• routine_body是SQL代碼的內(nèi)容，可以用BEGIN…END來表示SQL代碼的開始和結(jié)束。

編寫存儲(chǔ)過程并不是一件簡(jiǎn)單的事情，可能存儲(chǔ)過程中需要復(fù)雜的SQL語句，并且要有創(chuàng)建存儲(chǔ)過程的權(quán)限；但是使用存儲(chǔ)過程將簡(jiǎn)化操作，減少冗余的操作步驟，同時(shí)，還可以減少操作過程中的失誤，提高效率，因此存儲(chǔ)過程是非常有用的，而且應(yīng)該盡可能地學(xué)會(huì)使用。

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

create procedure AvgFruitPrice()
begin
	select avg(f_price) as avgprice
	from fruits;
end;

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

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

create procedure Proc()
begin
	select * from fruits;
end;

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

call Proc();
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   20.10 |
| b2   |  104 | berry      |    7.60 |
| b5   |  107 | xxxx       |    3.60 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | mellon     |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| l2   |  104 | lemon      |    6.40 |
| m1   |  106 | mango      |   15.70 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbababa    |    3.60 |
+------+------+------------+---------+

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

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

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

create procedure CountProc (out param1 int)
begin
	select count(*) into param1 
	from fruits;
end;

上述代碼的作用是創(chuàng)建一個(gè)獲取fruits表記錄條數(shù)的存儲(chǔ)過程，名稱是CountProc，COUNT(*)計(jì)算后把結(jié)果放入?yún)?shù)param1中。

當(dāng)使用DELIMITER命令時(shí)，應(yīng)該避免使用反斜杠（‘\’）字符，因?yàn)榉葱本€是MySQL的轉(zhuǎn)義字符。

1.2、創(chuàng)建存儲(chǔ)函數(shù)

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

create function func_name ( [func_parameter])
returns type
[characteristic ...] routine_body

• CREATE FUNCTION為用來創(chuàng)建存儲(chǔ)函數(shù)的關(guān)鍵字；
• func_name表示存儲(chǔ)函數(shù)的名稱；
• func_parameter為存儲(chǔ)過程的參數(shù)列表，參數(shù)列表形式如下：[in | out | inout] param_name type
  • IN表示輸入?yún)?shù)
  • OUT表示輸出參數(shù)
  • INOUT表示既可以輸入也可以輸出；
  • param_name表示參數(shù)名稱；
  • type表示參數(shù)的類型，該類型可以是MySQL數(shù)據(jù)庫中的任意類型;
• RETURNS type語句表示函數(shù)返回?cái)?shù)據(jù)的類型；
• characteristic指定存儲(chǔ)函數(shù)的特性，取值與創(chuàng)建存儲(chǔ)過程時(shí)相同

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

create function NameByZip()
returns char(50)
return (
	select s_name
	from suppliers
	where s_call = '48075'
);

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

指定參數(shù)為IN、OUT或INOUT只對(duì)PROCEDURE是合法的。?（FUNCTION中總是默認(rèn)為IN參數(shù)）?。RETURNS子句只能對(duì)FUNCTION做指定，對(duì)函數(shù)而言這是強(qiáng)制的。它用來指定函數(shù)的返回類型，而且函數(shù)體必須包含一個(gè)RETURN value語句。

1.3、變量的使用

1.3.1、定義變量

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

declare var_name [, varname]... date_type [default value];

• var_name為局部變量的名稱。
• DEFAULTvalue子句給變量提供一個(gè)默認(rèn)值。值除了可以被聲明為一個(gè)常數(shù)之外，還可以被指定為一個(gè)表達(dá)式。如果沒有DEFAULT子句，初始值為NULL。

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

declare myparam int default 100;

1.3.2、為變量賦值

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

set var_name = expr [, var_name = expr] ...;

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

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

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

declare var1, var2, var3 int;
set var1 = 10, var2 = 20;
set var3 = var1 + var2;

在MySQL中，還可以通過SELECT … INTO為一個(gè)或多個(gè)變量賦值，語法如下：

select col_name[, ...] int 0 var_name [, ...] table_expr;

這個(gè)SELECT語法把選定的列直接存儲(chǔ)到對(duì)應(yīng)位置的變量。col_name表示字段名稱；var_name表示定義的變量名稱；table_expr表示查詢條件表達(dá)式，包括表名稱和WHERE子句。

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

declare fruitname char(50)
declare fruitprice decimal(8, 2);
select f_name, f_price into fruitname, fruitprice
from fruits 
where f_id = 'a1';

1.4、定義條件和處理程序

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

1.4.1、定義條件

定義條件使用DECLARE語句，語法格式如下：

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

• condition_name參數(shù)表示條件的名稱；
• condition_type參數(shù)表示條件的類型；
• sqlstate_value和MySQL_error_code都可以表示MySQL的錯(cuò)誤，sqlstate_value為長度為5的字符串類型錯(cuò)誤代碼，MySQL_error_code為數(shù)值類型錯(cuò)誤代碼。例如，在ERROR 1142（42000）中，sqlstate_value的值是42000，MySQL_error_code的值是1142。

這個(gè)語句指定需要特殊處理的條件。它將一個(gè)名字和指定的錯(cuò)誤條件關(guān)聯(lián)起來。這個(gè)名字可以隨后被用在定義處理程序的DECLAREHANDLER語句中。

定義"ERROR 1148(42000)"錯(cuò)誤，名稱為command_not_allowed。可以用兩種不同的方法來定義，代碼如下：

//方法一：使用sqlstate_value
seclare command_not_allowed condition for sqlstate '43000';
//方法二：使用mysql_error_code
declare command_not_allowed condition for 1148;

1.4.2、定義處理程序

定義處理程序時(shí)，使用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 FOUNT
	| SQLEXCEPTION
	| mysql_error_code

• handler_type為錯(cuò)誤處理方式，參數(shù)取3個(gè)值：CONTINUE、EXIT和UNDO。
• CONTINUE表示遇到錯(cuò)誤不處理，繼續(xù)執(zhí)行；
• EXIT表示遇到錯(cuò)誤馬上退出；
• UNDO表示遇到錯(cuò)誤后撤回之前的操作，MySQL中暫時(shí)不支持這樣的操作。
• condition_value表示錯(cuò)誤類型，可以有以下取值：
  • SQLSTATE [VALUE]sqlstate_value包含5個(gè)字符的字符串錯(cuò)誤值；
  • condition_name表示DECLARE CONDITION定義的錯(cuò)誤條件名稱；
  • SQLWARNING匹配所有以01開頭的SQLSTATE錯(cuò)誤代碼；
  • NOT FOUND匹配所有以02開頭的SQLSTATE錯(cuò)誤代碼；
  • SQLEXCEPTION匹配所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE錯(cuò)誤代碼。
  • MySQL_error_code匹配數(shù)值類型錯(cuò)誤代碼。
• sp_statement參數(shù)為程序語句段，表示在遇到定義的錯(cuò)誤時(shí)需要執(zhí)行的存儲(chǔ)過程或函數(shù)。

定義處理程序的幾種方式，代碼如下：

//方法一：捕獲sqlstate_value
declare continue handler for sqlstate '42S02' set @info='NO_SUCH_TABLE';
//方法二：捕獲mysql_error_code
declare continue handler for 1146 set @info='NO_SUCH_TABLE';
//方法三：先定義條件，然后調(diào)用
declare no_such_table condition for 1146;
declare continue handler for no_such_table set @info='NO_SUCH_TABLE';
//方法四：使用SQLWARNING
declare exit handler for sqlwarning set @info='ERROR';
//方法五：使用NOT FOUND
declare exit handler for not found set @info='NO_SUCH_TABLE';
//方法六：使用SQLEXCEPTION
declare exit handler for sqlexception set @info='ERROR';

• 第一種方法是捕獲sqlstate_value值。如果遇到sqlstate_value值為“42S02”?，執(zhí)行CONTINUE操作，并且輸出“NO_SUCH_TABLE”信息。
• 第二種方法是捕獲MySQL_error_code值。如果遇到MySQL_error_code值為1146，執(zhí)行CONTINUE操作，并且輸出“NO_SUCH_TABLE”信息。
• 第三種方法是先定義條件，再調(diào)用條件。這里先定義no_such_table條件，遇到1146錯(cuò)誤就執(zhí)行CONTINUE操作。
• 第四種方法是使用SQLWARNING。SQLWARNING捕獲所有以01開頭的sqlstate_value值，然后執(zhí)行EXIT操作，并且輸出“ERROR”信息。
• 第五種方法是使用NOT FOUND。NOTFOUND捕獲所有以02開頭的sqlstate_value值，然后執(zhí)行EXIT操作，并且輸出“NO_SUCH_TABLE”信息。
• 第六種方法是使用SQLEXCEPTION。SQLEXCEPTION捕獲所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值，然后執(zhí)行EXIT操作，并且輸出“ERROR”信息。

定義條件和處理程序，具體執(zhí)行的過程如下：

create table test_db.t
(
	s1 int,
	primary key(s1)
);
delimiter $$
create procedure handlerdemo ()
begin
	declare continue handler for sqlstate '23000' set @x2 = 1;
	set @x = 1;
	insert into test_db.t values(1);
	set @x = 2;
	insert into test_db.t values(1);
	set @x = 3;
end;
$$
delimiter ;
call handlerdemo();
select @x;
+----+
| @x |
+----+
|  3 |
+----+

@x是1個(gè)用戶變量，執(zhí)行結(jié)果@x等于3，這表明MySQL被執(zhí)行到程序的末尾。如果“DECLARE CONTINUE HANDLER FORSQLSTATE ‘23000’ SET @x2 = 1;”這1行不在，第2個(gè)INSERT因PRIMARY KEY強(qiáng)制而失敗之后，MySQL可能已經(jīng)采取默認(rèn)（EXIT）路徑，并且SELECT @x可能已經(jīng)返 回2。

“@var_name”表示用戶變量，使用SET語句為其賦值，用戶變量與連接有關(guān)，一個(gè)客戶端定義的變量不能被其他客戶端看到或使用。當(dāng)客戶端退出時(shí)，該客戶端連接的所有變量將自動(dòng)釋放。

1.5、光標(biāo)的使用

查詢語句可能返回多條記錄，如果數(shù)據(jù)量非常大，需要在存儲(chǔ)過程和儲(chǔ)存函數(shù)中使用光標(biāo)來逐條讀取查詢結(jié)果集中的記錄。應(yīng)用程序可以根據(jù)需要滾動(dòng)或?yàn)g覽其中的數(shù)據(jù)。本節(jié)將介紹如何聲明、打開、使用和關(guān)閉光標(biāo)。

光標(biāo)必須在聲明處理程序之前被聲明，并且變量和條件還必須在聲明光標(biāo)或處理程序之前被聲明。

1.5.1、聲明光標(biāo)

在MySQL中，使用DECLARE關(guān)鍵字來聲明光標(biāo)，其語法的基本形式如下：

declare cursor_name cursor for select_statement

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

聲明名稱為cursor_fruit的光標(biāo)，代碼如下：

declare cursor_fruit cursor for 
select f_name, f_price 
from fruits;

在上面的示例中，光標(biāo)的名稱為cur_fruit，SELECT語句部分從fruits表中查詢出f_name和f_price字段的值。

1.5.2、打開光標(biāo)

打開光標(biāo)的語法如下：

open cursor_name{光標(biāo)名稱}

這個(gè)語句打開先前聲明的名稱為cursor_name的光標(biāo)。

打開名稱為cursor_fruit的光標(biāo)，代碼如下：

open cursor_fruit;

1.5.3、使用光標(biāo)

使用光標(biāo)的語法如下：

FETCH cursor_name INTO var_name [, var_name] ... {參數(shù)名稱}

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

使用名稱為cursor_fruit的光標(biāo)將查詢出來的數(shù)據(jù)存入fruit_name和fruit_price這兩個(gè)變量中，代碼如下：

fetch cursor_fruit into fruit_name, fruit_price;

上面的示例中，將光標(biāo)cursor_fruit中用SELECT語句查詢出來的信息存入fruit_name和fruit_price中。fruit_name和fruit_price必須在前面已經(jīng)定義。

1.5.4、關(guān)閉光標(biāo)

關(guān)閉光標(biāo)的語法如下：

CLOSE cursor_name{光標(biāo)名稱}

這個(gè)語句關(guān)閉先前打開的光標(biāo)。

如果未被明確地關(guān)閉，光標(biāo)在它被聲明的復(fù)合語句的末尾關(guān)閉。

關(guān)閉名稱為cursor_fruit的光標(biāo)，代碼如下：

close cursor_fruit;

MySQL中光標(biāo)只能在存儲(chǔ)過程和函數(shù)中使用。

1.6、流程控制的使用

流程控制語句用來根據(jù)條件控制語句的執(zhí)行。MySQL中用來構(gòu)造控制流程的語句有IF語句、CASE語句、LOOP語句、LEAVE語句、ITERATE語句、REPEAT語句和WHILE語句。

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

1.6.1、IF語句

IF語句包含多個(gè)條件判斷，根據(jù)判斷的結(jié)果為TRUE或FALSE執(zhí)行相應(yīng)的語句，語法格式如下：

IF expr_condition THEN statement_list
[ELSEIF expr_condition THEN statement_list] ...
[ELSE statement_list]
END IF

IF實(shí)現(xiàn)了一個(gè)基本的條件構(gòu)造。如果expr_condition求值為真（TRUE）?，相應(yīng)的SQL語句列表被執(zhí)行；如果沒有expr_condition匹配，則ELSE子句里的語句列表被執(zhí)行。statement_list可以包括一個(gè)或多個(gè)語句。

IF語句的示例，代碼如下：

if val is null
	then select 'val is null';
	else select 'val is not null'
end if;

該示例判斷val值是否為空，如果val值為空，輸出字符串“val is NULL”?；否則輸出字符串“val is not NULL”?。IF語句都需要使用ENDIF來結(jié)束。

1.6.2、CASE語句

CASE是另一個(gè)進(jìn)行條件判斷的語句，有兩種格式。

第1種格式如下：

CASE case_expr
	WHEN when_value THEN statement_list
	[WHEN when_value THEN statement_list] ...
	[ELSE statement_list]
END CASE

• case_expr參數(shù)表示條件判斷的表達(dá)式，決定了哪一個(gè)WHEN子句會(huì)被執(zhí)行；
• when_value參數(shù)表示表達(dá)式可能的值，如果某個(gè)when_value表達(dá)式與case_expr表達(dá)式結(jié)果相同，則執(zhí)行對(duì)應(yīng)THEN關(guān)鍵字后的statement_list中的語句；
• statement_list參數(shù)表示不同when_value值的執(zhí)行語句。

使用CASE流程控制語句的第1種格式，判斷val值等于1、等于2，或者兩者都不等，語句如下：

case val
	when 1 then select 'val is 1';
	when 2 then select 'val is 2';
	else select 'val is not 1 or 2';
end case;

當(dāng)val值為1時(shí)，輸出字符串“val is 1”?；當(dāng)val值為2時(shí)，輸出字符串“val is 2”?；否則輸出字符串“val is not 1 or 2”?。

CASE語句的第2種格式如下：

case
	when expr_condition then statement_list
	[when expr_condition then statement_list] ...
	[else statement_list] 
end case

• expr_condition參數(shù)表示條件判斷語句；
• statement_list參數(shù)表示不同條件的執(zhí)行語句。該語句中，WHEN語句將被逐個(gè)執(zhí)行，直到某個(gè)expr_condition表達(dá)式為真，則執(zhí)行對(duì)應(yīng)THEN關(guān)鍵字后面的statement_list語句。如果沒有條件匹配，ELSE子句里的語句被執(zhí)行;

使用CASE流程控制語句的第2種格式，判斷val是否為空、小于0、大于0或者等于0，語句如下：

case
	when val is null then select 'val is null';
	when val < 0 then select 'val is less than 0';
	when val > 0 then select 'val is greater than 0';
	else select 'val is 0';
end case;

當(dāng)val值為空，輸出字符串“val is NULL”?；當(dāng)val值小于0時(shí)，輸出字符串“val is less than0”?；當(dāng)val值大于0時(shí)，輸出字符串“val isgreater than 0”?；否則輸出字符串“val is0”?。

1.6.3、LOOP語句

LOOP循環(huán)語句用來重復(fù)執(zhí)行某些語句，與IF和CASE語句相比，LOOP只是創(chuàng)建一個(gè)循環(huán)操作的過程，并不進(jìn)行條件判斷。LOOP內(nèi)的語句一直重復(fù)執(zhí)行直到循環(huán)被退出（使用LEAVE子句）?，跳出循環(huán)過程。LOOP語句的基本格式如下：

[loop_label:] LOOP
	statement_list
END LOOP [loop_label]

• loop_label表示LOOP語句的標(biāo)注名稱，該參數(shù)可以省略；
• statement_list參數(shù)表示需要循環(huán)執(zhí)行的語句;

使用LOOP語句進(jìn)行循環(huán)操作，id值小于10時(shí)將重復(fù)執(zhí)行循環(huán)過程，代碼如下：

declare id int default 0;
add_loop: loop
set id = id + 1;
	if id >= 10 then leave add_loop;
	end if;
end loop add_loop;

該示例循環(huán)執(zhí)行id加1的操作。當(dāng)id值小于10時(shí)，循環(huán)重復(fù)執(zhí)行；當(dāng)id值大于或者等于10時(shí)，使用LEAVE語句退出循環(huán)。LOOP循環(huán)都以END LOOP結(jié)束。

1.6.4、LEAVE語句

LEAVE語句用來退出任何被標(biāo)注的流程控制構(gòu)造，基本格式如下：

LEAVE label

其中，label參數(shù)表示循環(huán)的標(biāo)志。LEAVE和BEGIN … END或循環(huán)一起被使用。

使用LEAVE語句退出循環(huán)，代碼如下：

add_num: LOOP
set @count=@count+1;
IF @count=50 THEN LEAVE add_num;
END LOOP add_num;

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

1.6.5、ITERATE語句

ITERATE語句將執(zhí)行順序轉(zhuǎn)到語句段開頭處，語句基本格式如下：

ITERATE label

ITERATE只可以出現(xiàn)在LOOP、REPEAT和WHILE語句內(nèi)。ITERATE的意思為“再次循環(huán)”?，label參數(shù)表示循環(huán)的標(biāo)志。ITERATE語句必須跟在循環(huán)標(biāo)志前面。

ITERATE語句示例，代碼如下：

create procedure doiterate()
begin
	declare p1 int default 0;
	my_loop: LOOP
	set p1 = p1 + 1;
	if p1 < 10 then iterate my_loop;
	elseif p1 > 20 then leave my_loop;
	end if;
	select 'p1 is between 10 and 20';
end LOOP my_loop;
end;

初始化p1=0，如果p1的值小于10時(shí)，重復(fù)執(zhí)行p1加1操作；當(dāng)p1大于等于10并且小于等于20時(shí)，打印消息“p1 is between 10 and20”?；當(dāng)p1大于20時(shí)，退出循環(huán)。

1.6.6、REPEAT語句

REPEAT語句創(chuàng)建一個(gè)帶條件判斷的循環(huán)過程，每次語句執(zhí)行完畢之后會(huì)對(duì)條件表達(dá)式進(jìn)行判斷，如果表達(dá)式為真，則循環(huán)結(jié)束；否則重復(fù)執(zhí)行循環(huán)中的語句。REPEAT語句的基本格式如下：

[repeat_label:] REPEAT
	statement_list
UNTIL expr_condition
END REPEAT [repeat_label]

repeat_label為REPEAT語句的標(biāo)注名稱，該參數(shù)可以省略；REPEAT語句內(nèi)的語句或語句群被重復(fù)，直至expr_condition為真。

REPEAT語句示例，id值小于10時(shí)將重復(fù)執(zhí)行循環(huán)過程，代碼如下：

declare id int default 0;
repeat
set id = id + 1;
until id >= 10
end repeat;

該示例循環(huán)執(zhí)行id加1的操作。當(dāng)id值小于10時(shí)，循環(huán)重復(fù)執(zhí)行；當(dāng)id值大于或者等于10時(shí)，退出循環(huán)。REPEAT循環(huán)都以ENDREPEAT結(jié)束。

1.6.7、WHILE語句

WHILE語句創(chuàng)建一個(gè)帶條件判斷的循環(huán)過程，與REPEAT不同，WHILE在執(zhí)行語句執(zhí)行時(shí)，先對(duì)指定的表達(dá)式進(jìn)行判斷，如果為真，就執(zhí)行循環(huán)內(nèi)的語句，否則退出循環(huán)。

WHILE語句的基本格式如下：

　　[while_label:] WHILE expr_condition DO
　　　　statement_list
　　END WHILE [while_label]

• while_label為WHILE語句的標(biāo)注名稱；
• expr_condition為進(jìn)行判斷的表達(dá)式，如果表達(dá)式結(jié)果為真，WHILE語句內(nèi)的語句或語句群被執(zhí)行，直至expr_condition為假，退出循環(huán);

WHILE語句示例，i值小于10時(shí)，將重復(fù)執(zhí)行循環(huán)過程，代碼如下：

declare i int default 0;
while i < 10 do
set i = i + 1
end while;

2、調(diào)用存儲(chǔ)過程和函數(shù)

存儲(chǔ)過程已經(jīng)定義好了，接下來需要知道如何調(diào)用這些過程和函數(shù)。存儲(chǔ)過程和函數(shù)有多種調(diào)用方法。存儲(chǔ)過程必須使用CALL語句調(diào)用，并且存儲(chǔ)過程和數(shù)據(jù)庫相關(guān)，如果要執(zhí)行其他數(shù)據(jù)庫中的存儲(chǔ)過程，需要指定數(shù)據(jù)庫名稱，例如CALL dbname.procname。存儲(chǔ)函數(shù)的調(diào)用與MySQL中預(yù)定義的函數(shù)的調(diào)用方式相同。

2.1、調(diào)用存儲(chǔ)過程

存儲(chǔ)過程是通過CALL語句進(jìn)行調(diào)用的，語法如下：

CALL sp_name([parameter [,...]])

CALL語句調(diào)用一個(gè)先前用CREATEPROCEDURE創(chuàng)建的存儲(chǔ)過程，其中sp_name為存儲(chǔ)過程名稱，parameter為存儲(chǔ)過程的參數(shù)。

定義名為CountProc1的存儲(chǔ)過 程，然后調(diào)用這個(gè)存儲(chǔ)過程。

use test_db;
delimiter $$
create procedure CountProc1 (in sid int, out num int)
begin
	select count(*) into num
	from fruits
	where s_id = sid;
end $$
delimiter ;
//調(diào)用
call CountProc1(101, @num);
//查看返回結(jié)果：
select @num;
+------+
| @num |
+------+
|    3 |
+------+

該存儲(chǔ)過程返回了指定s_id=101的水果商提供的水果種類，返回值存儲(chǔ)在num變量中，使用SELECT查看，返回結(jié)果為3。

2.2、調(diào)用存儲(chǔ)函數(shù)

在MySQL中，存儲(chǔ)函數(shù)的使用方法與MySQL內(nèi)部函數(shù)的使用方法是一樣的。換言之，用戶自己定義的存儲(chǔ)函數(shù)與MySQL內(nèi)部函數(shù)是一個(gè)性質(zhì)的。區(qū)別在于，存儲(chǔ)函數(shù)是用戶自己定義的，而內(nèi)部函數(shù)是MySQL的開發(fā)者定義的。

定義存儲(chǔ)函數(shù)CountProc2，然后調(diào)用這個(gè)函數(shù)，代碼如下：

delimiter $$
create function CountProc2 (sid int)
returns int
begin
	return ( select count(*) from fruits where s_id = sid);
end $$
delimiter ;

如果在創(chuàng)建存儲(chǔ)函數(shù)中報(bào)錯(cuò)“youmight want to use the less safelog_bin_trust_function_creators variable”?，需要執(zhí)行以下代碼：SET GLOBAL log_bin_trust_function_creators = 1;

調(diào)用存儲(chǔ)函數(shù)：

select CountProc2(101);
+-----------------+
| CountProc2(101) |
+-----------------+
|               3 |
+-----------------+

3、查看存儲(chǔ)過程和函數(shù)

MySQL存儲(chǔ)了存儲(chǔ)過程和函數(shù)的狀態(tài)信息:用戶可以使用SHOW STATUS語句或SHOWCREATE語句來查看，也可直接從系統(tǒng)的information_schema數(shù)據(jù)庫中查詢。

3.1、使用SHOW STATUS語句查看存儲(chǔ)過程和函數(shù)的狀態(tài)

SHOW STATUS語句可以查看存儲(chǔ)過程和函數(shù)的狀態(tài)，其基本語法結(jié)構(gòu)如下：

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

這個(gè)語句是一個(gè)MySQL的擴(kuò)展，返回子程序的特征，如數(shù)據(jù)庫、名字、類型、創(chuàng)建者及創(chuàng)建和修改日期。如果沒有指定樣式，那么根據(jù)使用的語句，所有存儲(chǔ)程序或存儲(chǔ)函數(shù)的信息都會(huì)被列出。

其中，PROCEDURE和FUNCTION分別表示查看存儲(chǔ)過程和函數(shù)；LIKE語句表示匹配存儲(chǔ)過程或函數(shù)的名稱。

SHOW STATUS語句示例，代碼如下：

show procedure status like 'C%' \G;
***************************[ 1. row ]***************************
Db                   | sys
Name                 | create_synonym_db
Type                 | PROCEDURE
Definer              | mysql.sys@localhost
Modified             | 2026-06-12 19:58:25
Created              | 2026-06-12 19:58:25
Security_type        | INVOKER
Comment              |
Description
-----------

“SHOW PROCEDURE STATUS LIKE’C%'\G”語句獲取數(shù)據(jù)庫中所有名稱以字母‘C’開頭的存儲(chǔ)過程的信息。通過上面的語句可以看到：這個(gè)存儲(chǔ)函數(shù)所在的數(shù)據(jù)庫為 test_db、存儲(chǔ)函數(shù)的名稱為CountProc等一些相關(guān)信息。

3.2、使用SHOW CREATE語句查看存儲(chǔ)過程和函數(shù)的定義

除了SHOW STATUS之外，MySQL還可以使用SHOW CREATE語句查看存儲(chǔ)過程和函數(shù)的狀態(tài)。

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

這個(gè)語句是一個(gè)MySQL的擴(kuò)展。類似于SHOW CREATE TABLE，它返回一個(gè)可用來重新創(chuàng)建已命名子程序的確切字符串。

• PROCEDURE和FUNCTION分別表示查看存儲(chǔ)過程和函數(shù)；
• sp_name參數(shù)表示匹配存儲(chǔ)過程或函數(shù)的名稱;

SHOW CREATE語句示例，代碼如下：

show create function test_db.CountProc2 \G;
***************************[ 1. row ]***************************
Function             | CountProc2
sql_mode             | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Create Function      | CREATE DEFINER=`root`@`%` FUNCTION `CountProc2`(sid int) RETURNS int
begin
        return ( select count(*) from fruits where s_id = sid);
end
character_set_client | utf8mb4
collation_connection | utf8mb4_0900_ai_ci
Database Collation   | utf8mb3_general_ci

執(zhí)行上面的語句可以得到存儲(chǔ)函數(shù)的名稱為CountProc2，sql_mode為sql的模式，Create Function為存儲(chǔ)函數(shù)的具體定義語句，還有數(shù)據(jù)庫設(shè)置的一些信息。

3.3、從information_schema.Routines表中查看存儲(chǔ)過程和函數(shù)的信息

MySQL中存儲(chǔ)過程和函數(shù)的信息存儲(chǔ)在information_schema數(shù)據(jù)庫下的Routines表中?？梢酝ㄟ^查詢?cè)摫淼挠涗泚聿樵兇鎯?chǔ)過程和函數(shù)的信息。其基本語法形式如下：

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME=' sp_name ' ;

• ROUTINE_NAME字段中存儲(chǔ)的是存儲(chǔ)過程和函數(shù)的名稱；
• sp_name參數(shù)表示存儲(chǔ)過程或函數(shù)的名稱;

從Routines表中查詢名稱為CountProc2的存儲(chǔ)函數(shù)的信息，代碼如下：

select * from information_schema.Routines
where routine_name = 'CountProc2'
and routine_type = 'FUNCTION' \G;
***************************[ 1. row ]***************************
SPECIFIC_NAME            | CountProc2
ROUTINE_CATALOG          | def
ROUTINE_SCHEMA           | test_db
ROUTINE_NAME             | CountProc2
ROUTINE_TYPE             | FUNCTION
DATA_TYPE                | int
CHARACTER_MAXIMUM_LENGTH | <null>
CHARACTER_OCTET_LENGTH   | <null>
NUMERIC_PRECISION        | 10
NUMERIC_SCALE            | 0
DATETIME_PRECISION       | <null>
DATA_TYPE                | int
CHARACTER_MAXIMUM_LENGTH | <null>
CHARACTER_OCTET_LENGTH   | <null>
NUMERIC_PRECISION        | 10
NUMERIC_SCALE            | 0
DATETIME_PRECISION       | <null>
CHARACTER_SET_NAME       | <null>
COLLATION_NAME           | <null>
DTD_IDENTIFIER           | int
ROUTINE_BODY             | SQL
ROUTINE_DEFINITION       | begin
        return ( select count(*) from fruits where s_id = sid);
end
EXTERNAL_NAME            | <null>
EXTERNAL_LANGUAGE        | SQL
PARAMETER_STYLE          | SQL
IS_DETERMINISTIC         | NO
SQL_DATA_ACCESS          | CONTAINS SQL
SQL_PATH                 | <null>
SECURITY_TYPE            | DEFINER
CREATED                  | 2026-06-25 17:55:01
LAST_ALTERED             | 2026-06-25 17:55:01
SQL_MODE                 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
ROUTINE_COMMENT          |
DEFINER                  | root@%
CHARACTER_SET_CLIENT     | utf8mb4
COLLATION_CONNECTION     | utf8mb4_0900_ai_ci
DATABASE_COLLATION       | utf8mb3_general_ci

在information_schema數(shù)據(jù)庫下的Routines表中，存儲(chǔ)所有存儲(chǔ)過程和函數(shù)的定義。使用SELECT語句查詢Routines表中的存儲(chǔ)過程和函數(shù)的定義時(shí)，一定要使用ROUTINE_NAME字段指定存儲(chǔ)過程或函數(shù)的名稱。否則，將查詢出所有的存儲(chǔ)過程或函數(shù)的定義。如果有存儲(chǔ)過程和存儲(chǔ)函數(shù)名稱相同，就需要同時(shí)指定ROUTINE_TYPE字段表明查詢的是哪種類型的存儲(chǔ)程序。

4、修改存儲(chǔ)過程和函數(shù)

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

• sp_name參數(shù)表示存儲(chǔ)過程或函數(shù)的名稱；
• characteristic參數(shù)指定存儲(chǔ)函數(shù)的特性，可能的取值有：
  • CONTAINS SQL，表示子程序包含SQL語句，但不包含讀或?qū)憯?shù)據(jù)的語句。
  • NO SQL，表示子程序中不包含SQL語句。
  • READS SQL DATA，表示子程序中包含讀數(shù)據(jù)的語句。
  • MODIFIES SQL DATA，表示子程序中包含寫數(shù)據(jù)的語句。
  • SQL SECURITY { DEFINER |INVOKER }，指明誰有權(quán)限來執(zhí)行。
  • DEFINER，表示只有定義者自己才能夠執(zhí)行。
  • INVOKER，表示調(diào)用者可以執(zhí)行。
  • COMMENT 'string'，表示注釋信息。

修改存儲(chǔ)過程使用ALTER PROCEDURE語句，修改存儲(chǔ)函數(shù)使用ALTER FUNCTION語句。但是，這兩個(gè)語句的結(jié)構(gòu)是一樣的，語句中的所有參數(shù)也是一樣的。而且，它們與創(chuàng)建存儲(chǔ)過程或函數(shù)的語句中的參數(shù)也是基本一樣的。

修改存儲(chǔ)過程CountProc的定義。將讀寫權(quán)限改為MODIFIES SQLDATA，并指明調(diào)用者可以執(zhí)行，代碼如下：

alter procedure CountProc
modifies sql data
sql security invoker;

執(zhí)行代碼，并查看修改后的信息。結(jié)果顯示如下：

select specific_name, sql_data_access, security_type
from information_schema.Routines
where routine_name = 'CountProc' and routine_type = 'PROCEDURE';
+---------------+-------------------+---------------+
| SPECIFIC_NAME | SQL_DATA_ACCESS   | SECURITY_TYPE |
+---------------+-------------------+---------------+
| CountProc     | MODIFIES SQL DATA | INVOKER       |
+---------------+-------------------+---------------+

結(jié)果顯示，存儲(chǔ)過程修改成功。從查詢的結(jié)果可以看出，訪問數(shù)據(jù)的權(quán)限（SQL_DATA_ACCESS）已經(jīng)變成MODIFIES SQLDATA，安全類型（SECURITY_TYPE）已經(jīng)變成INVOKER。

修改存儲(chǔ)函數(shù)CountProc2的定義。將讀寫權(quán)限改為READS SQL DATA，并加上注釋信息“FIND NAME”?，代碼如下：

alter function CountProc2
reads sql data
comment 'FIND NAME';

執(zhí)行代碼，并查看修改后的信息。結(jié)果顯示如下：

select specific_name, sql_data_access, routine_comment
from information_schema.Routines
where routine_name = 'CountProc2' and routine_type = 'FUNCTION';
+---------------+-----------------+-----------------+
| SPECIFIC_NAME | SQL_DATA_ACCESS | ROUTINE_COMMENT |
+---------------+-----------------+-----------------+
| CountProc2    | READS SQL DATA  | FIND NAME       |
+---------------+-----------------+-----------------+

存儲(chǔ)函數(shù)修改成功。從查詢的結(jié)果可以看出，訪問數(shù)據(jù)的權(quán)限（SQL_DATA_ACCESS）已經(jīng)變成READS SQL DATA，函數(shù)注釋（ROUTINE_COMMENT）已經(jīng)變成FIND NAME。

5、刪除存儲(chǔ)過程和函數(shù)

刪除存儲(chǔ)過程和函數(shù)，可以使用DROP語句，其語法結(jié)構(gòu)如下：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

這個(gè)語句被用來移除一個(gè)存儲(chǔ)過程或函數(shù)。sp_name為要移除的存儲(chǔ)過程或函數(shù)的名稱。

IF EXISTS子句是一個(gè)MySQL的擴(kuò)展。如果程序或函數(shù)不存儲(chǔ)，它可以防止發(fā)生錯(cuò)誤，產(chǎn)生一個(gè)用SHOW WARNINGS查看的警告。

刪除存儲(chǔ)過程和存儲(chǔ)函數(shù)，代碼如下：

DROP PROCEDURE CountProc;
DROP FUNCTION CountProc2;

上面語句的作用就是刪除存儲(chǔ)過程CountProc和存儲(chǔ)函數(shù)CountProc。

6、MySQL 8.0的新特性——全局變量的持久化

在MySQL數(shù)據(jù)庫中，全局變量可以通過SETGLOBAL語句來設(shè)置。例如，設(shè)置服務(wù)器語句超時(shí)的限制，可以通過設(shè)置系統(tǒng)變量max_execution_time來實(shí)現(xiàn)：

SET GLOBAL MAX_EXECUTION_TIME=2000;

使用SET GLOBAL語句設(shè)置的變量值只會(huì)臨時(shí)生效。數(shù)據(jù)庫重啟后，服務(wù)器又會(huì)從 MySQL配置文件中讀取變量的默認(rèn)值。

MySQL 8.0版本新增了SET PERSIST命令。例如，設(shè)置服務(wù)器的最大連接數(shù)為1000：

SET PERSIST max_connections = 1000;

MySQL會(huì)將該命令的配置保存到數(shù)據(jù)目錄下的mysqld-auto.cnf文件中，下次啟動(dòng)時(shí)會(huì)讀取該文件，用其中的配置來覆蓋默認(rèn)的配置文件。

下面通過一個(gè)案例來理解全部變量的持久化。

查看全局變量max_connections的值，結(jié)果如下：

show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 750   |
+-----------------+-------+

設(shè)置全局變量max_connections的值：

set persist max_connections = 1000;

重啟MySQL服務(wù)器，再次查詢max_connections的值：

show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000   |
+-----------------+-------+

7、綜合案例

1. 創(chuàng)建一個(gè)名稱為sch的數(shù)據(jù)表，表結(jié)構(gòu)如表所示，將表中的數(shù)據(jù)插入到sch表中。

創(chuàng)建一個(gè)sch表，并且向sch表中插入表格中的數(shù)據(jù)，代碼如下：

CREATE TABLE sch( 
	id INT(10),
	name VARCHAR(50),
	glass VARCHAR(50)
);
INSERT INTO sch VALUE(1,'xiaoming','glass 1'), (2,'xiaojun','glass 2');

2. 創(chuàng)建一個(gè)存儲(chǔ)函數(shù)，用來統(tǒng)計(jì)表sch中的記錄數(shù)。

創(chuàng)建一個(gè)可以統(tǒng)計(jì)表格內(nèi)記錄條數(shù)的存儲(chǔ)函數(shù)，函數(shù)名為count_sch()，代碼如下：

create function count_sch()
returns int
return (
	select count(*)
	from sch
);

執(zhí)行的結(jié)果如下：

select count_sch();
+-------------+
| count_sch() |
+-------------+
|           2 |
+-------------+

創(chuàng)建的儲(chǔ)存函數(shù)名稱為count_sch，通過SELCET count_sch()查看函數(shù)執(zhí)行的情況，這個(gè)表中只有兩條記錄，得到的結(jié)果也是兩條記錄，說明存儲(chǔ)函數(shù)成功的執(zhí)行。

創(chuàng)建一個(gè)存儲(chǔ)過程，通過調(diào)用存儲(chǔ)函數(shù)的方法來獲取表sch中的記錄數(shù)和sch表中id的和。

創(chuàng)建一個(gè)存儲(chǔ)過程add_id，同時(shí)使用前面創(chuàng)建的存儲(chǔ)函數(shù)返回表sch中的記錄數(shù)，計(jì)算出表中所有的id之和。代碼如下：

create procedure add_id(out count int)
begin
	declare itmp int;
	declare cur_id cursor for select id from sch;
	declare exit handler for not found close cur_id;
	select count_sch() into count;
	set @sum = 0;
	open cur_id;
	repeat
	fetch cur_id into itmp;
	if itmp < 10
	then set @sum = @sum + itmp;
	end if;
	until 0 end repeat;
	close cur_id;
end ;

這個(gè)存儲(chǔ)過程的代碼中使用到變量的聲明、光標(biāo)、流程控制、在存儲(chǔ)過程中調(diào)用存儲(chǔ)函數(shù)等知識(shí)點(diǎn)，結(jié)果應(yīng)該是兩條記錄，id之和為3，記錄條數(shù)是通過上面的存儲(chǔ)函數(shù)count_sch()獲取的，是在存儲(chǔ)過程中調(diào)用了存儲(chǔ)函數(shù)。代碼的執(zhí)行情況如下：

call add_id(@a);
select @a, @sum;
+----+------+
| @a | @sum |
+----+------+
|  2 |    3 |
+----+------+

表sch中只有兩條記錄，所有id之和為3，和預(yù)想的執(zhí)行結(jié)果完全相同。這個(gè)存儲(chǔ)過程創(chuàng)建了一個(gè)cur_id的光標(biāo)，使用這個(gè)光標(biāo)來獲取每條記錄的id，使用REPEAT循環(huán)語句來實(shí)現(xiàn)所有id號(hào)相加。

8、常見問題

8.1、MySQL存儲(chǔ)過程和函數(shù)有什么區(qū)別？

在本質(zhì)上它們都是存儲(chǔ)程序。

• 函數(shù)只能通過return語句返回單個(gè)值或者表對(duì)象；
• 存儲(chǔ)過程不允許執(zhí)行return，但是可以通過out參數(shù)返回多個(gè)值。
• 函數(shù)限制比較多，不能用臨時(shí)表，只能用表變量，還有一些函數(shù)都不可用等；
• 存儲(chǔ)過程的限制相對(duì)比較少。
• 函數(shù)可以嵌入SQL語句中使用，可以在SELECT語句中作為查詢語句的一個(gè)部分調(diào)用；
• 存儲(chǔ)過程一般作為一個(gè)獨(dú)立的部分來執(zhí)行；

8.2、存儲(chǔ)過程中的代碼可以改變嗎？

目前，MySQL還不提供對(duì)已存在的存儲(chǔ)過程代碼的修改，如果必須要修改存儲(chǔ)過程，必須使用DROP語句刪除之后再重新編寫代碼，或者創(chuàng)建一個(gè)新的存儲(chǔ)過程。

8.3、在存儲(chǔ)過程中可以調(diào)用其他存儲(chǔ)過程嗎？

存儲(chǔ)過程包含用戶定義的SQL語句集合，可以使用CALL語句調(diào)用存儲(chǔ)過程。當(dāng)然，在存儲(chǔ)過程中也可以使用CALL語句調(diào)用其他存儲(chǔ)過程，但是不能使用DROP語句刪除其他存儲(chǔ)過程。

8.4、存儲(chǔ)過程的參數(shù)不要與數(shù)據(jù)表中的字段名相同。

在定義存儲(chǔ)過程參數(shù)列表時(shí)，應(yīng)注意把參數(shù)名與數(shù)據(jù)庫表中的字段名區(qū)別開來，否則將出現(xiàn)無法預(yù)期的結(jié)果。

8.5、存儲(chǔ)過程的參數(shù)可以使用中文嗎？

一般情況下，可能會(huì)出現(xiàn)存儲(chǔ)過程中傳入中文參數(shù)的情況，例如某個(gè)存儲(chǔ)過程根據(jù)用戶的名字查找該用戶的信息，傳入的參數(shù)值可能是中文。這時(shí)需要在定義存儲(chǔ)過程的時(shí)候在后面加上character set gbk，不然調(diào)用存儲(chǔ)過程使用中文參數(shù)會(huì)出錯(cuò)，比如定義userInfo存儲(chǔ)過程，代碼如下：

create procedure userInfo (in u_name varchar(50) character set gbk, out u_age int)

到此這篇關(guān)于MySQL 8.0存儲(chǔ)過程和函數(shù)創(chuàng)建的使用的文章就介紹到這了,更多相關(guān)mysql存儲(chǔ)過程和函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論