MySQL-存储过程

十四、存储过程

14.1 介绍

14.2 基本语法

-- 创建
create procedure 存储过程名称([参数列表])
begin
		-- SQL语句
END;

#执行完创建语句，会生成routines这样的文件夹，里面存放的就是存储过程。


-- 调用
call 名称[(参数)];


-- 查看(两种形式)
#查询指定数据库的存储过程及状态信息
select * from information_schema.Routines where Routine_schema = 'xxx'; 

#查询某个存储过程的定义
show create procedure 存储过程名称;

#示例：
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'test';

show create procedure p1;

-- 删除
drop procedure [if exists] 存储过程名称;



-- 注意点：
在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符，不然命令行中遇到分号就以为SQL语句结束了，于是报了个错。

​ 此时命令行将$$视作结束符

​ 现在就执行成功了

​ 未指定结束符时，将创建语句复制粘贴到命令行

14.3 变量

-- 系统变量

系统变量 是MySQL服务器提供的，不是用户定义的，属于服务器层面。分为全局变量(global)、会话变量(session)
#比如给一个数据库开一个console控制台，就是开一个会话。全局变量就是说，如果你打开了多个控制台，那么这个全局变量在所有的控制台中都是有效的；而会话变量就是指仅在当前会话（控制台）内有效，比如你在当前会话中创建了一个会话变量，那么它就是能在当前会话中有效，在其他会话中是无效的。

-- 查看系统变量
#查看所有的系统变量
show [session|global] variables;

#通过模糊匹配查找变量
show [session|global] variables like '...';

#查看指定变量的值
select @@[session|global] 系统变量名;


/*
示例：
show variables ;

show variables like 'auto%';

show global variables;

show global variables like 'auto%';

select @@autocommit;

select @@global.autocommit;
*/

-- 设置系统变量
set [session|global] 系统变量名=值;

set @@[session|global] 系统变量名=值;

/*
不指定全局还是会话，则默认为session

mysql服务重新启动之后，所设置的全局参数会失效（就是你set过的值会恢复为mysql给它的初始值），想要不失效，可以在/etc/my.cnf中配置
*/

-- 用户自定义变量
用户自定义变量 是用户根据自己需要自行定义的变量，用户变量不用提前声明，在用的时候直接用'@变量名'使用就可以。其作用域为当前连接（就是当前会话）。

-- 赋值
set @var_name=expr [,@var_name=expr]...;
set @var_name:=expr [,@var_name:=expr]...;

select @var_name:=expr [,@var_name:=expr]...;
#将表中查询出的数据赋给变量
select 字段名 into @var_name from 表名;



-- 使用变量
select @var_name;

/*
示例：
-- 赋值
set @myname = 'itcast';
set @myage:=10;
set @mygender:='男',@myhobby:='原神';
select @mycolor = 'red';
select count(*) into @mycount from student;
-- 使用
select @myname,@myage,@mygender,@mycolor,@mycount;
*/

#注意：
用户定义的变量无需对其进行声明或者初始化，只不过不赋值的话获取的值则为null

select @abc;  -- 返回null

-- 局部变量
局部变量 是根据需要定义的在局部生效的变量，访问之前，需要declare声明。可以作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的begin...end块。

-- 声明
declare 变量名 变量类型[default...];
#变量类型就是数据库字段类型：int、bigint，char、varchar、date、time等

-- 赋值
set 变量名=值;
set 变量名:=值;
select 字段名 into 变量名 from 表名...;

/*
示例：
create procedure p2()
begin
    declare stu_count int default 0;
#     set stu_count := 100;
    select count(*)  into stu_count from student;
    select stu_count;
end;

call p2;
*/

14.4 if

-- 语法：
if 条件1 then
	...
elseif 条件2 then
	...
else
	...
end if;


#示例：
-- 创建存储过程
drop procedure if exists p3;
create procedure p3()
begin
    declare score int default 58;-- 将score默认设置为58
    declare result varchar(10);
    if score >=85 then
        set result := '优秀';
    elseif score>=60 then
        set result :='及格';
    else
        set result := '不及格';
    end if;
    select result;
end;

-- 执行
call p3;


-- 结果
不及格

14.5 参数

类型	含义	备注
in	该类参数作为输入，也就是需要调用时传入值	默认
out	该类参数作为输出，也就是该参数可以作为返回值
inout	既可以作为输入参数，又可以作为输出参数

-- 用法
create procedure 存储过程名称([in/out/inout 参数名 参数类型])
begin
	SQL语句
end;

#示例（if升级版）：
drop procedure if exists p4;
create procedure p4(in score int,out result varchar(10))
begin
    if score >=85 then
        set result := '优秀';
    elseif score>=60 then
        set result :='及格';
    else
        set result := '不及格';
    end if;
end;

call p4(70,@result);-- 传入实参跟返回值
select @result;



#将传入的200分制的分数进行换算，换算成百分制，然后返回分数
create procedure p5(inout score double)
begin
    set score := score * 0.5;
end;

set @score = 78;
call p5(@score);
select @score;
-- 这里我们自定义了一个变量，变量经过存储过程之后，会把结果赋给该变量

14.6 case

-- 语法一
case case_value
	when when_value1 then statement_list1
	[when when_value2 then statement_list2]...
	[else statement_list]
end case;

-- 语法二
case
	when search_condition1 then statement_list1
	[when search_condition2 then statement_list2]...
	[else statement_list]
end case;


#示例：
create procedure p6(in month int)
begin
    declare result varchar(10);

    case
        when month >= 1 and month <= 3 then
            set result = '第一季度';
        when month >= 4 and month <= 6 then
            set result = '第二季度';
        when month >= 7 and month <= 9 then
            set result = '第三季度';
        when month >= 10 and month <= 12 then
            set result = '第四季度';
        else
            set result := '非法参数';
    end case;

    select concat('您输入的月份为：',month,',所属的季度为：',result);
end;

call p6(12);

14.7 while

#先判定条件，如果条件为true，则执行逻辑，否则不执行
-- 具体语法：
while 条件 do
		SQL逻辑...
end while;

#计算从1累加到n的值，n为传入的参数值
create procedure p7(in n int)
begin
    declare total int default 0;
    while n>0 do
        set total :=total +n;
        set n := n-1;
        end while;
    select total;
end;

call p7(10);

14.8 repeat

#先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
repeat
	SQL逻辑...
	until 条件
end repeat;


#计算从1累加到n的值，n为传入的参数值
create procedure p8(in n int)
begin
    declare total int default 0;
    repeat
        set total := total+n;
        set n :=n-1;
    until n<=0
    end repeat;
    select total;
end;

call p8(10);

14.9 loop

-- loop实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。loop可以配合以下两个语句使用：
leave 配合循环使用，退出循环
iterate 必须在循环中使用，作用类似于continue

-- 语法：
[begin_label:]loop
	SQL逻辑...
end loop[end_label];

leave label; -- 退出指定标记的循环体
iterate label; -- 直接进入下一次循环


-- 计算1到n的偶数累加值
create procedure p10(in n int)
begin
     declare total int default 0;

    sum:loop
        if n<=0 then
            leave sum;
        elseif n%2 = 1 then
            set n := n-1;
            iterate sum;
        end if;
        set total := total + n;
        set n := n-1;
    end loop sum;-- 如果只有一个标签的话，这句可以省略标签

    select total;
end;

call p10(10);

14.10 游标

-- 由于局部变量只能接受单列的数据，对于表数据则没有办法，游标因此而生

/*
游标（cursor）是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环处理。游标的使用包括游标的声明、open、fetch和close。
*/
-- 语法：

#声明游标
declare 游标名称 cursor for 查询语句

#打开游标
open 游标名称

#获取游标记录
fetch 游标名称 into 变量[,变量];

#关闭游标
close 游标名称;

14.11 条件处理程序

-- 条件处理程序（handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

-- 语法：
declare handler_action handler for condition_value[,condition_value]... statement;

/*
-- hander_action
	continue:继续执行当前程序
	exit：终止执行当前程序

-- condition_value
	SQLSTATE sqlstate_value:状态码，如02000
	SQLWARNING：所有以01为开头的SQLSTATE代码的简写
	NOT FOUND：所有以02开头的SQLSTATE代码的简写
	SQLEXCEPTION：所有没有被SQLWARNING或者NOT FOUND捕获的SQLSTATE代码的简写

-- 官方文档，查询每一种状态码
https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html

-- 逻辑：
-- A.声明游标，存储查询结果集
-- B.准备：创建表结构
-- C.开启游标
-- D.获取游标中的记录
-- E.插入数据到新表中
-- F.关闭游标
create procedure p11(in uage int)
begin
    declare uname varchar(100);
    declare upro varchar(100);
    declare u_cursor cursor for select name, profession from tb_user where age <= uage;
-- 局部变量的声明必须在游标声明之前

    -- 当状态码为02000时，进行退出操作，同时关闭游标
    declare exit handler for SQLSTATE '02000' close u_cursor;
     -- SQLSTATE '02000' 改成 not found 也行

    drop table if exists tb_user_pro;
    create table if not exists tb_user_pro
    (
        id         int primary key auto_increment,
        name       varchar(100),
        profession varchar(100)
    );

    open u_cursor;
    while true
        do
            fetch u_cursor into uname,upro;
            insert into tb_user_pro values (null, uname, upro);
        end while;
    close u_cursor;
end;

call p11(40);

14.12 存储函数

-- 存储函数是有返回值的存储过程，存储函数的参数只能是in类型的

-- 语法：
create function 存储函数名称([参数列表])
returns type[characteristic...]
begin
	SQLy语句
	return...
end;

-- characteristic说明：
deterministic:相同的输入参数总是产生相同的结果
no sql:不包含SQL语句
reads SQL data:包含读取数据的语句，但不包含写入数据的语句


-- 从1到n的累加
create function fun1(n int)
returns int deterministic
begin
    declare total int default 0;

    while n>0 do
        set total :=total +n;
        set n := n-1;
        end while;
    return total;
end;

select fun1(100);

14.13 触发器

-- 创建
create trigger trigger_name
before/after insert/update/delete
on tbl_name for each row -- 行级触发器
begin
	trigger_statement;
end;

-- 查看
show triggers;

-- 删除
drop trigger [schema_name.]trigger_name; -- 如果没有指定schema_name，默认为当前数据库

-- 定义触发器
-- 插入数据的触发器
create trigger tb_user_insert_trigger
    after insert on tb_user for each row
begin
    insert into user_logs(id,operation,operate_time,operate_id,operate_params) values
    (null,'insert',now(),new.id,concat('插入的数据内容为：id=',new.id,',name=' ,new.name,',phone=',new.phone,',email=',new.email,',profession=',new.profession));
end;


show triggers ;

drop trigger tb_user_insert_trigger;




insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime)
VALUES (25,'二皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',now());



-- 修改数据触发器
create trigger tb_user_update_trigger
    after update on tb_user for each row
begin
    insert into user_logs(id,operation,operate_time,operate_id,operate_params) values
    (null,'update',now(),new.id,
     concat('更新之前的数据：id=',old.id,',name=' ,old.name,',phone=',old.phone,',email=',old.email,',profession=',old.profession,
        '| 更新之后的数据：id=',new.id,',name=' ,new.name,',phone=',new.phone,',email=',new.email,',profession=',new.profession));
end;

update tb_user set age = 32 where id = 23;




-- 删除数据的触发器
create trigger tb_user_delete_trigger
    after delete on tb_user for each row
begin
    insert into user_logs(id,operation,operate_time,operate_id,operate_params) values
    (null,'delete',now(),old.id,
     concat('删除之前的数据：id=',old.id,',name=' ,old.name,',phone=',old.phone,',email=',old.email,',profession=',old.profession));
end;
delete from tb_user where id = 25;



-- 数据更变的记录都可以在对于的日志表中看到