针对简易图书管理数据库 BooksDB,完成下述功能。
1、不带参数的存储过程:创建一个存储过程,查看所有读者的姓名、可借本数、可借天数和已借 书本数。
2、带输入参数的存储过程:创建一个存储过程,实现借书功能;
3、带输入参数的存储过程:创建一个存储过程,实现还书功能;
4、带输入参数和输出参数的存储过程:创建一个存储过程,输入读者的编号,输出该读者的姓名;
5、创建 DDL 触发器,禁止用户修改 BooksDB 数据库中的表;
6、测试 AFTER 触发器,理解 INSERTED 表和 DELETED 表的作用;
BooksDB中的几个表:
1、不带参数的存储过程:
创建一个存储过程,查看所有读者的姓名、可借本数、可借天数和已借书本数。
create procedure usp_GetInfo
as
select rdName, canLendQty, canLendDay, rdBorrowQty from Reader, ReaderType
where Reader.rdType = ReaderType.rdType
调用的时候:
exec usp_GetInfo
2、带输入参数的存储过程:
创建一个存储过程,实现借书功能;
create procedure usp_BorrowBook
@rdID char(9),
@bkID char(9)
as
--先判断有没有这本书
if not exists(select * from Book where @bkID in (select bkID from Book))
begin
raiserror('图书馆没有该书,借阅失败',10,1)
return
end
--先判断书是否在馆
declare @bkState int
select @bkState = bkState from Book
where bkID = @bkID
if @bkState = 0
begin
raiserror('该书不在馆,无法借阅',10,1)
return
end
--在判断该读者的借书数量是否达到最大借书数量
declare @rdBorrowQty int , @canLendQty int
select @rdBorrowQty = rdBorrowQty from Reader
where rdID = @rdID
select @canLendQty = canLendQty from ReaderType
where rdType = (select rdType from Reader where rdID = @rdID)
if @rdBorrowQty = @canLendQty
begin
raiserror('抱歉!你所借书的数量已经达到最大借书数量!借阅失败!',10,1)
return
end
--借书开始(1.修改书的状态,2.修改改读者的借书数量,3.向Borrow表中插入数据)
update Book
set bkState = 0
where bkID = @bkID
update Reader
set rdBorrowQty = rdBorrowQty + 1
where rdID = @rdID
declare @canLendDay int
select @canLendDay = canLendDay from ReaderType
where rdType = (select rdType from Reader where rdID = @rdID)
insert into Borrow values(@rdID,@bkID,GETDATE(),DATEADD(dd,@canLendDay,GETDATE()),null)
--调用:
exec usp_BorrowBook 'rd2017001','bk2017006'
exec usp_BorrowBook 'rd2017001','bk2017002'
exec usp_BorrowBook 'rd2017002','bk2017002'
--由于bk2017002已经被借出去了,所以会出现不在馆的消息
3、带输入参数的存储过程:
创建一个存储过程,实现还书功能;
1 create procedure usp_ReturnBook
2
3 @rdID char(9),
4
5 @bkID char(9)
6
7 as
8
9 --还书(1.修改书的状态,2.修改读者的借书数量,3.在Borrow表中删除这条借书纪录)
10
11 update Book set bkState = 1
12
13 where bkID = @bkID
14
15
16
17 update Reader set rdBorrowQty = rdBorrowQty - 1
18
19 where rdType = (select rdType from Reader where rdID = @rdID)
20
21
22
23 delete from Borrow
24
25 where rdID = @rdID and bkID = @bkID
26
27 --调用
28
29 exec usp_ReturnBook 'rd2017001','bk2017002'
4、带输入参数和输出参数的存储过程:
创建一个存储过程,输入读者的编号,输出该读者的姓名;
1 create procedure usp_GetName
2
3 @rdID char(9),
4
5 @rdName varchar(20) output
6
7 as
8
9 select @rdName = rdName from Reader
10
11 where rdID = @rdID
12
13 go
14
15 --调用
16
17 declare @rdName varchar(20)
18
19 exec usp_GetName 'rd2017001',@rdName output
20
21 select @rdName 姓名
5、创建 DDL 触发器:
禁止用户修改 BooksDB 数据库中的表;、
create trigger tri_OnBookDB on database
for ddl_table_events
as
print '无法在数据库BookDB中创建,删除,修改表!!'
rollback
--测试:
create table Test (a int,b char(6))
drop table Borrow
6、测试 AFTER 触发器,理解 INSERTED 表和 DELETED 表的作用;
Instead of触发器:
一开始Borrow表中有这样一条纪录:
当创建Instead of 触发器后:
create trigger tri_InsteadOf on Borrow instead of delete
as
select rdID, bkID from deleted
--调用:
delete from Borrow where rdID = 'rd2017001'
deleted表中会出现:
但是在Borrow表中这条纪录并没有被删除。原因是触发器中代替了所要执行的delete操作。
After 触发器:
当向Borrow表中插入一条纪录时候,应修改Reader表中的rdBorrowQty加1,而在Book表中修改相应的书的状态为0。
1 alter trigger tri_Insert on Borrow after insert
2
3 as
4
5 --先要判断该书在不在馆
6
7 if not exists(select bkState from Book, inserted where Book.bkID = inserted.bkID)
8
9 begin
10
11 raiserror('该书不在馆,无法插入!',10,1)
12
13 return
14
15 end
16
17
18
19 update Reader set rdBorrowQty = rdBorrowQty + 1
20
21 from Reader, inserted
22
23 where Reader.rdID = inserted.rdID
24
25
26
27 update Book set bkState = 0
28
29 from Book, inserted
30
31 where Book.bkID = inserted.bkID
32
33
34
35 --调用:
36
37 insert into Borrow values ('rd2017004','bk2017004',GETDATE(),30,null)
学习过程之中难免存在错误,望多多指出。