实验九--存储过程和触发器

1、实验九 存储过程和触发器实验内容 在已建立的TSGL数据库的基础上，按如下要求对数据库进行操作，按同前的命名要求保存操作代码和截图。 1. 利用TSGL数据库中的TREADER表和TBOOK表和historytable表，编写一无参存储过程用于查询每个读者的借阅历史，然后调用该存储过程。 2. 编写一存储过程，根据TSGL数据库的三个表查询指定读者（指定借书证号或指定姓名等）当前的借书情况。 3. 利用TSGL数据库中的TREADER表、TBOOK表及historytable表创建一存储过程，查询指定图书（ISBN或书名）的借阅历史。该存储过程在参数中使用模糊查询，如果没有提供参数，则使用预设

2、的默认值。4. 编写一存储过程，统计指定图书在给定时间段内的借阅次数，存储过程中使用输入和输出参数。5. 编写一存储过程，在TSGL数据库的TREADER表上声明并打开一个游标。通过游标读取所需信息。6. 创建加密过程，使用sp_helptext系统存储过程获得关于加密的存储过程的信息，然后尝试直接从syscomment表中获取关于该过程的信息。7. 对TSGL数据库中的三个表分别创建添加、修改、删除一条记录的存储过程。8. 创建触发器，当向LEND表中插入一条记录时，将TREADER表中该学生的借书数加1，将TBOOK表中该书的库存量减1。9. 创建触发器，当修改TREADER表中的借书证号

3、时，同时也要将LEND表中的借书证号修改成相应的借书证号（假设TREADER表和LEND表之间没有定义外键约束）10. 在删除TREADERB表中的一条生记录时将LEND表中该学生的相应记录也删除。11. 在数据库TSGL中创建一触发器，当向lend表插入一条记录时，检查该记录的借书证号在TREADER表中是否存在，检查图书的ISBN在TBOOK表中是否存在，以及图书的库存量是否大于0,若有一项为否，则不允许插入。12. 在数据库TSGL中创建一触发器，当删除TREADER表一条记录时，检查该记录的借书证号在JY表中是否存在，如果存在，则不允许删除。13. 修改数据库TSGL中在TREADER

4、表上定义的触发器(内容自定)。14. 删除以上所建触发器。- 1. 利用TSGL数据库中的TREADER表和TBOOK表和historytable表，-编写一无参存储过程用于查询每个读者的借阅历史，然后调用该存储过程。USE TSGL203GOIF EXISTS(SELECT name FROM sys.objects WHERE name = lend_history_info)DROP PROCEDURE lend_history_infoGO-创建查询读者借阅历史的存储过程CREATE PROCEDURE lend_history_infoWITH ENCRYPTIONASBEGINSE

5、LECT r.lno,r.dept,,r.bornum,b.ISBN,h.barno,b.bname,h.bbt,h.rbbtFROM (SELECT lno,dept,name,bornum FROM reader203) r,(SELECT ISBN,bname FROM book203)b,(SELECT lno,ISBN,barno,bbt,rbbt FROM history203) hWHERE r.lno = h.lno and h.ISBN =b.ISBN ENDGO-执行查询读者借阅历史的存储过程EXEC lend_history_info;USE TSGL203G

6、OIF EXISTS(SELECT name FROM sysobjects WHERE name = lend_current_info)DROP PROCEDURE lend_current_infoGO-创建查询读者当前借阅情况的存储过程CREATE PROCEDURE lend_current_infoASBEGINSELECT r.lno,dept,name,bornum,b.ISBN,barno,bname,bbtFROM (SELECT lno,dept,name,bornum FROM reader203) r,lend203 l,book203 bWHERE r.lno =

7、l.lno and l.ISBN = b.ISBNENDGO-执行查询读者当前借阅情况的存储过程EXEC lend_current_info ;GO-2. 编写一存储过程，根据TSGL数据库的三个表查询指定读者（指定借书证号或指定姓名等）当前的借书情况。USE TSGL203GOIF EXISTS(SELECT name FROM sys.objects WHERE name = lend_lno_info)DROP PROCEDURE lend_lno_infoGOCREATE PROCEDURE lend_lno_infolno char(12)ASBEGINSELECT r.lno,de

8、pt,name,bornum,b.ISBN,barno,bnameFROM (SELECT lno,dept,name,bornum FROM reader203 WHERE lno = lno) r,lend203 l,book203 bWHERE r.lno = l.lno and l.ISBN = b.ISBNENDGOEXEC lend_lno_info 1;-3. 利用TSGL数据库中的TREADER表、TBOOK表及historytable表创建一存储过程，- 查询指定图书（ISBN或书名）的借阅历史。该存储过程在参数中使用模糊查询，如果没有提供参数，则使用预设的默认值。USE T

9、SGL203GOIF EXISTS(SELECT name FROM sys.objects WHERE name = lend_book_info)DROP PROCEDURE lend_book_infoGO-创建查询指定图书（ISBN或书名）的借阅历史存储过程CREATE PROCEDURE lend_book_infobname varchar(40) =%ASBEGINSELECT b.ISBN,barno,bname,r.lno,r.dept,,bbt,rbbtFROM (SELECT lno,ISBN,barno,bbt,rbbt FROM history203) h

10、,(SELECT ISBN,bname FROM book203 WHERE bname like bname+%) b,(SELECT lno,dept,name,bornum FROM reader203) rWHERE h.ISBN = b.ISBN AND r.lno = h.lnoENDGO-执行存储过程EXEC lend_book_info 计算机;EXEC lend_book_info;EXEC lend_book_info SQL SERVER;-4.编写一存储过程，统计指定图书在给定时间段内的借阅次数，存储过程中使用输入和输出参数。-如果存在同名存储过程则删除USE TSGL

11、203GOIF EXISTS(SELECT name FROM sys.objects WHERE name = lend_book_times)DROP PROCEDURE lend_book_timesGO-创建新的存储过程CREATE PROCEDURE lend_book_timesbname varchar(40),start date,end date,times int OUTPUTASBEGINSELECT b.bname,h.ISBN,COUNT(h.ISBN) AS 借阅次数FROM (SELECT lno,ISBN,barno,bbt,rbbt FROM history2

12、03) h,(SELECT ISBN,bname FROM book203 WHERE bname like bname+%) bWHERE h.ISBN = b.ISBN AND bbt start AND bbt 0AND (SELECT COUNT(ISBN) FROM book203 WHERE ISBN = ISBN)0AND (invnum)0 )beginUPDATE reader203SET bornum = bornum +1WHERE lno =lnoPRINT 读者借阅数量已更新UPDATE book203SET invnum = invnum -1WHERE ISBN

13、=ISBNPRINT 外借书库存量已更新endELSEROLLBACKENDGO-执行向借阅表插入一条记录的存储过程SELECT * FROM lend203 WHERE lno = 3EXEC lend_insert 3,8-7012-1020-1,2013-10-30GOSELECT * FROM lend203 WHERE lno = 3-执行向借阅表插入一条记录的存储过程EXEC lend_insert 1,8-7012-1020-1,2013-10-31SELECT * FROM lend203 WHERE lno = 1-12.在数据库TSGL中创建一触发器，当删除TREADER表

14、一条记录时，-检查该记录的借书证号在JY表中是否存在，如果存在，则不允许删除。IF EXISTS(SELECT name FROM sys.objects WHERE name = reader_delete_trigger)DROP TRIGGER reader_delete_triggerGOCREATE TRIGGER reader_delete_triggerON reader203 AFTER DELETEASBEGINDECLARE lno char(12)SELECT lno FROM DELETEDIF( (SELECT COUNT(lno) FROM lend203 WHER

15、E lno = lno)0 )beginPRINT 该读者号在借阅表中存在借书记录，不允许删除.ROLLBACKend/*因为前面创建过删除lend 表中一条记录时将其插入到历史借阅表消息 2627，级别 14，状态 1，过程 lend_delete_trigger，第 5 行违反了 PRIMARY KEY 约束 PK_historyt_CC0F。不能在对象 dbo.history203 中插入重复键。语句已终止。ELSEbeginDELETE lend203WHERE lno = lnoend*/ENDEXEC reader_delete 2-13.修改数据库TSGL中在TREADER表上定义的触发器(内容自定)。GOALTER TRIGGER reader_delete_triggerON reader203 AFTER DELETEASBEGINDECLARE lno char(