管理数据库对象------存储过程与触发器

一、 存储过程
1． 定义
2． 作用
3． 创建和调用
delimiter //
例子1
create procedure p1()
begin
update student
set sage = 18
where sno = ‘0001’;
end//
创建存储过程p1，其功能是将1号年龄改为18岁
call p1()//
例子2
create procedure p2(in sid char(4))
begin
update student
set sage = 18
where sno = sid;
end//

call p2(‘0002’)//
例子3
create procedure p3(in sid char(4),in nl int)
begin
update student
set sage = nl
where sno = sid;
end//
create procedure p4(in sid char(4),in cid char(3),in grad int)
begin
insert into sc
values(sid,cid,grad);
end//
创建存储过程p10，其功能是更新学生成绩
create procedure p10(in sid char(4),in cid char(3),in grad int)
begin
update sc
set grade = grad
where sno = sid and cno = cid;
end//
创建存储过程p11，用来为student表添加sno和sname
create procedure p11(in sid char(4),in name char(20))
begin
insert into student(sno,sname)
values(sid,name);
end//
4． 查看
（1） 查看当前数据库下所有的存储过程
show procedure status//
（2） 查看某个存储过程的创建代码
show create procedure p10//
5． 删除
drop procedure p10//

存储过程练习

1. 创建存储过程p1，用来查询学生学号和姓名，并执行；
   create procedure p1()
   begin
   select sno,sname
   from student;
   end//

call p1()//
2. 创建存储过程p2，用来查询某位学生学号和姓名，并执行；
create procedure p2(in sid char(4))
begin
select sno,sname
from student
where sno = sid;
end//

call p2(‘0002’)//
3. 创建存储过程p3，用来更新某位学生某门课成绩，并执行；
create procedure p3(in sid char(4),in cid char(3),in grad int)
begin
update sc
set grade = grad
where sno = sid and cno = cid;
end//

call p3(‘0001’,‘c01’,100)//
4. 创建存储过程p4，用来为选课表插入数据，并执行；
create procedure p4(in cid char(3),in name char(20))
begin
insert into course(cno,cname)
values(cid,name);
end//

call p4(‘c09’,‘java’)//
5. 创建存储过程p5，用来删除某位学生的信息，并执行；
create procedure p5(in sid char(4))
begin
delete from student
where sno = sid;
end//

call p5(‘0002’)//
6. 删除存储过程p5；
drop procedure p5//

二、 触发器
1． 定义
2． 创建
举例1
create trigger tr1
after update on student for each row
begin
if new.sno != old.sno then
update sc
set sno = new.sno
where sno = old.sno;
end if;
end//
举例2
购买商品表：
create table capital
(id char(4),
balance int);

create table perchase
(id char(4),
gid char(3),
mount int)//

create table goods
(gid char(3),
gname char(20),
price int); //

insert into capital
values(‘0001’,5000) ; //
insert into capital
values(‘0002’,6000) ; //

insert into goods
values(‘001’,‘cap’,200) ;//
insert into goods
values(‘002’,‘shirt’,500) ;//
表结构：
mysql> select * from capital//
±-----±--------+
| id | balance |
±-----±--------+
| 0001 | 5000 |
| 0002 | 6000 |
±-----±--------+
2 rows in set (0.00 sec)

mysql> select * from goods//
±-----±------±------+
| gid | gname | price |
±-----±------±------+
| 001 | cap | 200 |
| 002 | shirt | 500 |
±-----±------±------+
2 rows in set (0.00 sec)

mysql> desc perchase//
±------±--------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±--------±-----±----±--------±------+
| id | char(4) | YES | | NULL | |
| gid | char(3) | YES | | NULL | |
| mount | int(11) | YES | | NULL | |
±------±--------±-----±----±--------±------+
3 rows in set (0.00 sec)
谁买的 id
商品名称 gid
商品数量 mount
单价 price
create trigger tr4
after insert on perchase for each row
begin
update capital
set balance = balance - new.mount * (select price
from goods
where gid = new.gid)
where id = new.id;
end//

触发器练习：

1. 创建触发器tr1，当更新course表的cno列时，对应sc表中的cno也要随之改变，并验证
   create trigger tr1
   after update on course for each row
   begin
   if new.cno != old.cno then
   update sc
   set cno = new.cno
   where cno = old.cno;
   end if;
   end //

update course
set cno = ‘c11’
where cno = ‘c01’//
select * from sc//
2. 创建触发器tr2，当购买商品时，你的余额会相应减少，并验证；
create trigger tr2
after insert on perchase for each row
begin
update captital
set balance = balance - new.mount * (select price
from goods
where gid = new.gid)
where cid = new.cid;
end //
3. 创建触发器tr3，当删除学生表记录时，对应sc表中的该同学记录也被删除，并验证；//级联删除
create trigger tr3
after delete on student for each row
begin
delete from sc
where sno = old.sno;
end //
验证：
delete from student
where sno = ‘0002’//
Query OK, 1 row affected (0.03 sec)
select * from sc//
4. 查看你所创建的触发器；
show triggers//
5. 删除触发器tr1。
drop trigger tr1//