课堂训练
【实训目的】
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的存储过程,该存储过程根据给定的图书编号显示该图书的信息。
CREATE PROCEDURE p_book3
@x char(9)
AS
SELECT * from 书籍表 where ____________________
调用存储过程: p_book3(图书编号为001105)
EXEC p_book3 ___________________
4.创建一个名为p_book4的存储过程,该存储过程根据给定的书名显示图书信息。
use Library
go
CREATE PROCEDURE p_book4
@c1 _________________
AS
SELECT * from ______________________________
调用存储过程: p_book4 (书名为大学语文)
_________________________________
5. 创建一个名为p_borower1的存储过程,该存储过程查询借阅表中借书证号为:001101的学生借书信息。
use Library
go
CREATE PROCEDURE p_borower1
AS
SELECT * FROM______________表 where ____________________________
调用存储过程: p_borower1
EXEC __________________________
6. 创建一个名为p_borower2的存储过程,该存储过程某个学生所借书籍的信息。
use Library
go
CREATE PROCEDURE ___________________
AS
SELECT * FROM 借阅表 ____________________________________
调用存储过程: p_borower2(借书证号=’
EXEC __________________________
7.读者还书存储过程:ReturnBook的创建,若读者没有借阅此书,则显示无法操作信息。
use Library
go
create procedure ReturnBook
___________________________________
as
-----------判断读者是否借阅此书,如果没有借阅此书,则不能进行还书操作
if not exists(select * from 借阅表 where 借书证号=@no and 图书编号=@Bid)
begin
print'对不起,你没有借阅此书,故而无法进行此次还书操作,请核实!'
end
调用存储过程: ReturnBook
EXEC __________________________
8.读者还书存储过程:ReturnBook_1的创建,成功还书
use Library
go
create procedure ________________
@no varchar(10),@Bid varchar(30)
as
if exists(select * from 借阅表 _______________________)
begin
-----------读者还书过程
Update 借阅表 set 已归还=’是’,归还日期=getdate()
where 借书证号=@no and 图书编号=@Bid
-----------输出还书成功信息
Select ‘成功地向图书馆归还’
end
go
调用存储过程: ReturnBook_1
EXEC __________________________
9.读者还书存储过程的创建:ReturnBook_2,若读者没有借阅此书,则显示无法操作信息,否则显示已归还操作。
use Library
go
create procedure ReturnBook_2
@no varchar(10),@Bid varchar(30)
as
-----------判断读者是否借阅此书,如果没有借阅此书,则不能进行还书操作
if not 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
create procedure 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
EXEC p_book1
2.修改名为p_book2的存储过程,返回书名为“高等数据”书籍的图书信息。
在查询编辑器中输入:
use Library
go
ALTERE PROCEDURE p_book2
AS
SELECT * from 书籍表 ______________________
调用存储过程: p_book2
EXEC ________________
(三)存储过程的删除
1.删除的存储过程:p_book1
DROP PROCEDURE p_book1
2.删除的存储过程:p_book2
____________________________________
二、图书馆管理系统数据库---触发器
(一)触发器的创建
1.在library数据库的“书籍表”上创建一个触发器:书籍表_INS_trigger,当执行INSERT操作该触发器被触发(即向所定义触发器的表中插入数据时将触发其触发器)。
USE library
GO
CREATE TRIGGER书籍表_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 '你无权删除一条记录'
__________________________
想一想,能否用INSTEAD OF DELETE取代?请试之。
4.在library数据库“借阅表”上建立一个名为date_UPDA_trigger1的触发器,该触发器将被操作UPDATE所激活,该触发器将不允许用户修改表的借书日期列(不使用INSTEAD OF而是通过ROLLBACK TRANSACTION子句恢复原来数据的方法来实现字段不被修改)。命令如下:
CREATE TRIGGER ________________
ON ________________
FOR ____________
AS
IF UPDATE (借书日期)
BEGIN
RAISERROR('你无权修改借书日期',10,1)
________________________
END
5.编写一个触发器:aa,在library数据库“借阅表”为借阅表添加一条记录,则自动在“书籍表”中的库存量减1。
CREATE TRIGGER ______________________
on借阅表
for insert
AS
DECLARE @c1 char(9)
Select @c1=图书编号 from _______________________
update 书籍表 set 库存量=库存量-1 where ______________________
6.编写触发器dd:在library数据库“借阅表”为借阅表还书,则自动在“书籍表”中的库存量加1。
CREATE TRIGGER _____________
on借阅表
_________________________
AS
DECLARE @c1 char(9)
Select @c1=图书编号 from inserted
update 书籍表 set 库存量=库存量+1 where ______________
7.在借阅表中创建一个触发器:tri_Book,若要借的书已无库存,则无法进行借书操作。
use Library
go
------------借书要求(书本没有库存,则无法进行借书操作)-----------
create trigger tri_Book
on 借阅表
for _________________
as
declare @btotal int,@bborrowed int
select @bborrowed=图书编号 from ________________
select @btotal=库存量 from 书籍表 where 图书编号=____________________
if(@btotal=0)
begin
rollback transaction
print '借阅失败!'
print'对不起,此书已经没有库存,无法进行本次借书操作!'
end
go
(二)触发器的管理
1.修改library数据库的“借阅表”中名为“借阅表_DELE_trigger2的触发器,该触发器将实现对借阅表中删除记录的操作给出报警,并取消当前的删除操作。要求用INSTEAD OF DELETE实现
ALTER 借阅表_DELE_trigger2
ON 借阅表
______________________
AS
PRINT '你无权删除一条记录'
2.修改触发器aa:在library数据库“借阅表”为借阅表还书,则自动在“书籍表”中的库存量加1。
Alter TRIGGER aa
On 借阅表
_________________________
AS
DECLARE @c1 char(9)
Select @c1=图书编号 from inserted
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