Detailed explanation of MySQL's creation of stored procedures (stored procedures and functions)

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语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量，如系统变量或者用户变量。

【例5】声明3个变量，分别为var1，var2和var3，数据类型为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语法把选定的列直接存储到对应位置的变量。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)定义条件和处理程序

特定条件需要特定处理。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样就增强了存储程序处理问题的能力，避免程序异常停止运行。

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为数值类型错误代码

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

这个语句指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的declare handler语句中。

【例7】定义"error 1148(42000)"错误，名称为command_not_allowed。可以用两种不同的方法来定义，代码如下：

[方法一]：使用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为错误处理方式，参数取3个值：continue、exit和undo。
• continue表示遇到错误不处理，继续执行；
• 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匹配数值类型错误代码

sp_statement参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程或函数。

【例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:先定义条件,然后调用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"，执行continue操作，并且输出"NO_SUCH_TABLE"信息。
第二种，捕获mysql_error_code值。如果遇到mysql_error_code值为1146，就执行continue操作，并且输出"NO_SUCH_TABLE"信息。
第三种，先定义条件再调用条件。这里先定义no_such_table条件，遇到1146错误就执行continue操作。
第四种，使用sqlwarning。sqlwarning捕获所有以01开头的sqlstate_value值，然后执行exit操作，并且输出"ERROE"信息。
第五种，使用not found。not found捕获所有以02开头的sqlstate_value值，然后执行exit操作，并且输出"NO_SUCH_TABLE"信息。
第六种，使用SQLEXCEPTION。sqlexception捕获所有没有被sqlwarning或not found捕获的sqlstate_value值，然后执行exit操作，并且输出"ERROR"信息。

【例9】定义条件和处理程序，具体执行的过程如下：

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)[调用存储过程]mysql> delimiter ;mysql> call handlerdemo();Query OK, 0 rows affected (0.08 sec)[查看调用过程结果]mysql> select @x;+------+| @x   |+------+|    3 |+------+1 row in set (0.00 sec)

可以看到，@x 是一个用户变量，执行结果@x等于3，这表明MySQL被执行到程序末尾。

• "var_name"表示用户变量，使用set语句为其赋值。用户变量与连接有关，一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。

(5)光标的使用

MySQL中光标只能在存储过程和函数中使用。

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

1.声明光标

MySQL中使用declare关键字来声明光标，语法形式如下：

declare cursor_name cursor for select_statement

其中，cursor_name参数表示光标的名称；select_statement表示select语句的内容，返回一个用于创建光标的结果集。

【例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 ] ... {参数名称}

其中，cursor_name参数表示光标的名称；var_name表示将光标中的select语句查询出来的信息存入该参数中，var_name必须在声明光标之前就定义好。

【例12】使用名称为cursor_fruit的光标。将查询出来的数据存入fruit_name和fruit_price两个变量中，代码如下:

fetch cursor_fruit into fruit_name,fruit_price;

4.关闭光标
关闭光标的语法格式:

close cursor_name(光标名称)

这个语句关闭先前打开的光标。
如果未被明确地关闭，那么光标将在它被声明的复合语句的末尾被关闭。

【例13】关闭名称为cursor_fruit的光标，代码如下：

close cursor_fruit;

(6)流程控制的使用

流程控制语句用来根据条件控制语句的执行。MySQL中用来构造控制流程的语句有IF语句、case语句、loop语句、leave语句、iterate语句、repeat语句和while语句。每个流程中可能包含一个单独语句，或者是使用begin…end构造的符合语句，构造可以被嵌套。

1.if语句

if语句包含多个条件判断，根据判断的结果为true或false执行相应的语句，语法格式如下：

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

如果expr_condition求值为真，相应的SQL语句列表被执行；如果没有expr_condition匹配，则else子句里的语句列表被执行。statement_list列表可包括一个或多个语句。

MySQL中还有一个if()函数，它不同于这里描述的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来结束。

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语句会被执行
• when_value表示表达式可能的值。
• 如果某个when_value表达式和case_expr表达式结果相同，则执行对应的then关键字后面的statement_list中的语句。
• statement_list表示不同when_value值的执行语句。

【例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表示不同条件的执行语句

该语句中，when语句将被逐个执行，直到某个expr_condition表达式为真，则执行对应then关键字后面的statement_list语句。如果没有条件匹配，else子句里的语句被执行。

注意：存储程序中的case语句和case控制流程函数的区别：
存储程序中的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循环语句用来重复执行某些语句，与if和case语句相比，loop只是创建一个循环操作过的过程，并不进行条件判断。退出循环过程使用leave子句。loop语法格式如下:

[loop_label:] loop
	statement_listend loop [loop_label]

loop_label表示loop语句的标注名称，该参数可省略。statement_list参数表示需要循环执行的语句。

【例17】使用loop语句进行循环操作，id值小于等于10之前，将重复执行循环过程，SQL语句如下：

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

该示例循环执行id加1的操作。当id值小于10时，循环重复执行。当id值大于或等于10时，使用leave语句退出循环。loop循环都以end loop结束。

4.leave语句

leave语句用来退出任何被标注的流程控制构造，leave语句基本格式如下：

leave label

其中，label参数表示循环的标志。leave和begin…end或循环一起被使用。

【例18】使用leave语句退出循环，代码如下：

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

该示例循环执行count加1的操作，当count的值等于50时，使用leave语句跳出循环。

5.iterate语句

iterater label语句将执行顺序转到语句段开头处，语法格式如下：

iterate label

iterate只可以出现在loop、repeat和while语句内。iterate的意思为"再次循环"，label参数表示循环的标志。iterate语句必须跟在循环标志前面。

【例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时重复执行p1加1操作；当p1大于等于10并且小于等于20时，打印消息"p1 is between 10 and 20";当p1大于20时，退出循环。

6.repeat语句

repeat语句创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，若表达式为真，则循环结束；否则重复执行循环中的语句。repeat语句的语法格式如下：

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

repeat_label为repeat语句的标注名称，该参数可以省略；repeat语句内的语句或语句群被重复，直至expr_condition为真。

【例20】repeat语句示例，id值等于10之前，将重复执行循环过程，代码如下：

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

该示例循环执行id加1的操作。当id值小于10时，循环重复执行；当id值大于或者等于10时，退出循环。repeat循环都以end repeat结束。

7.while语句

while语句创建一个带条件判断的循环过程，与repeat不同，while在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。while语句的基本格式如下：

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

• while_label为while语句的标注名称
• expr_condition为进行判断的表达式，如果表达式结果为真，while语句内的语句或语句群被执行，直至expr_condition为假，退出循环。

【例21】while语句示例，i值小于10时，将重复执行循环过程，代码如下：

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

相关免费学习推荐：mysql数据库(视频)

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!