【MySQL】存储过程与触发器的合集

发布于:2024-11-29 ⋅ 阅读:(34) ⋅ 点赞:(0)

区别特性

特性 存储过程 触发器
定义 一组预编译的SQL语句,可通过调用来执行 一种自动执行的数据库对象,当某表的INSERTUPDATEDELETE操作发生时触发
触发时机 需要显式调用(CALL 由表的指定事件自动触发
使用场景 复杂的业务逻辑、多次复用的操作 实现数据自动校验、记录日志、同步数据等简单的自动化处理
参数支持 支持IN(输入)、OUT(输出)和INOUT(输入输出)参数 不支持参数
调用方式 通过CALL 存储过程名(参数)调用 绑定到表的事件上,自动触发
依赖对象 独立于表,可以在多个表中使用 必须绑定到具体的表
维护难度 可维护性较好,适用于复杂逻辑 逻辑直接嵌入触发器,适合简单场景,但复杂触发器难以维护
性能影响 可以复用和优化,效率高 频繁触发时可能影响性能

案例演示

1.sql数据准备,以黑马数据演示


create database itcase;
show databases;
use itcast;
-- 建表
CREATE TABLE tb_users(
    id         INT PRIMARY KEY,
    name       VARCHAR(255),
    phone      VARCHAR(20),
    email      VARCHAR(255),
    profession VARCHAR(255),
    age        INT,
    gender     INT,
    status     INT,
    createtime DATETIME
);

INSERT INTO tb_users (id, name, phone, email, profession, age, gender, status, createtime)
VALUES (1, '吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, 1, 6, '2001-02-02 00:00:00'),
       (2, '曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, 1, 0, '2001-03-05 00:00:00'),
       (3, '赵云', '17799990002', '177999900@139.com', '英语', 34, 1, 2, '2002-03-02 00:00:00'),
       (4, '孙悟空', '17799990003', '177999900@sina.com', '工程造价', 54, 1, 0, '2001-07-02 00:00:00'),
       (5, '花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, 2, 1, '2001-04-22 00:00:00'),
       (6, '大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, 2, 0, '2001-02-07 00:00:00'),
       (7, '露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, 2, 0, '2001-02-08 00:00:00'),
       (8, '程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, 1, 0, '2001-05-23 00:00:00'),
       (9, '项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, 1, 1, '2001-09-18 00:00:00'),
       (10, '白起', '17799990009', 'baiqi666@163.com', '机械工程及其自动化', 27, 1, 2, '2001-08-16 00:00:00'),
       (11, '韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工程', 27, 1, 0, '2001-06-12 00:00:00'),
       (12, '荆轲', '17799990011', 'jingke123@163.com', '会计', 29, 1, 0, '2001-05-11 00:00:00'),
       (13, '兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, 1, 1, '2001-04-09 00:00:00'),
       (14, '狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, 1, 2, '2001-04-10 00:00:00'),
       (15, '貂蝉', '17799990014', '8495848374@qq.com', '软件工程', 40, 2, 1, '2001-02-12 00:00:00'),
       (16, '妲己', '17799990015', '2782382939@qq.com', '软件工程', 31, 2, 0, '2001-01-30 00:00:00'),
       (17, '半月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, 2, 0, '2000-05-03 00:00:00'),
       (18, '赢政', '17799990017', '8839434342@qq.com', '化工', 38, 1, 1, '2001-08-08 00:00:00'),
       (19, '狄仁杰', '17799990018', 'jujiamlm816@163.com', '国际贸易', 30, 1, 1, '2007-03-12 00:00:00'),
       (20, '安琪拉', '17799990019', 'jdodmlh@126.com', '城市规划', 51, 2, 0, '2001-08-15 00:00:00'),
       (21, '典韦', '17799990020', 'ycauanjian@163.com', '城市规划', 52, 1, 0, '2000-04-12 00:00:00'),
       (22, '廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, 1, 3, '2002-07-18 00:00:00'),
       (23, '后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, 1, 0, '2002-03-10 00:00:00'),
       (24, '姜子牙', '17799990023', '3748384@qq.com', '工程造价', 29, 1, 4, '2003-05-26 00:00:00');


2.存储过程、触发器、游标案例

-- 存储过程
-- 存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合(通常包含多个sql语句,相当于一次通讯,执行多条语句,减少单次执行所耗费的巨量通讯耗时)
-- 创建
create procedure p1()
begin
    select count(*) from student;
end;

-- 在命令行复制执行create时要先改一下delimiter $$
--     随后改回delimiter ;
-- 调用
call p1();

-- 查看存储过程
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';

show create procedure p1;

-- 删除
drop procedure if exists p1;

-- 查看系统变量
show variables;
-- 查看会话级别系统变量
show session variables;


show session variables like 'auto%'; -- 会话
show global variables like 'auto%'; -- 全局

-- 准确查询某一个系统变量的值
select @@autocommit;

-- 设置系统变量
set session autocommit = 0;
select @@autocommit;
select @@session.autocommit;
set session autocommit = 1;

-- 用户变量
set @myname = 'itcast';
set @myage := 10;
set @mygender := '男', @myhobby := 'java';

select @mycolor := 'red';
select count(*) into @mycount from tb_users;

-- 使用
select @myname, @myage, @mygender, @myhobby;
select @mycolor, @mycount;

-- 局部变量
-- 在begin和end之前存在
create procedure p2()
begin
    declare stu_count int default 0;
    select count(*) into stu_count from student;
    set stu_count = 55;
    select stu_count;

end;

call p2();

-- if判断
create procedure p3()
begin
    declare score int default 58;
    declare rs varchar(10);

    if score >= 85 then
        set rs = '优秀';
    elseif score >= 60 then
        set rs = '及格';
    else
        set rs = '不及格';
    end if;
    select rs;

end;

call p3();

/*
 参数:
 IN
该类参数作为输入,也就是需要调用时传入值
OUT
该类参数作为输出,也就是该参数可以作为返回值
INOUT
既可以作为输入参数,也可以作为输出参数
 */
create procedure p4(in score int, out rs varchar(10))
begin
    if score >= 85 then
        set rs = '优秀';
    elseif score >= 60 then
        set rs = '及格';
    else
        set rs = '不及格';
    end if;
end;

call p4(98, @rs);
select @rs;

create procedure p5(inout score double)
begin
    set score := score * 0.5;
end;

set @rs = 178;
call p5(@rs);
select @rs;

call p4(89, @rs);
select @rs;

-- case
create procedure p6(in month int)
begin
    declare rs varchar(10);

    case
        when month >= 3 and month <= 5 then
            set rs := '第一季度';
        when month >= 6 and month <= 8 then
            set rs := '第二季度';
        when month >= 9 and month <= 11 then
            set rs := '第三季度';
        when month >= 12 and month <= 2 then
            set rs := '第四季度';
        else
            set rs := '非法参数';
    end case;

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

drop procedure if exists p6;

call p6(7);

-- while 满足条件进行循环
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(100);

-- repeat 重复 满足条件退出循环
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);

drop procedure if exists p8;

-- loop 退出时要leave loop名
create procedure p9(in n int)
begin
    declare total int default 0;

    sum:loop
        if n <= 0 then
            leave sum;
        end if;

        set total := total + n;
        set n := n - 1;
    end loop sum;

    select total;
end;

call p9(15);

-- 结束循环的条件放前放后是不一样的
create procedure p10(in n int)
begin
    declare total int default 0;

    sum:loop
        set total := total + n;
        set n := n - 1;

        if n < 0 then
            leave sum;
        end if;

    end loop sum;

    select total;
end;

call p10(15);

-- loop 计算从1到n之间偶数累加的值,n为传入的参数
create procedure p11(in n int)
begin
    declare total int default 0;

    evenSum:loop

        if n % 2 = 1 then
            set n := n - 1;
            iterate evenSum;    -- iterate 迭代跳过
        end if;

        set total := total + n;
        set n := n - 1;

        if n < 0 then
            leave evenSum;
        end if;
    end loop evenSum;

    select total;
end;

drop procedure if exists p11;

call p11(2);

/*
 游标
根据传入的参数uage,来查询用户表tb_User中,所有的用户年龄小于等于Uage的用户姓名(name)和专业(profession),
并将用户的姓名和专业插入到所创建的一张新表(id,name,profession.)中。
 */
-- 逻辑:
-- A.声明游标,存储查询结果集
-- B.准备:创建表结构
-- C.开启游标
-- D.获取游标中的记录
-- E.插入数据到新表中
-- F.关闭游标

create procedure p12(in uage int)
begin
    -- 先声明普通变量,才能声明游标
    declare uname varchar(100); -- 为了存储游标中的查询数据,声明变量
    declare upro varchar(100);
    -- 声明游标,存储查询结果
    declare u_cursor cursor for select name, profession from tb_users where age <= uage;
    -- 退出条件 sqlstate '02000',也可以写成
    -- declare exit handler for not found close u_cursor;
    declare exit handler for sqlstate '02000' close u_cursor;

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

    open u_cursor;  -- 开启游标
    while true do   -- 这里直接不处理条件会触发No data fetched 错误码02000
        fetch u_cursor into uname, upro;    -- fetch 捕获游标中的记录
        insert into tb_user_pro values (id, uname, upro);
    end while;
    close u_cursor; -- 关闭游标
end;

drop procedure if exists p12;

call p12(40);

-- 存储函数 必须要有返回值,所以一般都用存储过程替代存储函数
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);

-- 触发器
/*
触发器是与表有关的数据库对象,指在insert/update./delete之前或之后,触发并执行触发器中定义的SQL语句集合。触发器的这种特
性可以协助应用在数据库端确保数据的完整性,日志记录,数据校验等操作。
 */

-- 触发器
-- 需求:通过触发器记录User表的数据变更日志(User_Logs),包含增加,修改,删除;
-- 准备工作:日志表User-Logs

-- user-log表表结构
create table user_logs(
  id int(11) not null auto_increment,
  operation varchar(20) not null comment '操作类型, insert/update/delete',
  operate_time datetime not null comment '操作时间',
  operate_id int(11) not null comment '操作的ID',
  operate_params varchar(500) comment '操作参数',
  primary key(`id`)
)engine=innodb default charset=utf8;

-- 1.插入数据的触发器
create trigger tb_user_insert_trigger
    after insert on tb_users 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, ', profession=', new.profession));
end;

-- show
show triggers;

-- drop
drop trigger if exists tb_user_insert_trigger;

-- 插入数据到tb_user 表,验证user_logs表有没有被触发器更新日志
insert into tb_users(id, name, phone, email, profession, age, gender, status, createtime) VALUES (26,'二皇子','18809091211','erhuangzi@163.com','软件工程',23,'1','1',now());

-- 2.修改数据的触发器
create trigger tb_user_update_trigger
    after update on tb_users 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, ',profession=', old.profession,
                            ' | 更新之后的数据:id=', new.id, ',name=', new.name, ',phone=', new.phone, ',profession=', new.profession));
end;

show triggers;
-- 测试
update tb_users set age = 32 where id = 23;

-- update 触发器会触发5次
update tb_users set profession = '会计' where id <= 5;

drop trigger if exists tb_user_update_trigger;

-- 3.删除数据的触发器
create trigger tb_user_delete_trigger
    after delete on tb_users for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
    (null, 'delete', now(), old.id,  -- 删除只需要返回old.id
     concat('删除之前的数据:id=', old.id, ',name=', old.name, ',phone=', old.phone, 'email=', old.email, ',profession=', old.profession));
end;

show triggers;
drop trigger if exists tb_user_delete_trigger;

-- 测试
delete from tb_users where id = 25;