mysql存储过程/函数/游标/触发器

存储过程和函数概述

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程和函数的区别在于函数必须有返回值，而存储过程没有。
函数︰是一个有返回值的过程;
过程∶是一个没有返回值的函数﹔

创建存储过程基本语法

create procedure procedure_name([pro_param[...]])
begin 
    sql语句
end;    

由于mysql默认命令结束符为分号,我们在写完SQL语句之后写分号会导致mysql执行该条语句,所以需要改变mysql

的结束符号 (在SQLyog,Navicat等编译器中似乎不存在该问题,在命令行中存在该问题)

delimiter 该关键字用来改变mysql结束符号

dlimiter $      --以$符号来结束命令,用完记得改回来!!!

例如

delimiter $
create procedure pro_test()
begin
    select * from a;
end$
delimiter ;

调用存储过程

call prp_test(); --call + 存储过程名字

查看存储过程

show procedure status; --查看完整信息
select name from mysql.proc where db='数据库名'; --查看name,需要指定数据库

查看存储过程创建语句

show create procedure pro_test;

删除存储过程

drop procedure pro_test;

简单案例

备注:delimiter  该命令在navicat中好像不起作用,一条语句中多个分号可以同时存在,可以不更改命令结束符

1.声明一个存储过程用于查询某表总记录数,将结果存入变量中

delimiter $$                      --声明结束符为$$
create procedure pro_test1()   --创建存储过程
begin                            --开始
declare num int;                --声明num变量
select count(*) into num from stu;--查询总记录数并赋值给num
select num;                            --查看num的值
end$$                            --结束语句

call pro_test3();                --调用存储过程

2.if判断某个值对应的LOL英雄

delimiter $$                      --声明结束符为$$
create procedure pro_test2()    --创建存储过程
begin                            --开始
declare age int default 16;        --声明一个变量,默认值为16
declare description varchar(10) default '';    --声明一个变量用来描述,默认为空串
if age<=6 then                    --判断变量的值
    set description='托儿索';      --判断age变量的值来更改description的值
elseif age>10 and age<13 then    
    set description='小学僧';
else                    
 set description='影流之主';
end if;                            --if结束
select description;                --查询description
end$$                            --结束
call pro_test2()                --调用存储过程查看结果

存储过程的参数传递

基本语法:

create procedure procedure_name([in/out/inout] 参数名 参数类型)
...

in:该参数可以作为输入,也就是需要调用方法传入值,默认
out:该参数作为输出,也就是该参数可以作为返回值
inout:即可以作为输入参数,也可以作为输出参数

3.通过传递的参数判断对应的LOL英雄

delimiter $$          
create procedure pro_test2(in age int)    --输入参数,类型为int
begin
declare description varchar(10) default '';
if age<=6 then
    set description='托儿索';
elseif age>10 and age<13 then
    set description='小学僧';
else
 set description='影流之主';
end if;
select description;
end$$
call pro_test2(5)            --调用时传递相关的值

4.通过传递的参数,返回对应LOL英雄

delimiter $$  
create procedure pro_test2(in age int,out description varchar(10))--加上输入参数和输出参数
begin
if age<=6 then
    set description='托儿索';
elseif age>10 and age<13 then
    set description='小学僧';
else
 set description='影流之主';
end if;
select description;
end$$
call pro_test2(12,@description)  --调用该存储过程返回对应的LOL英雄

5.使用case….when…then结构进行判断(案例同上)

delimiter $$          
create procedure pro_test2(age int)    --输入给定的年龄,输入型in为默认值,可以不写
begin
    declare description varchar(10) default ''; 
case
    when age<8 then 
        set description='托儿索';
    when age>=8 and age<=13 then
        set description='儿童劫';
    when age>14 then
        set description='放逐之刃';
    else
        set description='永恩';
    end case;
select description;
end$$
call pro_test2(5)            --调用时传递相关的值

6.存储过程中的while循环(计算从1加到n的值)

create procedure pro_test2(n int)
begin 
    declare result int default 0;
    declare num int default 1;
    while num<=n do                 --while + 条件 + do
        set result = result + num;  --循环体
        set num = num+1;
    end while;                      --结束while循环
select result;
end;
call pro_test2(4)                

7.存储过程中的repeat循环(案例同上)

repeat循环:有条件的循环控制语句,当满足条件则退出循环.

create procedure pro_test2(n int)
begin 
    declare result int default 0;   
    repeat                          --开始循环
        set result = result + n;    --进行累加
        set n = n-1;
        unyil n = 0                 --满足该条件退出循环
    end repeat;
select result;            
end;
call pro_test2(4)

8.存储过程中的loop循环(案例同上)

create procedure pro_test2(n int)
begin 
    declare result int default 0;   
    c:loop                      --给loop循环起个别名(可以不起)
        set result = result + n;    
        set n = n -1;
            if n<=0 then        --if判断
                leave c;        --满足条件则使用leave跳出循环
            end if;             
    end loop c;                 --结束循环
select result;            
end;
call pro_test2(4)

游标/光标

游标是用来存储查询结果集的数据类型,在存储过程和函数中可以使用光标堆结果进行循环处理.光标的使用包括声明,open,fetch和close

声明光标(将select_statement查询到的内容放入光标中)

declare cursor_name cursor for select_statement; 

open光标

OPEN cursor_name

fetch光标(每调用一次就会查询一行记录,可进行赋值,并且游标位置后移一位)

fetch cursor_name into var_name [, var_name]...

close光标

close cursor_name

简单案例

create table stu(            --简单建立一张表
id int primary key auto_increment,
sname varchar(10),
age int
)
insert into stu values(null,'张三',18),(null,'张三1',18),(null,'张三2',18);--插入数据

create procedure pro_test2()    --创建存储过程
begin             
    declare num int;            --该变量为了计算该表有多少条数据
    declare flag int(5) default 0;  --为了while循环声明的计数变量
    declare s_id int;           --以下三个为了存储每行记录的信息
    declare s_name varchar(10);
    declare s_age int;
    declare cur_stu cursor for select * from stu;   --声明光标并将查询的内容赋值给光标
    select count(*) into num from stu;      --查询该表总记录数
    open cur_stu;               
    while num>flag do                       --循环调用fetch,得到每一条数据
        fetch cur_stu into s_id,s_name,s_age;--将数据封装到变量中
        set flag = flag+1;
        select s_id,s_name,s_age;           --将数据显示
    end while;
    close cur_stu;          
end;

call pro_test2();

存储函数

基本语法

create function function_name([param type...])
returns type
begin
    ...
end;

简单案例(查询年龄小于给定年龄的人数)

create function fun1(sage int)     --创建函数,传递一个int类型的age
returns int                        --返回值为int
begin
    declare num int;            --声明num变量
    select count(*) into num from stu where age<sage;   --计算小于给定年龄的人数赋值给num
    return num;                 --返回变量
end;
select fun1(12);                --查看结果              

触发器

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

使用别名OLD和NEW来引用触发器中发生变化的记录内容,这与其他的数据库是相似的,现在的触发器还只支持行级触发,不支持语句触发

基本语法

create trigger trigger_name
before/after insert/update/delete
on tab_name
[for each row]    --加上代表行级触发器
trigger_stmt;   --触发器具体内容  函数相关

简单案例(通过触发器记录emp表的数据变更日志,包含增加,修改,删除)

创建一张日志表

create table stu_logs(
id int primary key 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(200) comment '操作参数'
)default charset=utf8;

创建stu表插入之后执行的触发器,向日志表中添加一条日志

create trigger stu_insert_after
after insert
on stu
for each row
begin
    insert into stu_logs values(null,'insert',now(),new.id,concat('具体数据为:','id:',new.id,'name:',new.sname,'age',new.age));
end;

简单案例2(创建一个stu修改的触发器,记录修改前后的数据)

create trigger stu_update_after
after update
on stu
for each row
begin
    insert into stu_logs values(null,'update',now(),new.id,concat('修改前数据为:','id:',old.id,'name:',old.sname,'age',old.age,'修改后数据为:','id:',new.id,'name:',new.sname,'age',new.age));
end;

简单案例3(创建删除前的触发器)

create trigger stu_delete_before
before delete
on stu
for each row
begin
    insert into stu_logs values(null,'delete',now(),old.id,concat('删除前数据为:','id:',old.id,'name:',old.sname,'age',old.age));
end;

删除触发器

drop trigger [schema_name...] trigger_name;

查看触发器

show triggers;