MySQL存储过程与触发器实战解析

内容导读

• MySQL存储过程

• MySQL触发器

一、MySQL存储过程

1.1 存储过程介绍

1、存储过程作用

将能够完成特定功能的SQL指令进行封装（SQL指令集），编译之后存储在数据库服务器上，并且为之取一个名字，客户端可以通过名字直接调用这个SQL指令集，获取执行结果。

2、存储过程优缺点

（1）优点

• SQL指令无需客户端编写，通过网络传送，可以节省网络开销，同时避免SQL指令在网络传输过程中被恶意篡改保证安全性

• 存储过程经过编译创建并保存在数据库中的，执行过程无需重复的进行编译操作，对SQL指令的执行过程进行了性能提升

• 存储过程中多个SQL指令之间存在逻辑关系，支持流程控制语句（分支、循环），可以实现更为复杂的业务

• 存储过程中可以使用事务管理，避免了数据的不一致或错误的问题

（2）缺点

• 存储过程是根据不同的数据库进行编译、创建并存储在数据库中；当我们需要切换到其他的数据库产品时，需要重写编写针对于新数据库的存储过程

• 存储过程受限于数据库产品，如果需要高性能的优化会成为一个问题

• 在互联网项目中，如果需要数据库的高（连接）并发访问，使用存储过程会增加数据库的连接执行时间（因为我们将复杂的业务交给了数据库进行处理）

1.2 创建存储过程

# 语法:
create procedure 存储过程名称 ([IN/OUT args])
begin
-- SQL
end;
# 创建一个存储过程实现加法运算:Java语法中，方法是有参数和返回值的，存储过程中，是有输入参数和输出参数的
create procedure proc_test1(IN a int,IN b int,OUT c int)
begin
   SET c = a+b;
end;

1.3 调用存储过程

# 调用存储过程
# 定义变量@m
set @m = 0;
-- 调用存储过程，将3传递给a，将2传递给b，将@m传递给c
call proc_test1(3,2,@m);
-- 显示变量值
select @m from dual;

1.4 存储过程中变量的使用

存储过程中的变量分为两种：局部变量和用户变量

定义局部变量

局部变量：定义在存储过程中的变量，只能在存储过程内部使用

# 局部变量要定义在存储过程中，而且必须定义在存储过程开始
declare <attr_name> <type> [default value];
create procedure proc_test2(IN a int,OUT r int)
begin
 declare x int default 0;  -- 定义x  int类型，默认值为0
 declare y int default 1;  -- 定义y
 set x = a*a;
 set y = a/2; 
 set r = x+y;
end;

定义用户变量

用户变量：相当于全局变量，定义的用户变量可以通过select @attrName from dual进行查询

# 用户变量会存储在mysql数据库的数据字典中（dual）
# 用户变量定义使用set关键字直接定义，变量名要以@开头
set @n=1;

给变量设置值

无论是局部变量还是用户变量，都是使用set关键字修改值

set @n=1;
call proc_test2(6,@n);
select @n from dual;

将查询结果赋值给变量

在存储过程中使用select..into..给变量赋值

# 查询学生数量
create procedure proc_test3(OUT c int)
begin
   select count(stu_num) INTO c from students; -- 将查询到学生数量赋值给c
end;
​
# 调用存储过程
call proc_test3(@n);
select @n from dual;

用户变量使用注意事项

因为用户变量相当于全局变量，可以在SQL指令以及多个存储过程中共享，在开发中建议尽量少使用用户变量，用户变量过多会导致程序不易理解、难以维护。

1.5 存储过程的参数

MySQL存储过程的参数一共有三种：IN \ OUT \ INOUT

1、IN 输入参数

输入参数：在调用存储过程中传递数据给存储过程的参数（在调用的过程必须为具有实际值的变量 或者 字面值）

# 创建存储过程：添加学生信息
create procedure proc_test4(IN snum char(8),IN sname varchar(20), IN gender char(2), IN age int, IN cid int, IN remark varchar(255))
begin
  insert into students(stu_num,stu_name,stu_gender,stu_age,cid,remark)
        values(snum,sname,gender,age,cid,remark);
end;
​
call proc_test4('20210108','小丽','女',20,1,'aaa');

2、OUT 输出参数

输出参数：将存储过程中产生的数据返回给过程调用者，相当于Java方法的返回值，但不同的是一个存储过程可以有多个输出参数

# 创建存储过程，根据学生学号，查询学生姓名
create procedure proc_test5(IN snum char(8),OUT sname varchar(20))
begin
   select stu_name INTO sname from students where stu_num=snum;
end;
​
set @name='';
call proc_test5('20210108',@name);
select @name from dual;

3、INOUT 输入输出参数

create procedure proc_test6(INOUT str varchar(20))
begin
   select stu_name INTO str from students where stu_num=str;
end;
​
set @name='20210108';
call proc_test6(@name);
select @name from dual;

1.6 存储过程中流程控制

在存储过程中支持流程控制语句用于实现逻辑的控制

1、分支语句

• if-then-else

# 单分支：如果条件成立，则执行SQL
if conditions then
        -- SQL
end if;
​
# 如果参数a的值为1，则添加一条班级信息
create procedure proc_test7(IN a int)
begin
         if a=1 then
            insert into classes(class_name,remark) values('Java2109','test');
         end if;
end;
# 双分支：如果条件成立则执行SQL1，否则执行SQL2
if conditions then
        -- SQL1
else
        -- SQL2
end if;
​
# 如果参数a的值为1，则添加一条班级信息；否则添加一条学生信息
create procedure proc_test7(IN a int)
begin
         if a=1 then
            insert into classes(class_name,remark) values('Java2109','test');
         else
            insert into students(stu_num,stu_name,stu_gender,stu_age,cid,remark) values('20210110','小花','女',19,1,'...');
         end if;
end;

• case

# case
create procedure proc_test8(IN a int)
begin
  case a
        when 1 then  
            -- SQL1   如果a的值为1  则执行SQL1
            insert into classes(class_name,remark) values('Java2110','wahaha');
        when 2 then
            -- SQL2   如果a的值为2  则执行SQL2
            insert into students(stu_num,stu_name,stu_gender,stu_age,cid,remark) 
            values('20210111','小刚','男',21,2,'...');
        else
            -- SQL (如果变量的值和所有when的值都不匹配，则执行else中的这个SQL)
            update students set stu_age=18 where stu_num='20210110';
  end case;
end;

2、循环语句

• while

# while
create procedure proc_test9(IN num int)
begin
  declare i int;
        set i = 0;
        while i<num do
            -- SQL
            insert into classes(class_name,remark) values( CONCAT('Java',i) ,'....');
            set i = i+1;
        end while;
end;
​
call proc_test9(4);

• repeat

# repeat
create procedure proc_test10(IN num int)
begin
        declare i int;
        set i = 1;
        repeat
            -- SQL
            insert into classes(class_name,remark) values( CONCAT('Python',i) ,'....');
            set i = i+1;
        until i > num end repeat;
end;
​
call proc_test10(4);

• loop

# loop
create procedure proc_test11(IN num int)
begin
   declare i int ;
         set i =0;
         myloop:loop
            -- SQL
            insert into classes(class_name,remark) values( CONCAT('HTML',i) ,'....');
            set i = i+1;
            if i=num then
               leave myloop;
            end if;
         end loop;
end;
​
call proc_test11(5);

1.7 存储过程管理

1、查询存储过程

存储过程是属于某个数据库的，也就是说当我们将存储过程创建在某个数据库之后，只能在当前数据库中调用此存储过程。

查询存储过程：查询某个数据库中有哪些存储过程

# 根据数据库名，查询当前数据库中的存储过程
show procedure status where db='db_test2';

​# 查询存储过程的创建细节
show create procedure db_test2.proc_test1;

2、修改存储过程

修改存储过程指的是修改存储过程的特征/特性

alter procedure <proc_name> 特征1 [特征2 特征3 ....]

存储过程的特征参数：

• CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句

• NO SQL 表示子程序中不包含 SQL 语句

• READS SQL DATA 表示子程序中包含读数据的语句

• MODIFIES SQL DATA 表示子程序中包含写数据的语句

• SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行

   DEFINER 表示只有定义者自己才能够执行

        INVOKER 表示调用者可以执行

• COMMENT 'string' 表示注释信息

alter procedure proc_test1 READS SQL DATA;

3、删除存储过程

# 删除存储过程
# drop 删除数据库中的对象 数据库、数据表、列、存储过程、视图、触发器、索引....
# delete 删除数据表中的数据
drop procedure proc_test1;

1.8 游 标

问题：如果我们要创建一个存储 过程，需要返回查询语句查询到的多条数据，该如何实现呢？

1、游标的概念

游标可以用来依次取出查询结果集中的每一条数据——逐条读取查询结果集中的记录

2、游标的使用步骤

step1：声明游标

declare mycursor cursor for select book_name,book_author,book_price from books;

step2：打开游标

open mycursor;

step3：使用游标，提取游标当前指向的记录（提取之后，游标自动下移）

# 定义新的属性名
fetch mycursor into bname,bauthor,bprice;

step4：关闭游标

close mycursor; 

3、游标使用案例

# 游标使用案例
create procedure proc_test2(OUT result varchar(200))
begin
   declare bname varchar(20);
         declare bauthor varchar(20);
         declare bprice decimal(10,2);
         declare num int; 
         declare i int;
         declare str varchar(50);
         -- 此查询语句执行之后返回的是一个结果集（多条记录），使用游标可以来遍历查询结果集
         declare mycursor cursor for select book_name,book_author,book_price from books;
         select count(1) INTO num from books;
         -- 打开游标
         open mycursor;
         -- 使用游标要结合循环语句
         set i=0;
         while i<num do
                  -- 使用游标：提取游标当前指向的记录（提取之后，游标自动下移）
                        FETCH mycursor INTO bname,bauthor,bprice;
                        set i=i+1;
                        -- set str=concat_ws('~',bname,bauthor,bprice);
                        select concat_ws('~',bname,bauthor,bprice) INTO str;
                        set result = concat_ws(',',result,str);
         end while;

         -- 关闭游标
         close mycursor;
end;

# 案例测试
set @r = '';
call proc_test2(@r);
select @r from dual;

二、MySQL触发器

1、触发器介绍

触发器，就是一种特殊的存储过程。触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段，但是触发器无需调用，当对数据表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动调用。

在MySQL，只有执行insert\delete\update操作才能触发触发器的执行。

2、触发器的使用

案例说明

# 学生信息表
create table students(
        stu_num char(4) primary key,
        stu_name varchar(20) not null,
        stu_gender char(2) not null,
        stu_age int not null
);
​
# 学生信息操作日志表
create table stulogs(
        id int primary key auto_increment,
        time TIMESTAMP,
        log_text varchar(200)
);
# 当向students表中添加学生信息时，同时要在 stulogs表中添加一条操作日志
insert into students(stu_num,stu_name,stu_gender,stu_age) values('1004','夏利','女',20);
# 手动进行记录日志
insert into stulogs(time,log_text) values(now(),'添加1004学生信息');

3、案例解析

当向学生信息表添加、删除、修改学生信息时，使用触发器自定进行日志记录

创建触发器

create trigger tri_name
<before|after>                     -- 定义触发时机
<insert|delete|update>             -- 定义DML类型
ON <table_name>
for each row                       -- 声明为行级触发器（只要操作一条记录就触发触发器执行一次）
sql_statement                      -- 触发器操作
# 创建触发器：当学生信息表发生添加操作时，则向日志信息表中记录一条日志
create trigger tri_test1
after insert on students
for each row
insert into stulogs(time,log_text) values(now(), concat('添加',NEW.stu_num,'学生信息'));

查看触发器

show triggers;

测试触发器

我们创建的触发器是在students表发生insert操作时触发，我们只需执行学生信息的添加操作

# 测试1：添加一个学生信息，触发器执行了一次
insert into students(stu_num,stu_name,stu_gender,stu_age) values('1005','小明','男',20);
​
# 测试2：一条SQL指令添加了2条学生信息，触发器就执行了2次
insert into students(stu_num,stu_name,stu_gender,stu_age) values('1006','小刚','男',20),('1007','李磊','男',20);

删除触发器

drop trigger tri_test1;

4、NEW与OLD(了解)

触发器用于监听对数据表中数据的insert、delete、update操作，在触发器中通常处理一些DML的关联操作；我们可以使用NEW和OLD关键字在触发器中获取触发这个触发器的DML操作的数据

• NEW : 在触发器中用于获取insert操作添加的数据、update操作修改后的记录

• OLD：在触发器中用于获取delete操作删除前的数据、update操作修改前的数据

（1）NEW

• insert操作中：NEW表示添加的新记录

create trigger tri_test1
after insert on students
for each row
insert into stulogs(time,log_text) values(now(), concat('添加',NEW.stu_num,'学生信息'));

• update操作中：NEW 表示修改后的数据

# 创建触发器 : 在监听update操作的触发器中，可以使用NEW获取修改后的数据
create trigger tri_test2
after update on students for each row
insert into stulogs(time,log_text) values(now(), concat('修改学生信息为：',NEW.stu_num,NEW.stu_name));

（2）OLD

• delete操作中：OLD表示删除的记录

create trigger tri_test3
after delete on students for each row
insert into stulogs(time,log_text) values(now(), concat('删除',OLD.stu_num,'学生信息'));

• update操作中：OLD表示修改前的记录

create trigger tri_test2
after update on students for each row
insert into stulogs(time,log_text) values(now(), concat('将学生姓名从【',OLD.stu_name,'】修改为【',NEW.stu_name,'】'));

5、触发器使用总结

（1）优点

• 触发器是自动执行的，当对触发器相关的表执行响应的DML操作时立即执行

• 触发器可以实现表中的数据的级联操作（关联操作），有利于保证数据的完整性

• 触发器可以对DML操作的数据进行更为复杂的合法性校验

（2）缺点

• 使用触发器实现的业务逻辑如果出现问题将难以定位，后期维护困难

• 大量使用触发器容易导致代码结构杂乱，增加了程序的复杂性

• 当触发器操作的数据量比较大时，执行效率会大大降低

（3）使用建议

• 在互联网项目中，应避免使用触发器

• 对于并发量不大的项目可以选择使用存储过程，但是在互联网引用中不提倡使用存储过程（原因：存储过程将实现业务的逻辑交给数据库处理，一则增减了数据库的负载，二则不利于数据库的迁移）

更多精彩内容请关注本站！！！