数据库第三部分.

实验报告课程名称数据库技术实践实验项目索引、存储过程和触发器、函数和游标实验仪器SQL Server 2008系别____计算机科学与技术系 _专业____计算机科学与技术____班级/学号___xxxxxxxxxxxxxxxxxxxxxxxx学生姓名_____xxxxxxxxxxx实验日期___2xxxxxxxxxx成绩_______________________指导教师___ 梁琦 _______ _______一．内容说明1.索引涉及索引类型，与不建立索引形成对比，可以提高执行效率2.存储过程存储过程是SQL语言在应用程序和数据库之间的主要编程接口，其存在形式有两种，一是在客户端存储代码，另一种是将SQL语言存储在数据库服务器端，由应用程序调用这些语言。

存储过程可以：接受输入参数并以输出参数的形式将多个数值返回、包含执行数据库的语句、将查询语句执行结果返回到客户端，其好处有，允许模块化程序设计、改善性能、减少网络流量、可作为安全机制使用。

3.触发器是有一段由对数据的更改操作引发的自动执行的代码，通常用于保证业务规则和数据完整性，用户可以编程的方法来实现复杂的处理逻辑和业务规则，增强了完整性约束的功能。

4.用户定义函数可以扩展数据操作的功能，期中包括，变量函数和表值函数，标量函数返回单个数据值，表值函数返回一个表，表值函数又分为内联表值函数和语句表值函数。

5.游标游标提供对结果集中的每一行或一部分行进行单独的处理，这在select无法实现的，其具有如下特点：允许定位结构集中的特定行、允许从结果集的当前位臵检索一行或多行、支持对结果集中当前行的数据进行修改、为由其他用户对显示在结果集中的数据所做的更改提供不同级别的可见性支持。

二. 索引本章上机练习均利用MySimpleDB数据库实现。

写出实现创建满足如下要求的索引的SQL语句，并执行这些语句。

1．请为下列查询设计一个最合适的索引，并查看建立索引前后该语句的执行计划，比较执行效率。

SELECT FirstName,LastName, EmailAddress,PhoneFROM Person.ContactWHERE Phone BETWEEN '300' AND '350'CREATE NONCLUSTERED INDEX IX_Person_ContactON Person.Contact(Phone)INCLUDE(FirstName,LastName,EmailAddress);建立索引之前：建立索引后：2．Production.ProductReview 表包含的列有：ProductID（int）、ReviewerName（nvarchar(50) ）和 Comments（nvarchar (3850)）。

假设经常执行下列形式的查询，请为该类查询创建合适的索引，以最大程度地提高查询效率。

SELECT Comments,ReviewerNameFROM Production.ProductReviewWHERE ProductID >= 937 and ReviewerName like '[a-d]%'; create index pidx on Production.ProductReview(ProductID) include(Comments,ReviewerName)WHERE ProductID>=937建立索引前：建立索引后：3．在Person.Address表上创建具有一个键列（PostalCode）和四个非键列（AddressLine1、AddressLine2、City、StateProvinceID）的包含列索引。

查看索引建立前后下列查询语句的执行计划，观察索引对效率的提高情况。

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCodeFROM Person.AddressWHERE PostalCode BETWEEN N'94000' and N'95999'; CREATE NONCLUSTERED INDEX IX_Address_PostalCodeON Person.Address(PostalCode)INCLUDE(AddressLine1,AddressLine2,City,StateProvinceID);索引使该查询速度变快了建立索引前：建立索引后：4.设经常需要执行下列类型的查询，以统计2003年某一段时间各产品的销售总量。

SELECT ProductID, SUM(sod.OrderQty) AS QtySoldFROM Sales.SalesOrderHeader sohJOIN Sales.SalesOrderDetail sodON soh.SalesOrderID = sod.SalesOrderIDWHERE soh.OrderDate >= '2003-08-02'AND soh.OrderDate < '2003-08-31'GROUP BY sod.ProductID为尽可能提高该类查询的执行效率，请分别为Sales.SalesOrderHeader 和Sales.SalesOrderDetail表建立合适的索引，并简单说明理由。

查看索引建立前后上述查询语句的执行计划，观察索引对该查询的效率提高情况。

索引使该查询速度变快了三．存储过程和触发器如无特别说明，以下各题均利用第6章建立的Students数据库以及Student、Course和SC表实现。

1．创建满足下述要求的存储过程，并查看存储过程的执行结果。

（1）查询每个学生的修课总学分，要求列出学生学号及总学分。

CREATE PROCEDURE SELECT_STUDENTASSELECT Sno,sum(Credit)as总学分FROM sc join course c on o=oGroup by SnoEXEC SELECT_STUDENT（2）查询学生的学号、姓名、修的课程号、课程名、课程学分，将学生所在系作为输入参数，默认值为“计算机系”。

执行此存储过程，并分别指定一些不同的输入参数值，查看执行结果。

CREATE PROC information@dept CHAR(20)='计算机系'ASSELECT sc$.sno,sname,sc$.cno,cname,credit FROM student$s inner join sc$ONs.sno=sc$.sno inner join course$c ON o=sc$.cno WHERE dept=@deptEXEC information'信息管理系'EXEC SELECT_STUDENT1'通信工程系',null（3）查询指定系的男生人数，其中系为输入参数，人数为输出参数。

CREATE PROCEDURE SELECT_STUDENT2@t_Dept nvarchar(20),@total_man int OUTPUTASSELECT@total_man=COUNT(*)FROM studentWHERE Dept=@t_Dept AND Sex='男'PRINT@total_manDECLARE@RE_man int--声明变量EXEC SELECT_STUDENT2'计算机系',@RE_man OUTPUT--PRINT @RE_man（4）查询指定学生（姓名）在指定学期的选课门数和考试平均成绩，要求姓名和学期为输入参数，选课门数和平均成绩用输出参数返回，平均成绩保留到小时点后2位。

create procedure ti_4@name char(10),@semes int,@countCno int output,@avgG numeric(4,2)outputasselect@countCno=COUNT(*),@avgG=avg(Grade*1.00)fromSC join Course c on o=ojoin Student s on s.Sno=SC.Snowhere Semester=@semesand Sname=@namegroup by odeclare@c int,@a numeric(4,2)exec ti_4"李勇", 2,@c output,@a outputprint@cprint@a（5）查询指定学生（学号）的选课门数。

如果指定学生不存在，则返回代码1；如果指定的学生没有选课，则返回代码2；如果指定学生有选课，则返回代码0，并用输出参数返回该学生的选课门数。

create procedure ti_5@sno char(7),@Count int outputasif exists(select*from Student where Sno=@sno)beginif exists(select*from sc where sno=@sno)beginselect@Count=count(Cno)from Student left join sc on Student.Sno=sc.Snowhere student.Sno=@snoreturn 0endelsereturn 2endelsereturn 1（6）删除指定学生（学号）的修课记录，如果指定的学生不存在，则显示提示信息“没有指定学生”；如果指定的学生没有选课，则显示提示信息“该学生没有选课”。

学号为输入参数。

create procedure ti_6@sno char(7)asif@sno is not null and exists(select*from Studentwhere Sno=@sno)beginif exists(select*from SC where Sno=@sno)begindelete from SC where Sno=@snoendelseprint'该学生没有选课'endelseprint'没有指定学生'（7）修改指定课程的开课学期。

输入参数为：课程号和修改后的开课学期。

create procedure ti_7@cno char(6),@sem intasupdate Course setSemester=@semwhere Cno=@cno（8）在Course表中插入一行数据，课程号、课程名、学分、开课学期均为输入参数。