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

区别特性

案例演示

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;