实验三SQLServer的视图、存储过程和触发器

实验三 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)

AS

SELECT

BookName AS 教材名称,

TeacherName AS 教师姓名,

StuBookNum+TeaBookNum AS 教材数量

FROM T_Teacher

INNER JOIN T_BookOrder ON T_erCode=T_erCode

INNER 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)

AS

SELECT

BookName AS 教材名称,

TeacherName AS 教师姓名,

StuBookNum+TeaBookNum AS 教材数量

FROM T_Academy,T_Teacher,T_BookOrder,T_BookInfo

WHERE T_ode=T_ode

AND T_erCode=T_erCode

AND T_ode=T_ode

AND 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)

AS

SELECT SUM(StuBookNum)+SUM(TeaBookNum) AS 预订数量

FROM T_BookOrder

GROUP BY BookCode

HAVING 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)

AS

BEGIN

INSERT INTO T_BookInfo

VALUES (@bookcode, @bookname, @publishercode, @author,

@publisherdate, @price, @isbncode, @stocknum, @booksort)

END

--执行存储过程proc_book3

EXEC 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 OUTPUT

AS

SELECT @bookcode=BookCode,@stocknum=StockNum

FROM T_BookInfo

INNER JOIN T_Publisher ON T_sherCode=T_sherCode

WHERE T_sher=@publisher

AND BookName=@bookname

--执行存储过程proc_book4

DECLARE @bookcode char(6),@stocknum smallint

EXEC proc_book4 'C语言程序设计', '电子工业出版社', @bookcode OUTPUT, @stocknum OUTPUT PRINT '该教材的代号'+CAST(@bookcode AS char(6))

PRINT '该教材的库存'+STR(@stocknum)

5．创建触发器tri_book1，在对T_Teacher表进行插入、修改和删除记录时，都会自动显示表中的内容。

CREATE TRIGGER tri_book1

ON T_Teacher

FOR INSERT,UPDATE,DELETE

AS

SELECT * FROM T_Teacher

6．创建触发器tri_book2，当修改T_Teacher表中的TeacherCode字段值时，该字段在T_BookOrder表中的对应值也作修改。

CREATE TRIGGER tri_book2

ON T_Teacher

FOR UPDATE

AS

IF UPDATE(TeacherCode)

UPDATE T_BookOrder

SET TeacherCode=(SELECT erCode FROM Inserted)

WHERE TeacherCode=(SELECT TeacherCode FROM Deleted)

7．创建触发器tri_book3，检查插入在T_BookInfo表中的教材库存(StockNum)是否大于等于0。

CREATE TRIGGER tri_book3

ON T_BookInfo

FOR INSERT,UPDATE

AS

DECLARE @stocknum smallint

SELECT @stocknum=StockNum FROM inserted

IF @stocknum<0

BEGIN

ROLLBACK

RAISERROR('库存量必须大于等于０!',16,1)

END

８．创建触发器tri_book４，当删除教师信息时同步删除T_BookOrder表中对应教师所预订的教材记录。

CREATE TRIGGER tri_book４

ON T_Teacher

FOR DELETE

AS

DELETE FROM T_BookOrder

WHERE T_erCode=(

SELECT TeacherCode

FROM deleted

)

９．创建视图view_book1，使其包含预订数量最高的20%的教材信息。要求显示教材代号(BookCode)、教材名称(BookName)、ISBN号(ISBNCode)和出版社名称(Publisher)。CREATE VIEW view_book1

AS

SELECT TOP 20 PERCENT

T_ode AS 教材代号,

BookName AS 教材名称,

ISBNCode AS ISBN号,

StuBookNum+TeaBookNum AS 数量,

Publisher AS 出版社名称

FROM T_BookOrder

INNER JOIN T_Publisher ON T_sherCode=T_sherCode

INNER JOIN T_BookInfo ON T_ode=T_ode

ORDER BY StuBookNum+TeaBookNum DESC

10．创建视图view_book2，查询预订了教材名中含有“程序”一词的教师姓名（TeacherName）和所在学院名称（AcadName）。

CREATE VIEW view_book2

AS

SELECT

TeacherName AS 教师姓名,

AcadName AS 所在学院名称

FROM T_Teacher,T_Academy,T_BookOrder,T_Bookinfo

WHERE T_ode=T_ode

AND T_erCode=T_erCode

AND T_ode=T_ode

AND BookName LIKE '%程序%'

或

CREATE VIEW view_book2

AS

SELECT

TeacherName AS 教师姓名,

AcadName AS 所在学院名称

SELECT TeacherName, AcadName

FROM T_Academy

INNER JOIN T_Teacher ON T_ode=T_ode

INNER JOIN T_BookOrder ON T_erCode=T_erCode

INNER JOIN T_Bookinfo ON T_ode=T_ode

WHERE BookName LIKE '%程序%'

垚

11．创建视图view_book3，使其包含“学院名称（AcadName）”、“教师姓名（TeacherName）”、“预订书名（BookName）”、“是否领走（TakeAway）”。

CREATE VIEW view_book3

AS

SELECT

AcadName AS 学院名称,

TeacherName AS 教师姓名,

BookName AS 预订书名,

TakeAway AS 是否领走

FROM T_BookInfo

INNER JOIN T_BookOrder ON T_ode=T_ode

INNER JOIN T_Teacher ON T_erCode = T_erCode

INNER JOIN T_Academy ON T_ode = T_ode