实验三 SQL Server的视图、存储过程和触发器一、实验目的本实验主要了解SQL Server视图、存储过程和触发器的基本概念和使用方法。

通过本实验，读者将学会在对象资源管理器中创建、修改、执行和删除存储过程的操作以及在查询分析器中执行的T-SQL语句；掌握触发器的创建、修改和删除的操作方法和T-SQL语句。

掌握视图的创建、修改和删除的操作方法和T-SQL语句。

二、实验环境●Microsoft SQL Server 2005 SSMS●Book数据库三、实验内容1．创建存储过程proc_book1，查询指定学院的教师预订教材的情况。

要求显示教材名称（T_ame）、教师姓名（T_erName）和教材数量（T_okNum+T_okNum）。

(1)查询条件为：学院代码CREATE PROC proc_book1@acode char(6)ASSELECTBookName AS 教材名称,TeacherName AS 教师姓名,StuBookNum+TeaBookNum AS 教材数量FROM T_TeacherINNER JOIN T_BookOrder ON T_erCode=T_erCodeINNER JOIN T_BookInfo ON T_ode= T_ode)WHERE T_ode=@acode--执行存储过程proc_book1，查询学院代号为'02'的教师预订教材的信息。

EXEC proc_book1 '02'或：EXEC proc_book1 @acode='02'（2）查询条件为：学院名称CREATE PROC proc_book1_2@aname varchar(50)ASSELECTBookName AS 教材名称,TeacherName AS 教师姓名,StuBookNum+TeaBookNum AS 教材数量FROM T_Academy,T_Teacher,T_BookOrder,T_BookInfoWHERE T_ode=T_odeAND T_erCode=T_erCodeAND T_ode=T_odeAND T_ame=@aname--执行存储过程proc_book1_2，查询学院名称为'人文学院'的教师预订教材的信息。

EXEC proc_book1_2 '人文学院'或EXEC proc_book1_2 @aname='人文学院'2．创建存储过程proc_book2，查询指定教材的预订数量。

统计条件为：教材代码CREATE PROCEDURE proc_book2@tcode char(20)ASSELECT SUM(StuBookNum)+SUM(TeaBookNum) AS 预订数量FROM T_BookOrderGROUP BY BookCodeHAVING BookCode=@tcode--执行存储过程proc_book2，统计教材代号为'010004'的预订信息。

exec proc_book2 '100001'3．创建添加教材信息的存储过程proc_book3。

CREATE PROCEDURE proc_book3@bookcode char(6), @bookname varchar(40),@publishercode varchar(2), @author varchar(40),@publisherdate datetime, @price money,@isbncode char(20), @stocknum smallint,@booksort char(20)ASBEGININSERT INTO T_BookInfoVALUES (@bookcode, @bookname, @publishercode, @author,@publisherdate, @price, @isbncode, @stocknum, @booksort)END--执行存储过程proc_book3EXEC proc_book3 '500001','信息系统管理技术','31','谭浩强','2007-05-11',30,'',30,'计算机'4．创建存储过程proc_book4，查询指定教材名称和出版社名称的教材所对应的教材代号和库存。

CREATE PROCEDURE proc_book4@bookname varchar(40), @publisher varchar(40),@bookcode char(6) OUTPUT, @stocknum smallint OUTPUTASSELECT @bookcode=BookCode,@stocknum=StockNumFROM T_BookInfoINNER JOIN T_Publisher ON T_sherCode=T_sherCodeWHERE T_sher=@publisherAND BookName=@bookname--执行存储过程proc_book4DECLARE @bookcode char(6),@stocknum smallintEXEC proc_book4 'C语言程序设计', '电子工业出版社', @bookcode OUTPUT, @stocknum OUTPUT PRINT '该教材的代号'+CAST(@bookcode AS char(6))PRINT '该教材的库存'+STR(@stocknum)5．创建触发器tri_book1，在对T_Teacher表进行插入、修改和删除记录时，都会自动显示表中的内容。

CREATE TRIGGER tri_book1ON T_TeacherFOR INSERT,UPDATE,DELETEASSELECT * FROM T_Teacher6．创建触发器tri_book2，当修改T_Teacher表中的TeacherCode字段值时，该字段在T_BookOrder表中的对应值也作修改。

CREATE TRIGGER tri_book2ON T_TeacherFOR UPDATEASIF UPDATE(TeacherCode)UPDATE T_BookOrderSET TeacherCode=(SELECT erCode FROM Inserted)WHERE TeacherCode=(SELECT TeacherCode FROM Deleted)7．创建触发器tri_book3，检查插入在T_BookInfo表中的教材库存(StockNum)是否大于等于0。

CREATE TRIGGER tri_book3ON T_BookInfoFOR INSERT,UPDATEASDECLARE @stocknum smallintSELECT @stocknum=StockNum FROM insertedIF @stocknum<0BEGINROLLBACKRAISERROR('库存量必须大于等于０!',16,1)END８．创建触发器tri_book４，当删除教师信息时同步删除T_BookOrder表中对应教师所预订的教材记录。

CREATE TRIGGER tri_book４ON T_TeacherFOR DELETEASDELETE FROM T_BookOrderWHERE T_erCode=(SELECT TeacherCodeFROM deleted)９．创建视图view_book1，使其包含预订数量最高的20%的教材信息。

要求显示教材代号(BookCode)、教材名称(BookName)、ISBN号(ISBNCode)和出版社名称(Publisher)。

CREATE VIEW view_book1ASSELECT TOP 20 PERCENTT_ode AS 教材代号,BookName AS 教材名称,ISBNCode AS ISBN号,StuBookNum+TeaBookNum AS 数量,Publisher AS 出版社名称FROM T_BookOrderINNER JOIN T_Publisher ON T_sherCode=T_sherCodeINNER JOIN T_BookInfo ON T_ode=T_odeORDER BY StuBookNum+TeaBookNum DESC10．创建视图view_book2，查询预订了教材名中含有“程序”一词的教师姓名（TeacherName）和所在学院名称（AcadName）。

CREATE VIEW view_book2ASSELECTTeacherName AS 教师姓名,AcadName AS 所在学院名称FROM T_Teacher,T_Academy,T_BookOrder,T_BookinfoWHERE T_ode=T_odeAND T_erCode=T_erCodeAND T_ode=T_odeAND BookName LIKE '%程序%'或CREATE VIEW view_book2ASSELECTTeacherName AS 教师姓名,AcadName AS 所在学院名称SELECT TeacherName, AcadNameFROM T_AcademyINNER JOIN T_Teacher ON T_ode=T_odeINNER JOIN T_BookOrder ON T_erCode=T_erCodeINNER JOIN T_Bookinfo ON T_ode=T_odeWHERE BookName LIKE '%程序%'垚11．创建视图view_book3，使其包含“学院名称（AcadName）”、“教师姓名（TeacherName）”、“预订书名（BookName）”、“是否领走（TakeAway）”。

CREATE VIEW view_book3ASSELECTAcadName AS 学院名称,TeacherName AS 教师姓名,BookName AS 预订书名,TakeAway AS 是否领走FROM T_BookInfoINNER JOIN T_BookOrder ON T_ode=T_odeINNER JOIN T_Teacher ON T_erCode = T_erCodeINNER JOIN T_Academy ON T_ode = T_ode。