视图、存储过程、触发器

视图：

– 还是以学生管理系统为例，假定MySQL任课老师需要查看学生考试成绩，
– 我们可以采用视图为任课教师提供查看学生考试成绩的视图，数据包括姓名，
– 学号，成绩，课程名称。
– create view 视图名
CREATE view vw_score
AS
– select 语句
select name,student.stuid,socre,subname from student,score,subject
where student.stuid=score.stuid and score.subid=subject.subid
and subname=‘MySQL’;

– 怎么调用一个视图
select * from vw_score
where name=‘张三’;

– 视图就是一个由查询结果组成的一个虚拟表
– 删除一个视图
drop view vw_score;

desc vw_score;

小结：
– 原表的数据发生改变，那么视图的查询结果会随之改变。
– 视图的数据修改之后，原表的数据也会改变。
– 视图是表的查询结果，如果表的数据发生增删改，name视图数据也会发生改变；反之相同。
– 但是一般不会对视图进行增删改，例如视图只显示了原表的几个字段，如果添加的话，原表
– 可能有一些非空字段，这时用视图做添加操作，肯定会有问题。

存储过程：
优点：
执行速度更快
允许模块化程序设计
提高系统安全性
减少网络流通量

存储过程参数：
存储过程的参数分三种：
输入参数(in)：
向存储过程传入值
输出参数(out)：
调用存储过程后，传出执行结果
输入输出参数（inout）:
把数据传递给存储过程和将存储过程的返回值传递给外部使用者
Inout代表即是输入参数，又是输出参数，表示该参数的值即可以由
调用程序指定，又可以将inout参数的计算结果返回给调用程序。

存储过程变量：
常用的存储过程变量分为两种：用户变量和局部变量。
（1）用户变量：用户定义的变量就叫用户变量，以"@“开始，形式为”@变量名"。用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效
（2）局部变量：作用范围在begin到end语句块之间。在该语句块里设置的变量。declare语句专门用于定义局部变量。
DECLARE 变量名 [,…] 变量类型[DEFAULT value]

局部变量和用户变量的区别：
局部变量与用户变量的区分在于两点:
用户变量是以"@"开头的。局部变量没有这个符号。
定义变量不同，局部变量使用declare语句定义 。
作用范围。局部变量只在begin-end语句块之间有效。在begin-end语句块运行完之后，局部变量就消失了。

创建存储过程：
CREATE PROCEDURE 存储过程名 (参数)
– BEGIN END 相当于java方法的大括号
– 查询MySQL科目的最高分
create PROCEDURE usp_maxMysqlScore()
BEGIN
– sql语句
select subname,MAX(socre) from score,subject
where score.subid=subject.subid
and subname=‘MySQL’;
END

– 调用存储过程
call usp_maxMysqlScore();

– 输入参数 in :(由用户调用存储过程时输入)
– 查询某一科考试没有及格的学生姓名，成绩，科目。
– 输入参数：科目，成绩及格线
create PROCEDURE usp_studentScore(in sc int,in suname VARCHAR(20))
BEGIN
– 查询某一科考试没有及格的学生姓名，成绩，科目。
select name,socre,subname from student,score,subject
where student.stuid=score.stuid and score.subid=subject.subid
and subname=suname and socre>sc;
END

call usp_studentScore(60,‘MySQL’);

– 删除存储过程
DROP PROCEDURE usp_maxScore;

– out :输出参数把执行结果返回给调用者
– 创建查找指定科目的最高分 输入参数科目，输出参数分数
create PROCEDURE usp_maxScore(in suname varchar(20),out maxsc int)
BEGIN
– 查询指定科目的最高分
select MAX(socre) from subject,score
where subject.subid=score.subid
and subname=suname into maxsc; – 把查询的结果赋值给maxsc
END
– 执行存储过程,定义一个成员变量 @maxsc 语法：变量名
call usp_maxScore(‘MySQL’,@maxsc);
– 查询最高分
select @maxsc;

– 查询某科考试没有及格的学生姓名，并获得参加考试的人数
– 和未及格的人数。
– 输入参数：科目名；输出参数：参加考试人数 未及格人数
create PROCEDURE usp_getCount(in suname varchar(20),out testCount int,out noTestCount int)
BEGIN
– 查询某科考试没有及格的学生姓名
select name,socre from student INNER JOIN score
on student.stuid=score.stuid
INNER JOIN subject on score.subid=subject.subid
where subname=suname and socre<60;
– 并获得参加考试的人数
select COUNT() from subject,score
where subject.subid=score.subid
and subname=suname into testCount;
– 未及格人数
select COUNT() from score,subject
where score.subid=subject.subid
and subname=suname and socre<60 into noTestCount;
– 输入参数：科目名；输出参数：参加考试人数 未及格人数
END
drop PROCEDURE usp_getCount;

call usp_getCount(‘MySQL’,@testCount,@noTestCount);
select @testCount,@noTestCount;
– 传一个年龄，自动让年龄增长10岁
create PROCEDURE usp_age(inout age int)
BEGIN
set age=age+10;
END
– 定义一个变量
set @age=10;
call usp_age(@age);

select @age;

– 流程控制
– 一女生向你表白，如果她长得貌美如花，你说“咱俩结婚吧”，
– 如果长得“还可以”，你说“考虑一下”，否则说“NO”。
create PROCEDURE getGrad(in text varchar(20))
BEGIN
DECLARE a varchar(20);
– 判断输入内容
if text=‘貌美如花’ then
set a=‘咱们结婚吧’;
elseif text=‘还可以’ THEN
set a=‘考虑一下’;
else
set a=‘NO’;
end IF;
select a;
END
drop PROCEDURE getGrad;

call getGrad(‘貌美如花’);

存储过程和存储函数的不同：
存储函数不能拥有输出参数，因为存储函数自身就是输出参数，只能有
一个返回值；而存储过程可以拥有多个输出参数。
可以直接对存储函数进行调用，而不需要使用call语句;而对存储过程的
调用，需要使用call语句。
存储函数中必须包含一条return语句，而这条特殊的sql语句不允许包含
于存储过程中。
存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，
而函数有很多限制，如不能在函数中使用insert,update,delete,create等
语句；存储函数只完成查询的工作，可接受输入参数并返回一个结果，也
就是函数实现的功能针对性比较强。
存储过程可以调用存储函数。但函数不能调用存储过程。

触发器：
触发器是一种特殊的存储过程，当在某个数据表中插入、修改、或删除记录时，触发器会自动被触发执行。

创建触发器的语法
create trigger 触发器名称
after/before (触发时间)
insert/update/delete (监视事件)
on 表名 (监视地址)
for each row --定死的
begin
sql1;
…
sqlN;
End

删除触发器：
drop trigger 触发器名称
查看触发器定义：
SHOW CREATE TRIGGER 搜索触发器名称
查看触发器：
SELECT * FROM information_schema.triggers

例题：
– 数据库productorder。
– （1）创建触发器recruit_insert当有一批新的产品入库时，供应商库存数量自动更新
create trigger recruit_insert
AFTER
INSERT
on recruit
for each row
BEGIN
update product set pcount=pcount+new.rcount where pid=new.pid;
END

– （2）创建触发器recruit_delete当入库需求取消时，供应商库存数量自动更新
create TRIGGER recruit_delete
after
DELETE
ON recruit
for EACH ROW
BEGIN
update product set pcount=pcount-old.rcount where pid=old.pid;
END
DELETE from recruit where rid=9;

– （3）创建触发器recruit_update当库存不要那么多产品时（修改入库订单数量），
– 供应商库存数量自动更新
create TRIGGER recruit_update
AFTER
UPDATE
on recruit
FOR EACH ROW
BEGIN
update product set pcount=pcount-old.rcount+new.rcount where pid=new.pid;
END

after 和 before 的区别：
after 是先完成数据的增删改，再触发，触发的语句晚于监视的增删改操作，无法影响前面的增删改动作；也就是说先插入订单记录，再更新商品的数量；
before 是先完成触发，再增删改，触发的语句先于监视的增删改，我们就有机会判断，修改即将发生的操作；