MySQL 6：MySQL存储过程、存储函数

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

存储过程有输入输出参数，可以声明变量，有if/else、case、while等控制语句，可以通过编写存储过程实现复杂的逻辑功能。存储过程有函数的共同特点：模块化、封装、代码重用。存储过程速度快，只需要第一次执行经过编译和优化步骤，后面的调用直接执行即可，免去以上步骤。

一、入门案例

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

创建数据库

create database mydb7_procedure; 

创建存储过程

delimiter $$ --bash以$$分隔，防止;结束SQL
create procedure proc01()
begin
  select empno,ename from emp; 
end  $$
delimiter ;

-- 调用存储过程
call proc01(); 

二、变量定义

1. 局部变量

declare var_name type [default var_value]; 

例如：

declare nickname varchar(32);

delimiter $$
create procedure proc02()
begin
    declare var_name01 varchar(20) default 'aaa';  -- 定义局部变量
    set var_name01 = 'zhangsan';   --为局部变量赋值
    select var_name01;
end $$
delimiter ;
-- 调用存储过程
call proc02();

MySQL 中还可以使用 SELECT..INTO 语句为变量赋值。其基本语法如下：

select col_name [...] into var_name[...] 
from table_name where condition;

其中：
col_name 参数表示查询的字段名称；
var_name 参数是变量的名称；
table_name 参数指表的名称；
condition 参数指查询条件。

当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列。

例如：

delimiter $$
create procedure proc03()
begin
 
  declare my_ename varchar(20) ;
  select ename into my_ename from emp where empno=1001;
  select my_ename;
end $$
delimiter ;
-- 调用存储过程
call proc03();

2. 用户变量

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

@var_name --不需要提前声明，使用即声明

例如： 

delimiter $$
create procedure proc04()
begin
    set @var_name01  = 'ZS';
end $$
delimiter ;
call proc04();
select @var_name01;  --可以看到结果

3. 系统变量

系统变量分为全局变量和会话变量。全局变量在 MySQL 启动时由服务器自动初始化为默认值，这些默认值可以通过改变文件 my.ini 来改变。每次建立新连接时，会话变量由 MySQL 初始化。 MySQL 会复制当前所有全局变量的值。 作为会话变量。也就是说，如果会话建立后没有手动更改会话变量和全局变量的值，那么所有这些变量的值都是相同的。全局变量和会话变量的区别在于全局变量的修改会影响整个服务器，而会话变量的修改只会影响当前会话（即当前数据库连接）。有些系统变量的值可以使用语句动态改变，但有些系统变量的值是只读的。 对于那些可以改变的系统变量，我们可以使用 set 语句来改变它们。

全局变量

由系统提供，在整个数据库有效。

@@global.var_name

例如：

-- 查看全局变量 
show global variables; 
-- 查看某全局变量 
select @@global.auto_increment_increment; --auto_increment步长
-- 修改全局变量的值 
set global sort_buffer_size = 40000; 
set @@global.sort_buffer_size = 40000;

会话变量

由系统提供，当前会话（连接）有效。

@@session.var_name

例如：

-- 查看会话变量
show session variables;
-- 查看某会话变量 
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size = 50000; 
set @@session.sort_buffer_size = 50000 ;

三、存储过程传参

1. in

in表示传入的参数，可以传入一个值或者一个变量，即使传入变量，变量的值也不会改变，可以在内部改变，只作用于函数范围内。

-- 封装有参数的存储过程，传入员工编号，查找员工信息
delimiter $$
create procedure dec_param01(in param_empno varchar(20))
begin
    select * from emp where empno = param_empno;
end $$
 
delimiter ;
call dec_param01('1001');

-- 封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息
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);

2. out

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;

-- 封装有参数的存储过程，传入员工编号，返回员工名字和薪资
delimiter $$
create procedure proc09(in empno int ,out out_ename varchar(50) ,out out_sal decimal(7,2))
begin
  select ename,sal into out_ename,out_sal from emp where emp.empno = empno;
end $$
 
delimiter ;
 
call proc09(1001, @o_dname,@o_sal);
select @o_dname;
select @o_sal;

3. inout

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 ;

in传入参数：指参数要传给存储过程，不能返回存储过程中修改的参数值。

out输出参数：可以在存储过程内部改变值，输出到外部。

inout输入输出参数：可以输入一个值，也可以传出一个值。

四、流程控制

1. if判断

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

例如：

-- 输入学生的成绩，来判断成绩的级别：
/*
  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);

-- 输入员工的名字，判断工资的情况。
delimiter $$
create procedure proc12_if(in in_ename varchar(50))
begin
    declare result varchar(20);
    declare var_sal decimal(7,2);
        select sal into  var_sal from emp where ename = in_ename;
    if var_sal < 10000 
        then set result = '试用薪资';
    elseif var_sal < 30000
        then set result = '转正薪资';
    else 
        set result = '元老薪资';
    end if;
    select result;
end$$
delimiter ;
call proc12_if('庞统');

2. case

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

例如：

-- 语法一
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);

-- 语法二
delimiter  $$
create procedure proc_15_case(in score int)
begin
  case
  when score < 60 
      then
          select '不及格';
    when  score < 80
      then
          select '及格' ;
    when score >= 80 and score < 90
       then 
           select '良好';
  when score >= 90 and score <= 100
       then 
           select '优秀';
     else
       select '成绩错误';
  end case;
end $$
delimiter  ;
 
call proc_15_case(88);

3. 循环

循环是一段在程序中只出现一次,但可能会连续运行多次的代码。循环中的代码会运行特定的次数,或者是运行到特定条件成立时结束循环。

循环分类：while、repeat、loop。

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

1. while

标签:while 循环条件 do
    循环体;
end while 标签;

例如：

-- 创建测试表
create table user (
    uid int primary_key,
    username varchar ( 50 ),
    password varchar ( 50 )
);

-- -------存储过程-while
delimiter $$
create procedure proc16_while1(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');
        set i=i+1;
    end while label;
end $$
delimiter ;
 
call proc16_while(10);

-- -------存储过程-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);

-- -------存储过程-while+iterate
truncate table user;
delimiter $$
create procedure proc16_while3(in insertcount int)
begin
    declare i int default 1;
    label:while i<=insertcount do
        set i=i+1;
        if i=5 then iterate label;
        end if;
        insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
    end while label;
end $$
delimiter ;
call proc16_while3(10);

2. repeat

标签:repeat 
 循环体;
until 条件表达式
end repeat 标签;

例如：

-- -------存储过程-循环控制-repeat 
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);

3. loop

标签: loop
  循环体;
  if 条件表达式 then 
     leave 标签; 
  end if;
end 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);

五、游标

游标（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

 例如：

use mysql7_procedure;
delimiter $$
create procedure proc20_cursor(in in_dname varchar(50))
begin
 -- 定义局部变量
 declare var_empno varchar(50);
 declare var_ename varchar(50);
 declare var_sal  decimal(7,2);
 
 -- 声明游标
 declare my_cursor cursor for
  select empno , ename, sal 
    from  dept a ,emp b
    where a.deptno = b.deptno and a.dname = in_dname;
    
    -- 打开游标
  open my_cursor;
  -- 通过游标获取每一行数据
  label:loop
        fetch my_cursor into var_empno, var_ename, var_sal;
        select var_empno, var_ename, var_sal;
    end loop label;
    
    -- 关闭游标
    close my_cursor;
end
 
-- 调用存储过程
call proc20_cursor('销售部');

六、异常处理-HANDLER 

MySQL存储过程也提供了对异常处理的功能：通过定义句柄（HANDLER）来完成异常声明的实现。

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement
 
handler_action: {
    CONTINUE
  | EXIT
  | UNDO
}
 
condition_value: {
    mysql_error_code
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION

在语法中，变量声明、游标声明、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;
        -- 判断标志位
        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('销售部');

这段SQL创建了一个名为“proc20_cursor”的存储过程。该存储过程使用一个输入参数in_dname，表示要查询的部门名称。在存储过程中，首先定义了三个局部变量：var_empno，var_ename和var_sal，用于存储查询结果中的员工编号、姓名和薪水。然后，声明了一个名为“my_cursor”的游标，用于执行查询操作。

在存储过程的主体中，使用了OPEN语句打开游标。然后，在一个名为“label”的标签下，使用了一个名为“loop”的循环来遍历游标中的每一行数据。使用FETCH语句获取每一行数据，然后使用IF语句检查标志位的值。如果标志位为1，使用SELECT语句显示结果，并指向游标的下一行数据。如果标志位为0，则使用LEAVE语句退出循环。在循环结束后，使用CLOSE语句关闭游标。

此外，在该存储过程中，使用了一个名为“continue handler”的句柄。这个句柄用于处理游标未找到数据的情况，并将标志位设置为0。当没有更多数据可供读取时，FETCH语句将返回一个NOT FOUND错误。此时，句柄将被触发，并将标志位设置为0，以便在下一次检查标志位时退出循环。

最后，使用CALL语句调用该存储过程，并将要查询的部门名称作为参数传递给存储过程。当存储过程执行时，将使用游标查询指定部门的所有员工，并显示其编号、姓名和薪水。

练习：

创建表user_2021_11_01，user_2021_11_02，...对应下个月的每一天

需求描述：
需要用一个表来记录很多数据，比如记录某个用户的搜索和购买行为（注意这里假设是存储在数据库中）。 当每天记录很多时，将所有数据记录在一张表中太大了，需要分表。需求是每天一张表，保存当天的统计数据，所以需要提前生产这些表——在每个月底创建下个月的日表。

PREPARE stmt_name FROM preparable_stmt
EXECUTE stmt_name [USING @var_name [, @var_name] ...]
{DEALLOCATE | DROP} PREPARE stmt_name
-- 知识点 时间的处理
-- EXTRACT(unit FROM date)截取时间的指定位置值
-- DATE_ADD(date,INTERVAL expr unit)  日期运算
-- LAST_DAY(date)  获取日期的最后一天
-- YEAR(date) 返回日期中的年
-- MONTH(date) 返回日期的月
-- DAYOFMONTH(date) 返回日

这段SQL语法是MySQL中动态SQL语句的格式，用于动态构建SQL语句并执行它。动态SQL语句允许在运行时构建SQL语句，从而提高代码的灵活性和可维护性。

下面是每个关键字的含义：

• PREPARE stmt_name FROM preparable_stmt：使用指定的名称创建一个预处理语句stmt_name，该预处理语句的内容为preparable_stmt。preparable_stmt可以是任何有效的SQL语句，包括SELECT、INSERT、UPDATE、DELETE、CREATE等。
• EXECUTE stmt_name [USING @var_name [, @var_name] ...]：执行由stmt_name指定的预处理语句，并使用USING子句绑定变量到该语句中的占位符。如果预处理语句不包含占位符，则不需要USING子句。
• DEALLOCATE PREPARE stmt_name或DROP PREPARE stmt_name：从MySQL服务器中删除预处理语句stmt_name，并释放其占用的资源。这个步骤是可选的，但是为了避免内存泄漏，建议在完成使用预处理语句后调用DEALLOCATE或DROP命令释放其资源。

PREPARE和EXECUTE命令用于动态构建SQL语句，并将其发送到MySQL服务器执行。DEALLOCATE或DROP命令用于释放预处理语句的资源。这种方式的优点是可以灵活地构建SQL语句，从而提高代码的可维护性和灵活性。但是，需要注意避免SQL注入攻击，确保输入的数据已经过滤和验证。

-- 思路：循环构建表名 user_2021_11_01 到 user_2020_11_30；并执行create语句。
use mysql7_procedure;
drop procedure if exists proc22_demo;
delimiter $$
create procedure proc22_demo()
begin
    declare next_year int;
    declare next_month int;
    declare next_month_day int;
        
    declare next_month_str char(2);
    declare next_month_day_str char(2);
    
    -- 处理每天的表名
    declare table_name_str char(10);
    
    declare t_index int default 1;
    -- declare create_table_sql varchar(200);
    -- 获取下个月的年份
    set next_year = year(date_add(now(),INTERVAL 1 month));
    -- 获取下个月是几月 
    set next_month = month(date_add(now(),INTERVAL 1 month));
    -- 下个月最后一天是几号
    set next_month_day = dayofmonth(LAST_DAY(date_add(now(),INTERVAL 1 month)));
    
    if next_month < 10
        then set next_month_str = concat('0',next_month);
    else
        set next_month_str = concat('',next_month);
    end if;
    
    
    while t_index <= next_month_day do
        
        if (t_index < 10)
            then set next_month_day_str = concat('0',t_index);
        else
            set next_month_day_str = concat('',t_index);
        end if;
        
-- 2021_11_01
        set table_name_str = concat(next_year,'_',next_month_str,'_',next_month_day_str);
        -- 拼接create sql语句
        set @create_table_sql = concat(
                    'create table user_',
                    table_name_str,
                    '(`uid` INT ,`ename` varchar(50) ,`information` varchar(50)) COLLATE=\'utf8_general_ci\' ENGINE=InnoDB');
        -- FROM后面不能使用局部变量！
        prepare create_table_stmt FROM @create_table_sql;
        execute create_table_stmt;
        DEALLOCATE prepare create_table_stmt;
        
        set t_index = t_index + 1;
        
    end while;  
end $$
delimiter ;
 
call proc22_demo();

这段SQL创建了一个名为“proc22_demo”的存储过程。该存储过程的目的是循环构建表名 user_2021_11_01 到 user_2020_11_30，并执行CREATE TABLE语句来创建这些表。在存储过程中，首先定义了一些局部变量，包括next_year、next_month和next_month_day等变量，用于存储下一个月的年份、月份和最后一天的日期。

然后，使用IF语句和CONCAT函数构建了next_month_str和next_month_day_str变量，用于拼接表名的月份和日期。在循环中，使用WHILE语句遍历每一天，使用CONCAT函数构建表名，并使用PREPARE语句和EXECUTE语句执行CREATE TABLE语句来创建表。在循环结束后，关闭游标。

需要注意的是，在PREPARE语句中，不能使用局部变量。因此，在存储过程中，使用@create_table_sql变量来存储拼接的CREATE TABLE语句，并将其传递给PREPARE和EXECUTE语句。

最后，使用CALL语句调用该存储过程。当存储过程执行时，将遍历每一天并创建相应的表。

七、存储函数

MySQL存储函数（自定义函数），函数一般用于计算和返回一个值，可以将需要经常使用的计算或函数写成函数。存储函数与存储过程一样，是在数据库中定义的 SQL 语句的集合。 

1. 存储函数和存储过程的区别

存储函数只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值。

存储函数只能有入参，不能带入，而存储过程可以有多个in、out、inout参数。

存储过程中的语句功能更加强大。 存储过程可以实现非常复杂的业务逻辑，但是函数有很多限制，函数中不能使用insert、update、delete、create等语句；

存储函数只完成查询工作，接受输入参数并返回一个结果，即函数实现的功能比较有针对性。

存储过程可以调用存储函数。 但是函数不能调用存储过程。

存储过程一般作为一个独立的部分执行（call调用）。 函数可以作为查询语句的一部分被调用。

create function func_name ([param_name type[,...]])
returns type
[characteristic ...] 
begin
    routine_body
end;

参数说明：
（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;
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();

-- 创建存储过程-有输入参数
 
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);