MySQL 8.0——存储过程和函数

简单地说，存储过程就是一条或者多条SQL语句的集合，可视为批文件，但是其作用不仅限于批处理。本章主要介绍如何创建存储过程和存储函数以及变量的使用，如何调用、查看、修改、删除存储过程和存储函数等。

1、创建存储过程和函数

存储程序可以分为存储过程和函数。在MySQL中，创建存储过程和函数使用的语句分别是CREATE PROCEDURE和CREATEFUNCTION。

使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用（引用函数名）​，也能返回标量值。存储过程也可以调用其他存储过程。

1.1、创建存储过程

创建存储过程，需要使用CREATEPROCEDURE语句，基本语法格式如下：

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

• CREATE PROCEDURE为用来创建存储函数的关键字；
• sp_name为存储过程的名称；
• proc_parameter为指定存储过程的参数列表，列表形式如下：[in | out | inout ] param_name type
  • IN表示输入参数
  • OUT表示输出参数
  • INOUT表示既可以输入也可以输出；
  • param_name表示参数名称；
  • type表示参数的类型，该类型可以是MySQL数据库中的任意类型;
• characteristics指定存储过程的特性，有以下取值：
  • LANGUAGE SQL：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL。SQL是LANGUAGE特性的唯一值。
  • [NOT] DETERMINISTIC：指明存储过程执行的结果是否正确。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOTDETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。
  • { CONTAINS SQL | NO SQL |READS SQL DATA | MODIFIESSQL DATA }：指明子程序使用SQL语句的限制。CONTAINS SQL表明 子程序包含SQL语句，但是不包含读写数据的语句；NO SQL表明子程序不包含SQL语句；READS SQLDATA说明子程序包含读数据的语句；MODIFIES SQL DATA表明子程序包含写数据的语句。默认情况下，系统会指定为CONTAINSSQL。
  • SQL SECURITY { DEFINER |INVOKER }：指明谁有权限来执行。DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下，系统 指定为DEFINER。
  • COMMENT 'string'：注释信息，可以用来描述存储过程或函数。
• routine_body是SQL代码的内容，可以用BEGIN…END来表示SQL代码的开始和结束。

编写存储过程并不是一件简单的事情，可能存储过程中需要复杂的SQL语句，并且要有创建存储过程的权限；但是使用存储过程将简化操作，减少冗余的操作步骤，同时，还可以减少操作过程中的失误，提高效率，因此存储过程是非常有用的，而且应该尽可能地学会使用。

下面的代码演示了存储过程的内容，名称为AvgFruitPrice，返回所有水果的平均价格，输入代码如下：

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

上述代码中，此存储过程名为AvgFruitPrice，使用CREATE PROCEDUREAvgFruitPrice ()语句定义。此存储过程没有参数，但是后面的()仍然需要。BEGIN和END语句用来限定存储过程体，过程本身仅是一个简单的SELECT语句（AVG为求字段平均值的函数）​。

创建查看fruits表的存储过程，代码如下：

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

这行代码创建了一个查看fruits表的存储过 程，每次调用这个存储过程的时候都会执行SELECT语句查看表的内容，代码的执行过程如下：

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 |
+------+------+------------+---------+

这个存储过程和使用SELECT语句查看表的效果得到的结果是一样的，当然存储过程也可以是很多语句复杂的组合，就好像这个例子刚开始给出的那个语句一样，其本身也可以调用其他的函数来组成更加复杂的操作。

“DELIMITER //”语句的作用是将MySQL的结束符设置为//，因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符，并以“END //”结束存储过程。存储过程定义完毕之后再使用“DELIMITER ;”恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。

创建名称为CountProc的存储过程，代码如下：

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

上述代码的作用是创建一个获取fruits表记录条数的存储过程，名称是CountProc，COUNT(*)计算后把结果放入参数param1中。

当使用DELIMITER命令时，应该避免使用反斜杠（‘\’）字符，因为反斜线是MySQL的转义字符。

1.2、创建存储函数

创建存储函数，需要使用CREATEFUNCTION语句，基本语法格式如下：

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

• CREATE FUNCTION为用来创建存储函数的关键字；
• func_name表示存储函数的名称；
• func_parameter为存储过程的参数列表，参数列表形式如下：[in | out | inout] param_name type
  • IN表示输入参数
  • OUT表示输出参数
  • INOUT表示既可以输入也可以输出；
  • param_name表示参数名称；
  • type表示参数的类型，该类型可以是MySQL数据库中的任意类型;
• RETURNS type语句表示函数返回数据的类型；
• characteristic指定存储函数的特性，取值与创建存储过程时相同

创建存储函数，名称为NameByZip，该函数返回SELECT语句的查询结果，数值类型为字符串型，代码如下：

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

如果在存储函数中的RETURN语句返回一个类型不同于函数的RETURNS子句中指定类型的值，返回值将被强制为恰当的类型。比如，如果一个函数返回一个ENUM或SET值，但是RETURN语句返回一个整数，对于SET成员集相应的ENUM成员，从函数返回的值是字符串。

指定参数为IN、OUT或INOUT只对PROCEDURE是合法的。​（FUNCTION中总是默认为IN参数）​。RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

1.3、变量的使用

1.3.1、定义变量

在存储过程中使用DECLARE语句定义变量，语法格式如下：

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

• var_name为局部变量的名称。
• DEFAULTvalue子句给变量提供一个默认值。值除了可以被声明为一个常数之外，还可以被指定为一个表达式。如果没有DEFAULT子句，初始值为NULL。

定义名称为myparam的变量，类型为INT类型，默认值为100，代码如下：

declare myparam int default 100;

1.3.2、为变量赋值

定义变量之后，为变量赋值可以改变变量的默认值。在MySQL中，使用SET语句为变量赋值，语法格式如下：

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

在存储程序中的SET语句是一般SET语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量，如系统变量或者用户变量。

在存储程序中的SET语句作为预先存在的SET语法的一部分来实现，允许SET a=x, b=y, …这样的扩展语法。其中，不同的变量类型（局域变量和全局变量）可以被混合起来。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。

声明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[, ...] int 0 var_name [, ...] table_expr;

这个SELECT语法把选定的列直接存储到对应位置的变量。col_name表示字段名称；var_name表示定义的变量名称；table_expr表示查询条件表达式，包括表名称和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、定义条件和处理程序

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

1.4.1、定义条件

定义条件使用DECLARE语句，语法格式如下：

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的错误，sqlstate_value为长度为5的字符串类型错误代码，MySQL_error_code为数值类型错误代码。例如，在ERROR 1142（42000）中，sqlstate_value的值是42000，MySQL_error_code的值是1142。

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

定义"ERROR 1148(42000)"错误，名称为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、定义处理程序

定义处理程序时，使用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为错误处理方式，参数取3个值：CONTINUE、EXIT和UNDO。
• CONTINUE表示遇到错误不处理，继续执行；
• EXIT表示遇到错误马上退出；
• UNDO表示遇到错误后撤回之前的操作，MySQL中暂时不支持这样的操作。
• condition_value表示错误类型，可以有以下取值：
  • SQLSTATE [VALUE]sqlstate_value包含5个字符的字符串错误值；
  • condition_name表示DECLARE CONDITION定义的错误条件名称；
  • SQLWARNING匹配所有以01开头的SQLSTATE错误代码；
  • NOT FOUND匹配所有以02开头的SQLSTATE错误代码；
  • SQLEXCEPTION匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码。
  • MySQL_error_code匹配数值类型错误代码。
• sp_statement参数为程序语句段，表示在遇到定义的错误时需要执行的存储过程或函数。

定义处理程序的几种方式，代码如下：

//方法一：捕获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';

//方法三：先定义条件，然后调用
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”​，执行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操作，并且输出“ERROR”信息。
• 第五种方法是使用NOT FOUND。NOTFOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出“NO_SUCH_TABLE”信息。
• 第六种方法是使用SQLEXCEPTION。SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出“ERROR”信息。

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

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个用户变量，执行结果@x等于3，这表明MySQL被执行到程序的末尾。如果“DECLARE CONTINUE HANDLER FORSQLSTATE ‘23000’ SET @x2 = 1;”这1行不在，第2个INSERT因PRIMARY KEY强制而失败之后，MySQL可能已经采取默认（EXIT）路径，并且SELECT @x可能已经返 回2。

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

1.5、光标的使用

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和储存函数中使用光标来逐条读取查询结果集中的记录。应用程序可以根据需要滚动或浏览其中的数据。本节将介绍如何声明、打开、使用和关闭光标。

光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

1.5.1、声明光标

在MySQL中，使用DECLARE关键字来声明光标，其语法的基本形式如下：

declare cursor_name cursor for select_statement

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

声明名称为cursor_fruit的光标，代码如下：

declare cursor_fruit cursor for 
select f_name, f_price 
from fruits;

在上面的示例中，光标的名称为cur_fruit，SELECT语句部分从fruits表中查询出f_name和f_price字段的值。

1.5.2、打开光标

打开光标的语法如下：

open cursor_name{光标名称}

这个语句打开先前声明的名称为cursor_name的光标。

打开名称为cursor_fruit的光标，代码如下：

open cursor_fruit;

1.5.3、使用光标

使用光标的语法如下：

FETCH cursor_name INTO var_name [, var_name] ... {参数名称}

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

使用名称为cursor_fruit的光标将查询出来的数据存入fruit_name和fruit_price这两个变量中，代码如下：

fetch cursor_fruit into fruit_name, fruit_price;

上面的示例中，将光标cursor_fruit中用SELECT语句查询出来的信息存入fruit_name和fruit_price中。fruit_name和fruit_price必须在前面已经定义。

1.5.4、关闭光标

关闭光标的语法如下：

CLOSE cursor_name{光标名称}

这个语句关闭先前打开的光标。

如果未被明确地关闭，光标在它被声明的复合语句的末尾关闭。

关闭名称为cursor_fruit的光标，代码如下：

close cursor_fruit;

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

1.6、流程控制的使用

流程控制语句用来根据条件控制语句的执行。MySQL中用来构造控制流程的语句有IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句。

每个流程中可能包含一个单独语句，或者是使用BEGIN … END构造的复合语句，构造可以被嵌套。

1.6.1、IF语句

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

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

IF实现了一个基本的条件构造。如果expr_condition求值为真（TRUE）​，相应的SQL语句列表被执行；如果没有expr_condition匹配，则ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。

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来结束。

1.6.2、CASE语句

CASE是另一个进行条件判断的语句，有两种格式。

第1种格式如下：

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值的执行语句。

使用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;

当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流程控制语句的第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;

当val值为空，输出字符串“val is NULL”​；当val值小于0时，输出字符串“val is less than0”​；当val值大于0时，输出字符串“val isgreater than 0”​；否则输出字符串“val is0”​。

1.6.3、LOOP语句

LOOP循环语句用来重复执行某些语句，与IF和CASE语句相比，LOOP只是创建一个循环操作的过程，并不进行条件判断。LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子句）​，跳出循环过程。LOOP语句的基本格式如下：

[loop_label:] LOOP
	statement_list
END LOOP [loop_label]

• loop_label表示LOOP语句的标注名称，该参数可以省略；
• statement_list参数表示需要循环执行的语句;

使用LOOP语句进行循环操作，id值小于10时将重复执行循环过程，代码如下：

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;

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

1.6.4、LEAVE语句

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

LEAVE label

其中，label参数表示循环的标志。LEAVE和BEGIN … END或循环一起被使用。

使用LEAVE语句退出循环，代码如下：

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

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

1.6.5、ITERATE语句

ITERATE语句将执行顺序转到语句段开头处，语句基本格式如下：

ITERATE label

ITERATE只可以出现在LOOP、REPEAT和WHILE语句内。ITERATE的意思为“再次循环”​，label参数表示循环的标志。ITERATE语句必须跟在循环标志前面。

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

1.6.6、REPEAT语句

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

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

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

REPEAT语句示例，id值小于10时将重复执行循环过程，代码如下：

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

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

1.6.7、WHILE语句

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

WHILE语句的基本格式如下：

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

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

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

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

2、调用存储过程和函数

存储过程已经定义好了，接下来需要知道如何调用这些过程和函数。存储过程和函数有多种调用方法。存储过程必须使用CALL语句调用，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称，例如CALL dbname.procname。存储函数的调用与MySQL中预定义的函数的调用方式相同。

2.1、调用存储过程

存储过程是通过CALL语句进行调用的，语法如下：

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

CALL语句调用一个先前用CREATEPROCEDURE创建的存储过程，其中sp_name为存储过程名称，parameter为存储过程的参数。

定义名为CountProc1的存储过 程，然后调用这个存储过程。

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 ;

//调用
call CountProc1(101, @num);

//查看返回结果：
select @num;

+------+
| @num |
+------+
|    3 |
+------+

该存储过程返回了指定s_id=101的水果商提供的水果种类，返回值存储在num变量中，使用SELECT查看，返回结果为3。

2.2、调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于，存储函数是用户自己定义的，而内部函数是MySQL的开发者定义的。

定义存储函数CountProc2，然后调用这个函数，代码如下：

delimiter $$

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

delimiter ;

如果在创建存储函数中报错“youmight want to use the less safelog_bin_trust_function_creators variable”​，需要执行以下代码：SET GLOBAL log_bin_trust_function_creators = 1;

调用存储函数：

select CountProc2(101);

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

3、查看存储过程和函数

MySQL存储了存储过程和函数的状态信息:用户可以使用SHOW STATUS语句或SHOWCREATE语句来查看，也可直接从系统的information_schema数据库中查询。

3.1、使用SHOW STATUS语句查看存储过程和函数的状态

SHOW STATUS语句可以查看存储过程和函数的状态，其基本语法结构如下：

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

这个语句是一个MySQL的扩展，返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。如果没有指定样式，那么根据使用的语句，所有存储程序或存储函数的信息都会被列出。

其中，PROCEDURE和FUNCTION分别表示查看存储过程和函数；LIKE语句表示匹配存储过程或函数的名称。

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”语句获取数据库中所有名称以字母‘C’开头的存储过程的信息。通过上面的语句可以看到：这个存储函数所在的数据库为 test_db、存储函数的名称为CountProc等一些相关信息。

3.2、使用SHOW CREATE语句查看存储过程和函数的定义

除了SHOW STATUS之外，MySQL还可以使用SHOW CREATE语句查看存储过程和函数的状态。

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

这个语句是一个MySQL的扩展。类似于SHOW CREATE TABLE，它返回一个可用来重新创建已命名子程序的确切字符串。

• PROCEDURE和FUNCTION分别表示查看存储过程和函数；
• sp_name参数表示匹配存储过程或函数的名称;

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

执行上面的语句可以得到存储函数的名称为CountProc2，sql_mode为sql的模式，Create Function为存储函数的具体定义语句，还有数据库设置的一些信息。

3.3、从information_schema.Routines表中查看存储过程和函数的信息

MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语法形式如下：

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

• ROUTINE_NAME字段中存储的是存储过程和函数的名称；
• sp_name参数表示存储过程或函数的名称;

从Routines表中查询名称为CountProc2的存储函数的信息，代码如下：

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数据库下的Routines表中，存储所有存储过程和函数的定义。使用SELECT语句查询Routines表中的存储过程和函数的定义时，一定要使用ROUTINE_NAME字段指定存储过程或函数的名称。否则，将查询出所有的存储过程或函数的定义。如果有存储过程和存储函数名称相同，就需要同时指定ROUTINE_TYPE字段表明查询的是哪种类型的存储程序。

4、修改存储过程和函数

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

• sp_name参数表示存储过程或函数的名称；
• characteristic参数指定存储函数的特性，可能的取值有：
  • CONTAINS SQL，表示子程序包含SQL语句，但不包含读或写数据的语句。
  • NO SQL，表示子程序中不包含SQL语句。
  • READS SQL DATA，表示子程序中包含读数据的语句。
  • MODIFIES SQL DATA，表示子程序中包含写数据的语句。
  • SQL SECURITY { DEFINER |INVOKER }，指明谁有权限来执行。
  • DEFINER，表示只有定义者自己才能够执行。
  • INVOKER，表示调用者可以执行。
  • COMMENT 'string'，表示注释信息。

修改存储过程使用ALTER PROCEDURE语句，修改存储函数使用ALTER FUNCTION语句。但是，这两个语句的结构是一样的，语句中的所有参数也是一样的。而且，它们与创建存储过程或函数的语句中的参数也是基本一样的。

修改存储过程CountProc的定义。将读写权限改为MODIFIES SQLDATA，并指明调用者可以执行，代码如下：

alter procedure CountProc
modifies sql data
sql security invoker;

执行代码，并查看修改后的信息。结果显示如下：

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       |
+---------------+-------------------+---------------+

结果显示，存储过程修改成功。从查询的结果可以看出，访问数据的权限（SQL_DATA_ACCESS）已经变成MODIFIES SQLDATA，安全类型（SECURITY_TYPE）已经变成INVOKER。

修改存储函数CountProc2的定义。将读写权限改为READS SQL DATA，并加上注释信息“FIND NAME”​，代码如下：

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

执行代码，并查看修改后的信息。结果显示如下：

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       |
+---------------+-----------------+-----------------+

存储函数修改成功。从查询的结果可以看出，访问数据的权限（SQL_DATA_ACCESS）已经变成READS SQL DATA，函数注释（ROUTINE_COMMENT）已经变成FIND NAME。

5、删除存储过程和函数

删除存储过程和函数，可以使用DROP语句，其语法结构如下：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

这个语句被用来移除一个存储过程或函数。sp_name为要移除的存储过程或函数的名称。

IF EXISTS子句是一个MySQL的扩展。如果程序或函数不存储，它可以防止发生错误，产生一个用SHOW WARNINGS查看的警告。

删除存储过程和存储函数，代码如下：

DROP PROCEDURE CountProc;
DROP FUNCTION CountProc2;

上面语句的作用就是删除存储过程CountProc和存储函数CountProc。

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

在MySQL数据库中，全局变量可以通过SETGLOBAL语句来设置。例如，设置服务器语句超时的限制，可以通过设置系统变量max_execution_time来实现：

SET GLOBAL MAX_EXECUTION_TIME=2000;

使用SET GLOBAL语句设置的变量值只会临时生效。数据库重启后，服务器又会从 MySQL配置文件中读取变量的默认值。

MySQL 8.0版本新增了SET PERSIST命令。例如，设置服务器的最大连接数为1000：

SET PERSIST max_connections = 1000;

MySQL会将该命令的配置保存到数据目录下的mysqld-auto.cnf文件中，下次启动时会读取该文件，用其中的配置来覆盖默认的配置文件。

下面通过一个案例来理解全部变量的持久化。

查看全局变量max_connections的值，结果如下：

show variables like '%max_connections%';

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 750   |
+-----------------+-------+

设置全局变量max_connections的值：

set persist max_connections = 1000;

重启MySQL服务器，再次查询max_connections的值：

show variables like '%max_connections%';

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000   |
+-----------------+-------+

7、综合案例

1. 创建一个名称为sch的数据表，表结构如表所示，将表中的数据插入到sch表中。

创建一个sch表，并且向sch表中插入表格中的数据，代码如下：

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. 创建一个存储函数，用来统计表sch中的记录数。

创建一个可以统计表格内记录条数的存储函数，函数名为count_sch()，代码如下：

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

执行的结果如下：

select count_sch();

+-------------+
| count_sch() |
+-------------+
|           2 |
+-------------+

创建的储存函数名称为count_sch，通过SELCET count_sch()查看函数执行的情况，这个表中只有两条记录，得到的结果也是两条记录，说明存储函数成功的执行。

创建一个存储过程，通过调用存储函数的方法来获取表sch中的记录数和sch表中id的和。

创建一个存储过程add_id，同时使用前面创建的存储函数返回表sch中的记录数，计算出表中所有的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 ;

这个存储过程的代码中使用到变量的声明、光标、流程控制、在存储过程中调用存储函数等知识点，结果应该是两条记录，id之和为3，记录条数是通过上面的存储函数count_sch()获取的，是在存储过程中调用了存储函数。代码的执行情况如下：

call add_id(@a);

select @a, @sum;

+----+------+
| @a | @sum |
+----+------+
|  2 |    3 |
+----+------+

表sch中只有两条记录，所有id之和为3，和预想的执行结果完全相同。这个存储过程创建了一个cur_id的光标，使用这个光标来获取每条记录的id，使用REPEAT循环语句来实现所有id号相加。

8、常见问题

8.1、MySQL存储过程和函数有什么区别？

在本质上它们都是存储程序。

• 函数只能通过return语句返回单个值或者表对象；
• 存储过程不允许执行return，但是可以通过out参数返回多个值。
• 函数限制比较多，不能用临时表，只能用表变量，还有一些函数都不可用等；
• 存储过程的限制相对比较少。
• 函数可以嵌入SQL语句中使用，可以在SELECT语句中作为查询语句的一个部分调用；
• 存储过程一般作为一个独立的部分来执行；

8.2、存储过程中的代码可以改变吗？

目前，MySQL还不提供对已存在的存储过程代码的修改，如果必须要修改存储过程，必须使用DROP语句删除之后再重新编写代码，或者创建一个新的存储过程。

8.3、在存储过程中可以调用其他存储过程吗？

存储过程包含用户定义的SQL语句集合，可以使用CALL语句调用存储过程。当然，在存储过程中也可以使用CALL语句调用其他存储过程，但是不能使用DROP语句删除其他存储过程。

8.4、存储过程的参数不要与数据表中的字段名相同。

在定义存储过程参数列表时，应注意把参数名与数据库表中的字段名区别开来，否则将出现无法预期的结果。

8.5、存储过程的参数可以使用中文吗？

一般情况下，可能会出现存储过程中传入中文参数的情况，例如某个存储过程根据用户的名字查找该用户的信息，传入的参数值可能是中文。这时需要在定义存储过程的时候在后面加上character set gbk，不然调用存储过程使用中文参数会出错，比如定义userInfo存储过程，代码如下：

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