mysql-存储过程函数+触发器(十)

存储过程和函数

存储过程和函数可以简单地理解为‘一组经过编译并保存在数据库中的SQL语句的集合’，可以‘随时被调用’。
优点：

1. 允许标准组件式编程
2. 较快的执行速度
3. 减少网络流量
4. 安全

创建并调用存储过程和函数

存储程序可以分为存储过程和函数。

存储过程和函数的操作主要包括：创建存储过程和函数、调用存储过程和函数、查看存储过程和函数，以及修改和删除存储过程和函数。

创建存储过程

格式：
create procedure 存储过程名(in|out|inout|无  参数名  参数类型) 
begin  存储过程体（一组合法的sql语句）  end;

其中参数列表：参数模式(in|out|inout)+参数名+参数类型
in: 该参数可作为输入。即该参数需要调用方传入值
out: 该参数可作为输出，该参数可以作为返回值
inout: 该参数可作为输入，也可以作为输出；即该参数既需要传入值，又可以返回值

例：创建一个名为proc的简单存储过程，用于获取goods表中的记录数

create procedure proc(out num int)
begin
select count(*) into num from goods;
end;
...
call proc(@num);
select @num;

创建存储函数

格式：
create function 函数名(in|out|inout|无 参数名 参数类型) returns 返回类型
return (函数体（sql语句）);

returns type子句对于存储函数而言是必须存在的,
如果return子句返回值的数据类型与returns type子句指定的数据类型不同，
mysql会将返回值强制转换为returns type子句指定的类型。

• 例:创建一个名为func的简单存储函数，用于获取goods表中的记录数

create function func()
returns int(11)
return (select count(*) from goods);
...
select func();

调用存储过程和函数(就是执行对应的函数)

存储过程必须使用关键字call调用，而存储函数与MySQL内置函数的调用相同，使用关键字select。

存储过程的好处:
处理逻辑都封装在数据库端，调用者不需要了解中间的处理逻辑，当处理逻辑发生变化时，只需要修改存储过程即可，而对调用者的程序完全没有影响。

1. 调用存储过程:

格式：call 存储过程名(变量名);

call proc(@num);
select @num;

2. 调用存储函数:

格式：select 函数名(变量名);

select func();

查看存储过程和函数

创建完存储过程和函数后，mysql存储了其状态信息和定义语句，用户可以分别使用show status和show create语句进行查看，也可以在系统数据库information_schema中查看。

查看存储过程和函数的状态

格式：show  procedure|function  status [like '存储名'];

show procedure status like'proc';

查看存储过程和函数的定义

格式：show  create  procedure|function  [数据库名.]存储名;

show create function `day`.func;

查看存储过程和函数的信息

格式：select * from  information_schema.routines  where  rountine_name ='存储名';

select * from information_schema.routines 
where routine_name='proc' and routine_type='procedure';

修改和删除存储过程和函数

修改存储过程和函数

格式：alter procedure|function 存储名 存储特性;

characteristic表示存储过程和函数的特性，其可取值有contains sql，no sql，reads sql data，modifies sql data，sql security { definer | invoker }，
各值的意义与创建存储过程和函数时相同。
不能使用关键字alter更改存储过程的参数或子程序，如果需要修改，必须删除存储过程后再重新创建。

• 例：修改存储过程proc的读写权限和安全类型

alter procedure proc modifies sql data sql security invoker;

select specific_name,sql_data_access,security_type
from  information_schema.routines
where routine_name='proc' and routine_type='procedure';

删除存储过程和函数

drop  procedure|function  [if exists]  存储名;

例：

drop procedure proc;

注: in模式——自己输出结果； 只要有out模式——必须手动搜索select

1. 案例看看：

create procedure test1()
begin 
insert into goods 
values(4,'桃子',10);
end;

调用：
CAll test1();

select * from goods;

2. 输入商品名称，查询对应的商品信息

create procedure test2(in gname varchar(20))
begin 
		select * from goods where name = gname;
end;

-- 调用：
call test2('这里输入对应的值');
call test2('桃子');

3. 输入商品名称，返回对应的商品数量

create procedure test3(in gname varchar(20),out gnum int)
begin 
		select num into gnum #使用into赋值
		from goods where name = gname;
end;

-- 调用：
call test3('果粒橙',@gnum); 
select @gnum;

4. 入a,b两个值，最后两个值翻倍返回

create procedure test4(inout a int, inout b int)
begin 
		set a=a*2;
		set b=b*2;
end;

-- 调用：
set @m=10;
set @n=20;
call  test4(@m,@n);
select @m,@n;

触发器

触发器是存储在系统内部的一段程序代码

触发器无需人工调用，当程序满足定义条件时就会被mysql自动调用。

这些条件可以称为触发事件，包括insert、update和delete操作。

注意：触发器常被用在数据库端确保数据的完整性。

创建触发器

create trigger 触发名  after|before  insert|update|delete
on 表名 for each row 
begin sql语句; end;

注意：触发器只能创建在永久表（Permanent Table）上，不能创建在临时表（Temporary Table）上

创建after触发器

after触发器是指触发器监视的触发事件执行之后，再激活触发器，激活后所执行的操作无法影响触发器所监视的事件

1. insert型

create trigger tg1
after insert on orders1
for each row
begin
update goods set num=num-1 where id=1;
end;

insert into orders1(oid,gid,amount) values(1,1,1);

改成更符合实际一点：

drop trigger tg1;

create trigger tg2
after insert on orders1
for each row
begin
update goods set num=num-new.amount where id=new.gid;
end;

insert into orders1(oid,gid,amount) values(2,2,3);

对于insert型触发器而言，新插入的行使用new表示，引用行中的字段值可以使用“new.字段名”。

2. update型

create trigger tg3 
after update on orders 
for each row 
begin 
update goods set num = num+old.amount-new.amount where id=new.gid;
end;

update orders1 set amount=5 where oid=1;

对于update型触发器而言，修改操作之前的记录使用old表示，引用此条记录中的字段值可以使用“old.字段名”；
修改操作后的记录使用new表示，引用此条记录中的字段值可以使用“new.字段名”。

3. delete型

create trigger tg4
after delete on orders1
for each row
begin update goods set num=num+old.amount where id=old.gid;
end;

delete from orders1 where oid=2;

对于delete型触发器而言，被删除的一行记录使用old表示，引用此条记录中的字段值可以使用“old.字段名”。

创建before触发器

before触发器是指触发器在所监视的触发事件执行之前激活，激活后执行的操作先于监视的事件，这样就有机会进行一些判断，或修改即将发生的操作。

注意：

1. row_count()函数用于记录更新操作影响的行数，如果其值不等于1，就说明订单中商品的数量大于库存数量，goods表没有更新，此时将执行下面的语句，将“商品名+库存不足”赋给变量msg。
2. signal语句用于在存储程序（例如存储过程、存储函数、触发器或事件）中向调用者返回错误或警告条件。此外，它还提供对错误特征（错误编号:sqlstate值，消息）的控制。
3. declare… 声明一个变量
4. concat(字段名,‘添加物’)字符串(数据库)连接函数
5. message_text 把值赋给msg

create trigger tg5
before insert on orders1
for each row
begin
declare msg varchar(200);
update goods set num=num-new.amount where id=new.gid and num>=new.amount;
if row_count() <> 1 then
select concat(name,'库存不足') into msg from goods where id=new.gid;
signal sqlstate 'TX000' set message_text = msg;
end if;
end;

insert into orders1 (oid,gid,amount) values(3,3,20);

查看触发器

查看触发器

1. show triggers;——查看mysql中存在的触发器
2. show triggers from 数据库名 like ‘表名’;——某表上的触发器

show triggers from day like 'orders1';

3. show triggers where trigger like ‘触发名%’;——精确查看某些触发器

show triggers where `trigger` like 'tg%';

精确查看某一个触发器时，where子句中的列名trigger需要使用反引号“`”，该符号位于键盘左上角

查看触发器的详细信息

select * from information_schema.triggers where trigger_name='触发名';

select * from information_schema.triggers where trigger_name='tg4';

删除触发器

-- 格式：
drop trigger 触发器所属数据库名.触发名;

drop trigger day.tg3;
drop trigger tg5;