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

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

一、实验目的

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

二、实验环境

●Microsoft SQL Server 2005 SSMS

●Book数据库

三、实验内容

1．创建存储过程proc_book1，查询指定学院的教师预订教材的情况。要求显示教材名称（T_BookInfo.BookName）、教师姓名（T_Teacher.TeacherName）和教材数量（T_BookOrder.StuBookNum+T_BookOrder.TeaBookNum）。

(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_Teacher.TeacherCode=T_BookOrder.TeacherCode

INNER JOIN T_BookInfo ON T_BookOrder.BookCode= T_BookInfo.BookCode)

WHERE T_Teacher.AcadCode=@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_Academy.AcadCode=T_Teacher.AcadCode

AND T_Teacher.TeacherCode=T_BookOrder.TeacherCode

AND T_BookInfo.BookCode=T_BookOrder.BookCode

AND T_Academy.AcadName=@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,'760211008',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