实验九存储过程和触发器.docx
《实验九存储过程和触发器.docx》由会员分享，可在线阅读，更多相关《实验九存储过程和触发器.docx（19页珍藏版）》请在冰豆网上搜索。
实验九存储过程和触发器
实验九存储过程和触发器
实验内容
在已建立的TSGL数据库的基础上，按如下要求对数据库进行操作，按同前的命名要求保存操作代码和截图。
1.利用TSGL数据库中的TREADER表和TBOOK表和historytable表，编写一无参存储过程用于查询每个读者的借阅历史，然后调用该存储过程。
2.编写一存储过程，根据TSGL数据库的三个表查询指定读者（指定借书证号或指定姓名等）当前的借书情况。
3.利用TSGL数据库中的TREADER表、TBOOK表及historytable表创建一存储过程，查询指定图书（ISBN或书名）的借阅历史。
该存储过程在参数中使用模糊查询，如果没有提供参数，则使用预设的默认值。
4.编写一存储过程，统计指定图书在给定时间段内的借阅次数，存储过程中使用输入和输出参数。
5.编写一存储过程，在TSGL数据库的TREADER表上声明并打开一个游标。
通过游标读取所需信息。
6.创建加密过程，使用sp_helptext系统存储过程获得关于加密的存储过程的信息，然后尝试直接从syscomment表中获取关于该过程的信息。
7.对TSGL数据库中的三个表分别创建添加、修改、删除一条记录的存储过程。
8.创建触发器，当向LEND表中插入一条记录时，将TREADER表中该学生的借书数加1，将TBOOK表中该书的库存量减1。
9.创建触发器，当修改TREADER表中的借书证号时，同时也要将LEND表中的借书证号修改成相应的借书证号（假设TREADER表和LEND表之间没有定义外键约束）
10.在删除TREADERB表中的一条生记录时将LEND表中该学生的相应记录也删除。
11.在数据库TSGL中创建一触发器，当向lend表插入一条记录时，检查该记录的借书证号在TREADER表中是否存在，检查图书的ISBN在TBOOK表中是否存在，以及图书的库存量是否大于0,若有一项为否，则不允许插入。
12.在数据库TSGL中创建一触发器，当删除TREADER表一条记录时，检查该记录的借书证号在JY表中是否存在，如果存在，则不允许删除。
13.修改数据库TSGL中在TREADER表上定义的触发器（内容自定）。
14.删除以上所建触发器。
--1.利用TSGL数据库中的TREADER表和TBOOK表和historytable表，
--编写一无参存储过程用于查询每个读者的借阅历史，然后调用该存储过程。
USETSGL203
GO
IFEXISTS（SELECTnameFROMsys.objectsWHEREname='lend_history_info'）
DROPPROCEDURElend_history_info
GO
--创建查询读者借阅历史的存储过程
CREATEPROCEDURElend_history_info
WITHENCRYPTION
AS
BEGIN
SELECTr.lno,r.dept,r.name,r.bornum,b.ISBN,h.barno,b.bname,h.bbt,h.rbbt
FROM（SELECTlno,dept,name,bornumFROMreader203）r,
（SELECTISBN,bnameFROMbook203）b,
（SELECTlno,ISBN,barno,bbt,rbbtFROMhistory203）h
WHEREr.lno=h.lnoandh.ISBN=b.ISBN
END
GO
--执行查询读者借阅历史的存储过程
EXEClend_history_info;
USETSGL203
GO
IFEXISTS（SELECTnameFROMsysobjectsWHEREname='lend_current_info'）
DROPPROCEDURElend_current_info
GO
--创建查询读者当前借阅情况的存储过程
CREATEPROCEDURElend_current_info
AS
BEGIN
SELECTr.lno,dept,name,bornum,b.ISBN,barno,bname,bbt
FROM（SELECTlno,dept,name,bornumFROMreader203）r,
lend203l,book203b
WHEREr.lno=l.lnoandl.ISBN=b.ISBN
END
GO
--执行查询读者当前借阅情况的存储过程
EXEClend_current_info;
GO
-- 2.编写一存储过程，根据TSGL数据库的三个表查询指定读者（指定借书证号或指定姓名等）当前的借书情况。
USETSGL203
GO
IFEXISTS（SELECTnameFROMsys.objectsWHEREname='lend_lno_info'）
DROPPROCEDURElend_lno_info
GO
CREATEPROCEDURElend_lno_info
@lnochar（12）
AS
BEGIN
SELECTr.lno,dept,name,bornum,b.ISBN,barno,bname
FROM（SELECTlno,dept,name,bornumFROMreader203WHERElno=@lno）r,
lend203l,book203b
WHEREr.lno=l.lnoandl.ISBN=b.ISBN
END
GO
EXEClend_lno_info200807042101;
-- 3.利用TSGL数据库中的TREADER表、TBOOK表及historytable表创建一存储过程，
--查询指定图书（ISBN或书名）的借阅历史。
该存储过程在参数中使用模糊查询，如果没有提供参数，则使用预设的默认值。
USETSGL203
GO
IFEXISTS（SELECTnameFROMsys.objectsWHEREname='lend_book_info'）
DROPPROCEDURElend_book_info
GO
--创建查询指定图书（ISBN或书名）的借阅历史存储过程
CREATEPROCEDURElend_book_info
@bnamevarchar（40）='%'
AS
BEGIN
SELECTb.ISBN,barno,bname,r.lno,r.dept,r.name,bbt,rbbt
FROM（SELECTlno,ISBN,barno,bbt,rbbtFROMhistory203）h,
（SELECTISBN,bnameFROMbook203WHEREbnamelike@bname+'%'）b,
（SELECTlno,dept,name,bornumFROMreader203）r
WHEREh.ISBN=b.ISBNANDr.lno=h.lno
END
GO
--执行存储过程
EXEClend_book_info'计算机';
EXEClend_book_info;
EXEClend_book_info'SQLSERVER';
-- 4.编写一存储过程，统计指定图书在给定时间段内的借阅次数，存储过程中使用输入和输出参数。
--如果存在同名存储过程则删除
USETSGL203
GO
IFEXISTS（SELECTnameFROMsys.objectsWHEREname='lend_book_times'）
DROPPROCEDURElend_book_times
GO
--创建新的存储过程
CREATEPROCEDURElend_book_times
@bnamevarchar（40）,@startdate,@enddate,
@timesintOUTPUT
AS
BEGIN
SELECTb.bname,h.ISBN,COUNT（h.ISBN）AS借阅次数
FROM（SELECTlno,ISBN,barno,bbt,rbbtFROMhistory203）h,
（SELECTISBN,bnameFROMbook203WHEREbnamelike@bname+'%'）b
WHEREh.ISBN=b.ISBNANDbbt>@startANDbbt<@end
groupbyb.bname,h.ISBN
END
GO
--执行存储过程
DECLARE@timesint
EXECUTElend_book_times'','2013-02-01','2013-05-01',@timesOUTPUT
GO
DECLARE@timesint
EXECUTElend_book_times'计算机','2013-02-01','2013-05-01',@timesOUTPUT
-- 5.编写一存储过程，在TSGL数据库的TREADER表上声明并打开一个游标。
通过游标读取所需信息。
--如果存在同名存储过程则删除
USETSGL203
GO
IFEXISTS（SELECTnameFROMsys.objectsWHEREname='reader_info'）
DROPPROCEDUREreader_info
GO
--创建新的存储过程
CREATEPROCEDUREreader_info
@cursorCURSORVARYINGOUTPUT
AS
BEGIN
SET@cursor=CURSORFORWARD_ONLYSTATICFOR
SELECT*
FROMreader203
OPEN@cursor
END
--执行存储过程
DECLARE@MyCursorCURSOR
EXECreader_info@cursor=@MyCursorOUTPUT
--读取游标中数据
FETCHNEXTFROM@MyCursor
WHILE（@@FETCH_STATUS=0）
BEGIN
FETCHNEXTFROM@MyCursor
END
CLOSE@MyCursor
DEALLOCATE@MyCursor
GO
-- 6.创建加密过程，使用sp_helptext系统存储过程获得关于加密的存储过程的信息，
--然后尝试直接从syscomment表中获取关于该过程的信息。
CREATEPROCEDUREencrypt_this
WITHENCRYPTION
AS
SELECT*
FROMreader203
EXECsp_helptextencrypt_this
--对已加密的存储过程在syscomments表中的检索
SELECT*FROMsys.syscomments
WHEREid=（
SELECTobject_id
FROMsys.objects
WHEREname='encrypt_this'）;
--对未加密的存储过程在syscomments表中的检索
SELECTtextFROMsys.syscomments
WHEREid=（
SELECTobject_id
FROMsys.objects
WHEREname='reader_info'）;
-- 7.对TSGL数据库中的三个表分别创建添加、修改、删除一条记录的存储过程。
USETSGL203
GO
--创建更新读者表中读者号的存储过程
IFEXISTS（SELECTnameFROMsys.objectsWHEREname='reader_update'）
DROPPROCEDUREreader_update
GO
CREATEPROCreader_update@new_lnochar（12）,@old_lnochar（12）
AS
BEGIN
UPDATEreader203
SETlno=@new_lno
WHERElno=@old_lno
PRINT'读者号已更新.'
END
--创建根据读者号删除读者表中一条记录的存储过程
IFEXISTS（SELECTnameFROMsys.objectsWHEREname='reader_delete'）
DROPPROCEDUREreader_delete
GO
CREATEPROCreader_delete@lnochar（12）
AS
BEGIN
DELETEreader203
WHERElno=@lno
PRINT'已删除读者表中一条记录.'
END
--创建删除借阅表中一条记录的存储过程
IFEXISTS（SELECTnameFROMsys.objectsWHEREname='lend_delete'）
DROPPROCEDURElend_delete
GO
CREATEPROClend_delete@lnochar（12）,@ISBNvarchar（16）,@barnovarchar（10）
AS
BEGIN
DELETE
FROMlend203
WHERElno=@lnoANDISBN=@ISBNANDbarno=@barno
END
GO
--创建向借阅表中添加一条记录的存储过程
IFEXISTS（selectnameFROMsys.objectsWHEREname='lend_insert'）
DROPPROClend_insert
GO
CREATEPROClend_insert@lnochar（12）,@ISBNvarchar（16）,@barnovarchar（10）,@bbtdate
AS
BEGIN
INSERTINTOlend203
VALUES（@lno,@ISBN,@barno,@bbt）
END
GO
--创建借阅表触发器，删除一条记录后，插入到历史借阅表
--对于还书日期rbbt获取当前系统时间，并插入到对应记录的还书日期字段（未实现）
IFEXISTS（SELECTnameFROMsys.objectsWHEREname='lend_delete_trigger'）
DROPTRIGGERlend_delete_trigger
GO
CREATETRIGGERlend_delete_trigger
ONlend203AFTERDELETE
AS
BEGIN
INSERTINTOhistory203（lno,ISBN,barno,bbt）
SELECTlno,ISBN,barno,bbtFROMdeleted
PRINT'已删除的借书记录被插入到历史借阅表.'
--更新读者表
UPDATEreader203
SETbornum=bornum-1
WHERElno=（SELECTlnoFROMdeleted）
PRINT'读者表中该读者借阅数量已更新.'
--更新图书表
UPDATEbook203
SETinvnum=invnum+1
WHEREISBN=（SELECTISBNFROMdeleted）
PRINT'图书表中该书库存量已更新.'
END
--执行删除借阅表一条记录存储过程
EXEClend_delete'200807035102','8-7012-1020-1','466241'
-- 8.创建触发器，当向LEND表中插入一条记录时，将TREADER表中该学生的借书数加1，将TBOOK表中该书的库存量减1。
GO
IFEXISTS（SELECTnameFROMsys.objectsWHEREname='lend_insert_trigger'）
DROPTRIGGERlend_insert_trigger
GO
CREATETRIGGERlend_insert_trigger
ONlend203AFTERINSERT
AS
BEGIN
DECLARE@lnochar（12）,@ISBNvarchar（16）
SELECT@lno=lno,@ISBN=ISBN
FROMINSERTED
UPDATEreader203
SETbornum=bornum+1
WHERElno=@lno
PRINT'读者借阅数量已更新'
UPDATEbook203
SETinvnum=invnum-1
WHEREISBN=@ISBN
PRINT'外借书库存量已更新'
END
GO
SELECTlno,bornum更新前借阅数量
FROMreader203
WHERElno=200807035102
GO
--执行向借阅表插入一条记录的存储过程
EXEClend_insert'200807035102','4-6076-1087-3','00422310','2013-10-29'
SELECTl.lno,r.bornum更新后借阅数量
FROMlend203l,（selectlno,bornumfromreader203wherelno=200807035102）r
WHEREl.lno=r.lno
-- 9.创建触发器，当修改TREADER表中的借书证号时，同时也要将LEND表中的借书证号修改成相应的借书证号
--（假设TREADER表和LEND表之间没有定义外键约束）。
IFEXISTS（SELECTnameFROMsys.objectsWHEREname='reader_lno_trigger'）
DROPTRIGGERreader_lno_trigger
GO
CREATETRIGGERreader_lno_trigger
ONreader203AFTERUPDATE
AS
BEGIN
DECLARE@old_lnochar（12）,@new_lnochar（12）
SELECT@old_lno=（SELECTlnoFROMdeleted）
SELECT@new_lno=（SELECTlnoFROMinserted）
UPDATElend203
SETlno=@new_lno
WHERElno=@old_lno
END
SELECTr.lno更新前读者表中读者号,name,l.lno更新前借阅表中读者号
FROMreader203r,lend203l
WHEREr.lno=l.lnoANDr.lno='200807035102';
--执行更新读者表中读者号存储过程，前面新的读者号，后面是旧读者号
EXECreader_update'200805035133','200807035102'
SELECTr.lno更新后读者表中读者号,name,l.lno更新后借阅表中读者号
FROMreader203r,lend203l
WHEREr.lno=l.lnoANDr.lno='200805035133';
-- 10.在删除TREADERB表中的一条生记录时将LEND表中该学生的相应记录也删除。
IFEXISTS（SELECTnameFROMsys.objectsWHEREname='reader_delete_trigger'）
DROPTRIGGERreader_delete_trigger
GO
CREATETRIGGERreader_delete_trigger
ONreader203AFTERDELETE
AS
BEGIN
DECLARE@old_lnochar（12）
SELECT@old_lno=（SELECTlnoFROMdeleted）
DELETElend203
WHERElno=@old_lno
END
--执行删除读者表中一条记录
EXECreader_delete'200805035133'
-- 11.在数据库TSGL中创建一触发器，当向lend表插入一条记录时，检查该记录的借书证号在TREADER表中是否存在，
--检查图书的ISBN在TBOOK表中是否存在，以及图书的库存量是否大于0,若有一项为否，则不允许插入。
IFEXISTS（SELECTnameFROMsys.objectsWHEREname='lend_insert_trigger'）
DROPTRIGGERlend_insert_trigger
GO
CREATETRIGGERlend_insert_trigger
ONlend203AFTERINSERT
AS
BEGIN
DECLARE@lnochar（12）,@ISBNvarchar（16）,@invnumint
SELECT@lno=lno,@ISBN=ISBNFROMINSERTED
SELECT@invnumFROMbook203WHEREISBN=@ISBN
IF（（SELECTCOUNT（lno）FROMreader203WHERElno=@lno）>0
AND
（SELECTCOUNT（ISBN）FROMbook203WHEREISBN=@ISBN）>0
AND
（@invnum）>0）
begin
UPDATEreader203
SETbornum=bornum+1
WHERElno=@lno
PRINT'读者借阅数量已更新'
UPDATEbook203
SETinvnum=invnum-1
WHEREISBN=@ISBN
PRINT'外借书库存量已更新'
end
ELSE
ROLLBACK
END
GO
--执行向借阅表插入一条记录的存储过程
SELECT*FROMlend203W
升级会员 