MYSQL---存储过程/触发器

个人笔记不喜勿喷,有不好的地方,请指正!!!

TODO:后期会将视图的笔记也加进来,视图笔记丢失了

存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的

存储过程思想上很简单,就是数据库SQL语言层面的代码封装与重用

存储过程的特点:封装,复用,可以接受参数,也可以返回数据,减少网络交互,效率提升

一.语法:

创建存储过程:

create procedure 存储过程名称([参数列表])

BEGIN

           --SQL语句

End;

调用:

call 名称([参数])

查看:

select * from information_schema.Routines where routine_schma='表名' ---查询指定数据库的存储过程及状态信息

show create procedure 存储过程名称 ---查询某个存储过程

删除:

drop procedure[IF EXISTS]存储过程名称;

注意!!!

存储过程在命令行(linux)中执行创建语句会出现问题:需要通过关键字delimiter指定命令行语句的结束符.因为命令行看到分号意味结束

例句:

---创建

create procedure p1()

begin

          select count(*) from student;

end;

--- 调用

call p1();

---查看

select * from information_schema.Routines where routine_schma='itcast'

show create procedure p1;

----删除

drop procedure IF EXISTS 存储过程名称;

二.参数

in:该类参数作为输入,也就是需要调用时传入值 默认

out:该类参数作为输出,也就是该参数可以作为返回值

inout:既可以作为输入参数,可以作为输出参数;

create procedure 存储过程名称([in/out/inout 参数名 参数类型])

BEGIN

--SQL语句

End;

变量:

系统变量:是MySQL服务器提供,不是用户定义的,属于服务器层面,分为全局变量(global).会话变量(session)

查看系统变量:

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

show [session|global] variables like'.....' 通过like模糊查找

select @@[session|global] 系统变量名;  查看执行变量的值

设置系统变量

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

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

用户定义变量 (用户定义的变量无序对其进行声明和初始话,只不过湖区到的值为null)

是用户根据需要自己定义的变量,用户变量不用提前声明,在用的时候直接用"@变量名"使用就可以,其作用域为当前连接.

声明赋值:

set @变量名 = 值

set @变量名 := 值,@变量名 := 值

select @变量名 := 值

select 字段名 into@变量名 from 表名;表示查询出来的数据赋值给这个变量

使用

select @变量名;

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

声明变量:

declare 变量名 变量类型[default...];

赋值:

set 变量名=值;

set 变量名 := 值,变量名 := 值

select 变量名 := 值

三.函数

if 语法:

if 条件1 then elseif 条件2 then  else ...  end if;

case 语法:

1.case 字段名

        when 条件 then 执行语句

        when 条件 then 执行语句

   end case;

2.case

        when 条件 then 执行语句

        when 条件 then 执行语句

   end case;  

循环

1.while 条件 do

      sql逻辑...

end while;

2.repeat 是有条件的循环控制语句,当满足条件的时候退出循环,具体语法为:

repeat

              sql逻辑...

              until(直到)...

end repeat;

3.loop 实现简单的循环,如果不在sql逻辑中增加退出循环的条件,可以用其来实现简单的死循环,loop可以配合以下两个语句使用:

leave:配合循环使用,退出循环

iterate:必须用在循环中,作用是跳过当前循环剩下的语句,进入下一次循环

语法:

[begin_label:] loop

                sql逻辑..

end loop [end_label];

四.游标(sursor) 先声明变量在声明游标

游标(sursor):是用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果集进行循环的处理,游标的使用包括游标的声明,open,fetch 和close,其语法分别如下

声明游标

declare 游标名称 cursor for 查询语句;

打开游标:

open 游标名称;

获取游标记录:

fetch 游标名称 into 变量[,变量];

关闭游标:

close 游标名称;

五.条件处理程序:

可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤,具体语法为:

declare handler_action handler for condition_value[,condition_value]... sql逻辑

handler_action:

         continue:继续执行当前程序

         exit:终止执行当前程序

condition_value:

        sqlstate sqlstate_value: 状态码,如02000

        sqlwarning:所有以01开头的sqlstate代码的简写

        not found:所有以02开头的sqlstate代码的简写

        sqlexception: 所有没有被sqlwarning和not found捕获的sqlstate代码简写

例子 declare exit handler for sqlstate '02000' close 游标名称;

例句

create procedure p11(int uage int)

begin

        declare unam varchar(100)

        declare upro varchar(100)

        declare u_cursor cursor for select name, profession from tb_user where age <= uage; ---- 定         义游标

        declare exit handler sqlstate '02000' close u_cursor; --- 定义条件处理程序

        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 unam,upro; ---获取游标记录

                insert into tb_user_pro values(bull,uname,upro);

        end while;

        close u_cursor; ----关闭游标

end;

六 存储函数

存储函数是有返回值的存储过程,存储函数的参数只能是in类型的,具体语法为:

create function 存储函数名称([参数列表])

returns 返回值类型 [characteristic....]    ---执行返回值的类型

begin

        --SQL语句

        return....;  ---将结果返回

end;

characteristic说明 (在mysql 8+版本 是默认开启二进制日志的所以必须指定)

determinstic:相同的输入参数总是产生相同的结果

no sql : 不包含sql语句

reads sql data:包含读取数据的语句,但是不包含写入数据的语句

例句

从1到n的累加

create function fun1(in n int)

returns int determinstic

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语句集合,触发器的这种特性可以协助应用在数据库段确保数据的完整性,日志记录,数据校验等操作,使用别名OLD和NEW来引用触发器中发生变化的记录内容,这与其他的数据库是相似的,现在触发器还只支持行级触发,不支持语句级触发

Old 表示之前哪一行的数据,nwe 表示新的一行的数据

语法:

 

例:插入数据的触发器记录日志

Create trigger  触发器名

 after insert on 表名 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,....))