MySQL存储函数
一、存储程序的分类
根据调用方式的不同，可以把存储程序分为存储例程、触发器和事件这几种类型。其中，存储例程又可以被分为存储函数和存储过程。
二、几个相关概念
1. 用户自定义变量
1.1 自定义变量的方式
通过set语句来自定义一些自己的变量，例如：
set @value = 100;
该语句定义了一个称为a的变量，并且把整数1赋值给了这个变量。用户自定义变量的前面必须加上@符号。
1.2 查看变量的值
select @a;
1.3 同一个变量可以存储不同类型的值，例如：
set @value = 'string';
值得注意的是，单引号和双引号都可以代表字符串。
1.4 把一个变量赋值给另一个变量，例如：
set @b = @a;
该语句的的表示将变量a的值赋值给变量b，在改变a的值并不会影响b的值。
1.5 可以将某个查询结果赋值给一个变量
set @a = select v1 from t1 limit 1;
1.6 使用into子句替代set语句来为变量赋值，例如：
select v1 from t1 limit 1 into @b;
值得注意的是：1.5和1.6的效果是等价的。
1.7 查询结果集是单行多列的赋值，例如：
select v1, v2 from t1 limit 1 into @c, @d;
该语句的结果只有一条记录（limit 1）， 但是有多个列（v1, v2）， 这里是把v1的值赋给c， 把v2的值赋给d。
三、存储函数
存储程序实际上就是将一系列简单、零散的查询语句封装到一个框架下。
1. 创建存储函数
MySQL中定义存储函数的语句如下：
create function producure_name([parameter_list]) return return_type begin function body; end
定义存储函数时，需要指定存储函数名称（function_name）、参数列表（[parameter_list]）、返回值类型（return_type）以及函数体内容(function_body)。
注意：[ parameter_list ] 这里的中括号的意思是，如果没有参数，则可以省略不写。函数体中的语句以（；）也即分号结尾。
其它注意事项：MySQL客户端默认将分号（；）作为语句的分隔符，每当它读取分号后，就会将该分号之前的内容作为一个请求发送给服务器。
1.1 函数实例
delimiter $$
create function avg_score(s varchar(100)) returns double begin return (select avg(score) from student_score where subject = s; end $$
delimiter ;
这里定义了一个函数名称为avg_score的函数，它接受一个varchar(100)类型的参数，返回值的类型是double。函数体内容是在return单词后面接了一个select语句，表明函数avg_score的返回结果就是这个select语句的结果集，也就是某个指定科目的平均成绩。
delimiter语句的作用是临时修改mysql客户端的语句分隔符。在写完函数定义语句后，将客户端语句分隔符改回分号。（不是必须，但是强烈建议）
2. 存储函数的调用
自定义函数与系统内置函数的用法是一样的，都是在函数名后面加上小括号（）来表示函数调用。具体的用法与其它编程语言的方法调用相似。
2.1 调用的实例‘
select avg_score("mysql中函数的调用");
3. 查看存储函数
3.1 查看定义存储函数的个数以及各个存储函数的相关属性，例如：
show function status [ like 需要匹配的函数名 ]
3.1 查看具体函数的定义，例如：
show create function fucntion_name;
4. 删除存储函数
删除某个存储函数，例如：
drop function function_name;
5. 函数体的定义
函数体中通常包含多条语句。（如果只封装一条语句意义不大）
5.1 在函数体中定义局部变量
可以直接使用set语句为自定义变量赋值而不用事先声明。如果要在函数体中使用变量，则必须提前使用declare语句声明该变量，声明语法如下：
declare value1， value2， 。。。dataType [ default default_value ] ;
5.2 局部变量实例
delimiter $$ create function var_demo() returns int begin declare c int; set c = 5; return c; end $$ delimiter ;
5.3 函数调用实例
select var_demo();
5.4 指定局部变量的默认值
delimiter $$ create function var_default_dome() returns int begin declare c int default 1; #声明了一个局部变量c，并且指定它的默认值为1 return c; end $$ delimiter ;
函数调用，如下：
select var_default_demo();
5.5 把查询结果赋值给局部变量
delimiter $$ create function avg_score(s varchar(100)) returns double begin declare a double; set a = (select avg(score) from student_score where subject = s); return a; end $$ delimiter ;
先把查询的结果赋值给a，然后再返回a的值。
函数调用实例，如下：
select avg_score('语文');
6. 再函数体中使用用户自定义变量
除了局部变量之外，也可以再函数体中使用之前使用的用户自定义变量。例如：
delimiter $$ create function user_defined_var_demo() returns int begin set @abc = 10; return @abc; end $$ delimiter ;
调用实例及顺序：
select @abc; #NULL select user_defined_var_demo(); # 10 select @abc; # 10
最开始变量abc的值为NULL， 在调用user_defined_var_demo()之后，自变量abc的值变为10了。
这也就意味着及时存储函数执行完毕，该存储函数修改过的用户自定义变量的值将继续生效。这一点与再函数中使用declare声明的局部变量有明显的区别。
7. 带参数的函数
在定义存储函数的时候，可以指定多个参数，且每个参数都要指定对应的数据类型。其格式如下：
parameter_name dataType
7.1 带参数函数的实例
delimiter $$ create function find_emp_id(empName varchar(50)) returns int begin return select id from employees where emp_name = empName; end $$ delimiter ;
需要注意的是：这个参数名不要与函数体语句中的其它变量名、列名冲突。如果将上面例子中的变量名改成emp_name，则系统会有冲突提示。 在使用带参数的函数时，一定要携带参数值。
8. 判断语句
MySQL中的判断语句格式如下：
if express then 语句列表 [ elseif express then 语句列表 ] ... [ else 语句列表 ] end if;
8.1 判断语句实例
delimiter $$ create function condition_demo( rate int ) returns varchar(100) begin declare result varchar(100); if rate = 1 then set result = "result is 1"; elseif rate = 2 then set result = "result is 2"; elseif rate = 3 then set resutlt = "result is 3" else set result = "非法参数"; end if; return result; end $$
9. 循环语句
mysql提供了三种循环语句：while、repeat和loop
9.1 while循环语句
语法格式如下：
while express do 语句列表 end while;
如果给定的表达式为真，则执行语句列表中语句，否则退出循环。
delimiter $$ create function sum_all(n int unsigned) return int begin declare result int default 0; declare i int default 1; while i <= n do set result = result + i; set i = i + 1; end while; return result; end $$ delimiter ;
9.2 repeat语句
语法格式如下：
repeat 语句列表 until express end repeat;
repeat循环语句的含义是先执行语句列表中的语句，再判断表达式是否为真，如果为真则退出循环，否则继续执行。（do...while...）
delimiter $$ create function sum_all(n int unsigned) returns int begin declare result int default 0; declare i int default 1; repeat set result = result + 1; set i = i + 1; unitil i > n end repeat; return result; end $$ delimiter ;
9.3 loop循环语句
语法格式如下：
loop 语句列表 end loop;
LOOP循环语句没有判断循环终止的条件。其循环终止条件卸载语句列表中，然后使用return语句直接终止函数，达到停止循环的效果。
delimiter $$ create function sum_all(n int unsigned) returns int begin declare result int default 0; declare i int default 1; loop if i > n then return result; end if; set result = result + 1; set i = i + 1; end loop; end $$ delimiter ;
10. 存储过程
delimiter $ mysql> create procedure t1_operation( -> m1_value int, -> n1_value char(1) -> ) -> begin -> select * from t1; -> insert into t1(m1, n1) values(m1_value, n1_value); -> select * from t1; -> end $
11. 查找名称包含in_字符的存储过程
mysql> show procedure status like '%in_%'\G; *************************** 1. row *************************** Db: sys Name: ps_setup_disable_instrument Type: PROCEDURE Definer: mysql.sys@localhost Modified: 2022-01-18 20:00:14 Created: 2022-01-18 20:00:14 Security_type: INVOKER Comment: Description ----------- Disables instruments within Performance Schema matching the input pattern. Parameters ----------- in_pattern (VARCHAR(128)): A LIKE pattern match (using "%in_pattern%") of events to disable Example ----------- To disable all mutex instruments: mysql> CALL sys.ps_setup_disable_instrument('wait/synch/mutex'); +--------------------------+ | summary | +--------------------------+ | Disabled 155 instruments | +--------------------------+ 1 row in set (0.02 sec) To disable just a specific TCP/IP based network IO instrument: mysql> CALL sys.ps_setup_disable_instrument('wait/io/socket/sql/server_tcpip_socket'); +------------------------+ | summary | +------------------------+ | Disabled 1 instruments | +------------------------+ 1 row in set (0.00 sec) To disable all instruments:
11.1 查看存储过程的定义
mysql> show create procedure t1_operation\G; *************************** 1. row *************************** Procedure: t1_operation sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `t1_operation`( m1_value int, n1_value char(1) ) begin select * from t1; insert into t1(m1, n1) values(m1_value, n1_value); select * from t1; end character_set_client: gbk collation_connection: gbk_chinese_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
11.2 删除存储过程
mysql> drop procedure t1_operation; Query OK, 0 rows affected (0.01 sec)
11.3 存储过程的参数前缀
mysql> create procedure p_in(in arg int) -> begin -> select arg; -> set arg = 123; -> end $ Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> set @a = 1; Query OK, 0 rows affected (0.00 sec) mysql> call p_in(@a); +------+ | arg | +------+ | 1 | +------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
mysql> create procedure p_out(out arg int) -> begin -> select arg; -- 第一个读取语句，并没有获取到参数的值，也就是说out参数的值默认为null -> set arg = 123; -> end $ Query OK, 0 rows affected (0.04 sec) mysql> set @b = 2; -- 在存储过程执行完成之后，再次读取b的值，发现它已经被设置成123，这说明在过程中对该变量的赋值对调用者是可见的！ 这也就是说，out参数只能用于赋值。 在调用存储过程时，实际的参数就不允许是常量。 -> $ Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call p_out(@b); -- 第一个读取语句，并没有获取到参数的值，也就是说out参数的值默认为null +------+ | arg | +------+ | NULL | +------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> select @b; +------+ | @b | +------+ | 123 | +------+ 1 row in set (0.00 sec)
114 游标
mysql> delimiter $ mysql> create procedure cursor_demo() -> begin -> declare t1_record_cursor cursor for select m1, n1 from t1; -> end $ Query OK, 0 rows affected (0.01 sec) mysql> drop procedure if exists cursor_demo; -> $ Query OK, 0 rows affected (0.04 sec) mysql> create procedure cursor_demo() -> begin -> declare t1_record_cursor cursor for select m1, n1 from t1; -> open t1_record_cursor; -> close t1_record_cursor; -> end $ Query OK, 0 rows affected (0.04 sec) mysql> drop procedure if exists cursor_demo; -> $ Query OK, 0 rows affected (0.04 sec) mysql> create procedure cursor_demo() -> begin -> declare m_value int; -> declare n_value char(1); -> declare t1_record_cursor cursor for select m1, n1 from t1; -> open t1_record_cursor; -> fetch t1_record_cursor into m_value, n_value; -> select m_value, n_value; -> close t1_record_cursor; -> end $ Query OK, 0 rows affected (0.00 sec) mysql> call cursor_demo()$ +---------+---------+ | m_value | n_value | +---------+---------+ | 1 | a | +---------+---------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.02 sec) mysql> drop procedure cursor_demo; -> $ Query OK, 0 rows affected (0.01 sec) mysql> create procedure cursor_demo() -> begin -> declare m_value int; -> declare n_value char(1); -> declare record_count int; -> declare i int default 0; -> declare t1_record_cursor cursor for select m1, n1 from t1; -> select count(*) from t1 into record_count; -> open t1_record_cursor; -> while i < record_count do -> fetch t1_record_cursor into m_value, n_value; -> select m_value, n_value; -> set i = i + 1; -> end while; -> close t1_record_cursor; -> end $ Query OK, 0 rows affected (0.01 sec)
mysql> create procedure cursor_demo() -> begin -> declare m_value int; -> declare n_value char(1); -> declare done int default 0; -> declare t1_record_cursor cursor for select m1, n1 from t1; -> declare continue handler for not found set done = 1; -> open t1_record_cursor; -> flag : loop -> fetch t1_record_cursor into m_value, n_value; -> if done = 1 then -> leave flag; -> end if; -> select m_value, n_value, done; -> end loop flag; -> close t1_record_cursor; -> end $ Query OK, 0 rows affected (0.01 sec) mysql> call cursor_demo; -> $ +---------+---------+------+ | m_value | n_value | done | +---------+---------+------+ | 1 | a | 0 | +---------+---------+------+ 1 row in set (0.01 sec) +---------+---------+------+ | m_value | n_value | done | +---------+---------+------+ | 2 | b | 0 | +---------+---------+------+ 1 row in set (0.01 sec)
浙公网安备 33010602011771号