Simply put, a stored procedure is a combination of one or more SQL statements, which can be regarded as a batch file, but its role is not limited to batch processing.

(1)Create stored procedure
(2)Create stored function
(3)Use of variables
(4)Define conditions and handlers
(5)Use of cursor
(6) Use of process control

(Free learning recommendation: mysql video tutorial)

(1) Create a stored procedure

To create a stored procedure, you need to use the create procedure statement. The basic syntax format is as follows:

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

create procedure is Keywords used to create stored functions; sp_name is the name of the stored procedure; proc_parameter is the parameter list of the stored procedure. The list format is as follows:

[in?|?out?|?inout]?param_name?type

• in represents the input parameter
• out represents Output parameters
• inout indicates that it can be input or output.
• param_name indicates the parameter name; type indicates the type of the parameter.

characteristics specifies the characteristics of the stored procedure, which are as follows Value:

• language SQL: Description The routine_body part is composed of SQL statements. The language supported by the current system is SQL, and SQL is the only value of the language attribute.

• [not] deterministic: Indicates whether the result of stored procedure execution is correct. Deterministic means that the same input will get the same output every time the stored procedure is executed; while not deterministic means that the same input may get different outputs. The default is not deterministic.

• {contains SQL | no SQL | reads SQL date | modifies SQL date }: Indicates the restrictions on the use of SQL statements by subprograms. contains SQL indicates that the subroutine contains SQL statements; no SQL indicates that the subroutine does not contain SQ; reads SQL data indicates that the subroutine contains statements for reading data; modifies SQL data indicates that the subroutine contains statements for writing data. Defaults to contains SQL.

• SQL security {definer | invoker}: Specify who has permission to execute. definer means that only the definer can execute it; invoker means that the caller with permission can execute it. The default is definer.

• comment ‘string’: Comment information, which can be used to describe stored procedures or functions.

routine_body is the content of the SQL code. You can use begin...end to indicate the beginning and end of the SQL code.

[Example 1] Create a stored procedure for viewing the fruits table. The code statement is as follows:

create?procedure?proc()
	BEGIN
	select?*?from?fruits;
	END?;

This code creates a stored procedure for viewing the fruits table. The code execution process is as follows:

mysql>?delimiter?//mysql>?create?procedure?Proc()
????->?begin
????->?select?*?from?fruits;
????->?end?//Query?OK,?0?rows?affected?(0.36?sec)mysql>?delimiter?;

• Tip: The function of the "delimiter //" statement is to set the end symbol of MySQL to //, because the default statement end symbol of MySQL is a semicolon ";". This is done to avoid conflicts with SQL in stored procedures. The statement terminators conflict. After the stored procedure is defined, use "delimiter;" to restore the default terminator. When using the delimiter command, you should avoid using the backslash "" because the backslash is an escape character in MySQL.

[Example 2] Create a stored procedure named CountProc. The code is as follows:

create?procedure?CountProc?(OUT?paraml?int)beginselect?count(*)?into?paraml?from?fruits;end;

The above code creates a stored procedure to obtain the number of records in the fruits table. The name is CountProc. After count(*) calculation, the result is put into parameter paraml. The execution results of the code are as follows:

mysql>?delimiter??//mysql>?create?procedure?CountProc(OUT?paraml?int?)
????->?begin
????->?select?count(*)?into?paraml?from?fruits;
????->?end?//Query?OK,?0?rows?affected?(0.08?sec)mysql>?delimiter?;

(2) Create a stored function

To create a stored function, you need to use the create function statement. The basic syntax is as follows:

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

• create function is the keyword used to create a stored function
• func_name represents the name of the stored function
• func_parameter is the parameter list of the stored procedure. The parameter list format is: [in | out | inout ] param_name type

Among them, in represents the input parameter, out represents the output parameter, inout represents both input and output param_name represents the parameter name, type represents the type of the parameter; returns type statement represents The type of data returned by the function; characteristic specifies the characteristics of the stored function, and the value is the same as when creating the stored procedure.

[Example 3] Create a storage function named NameByZip. This function returns the query result of the select statement. The numerical type is string type. The code is as follows:

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

The execution result of the code is as follows;

mysql>?delimiter?//mysql>?create?function?NameByZip()
????->?returns?char(50)
????->?return?(select?s_name?from?suppliers?where?s_call?=?'48075');
????->?//Query?OK,?0?rows?affected?(0.06?sec)mysql>?delimiter;

If a ruturn statement in a stored function returns a value of a type different from the type specified in the function's returns clause, the return value will be coerced to the appropriate type.

• Note: Specifying parameters as in, out or inout is only legal for procedures. (The function always defaults to the in parameter.) The returns clause can only be specified for the function and is mandatory for the function. It is used to specify the return type of the function, and the function body must contain a return value statement.

(3) Use of variables

Variables can be declared and used in subprograms, and the scope of these variables is in the begin...end program.

1. Define variables

Use the declar statement to define variables in the stored procedure. The syntax format is as follows:

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

var_name is the name of the local variable. The default value clause provides a default value for the variable. In addition to being declared as a constant, a value can also be specified as an expression. If there is no default clause, the initial value is null.

【例4】定義名稱為myparam的變量，類型為int類型，默認值為100，代碼如下：

declare?myparam?int?default?100;

2.為變量賦值

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

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

【例5】聲明3個變量，分別為var1，var2和var3，數(shù)據(jù)類型為int，使用set為變量賦值，代碼如下：

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

MySQL中還可以通過select…into為一個或多個變量賦值，語句如下：

select?col_name[,...]?into?var_name[,...]?table_expr;

這個select語法把選定的列直接存儲到對應(yīng)位置的變量。col_name表示字段名稱;var_name表示定義的變量名稱；table_expr表示查詢條件表達式，包括表名稱和where子句。

【例6】聲明變量fruitname和fruitprice，通過select…into語句查詢指定記錄并為變量賦值,代碼如下:

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

(4)定義條件和處理程序

特定條件需要特定處理。定義條件是事先定義程序執(zhí)行過程中遇到的問題，處理程序定義了在遇到這些問題時應(yīng)當采取的處理方式，并且保證存儲過程或函數(shù)在遇到警告或錯誤時能繼續(xù)執(zhí)行。這樣就增強了存儲程序處理問題的能力，避免程序異常停止運行。

1.定義條件
定義條件使用declare語句，語法格式如下：

declare?conditon_name?Condition?for?[condition_type][condition_type];SQLSTATE?[value]?sqlstate_value?|?mysql_error_code

• condition_name表示條件的名稱
• condition_type表示條件的類型
• sqlstate_value和mysql_error_code都可以表示MySQL的錯誤
• sqlstate_value為長度為5的字符類型錯誤代碼
• mysql_error_code為數(shù)值類型錯誤代碼

例如：ERROR1142(42000)中，sqlstate_value的值是42000，mysql_error_code的值為1142。

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

【例7】定義"error 1148(42000)"錯誤，名稱為command_not_allowed?？梢杂脙煞N不同的方法來定義，代碼如下：

[方法一]：使用sqlstate_valuedeclare?command_not_allowed?condition?for?sqlstate?'42000'[]方法二]:使用mysql_error_codedeclare?command_not_allowed?condition?for?1148

2.定義處理程序

定義處理程序時，使用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?found	|sqlexception	|mysql_error_code

其中，

• handler_type為錯誤處理方式，參數(shù)取3個值：continue、exit和undo。
• continue表示遇到錯誤不處理，繼續(xù)執(zhí)行；
• exit遇到錯誤馬上退出；
• undo表示遇到錯誤后撤回之前的操作，MySQL中暫時不支持這樣的操作。

condition_value表示錯誤類型，可以有以下取值：

• sqlstate[value] sqlstate_value包含5個字符串錯誤值
• condition_name表示declare condition定義的錯誤條件名稱
• sqlwarning匹配所有以01開頭的sqlstate錯誤代碼
• notfound 匹配所有以02開頭的sqlstate錯誤代碼
• sqlexception匹配所有沒有被sqlwarning或not found捕獲的sqlstate錯誤代碼
• mysql_error_code匹配數(shù)值類型錯誤代碼

sp_statement參數(shù)為程序語句段，表示在遇到定義的錯誤時，需要執(zhí)行的存儲過程或函數(shù)。

【例8】定義處理程序的幾種方式如下：

方法1：捕獲sqlstate_valuedeclare?continue?handler?for?sqlstate?'42S02'?set?@info='No_SUCH_TABLE';方法2:捕獲mysql_error_codedeclare?continue?handler?for?1146?set?@info='No_SUCH_TABLE';方法3:先定義條件,然后調(diào)用declare?no_such_table?condition?for?1146;declare?continue?handler?for?NO_SUCH_TABLE?SET?@info='NO_SUCH_TABLE';方法4:使用sqlwarningdeclare?exit?handler?for?sqlwarning?set?@info='ERROR';方法5:使用not?founddeclare?exit?handler?for?not?found?set?@info='?NO_SUCH_TABLE?';方法6:使用sqlexceptiondeclare?exit?handler?forsqlexception?set?@info='ERROR';

上述代碼是6種定義處理程序的方法。

第一種，捕獲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錯誤就執(zhí)行continue操作。
第四種，使用sqlwarning。sqlwarning捕獲所有以01開頭的sqlstate_value值，然后執(zhí)行exit操作，并且輸出"ERROE"信息。
第五種，使用not found。not found捕獲所有以02開頭的sqlstate_value值，然后執(zhí)行exit操作，并且輸出"NO_SUCH_TABLE"信息。
第六種，使用SQLEXCEPTION。sqlexception捕獲所有沒有被sqlwarning或not found捕獲的sqlstate_value值，然后執(zhí)行exit操作，并且輸出"ERROR"信息。

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

mysql>?create?table?test.t(s1?int,primary?key?(s1));Query?OK,?0?rows?affected?(0.14?sec)mysql>?delimiter?//mysql>?create?procedure?handlerdemo()
????->?begin
????->?declare?continue?handler?for?sqlstate?'23000'?set?@x2=1;
????->?set?@x?=1;
????->?insert?into?test.t?values(1);
????->?set?@x=2;
????->?insert?into?test.t?values(1);
????->?set?@x=3;
????->?end;
????->?//Query?OK,?0?rows?affected?(0.06?sec)[調(diào)用存儲過程]mysql>?delimiter?;mysql>?call?handlerdemo();Query?OK,?0?rows?affected?(0.08?sec)[查看調(diào)用過程結(jié)果]mysql>?select?@x;+------+|?@x???|+------+|????3?|+------+1?row?in?set?(0.00?sec)

可以看到，@x 是一個用戶變量，執(zhí)行結(jié)果@x等于3，這表明MySQL被執(zhí)行到程序末尾。

• "var_name"表示用戶變量，使用set語句為其賦值。用戶變量與連接有關(guān)，一個客戶端定義的變量不能被其他客戶端看到或使用。當客戶端退出時，該客戶端連接的所有變量將自動釋放。

(5)光標的使用

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

查詢語句可能返回多條記錄，如果數(shù)據(jù)量非常大，需要在存儲過程和存儲函數(shù)中使用光標來逐條讀取查詢結(jié)果集中的記錄。光標必須在聲明處理程序之前被聲明，并且變量和條件還必須在聲明光標或處理程序之前被聲明。

1.聲明光標

MySQL中使用declare關(guān)鍵字來聲明光標，語法形式如下：

declare?cursor_name?cursor?for?select_statement

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

【例10】聲明名稱為cursor_fruit的光標，代碼如下：

declare?cursor_fruit?cursor?for?select?f_name,f_price?from?fruits;

該代碼中光標名稱為cursor_fruit，select語句部分從fruits表匯總查詢出f_name和f_price字段的值。

2.打開光標

open?cursor_name{光標名稱}

這個語句打開先前聲明的名稱為cursor_name的光標。

【例11】打開名稱為cursor_fruit的光標，代碼如下：

open?cursor_fruit?;

3.使用光標

使用光標的語法格式:

fetch?cursor_name?into?var_name?[，var_name?]?...?{參數(shù)名稱}

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

【例12】使用名稱為cursor_fruit的光標。將查詢出來的數(shù)據(jù)存入fruit_name和fruit_price兩個變量中，代碼如下:

fetch?cursor_fruit?into?fruit_name,fruit_price;

4.關(guān)閉光標
關(guān)閉光標的語法格式:

close?cursor_name(光標名稱)

這個語句關(guān)閉先前打開的光標。
如果未被明確地關(guān)閉，那么光標將在它被聲明的復(fù)合語句的末尾被關(guān)閉。

【例13】關(guān)閉名稱為cursor_fruit的光標，代碼如下：

close?cursor_fruit;

(6)流程控制的使用

流程控制語句用來根據(jù)條件控制語句的執(zhí)行。MySQL中用來構(gòu)造控制流程的語句有IF語句、case語句、loop語句、leave語句、iterate語句、repeat語句和while語句。每個流程中可能包含一個單獨語句，或者是使用begin…end構(gòu)造的符合語句，構(gòu)造可以被嵌套。

1.if語句

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

if?expr_condition?then?statement_list	[elseif?expr_condition?then?statement_list]...
	[else?statement_list]end?if

如果expr_condition求值為真，相應(yīng)的SQL語句列表被執(zhí)行；如果沒有expr_condition匹配，則else子句里的語句列表被執(zhí)行。statement_list列表可包括一個或多個語句。

MySQL中還有一個if()函數(shù)，它不同于這里描述的if語句。

【例14】if語句示例

if?val?is?null
	then?select?‘val?is?null’;
	else?select?'val?is?not?null';end?if

該示例判斷val值是否為空，如果為空輸出字符串"val is null"；否則輸出字符串"val is not null"。if語句都需要使用end if來結(jié)束。

2.case語句

case是另一個進行條件判斷的語句，有兩種語句格式，第一種：

case?case_expr	when?when_value?then?statement_list	[when?when_value?then?statement_list]...
	[else?statement_list]end?case

• case_expr表示條件判斷的表達式，決定了哪一個when語句會被執(zhí)行
• when_value表示表達式可能的值。
• 如果某個when_value表達式和case_expr表達式結(jié)果相同，則執(zhí)行對應(yīng)的then關(guān)鍵字后面的statement_list中的語句。
• statement_list表示不同when_value值的執(zhí)行語句。

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

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;

當val值為1時，輸出字符串"val is 1";當val值為2時，輸出字符串"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表示條件判斷語句
• statement_list表示不同條件的執(zhí)行語句

該語句中，when語句將被逐個執(zhí)行，直到某個expr_condition表達式為真，則執(zhí)行對應(yīng)then關(guān)鍵字后面的statement_list語句。如果沒有條件匹配，else子句里的語句被執(zhí)行。

注意：存儲程序中的case語句和case控制流程函數(shù)的區(qū)別：
存儲程序中的case語句不能有else null子句，并且用end case替代end來終止。

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

case
	when?val?is?null?then?select??‘val?is?null’;
	when?val?< 0 then  select &#39;val is less than 0&#39;;
	when val >?0?then?select?'val?is?greater?than?0';
	else?select?'val?is?0';end?case;

當val值為空時，輸出字符串"val is null";當val值小于0時，輸出字符串"val is less than 0";當val值大于0時，輸出字符串"val is greater than 0";否則輸出字符串"val is 0"。

3.loop語句

loop循環(huán)語句用來重復(fù)執(zhí)行某些語句，與if和case語句相比，loop只是創(chuàng)建一個循環(huán)操作過的過程，并不進行條件判斷。退出循環(huán)過程使用leave子句。loop語法格式如下:

[loop_label:]?loop
	statement_listend?loop?[loop_label]

loop_label表示loop語句的標注名稱，該參數(shù)可省略。statement_list參數(shù)表示需要循環(huán)執(zhí)行的語句。

【例17】使用loop語句進行循環(huán)操作，id值小于等于10之前，將重復(fù)執(zhí)行循環(huán)過程，SQL語句如下：

declare?id?int?default?10add_loop:loopset?id?=?id?+1;
	if?>=10?then?leave?add_loop;
	end?if;end?loop?add_?loop;

該示例循環(huán)執(zhí)行id加1的操作。當id值小于10時，循環(huán)重復(fù)執(zhí)行。當id值大于或等于10時，使用leave語句退出循環(huán)。loop循環(huán)都以end loop結(jié)束。

4.leave語句

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

leave?label

其中，label參數(shù)表示循環(huán)的標志。leave和begin…end或循環(huán)一起被使用。

【例18】使用leave語句退出循環(huán)，代碼如下：

add_num:loopset?@count=@count+1;if?@count=50?then?leave?add_num;end?loop?add_num;

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

5.iterate語句

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

iterate?label

iterate只可以出現(xiàn)在loop、repeat和while語句內(nèi)。iterate的意思為"再次循環(huán)"，label參數(shù)表示循環(huán)的標志。iterate語句必須跟在循環(huán)標志前面。

【例19】iterate語句示例：

create?procedure?doiterate()begin
?declare?p1?int?default?0;
?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時重復(fù)執(zhí)行p1加1操作；當p1大于等于10并且小于等于20時，打印消息"p1 is between 10 and 20";當p1大于20時，退出循環(huán)。

6.repeat語句

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

[repeat_label:]?repeat
	statement_list
until?expr_conditionend?repeat?[repeat_label]

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

【例20】repeat語句示例，id值等于10之前，將重復(fù)執(zhí)行循環(huán)過程，代碼如下：

declare?id?int?default?0；repeatset?id?=?id?+?1；
until?id?>=?10end?repeat;

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

7.while語句

while語句創(chuàng)建一個帶條件判斷的循環(huán)過程，與repeat不同，while在執(zhí)行語句執(zhí)行時，先對指定的表達式進行判斷，如果為真，就執(zhí)行循環(huán)內(nèi)的語句，否則退出循環(huán)。while語句的基本格式如下：

[while_label:]?while?expr_condition?do
	statement_listend?while?[while_label]

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

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

declare?i?int?default?0；while?i?<10?doset?i?=?i?+?1;end?while;

相關(guān)免費學(xué)習(xí)推薦：mysql數(shù)據(jù)庫(視頻)

The above is the detailed content of Detailed explanation of MySQL's creation of stored procedures (stored procedures and functions). For more information, please follow other related articles on the PHP Chinese website!