课堂训练
【实训目的】
1. 理解存储过程的概念、作用。
2. 掌握触发器的概念、用途。。
3.能创建、管理存储过程。
4.能创建、管理触发器。
【实训内容】
一、存储过程(在library数据库中执行)
(一)存储过程的创建和调用
1.创建一个名为p_book1的存储过程,返回“书籍表”中图书编号为020001的图书信息。
在查询编辑器中输入:
use Library
go
CREATE____________ p_book1
AS
SELECT * from书籍表 where图书编号='020001'
调用存储过程: p_book1
EXEC _______________
2.创建一个名为p_book2的存储过程,返回书名为“大学英语”书籍的图书信息。
在查询编辑器中输入:
use Library
go
CREATE________________________
AS
SELECT * from 书籍表______________________
调用存储过程: p_book2
________________________
3.创建一个名为p_book3的存储过程,该存储过程根据给定的图书编号显示该图书的信息。
CREATEPROCEDURE p_book3
@x char(9)
AS
SELECT * from 书籍表 where ____________________
调用存储过程: p_book3(图书编号为001105)
EXEC p_book3___________________
4.创建一个名为p_book4的存储过程,该存储过程根据给定的书名显示图书信息。
use Library
go
CREATEPROCEDURE p_book4
@c1_________________
AS
SELECT * from______________________________
调用存储过程: p_book4 (书名为大学语文)
_________________________________
5.创建一个名为p_borower1的存储过程,该存储过程查询借阅表中借书证号为:001101的学生借书信息。
use Library
go
CREATEPROCEDURE p_borower1
AS
SELECT* FROM______________表where____________________________
调用存储过程: p_borower1
EXEC __________________________
6.创建一个名为p_borower2的存储过程,该存储过程某个学生所借书籍的信息。
use Library
go
CREATEPROCEDURE ___________________
AS
SELECT * FROM 借阅表____________________________________
调用存储过程: p_borower2(借书证号=’001115’)
EXEC __________________________
7.读者还书存储过程:ReturnBook的创建,若读者没有借阅此书,则显示无法操作信息。
use Library
go
create procedure ReturnBook
___________________________________
as
-----------判断读者是否借阅此书,如果没有借阅此书,则不能进行还书操作
ifnot exists(select * from 借阅表where借书证号=@noand 图书编号=@Bid)
begin
print'对不起,你没有借阅此书,故而无法进行此次还书操作,请核实!'
end
调用存储过程: ReturnBook
EXEC __________________________
8.读者还书存储过程:ReturnBook_1的创建,成功还书
use Library
go
createprocedure ________________
@no varchar(10),@Bid varchar(30)
as
ifexists(select * from 借阅表_______________________)
begin
-----------读者还书过程
Update借阅表 set已归还=’是’,归还日期=getdate()
where借书证号=@noand 图书编号=@Bid
-----------输出还书成功信息
Select‘成功地向图书馆归还’
end
go
调用存储过程: ReturnBook_1
EXEC __________________________
9.读者还书存储过程的创建:ReturnBook_2,若读者没有借阅此书,则显示无法操作信息,否则显示已归还操作。
use Library
go
createprocedure ReturnBook_2
@no varchar(10),@Bid varchar(30)
as
-----------判断读者是否借阅此书,如果没有借阅此书,则不能进行还书操作
ifnot exists(___________________________________________)
begin
____________________________________________________________
end
else
begin
-----------读者还书过程
____________________________________________________________________
-----------输出还书成功信息
Select‘成功地向图书馆归还’
end
go
调用存储过程: ReturnBook_2
EXEC __________________________
10.存储过程的创建:RqueryBook,该存储过程将根据借书证号,读者查询借阅图书情况存储过程
use Library
go
create procedure RQueryBook
@no varchar(10)
as
select* from 借阅表 where借书证号=________________
go
调用存储过程: RQueryBook
EXEC __________________________
11.存储过程的创建:RIndexBook,该存储过程将根据书名,检索图书信息
use Library
go
createprocedure RIndexBook
___________________________
as
set@bname='%'+@parm+'%'
if(exists(select * from 书籍表where书名 like@bname ))
begin
select '你所检索的图书信息如下:'
select* from ______________where 书名 like_________________
go
调用存储过程: RIndexBook
EXEC __________________________
(二)存储过程的管理
1.修改p_book1的存储过程,返回“书籍表”中图书编号为020009的图书信息。
在查询编辑器中输入:
use Library
go
ALTER PROCEDURE p_book1
AS
SELECT * from书籍表 where图书编号='020009'
调用存储过程: p_book1
EXECp_book1
2.修改名为p_book2的存储过程,返回书名为“高等数据”书籍的图书信息。
在查询编辑器中输入:
useLibrary
go
ALTEREPROCEDURE p_book2
AS
SELECT * from 书籍表______________________
调用存储过程: p_book2
EXEC ________________
(三)存储过程的删除
1.删除的存储过程:p_book1
DROPPROCEDUREp_book1
2.删除的存储过程:p_book2
____________________________________
二、图书馆管理系统数据库---触发器
(一)触发器的创建
1.在library数据库的“书籍表”上创建一个触发器:书籍表_INS_trigger,当执行INSERT操作该触发器被触发(即向所定义触发器的表中插入数据时将触发其触发器)。
USElibrary
GO
CREATETRIGGER书籍表_INS_trigger
ON学生表
for_______________
AS
Print'一条记录被插入'
2.在library数据库的“书籍表”上建立一个名为书籍表_DELE_trigger1的DELETE触发器,该触发器将实现对书籍表中删除记录的操作给出信息。
_________________ 书籍表_DELE_trigger1
ON学生表
FOR________________
AS
PRINT'___________________'
3.在library数据库的“借阅表”上建立一个名为借阅表_DELE_trigger2的DELETE触发器,该触发器将实现对借阅表中删除记录的操作给出报警,并取消当前的删除操作。
_____________借阅表_DELE_trigger2
ON 借阅表
FOR____________
AS
PRINT '你无权删除一条记录'
__________________________
想一想,能否用INSTEADOF DELETE取代?请试之。
4.在library数据库“借阅表”上建立一个名为date_UPDA_trigger1的触发器,该触发器将被操作UPDATE所激活,该触发器将不允许用户修改表的借书日期列(不使用INSTEADOF而是通过ROLLBACKTRANSACTION子句恢复原来数据的方法来实现字段不被修改)。命令如下:
CREATETRIGGER ________________
ON ________________
FOR____________
AS
IF UPDATE (借书日期)
BEGIN
RAISERROR('你无权修改借书日期',10,1)
________________________
END
5.编写一个触发器:aa,在library数据库“借阅表”为借阅表添加一条记录,则自动在“书籍表”中的库存量减1。
CREATETRIGGER ______________________
on借阅表
for insert
AS
DECLARE @c1 char(9)
Select @c1=图书编号from_______________________
update书籍表 set库存量=库存量-1 where ______________________
6.编写触发器dd:在library数据库“借阅表”为借阅表还书,则自动在“书籍表”中的库存量加1。
CREATETRIGGER _____________
on借阅表
_________________________
AS
DECLARE @c1 char(9)
Select @c1=图书编号frominserted
update 书籍表set 库存量=库存量+1 where ______________
7.在借阅表中创建一个触发器:tri_Book,若要借的书已无库存,则无法进行借书操作。
use Library
go
------------借书要求(书本没有库存,则无法进行借书操作)-----------
create trigger tri_Book
on借阅表
for_________________
as
declare @btotal int,@bborrowedint
select @bborrowed=图书编号 from________________
select@btotal=库存量from书籍表 where图书编号=____________________
if(@btotal=0)
begin
rollback transaction
print '借阅失败!'
print'对不起,此书已经没有库存,无法进行本次借书操作!'
end
go
(二)触发器的管理
1.修改library数据库的“借阅表”中名为“借阅表_DELE_trigger2的触发器,该触发器将实现对借阅表中删除记录的操作给出报警,并取消当前的删除操作。要求用INSTEADOF DELETE实现
ALTER 借阅表_DELE_trigger2
ON 借阅表
______________________
AS
PRINT '你无权删除一条记录'
2.修改触发器aa:在library数据库“借阅表”为借阅表还书,则自动在“书籍表”中的库存量加1。
AlterTRIGGER aa
On借阅表
_________________________
AS
DECLARE @c1 char(9)
Select @c1=图书编号frominserted
update书籍表 set____________________ where ______________
(三)触发器的删除
1.删除触发器:学生表_INS_trigger
DROP Trigger学生表_INS_trigger
2.删除触发器:学生表_DELE_trigger1
__________________学生表_DELE_trigger1
课外训练
一、宿舍管理系统数据库---存储过程
(一)存储过程的创建和调用
1.在bedroom数据库中,创建一个名为p_stu1的存储过程,返回“学生表”中学号为020001的学生信息。
在查询编辑器中输入:
___________________________
调用存储过程: p_stu1
__________________________________
2.创建一个名为p_stu2的存储过程,返回姓名为“李小明”同学的信息。
在查询编辑器中输入:
_____________________________
调用存储过程: p_stu2
________________
3.创建一个名为p_stu3的存储过程,该存储过程根据给定的学号显示该同学的信息。
_________________________________
调用存储过程: p_stu3(学号为001105)
_________________________________
4.创建一个名为p_stu4的存储过程,该存储过程根据给定的姓名显示学生信息。
____________________________
调用存储过程: p_stu4(姓名任意,只要表中存在)
__________________________
5.在住宿表中创建一个存储过程:p_l1,该存储过程查询住宿表中宿舍号为:2-301的住宿信息。
___________________________
调用存储过程: p_l1
________________________________________
6.在住宿表中创建一个存储过程:p_l2,,该存储过程查询某个宿舍号的住宿的信息。显示住在这个宿舍中的学生信息,有学号,姓名,性别、专业、班级、联系方式等
_____________________________________________
调用存储过程:p_l2(例:宿舍号为:3-301)
_____________________________________
(二)存储过程的管理
1.在bedroom数据库中,修改名为p_stu1的存储过程,返回“学生表”中学号为030001的学生信息。
在查询编辑器中输入:
___________________________
2.在bedroom数据库中,修改名为p_stu2的存储过程,返回姓名为“陈小明”同学的信息。
在查询编辑器中输入:
____________________________________________
3.在bedroom数据库中,修改名为p_stu3的存储过程,该存储过程根据给定的姓名显示该同学的信息。
_________________________________
4.在bedroom数据库中,修改名为p_l2的存储过程,该存储过程查询某个宿舍号的住宿的信息。显示住在这个宿舍中的学生信息,有学号,姓名,性别、专业、班级、联系方式、入住时间等
_____________________________________________
(三)存储过程的删除
1.删除的存储过程:p_stu1
2.删除的存储过程:p_stu2
二、宿舍管理系统数据库---触发器
(一)触发器的创建
1.在bedroom数据库中为“学生表”上创建一个触发器:学生表_INS_trigger,当执行INSERT操作该触发器被触发(即向所定义触发器的表中插入数据时将触发其触发器)。
2.在bedroom数据库的“学生表”上建立一个名为学生表_DELE_trigger1的DELETE触发器,该触发器将实现对学生表中删除记录的操作给出信息。
3.在bedroom数据库的“住宿表”上建立一个名为住宿表_DELE_trigger2的DELETE触发器,该触发器将实现对住宿表中删除记录的操作给出报警,并取消当前的删除操作。
4.在bedroom数据库的“住宿表”上建立一个名为:住宿表_UPDA_trigger1的触发器,该触发器将被操作UPDATE所激活,该触发器将不允许用户修改表的入住日期列。
(二)触发器的管理
1.修改在bedroom数据库中“学生表”上的触发器:学生表_INS_trigger,当执行update操作该触发器被触发(即向所定义触发器的表中修改数据时将触发其触发器)。
2.修改在bedroom数据库的“学生表”上名为:学生表_DELE_trigger1触发器,该触发器将实现对学生表中插入记录的操作给出信息。
3.修改在bedroom数据库的“住宿表”上名为:住宿表_DELE_trigger2的触发器,该触发器将实现对住宿表中插入记录的操作给出报警,并取消当前的插入操作。
(三)触发器的删除
1.删除触发器:学生表_INS_trigger
2.删除触发器:学生表_DELE_trigger1