【MySQL】存储过程与存储函数

1. 存储过程概述

• MySQL 5.0 版本开始支持存储过程。
• 简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法。
• 存储过就是数据库 SQL 语言层面的代码封装与重用。

存储过程有哪些特征？

• 有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；
• 函数的普遍特性：模块化，封装，代码复用；
• 速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

存储过程有哪些优点？

• 存储过程有助于减少应用程序和数据库服务器之间的流量。
  • 因为应运程序不必发送多个冗长的SQL语句，只用发送存储过程中的名称和参数即可。
• 存储过程度任何应用程序都是可重用的和透明的。存储过程将数据库接口暴露给所有的应用程序，以方便开发人员不必开发存储过程中已支持的功能。
• **存储的程序是安全的。**数据库管理员是可以向访问数据库中存储过程的应用程序授予适当的权限，而不是向基础数据库表提供任何权限。
• 通常存储过程都是有助于提高应用程序的性能。当创建的存储过程被编译之后，就存储在数据库中。
• 简化对变动的管理。如果表名、列名、或业务逻辑有了变化。只需要更改存储过程的代码。使用它的人不用更改自己的代码。

存储过程有哪些缺点？

• 如果使用大量的存储过程，那么使用这些存储过程的每个连接的内存使用量将大大增加。
  • 如果在存储过程中过度使用大量的逻辑操作，那么CPU的使用率也在增加，因为MySQL数据库最初的设计就侧重于高效的查询，而不是逻辑运算。
• 如果在存储过程中过度使用大量的逻辑操作，那么CPU的使用率也在增加，因为MySQL数据库最初的设计就侧重于高效的查询，而不是逻辑运算。
• **很难调试存储过程。**只有少数数据库管理系统允许调试存储过程。不幸的是，MySQL不提供调试存储过程的功能。
• **开发和维护存储过程都不容易。**储过程类似于一门新的语言，不同语言之间跨度较大。
• 对数据库依赖程度较高，移值性差。

---

2. MySQL中的存储结构

MySQL中存储结构的语法

delimiter 自定义结束符号
create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...)
begin
  sql语句
end 自定义的结束符合
delimiter ;
1
2
3
4
5
6

一个简单的MySQL存储结构的例子

delimiter $$
create procedure proc01()
begin
  select empno,ename from emp; 
end  $$
delimiter ;

-- 调用存储过程
call proc01(); 
1
2
3
4
5
6
7
8
9

delimiter简介

delimiter是MySQL分隔符，在MySQL客户端中分隔符默认是分号（；）。如果一次输入的语句较多，并且语句中间有分号，这时需要新指定一个特殊的分隔符。

其实就是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。

---

3. MySQL中的变量

在MySQL中，变量分为以下几种：

1. 局部变量：在begin/end块中有效
2. 用户变量：用户自定义，当前会话（连接）有效。类比java的成员变量
3. 系统变量：分为全局变量与会话变量，由系统提供，在整个数据库有效。
   1. 全局变量：在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。
   2. 会话变量：在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。

在MySQL可以通过使用 SELECT…INTO 语句为变量赋值。其基本语法如下：

select col_name [...] into var_name[,...] 
from table_name wehre condition 

其中：
col_name 参数表示查询的字段名称；
var_name 参数是变量的名称；
table_name 参数指表的名称；
condition 参数指查询条件。
注意：当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列。
1
2
3
4
5
6
7
8
9

---

3.1 局部变量

局部变量由用户自定义，在begin/end块中有效。

语法

语法： 声明变量 declare var_name type [default var_value]; 
举例：declare nickname varchar(32);
1
2

例子

delimiter $$
create procedure proc02()
begin
    declare var_name01 varchar(20) default ‘aaa’;  -- 定义局部变量
    set var_name01 = ‘zhangsan’;  
    select var_name01;
end $$
delimiter ;
-- 调用存储过程
call proc02();
1
2
3
4
5
6
7
8
9
10

---

3.2 用户变量

用户自定义，当前会话（连接）有效。类比java的成员变量 。

语法

语法： 
@var_name
不需要提前声明，使用即声明
1
2
3

例子

delimiter $$
create procedure proc04()
begin
    set @var_name01  = 'ZS';
end $$
delimiter;
call proc04() ;
select @var_name01  ;  --可以看到结果
1
2
3
4
5
6
7
8

---

3.3 系统变量

• 系统变量又分为全局变量与会话变量
• 全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。
• 会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。
• 也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。
• 全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。
• 有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改。

全局语法

语法：
@@global.var_name
1
2

全局例子

-- 查看全局变量 
show global variables; 
-- 查看某全局变量 
select @@global.auto_increment_increment; 
-- 修改全局变量的值 
set global sort_buffer_size = 40000; 
set @@global.sort_buffer_size = 40000;
1
2
3
4
5
6
7

会话变量语法

语法：
@@session.var_name
1
2

会话变量例子

-- 查看会话变量
show session variables;
-- 查看某会话变量 
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size = 50000; 
set @@session.sort_buffer_size = 50000 ;
1
2
3
4
5
6
7

---

4. 存储过程的输入参数与输出参数与及时输入参数

在存储过程中，参数分为in、out和inout三种：

1. in：表示传入的参数， 可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内。
2. out：表示从存储过程内部传值给调用者
3. inout：表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完）

in例子

-- 封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息
delimiter $$
create procedure dec_param0x(in dname varchar(50),in sal decimal(7,2),)
begin
        select * from dept a, emp b where b.sal > sal and a.dname = dname;
end $$
 
delimiter ;
call dec_param0x('学工部',20000);
1
2
3
4
5
6
7
8
9

out例子

-- ---------传出参数：out---------------------------------
use mysql7_procedure;
-- 封装有参数的存储过程，传入员工编号，返回员工名字
delimiter $$
create procedure proc08(in empno int ,out out_ename varchar(50) )
begin
  select ename into out_ename from emp where emp.empno = empno;
end $$
 
delimiter ;
 
call proc08(1001, @o_ename);
select @o_ename;
1
2
3
4
5
6
7
8
9
10
11
12
13

inout例子

-- 传入员工名，拼接部门号，传入薪资，求出年薪
delimiter $$
create procedure proc10(inout inout_ename varchar(50),inout inout_sal int)
begin
  select  concat(deptno,"_",inout_ename) into inout_ename from emp where ename = inout_ename;
  set inout_sal = inout_sal * 12;  
end $$
delimiter ;
set @inout_ename = '关羽';
set @inout_sal = 3000;
call proc10(@inout_ename, @inout_sal) ;
select @inout_ename ;
select @inout_sal ;
1
2
3
4
5
6
7
8
9
10
11
12
13

---

5.存储过程流程控制

存储过程的流程控制主要以下几种：

• 判断
• case
• 循环

IF语句包含多个条件判断，根据结果为TRUE、FALSE执行语句，与编程语言中的if、else if、else语法类似，其语法格式如下：

-- 语法
if search_condition_1 then statement_list_1
    [elseif search_condition_2 then statement_list_2] ...
    [else statement_list_n]
end if
1
2
3
4
5

IF例子

-- 输入学生的成绩，来判断成绩的级别：
/*
  score < 60 :不及格
  score >= 60  , score <80 :及格
    score >= 80 , score < 90 :良好
    score >= 90 , score <= 100 :优秀
    score > 100 :成绩错误
*/
delimiter  $$
create procedure proc_12_if(in score int)
begin
  if score < 60 
      then
          select '不及格';
    elseif  score < 80
      then
          select '及格' ;
    elseif score >= 80 and score < 90
       then 
           select '良好';
  elseif score >= 90 and score <= 100
       then 
           select '优秀';
     else
       select '成绩错误';
  end if;
end $$
delimiter  ;
call proc_12_if(120)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

而CASE是另一个条件判断的语句，类似于编程语言中的switch语法。

-- 语法一（类比java的switch）：
case case_value
    when when_value then statement_list
    [when when_value then statement_list] ...
    [else statement_list]
end case
-- 语法二：
case
    when search_condition then statement_list
    [when search_condition then statement_list] ...
    [else statement_list]
end case
1
2
3
4
5
6
7
8
9
10
11
12

case例子

-- 语法一
delimiter $$
create procedure proc14_case(in pay_type int)
begin
  case pay_type
        when  1 
          then 
              select '微信支付' ;
        when  2 then select '支付宝支付' ;
        when  3 then select '银行卡支付';
      else select '其他方式支付';
    end case ;
end $$
delimiter ;
 
call proc14_case(2);
call proc14_case(4);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

在存储过程中，循环分为以下三种：

• while
• repeat
• loop

而循环控制分为以下两种：

• leave 类似于 break，跳出，结束当前所在的循环
• iterate类似于 continue，继续，结束本次循环，继续下一次

while语法

【标签:】while 循环条件 do
    循环体;
end while【 标签】;
1
2
3

while例子：

-- -------存储过程-while
delimiter $$
create procedure proc16_while1(in insertcount int)
begin
    declare i int default 1;
    -- label是一个标签，也就是这个循环的名称
    label:while i<=insertcount do
        insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
        set i=i+1;
    end while label;
end $$
delimiter ;
 
call proc16_while(10);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- -------存储过程-while + leave
truncate table user;
delimiter $$
create procedure proc16_while2(in insertcount int)
begin
    declare i int default 1;
    label:while i<=insertcount do
        insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
        if i=5 then leave label;
        end if;
        set i=i+1;
    end while label;
end $$
delimiter ;
 
call proc16_while2(10);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

repeat语法

[标签:]repeat 
 循环体;
until 条件表达式
end repeat [标签];
1
2
3
4

repeat例子

-- -------存储过程-循环控制-repeat （优点像while do）
use mysql7_procedure;
truncate table user;
 
 
delimiter $$
create procedure proc18_repeat(in insertCount int)
begin
     declare i int default 1;
     label:repeat
         insert into user(uid, username, password) values(i,concat('user-',i),'123456');
         set i = i + 1;
         -- 循环结束条件
         until  i  > insertCount
     end repeat label;
     select '循环结束';
end $$
delimiter ;
 
call proc18_repeat(100);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

loop语法

[标签:] loop
  循环体;
  if 条件表达式 then 
     leave [标签]; 
  end if;
end loop;
1
2
3
4
5
6

loop例子

-- -------存储过程-循环控制-loop
truncate table user;
 
delimiter $$
create procedure proc19_loop(in insertCount int) 
begin
     declare i int default 1;
     label:loop
         insert into user(uid, username, password) values(i,concat('user-',i),'123456');
         set i = i + 1;
         if i > 5 
          then 
           leave label;
         end if;
     end loop label;
     select '循环结束';
end $$
delimiter ;
 
call proc19_loop(10);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

---

6. 游标与句柄

**游标(cursor)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。**光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE.

游标语法

-- 声明语法
declare cursor_name cursor for select_statement
-- 打开语法
open cursor_name
-- 取值语法
fetch cursor_name into var_name [, var_name] ...
-- 关闭语法
close cursor_name
1
2
3
4
5
6
7
8

而句柄是MySql存储过程提供的对异常处理的功能

句柄语法：

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement
 
handler_action: {
    CONTINUE -- 程序继续。
  | EXIT -- 开始的执行终止... END复合语句，其中处理程序是 宣布。即使条件发生在 内块。
  | UNDO -- 暂不支持
}
 
condition_value: {
    mysql_error_code
  | condition_name --  MySQL 错误代码或 SQLSTATE 值
  | SQLWARNING -- SQL警告
  | NOT FOUND -- 没有找到
  | SQLEXCEPTION -- SQL异常
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

⚠️⚡特别注意：

在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。

例子

use mysql7_procedure;
drop procedure if exists proc21_cursor_handler;
-- 需求：输入一个部门名，查询该部门员工的编号、名字、薪资 ，将查询的结果集添加游标
delimiter $$
create procedure proc20_cursor(in in_dname varchar(50))
begin
  -- 定义局部变量
    declare var_empno int;
    declare var_ename varchar(50);
    declare var_sal decimal(7,2);
    
    declare flag int default 1; -- ---------------------
    
    -- 声明游标
    declare my_cursor cursor for
        select empno,ename,sal
        from dept a, emp b
        where a.deptno = b.deptno and a.dname = in_dname;
    
    -- 定义句柄，当数据未发现时将标记位设置为0
    declare continue handler for NOT FOUND set flag = 0;  
        -- 打开游标
    open my_cursor;
    -- 通过游标获取值
    label:loop
        fetch my_cursor into var_empno, var_ename,var_sal;
        -- 判断标志位，如果为0证明已经出现了异常
        if flag = 1 then
            select var_empno, var_ename,var_sal;
        else
            leave label;
        end if;
    end loop label;
    
    -- 关闭游标
    close my_cursor;
end $$;
 
delimiter ;
call proc21_cursor_handler('销售部');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

---

7. 存储函数

存储函数是什么？

MySQL存储函数（自定义函数），函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。

存储函数和存储过程一样，都是在数据库中定义一些 SQL 语句的集合。

语法

create function func_name ([param_name type[,...]])
returns type
[characteristic ...] 
begin
    routine_body
end;
1
2
3
4
5
6

参数说明：

1. func_name ：存储函数的名称。
2. param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型可以是MySQL数据库中所有支持的类型。
3. RETURNS type：指定返回值的类型。
4. characteristic：可选项，指定存储函数的特性。
5. routine_body：SQL代码内容。

例子

create database mydb9_function;
-- 导入测试数据
use mydb9_function;
-- mysql8.0有可能无法创建存储函数，加了这个就可以了
set global log_bin_trust_function_creators=TRUE; -- 信任子程序的创建者
 
-- 创建存储函数-没有输输入参数
drop function if exists myfunc1_emp;
 
delimiter $$
create function myfunc1_emp() returns int
begin
  declare cnt int default 0;
    select count(*) into  cnt from emp;
  return cnt;
end $$
delimiter ;
-- 调用存储函数
select myfunc1_emp();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- 创建存储过程-有输入参数
 
drop function if exists myfunc2_emp;
delimiter $$
create function myfunc2_emp(in_empno int) returns varchar(50)
begin
    declare out_name varchar(50);
    select ename into out_name from emp where  empno = in_empno;
    return out_name;
end $$
delimiter ;
 
 
select myfunc2_emp(1008);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

---

8.存储过程与存储函数的区别

1. 存储函数有且只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值。
2. 存储函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。
3. 存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制**，如不能在函数中使用insert,update,delete,create等语句**；
4. 存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。
5. 存储过程可以调用存储函数。但函数不能调用存储过程。
6. 存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。

---

参考：

• MySQL中的存储过程（详细篇）_星辰与晨曦的博客-CSDN博客_mysql存储过程
• MySQL中delimiter关键字的使用_pan_junbiao的博客-CSDN博客_delimiter用法
• 2022新版黑马程序员MySQL知识精讲+mysql实战案例_零基础mysql数据库入门到高级全套教程