Mysql 存储过程+触发器+存储函数+游标

视图（view）

虚拟存在的表，不保存查询结构，只保存查询的sql逻辑

语法

存储过程

实现定义并存储在数据库的一段sql语句的集合，可减少网络交互，提高性能，代码复用,内容包括：变量，if，case，参数（in/out/inout），循环（while，repeat，loop），游标和handler。

存储过程-基本语法

create和call

create procedure p1()
begin 
    select  count(*) from course;
end;
#调用
call p1();

存储过程-系统变量

#系统变量
show  session  variables ; #查看所有系统变量
show session variables like 'auto%';（等价于 show global variables like 'auto%';）
select  @@session.autocommit;#查看指定系统变量
set session autocommit=0;#设置系统变量，关闭自动提交

存储过程-局部变量

局部变量是存在于存储过程中的，通过declare声明局部变量，同时可以在存储过程中给局部变量赋值

#局部变量
create procedure p2()
begin
    declare stu_count int default 0;#说明
    select count(*) into  stu_count from student;#赋值
    select stu_count;#查询
end;
call p2();

存储过程-用户自定义变量

定义用户变量用@

#用户变量
#赋值方式一：推荐使用：=
set @myname='itcast';
set @myage:=10;
set @mygender:='男',@myhobby:='java';
#赋值方式二：通过select
select  @mycolor:='red';
select  count(*) into @mycount from student;
#查看
select @myage,@myname;

存储过程-参数（in，out，inout）

#存储过程参数 in：该类参数作为输入，也就是需要调用时传入值
#          out：该类参数作为输出，也就是该参数可以作为返回值
#          inout：既可以作为输入参数，也可以作为输出参数

#存储过程参数 in：该类参数作为输入，也就是需要调用时传入值
#          out：该类参数作为输出，也就是该参数可以作为返回值
#          inout：既可以作为输入参数，也可以作为输出参数

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(99,@result);
select  @result;


create procedure p5(inout score int)
begin
    set score:=score*0.5;
end;
#注意点：out和inout需要传的是变量，不能直接传值！！
set  @score=200;
call p5(@score);
select  @score;

存储过程-case

存储过程-while循环

存储过程-repeat循环

存储过程-loop循环

loop循环和leave，iterate关键字配合使用，leave终止循环（相当于break），iterate结束本次循环（相当于continue）

下面例子 计算的是从1加到10的偶数的和

/*
loop循环
 leave：配合循环使用，退出循环
 iterate：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次
*/
create  procedure  p10(in n int)
begin
    declare total int default 0;
    sum:loop
        if n<=0 then
            leave  sum;
        end if;
        if 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)

游标和handler

游标的声明，打开，获取，关闭。游标类似二维数组，又不太像，因为游标获取一条记录就同时向新表插入一条记录。游标声明：从基本表中获取哪些字段数据，游标获取：将游标从基本表中获取的每一组字段插入到新表中

游标语法：

handler类似异常处理，在下面例子中作用是当while循环游标fetch不到下一组数据，反映给handler退出程序。

handler语法：

 代码练习

create procedure p1(in uage int)
begin
    declare uname varchar(100);
    declare ujob varchar(100);
    #注意游标要在变量之后声明
    declare u_cursor cursor for  select  name,job from emp where age<=uage;
    #handler,处理异常
   # declare exit handler for not found close u_cursor;
    drop  table if exists tb_user_pro;
    create table tb_user_pro(
        id int primary key auto_increment,
        name varchar(100),
        professsion varchar(100)
    );
    #打开游标
    open u_cursor;
    while true do
        fetch u_cursor into uname,ujob;#通过游标获取数据
        insert into tb_user_pro values (null,uname,ujob);
        end while;
    #关闭游标
    close u_cursor;
end;
drop  procedure p1;
call p1(30);

存储函数

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

存储函数语法：

 代码：

create function f1(n int)
    returns int deterministic
begin
    declare sum int  default 0;
    while n>0 do
        set sum:=sum+n;
        set n:=n-1;

    end while;
    return sum;
end;

select  f1(100);

触发器

触发器是和表相关的数据库对象，指的是在对表进行增删改查操作前后触发并执行触发器中定义的sql语句，触发器能保证数据的完整性，分为insert触发器，delete触发器，update触发器

inserted表：用来存储INSERT和UPDATE语句所影响的行的副本。意思就是在inserted表中临时保存了被插入或被更新后的记录行。在执行 INSERT 或UPDATE 语句时，新加行被同时添加到inserted表和触发器表中。因此，可以从inserted表检查插入的数据是否满足需求，如不满足则回滚撤消操作。

deleted表：用来存储DELETE和UPDATE语句所影响行的副本。意思是在delete表中临时保存了被删除或被更新前的记录行。在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传到deleted表中。所以可以从deleted表中检查删除的数据行是否能删除。
 

触发器语法

 插入触发器

问题：我们要向表tb_user插入数据，希望在日志表中看到插入后的新记录是什么

 解决：每向表tb_user插入一次，就执行一次insert触发器中的sql语句

代码

create table user_logs(
    id int(11) not null auto_increment primary key,
    operation varchar(20) not null  comment '操作类型,增/删/改',
    operation_time datetime not null comment '操作时间',
    operation_id int(11) not null comment '操作的id',
    operation_params varchar(500) comment '操作参数'

)engine =innodb default charset utf8;

-- 插入数据触发器
create trigger tb_user_insert_trigger
    after insert on tb_user for each row

begin
        insert into user_logs(id, operation, operation_time, operation_id, operation_params)
            VALUES(null,'insert',now(),new.id,concat('插入的内容为：id=',new.id,',name=',NEW.name,',age=',NEW.age));
end;
show triggers ;
insert into tb_user(id, name, age) VALUES (null,'小何',54);

 修改触发器

-- 修改数据触发器
-- 要在日志表中展示修改前后的数据
create trigger tb_user_update_trigger
    after update on tb_user for each row

begin
        insert into user_logs(id, operation, operation_time, operation_id, operation_params)
            VALUES(null,'update',now(),new.id,
                   concat('更新前的内容为：id=',OLD.id,',name=',OLD.name,',age=',OLD.age,
                   '更新后的内容为：id=',new.id,',name=',NEW.name,',age=',NEW.age)
                   );
end;
show triggers ;

update tb_user set name='小林' where id=4;

 删除触发器

create trigger tb_user_delete_trigger
    before delete on tb_user for each row
begin
              insert into user_logs(id, operation, operation_time, operation_id, operation_params)
            VALUES(null,'delete',now(),OLD.id,
                   concat('删除前的内容为：id=',OLD.id,',name=',OLD.name,',age=',OLD.age));
end;

show triggers ;
delete from  first.tb_user where id=4;