数据库第三部分.doc

. . . .

实 验 报 告

课程名称 数据库技术实践
实验项目索引、存储过程和触发器、函数和游标
实验仪器 SQL Server 2008

系 别____计算机科学与技术系 _
专 业____计算机科学与技术____
班级/学号___xxxxxxxxxxxxxxxxxxxxxxxx
学生 _____xxxxxxxxxxx
实验日期 ___2xxxxxxxxxx
成 绩 _______________________
指导教师 ___ 梁琦 ______________

一． 容说明
1.索引
涉与索引类型，与不建立索引形成比照，可以提高执行效率
2.存储过程
存储过程是SQL语言在应用程序和数据库之间的主要编程接口，其存在形式有两种，一是在客户端存储代码，另一种是将SQL语言存储在数据库服务器端，由应用程序调用这些语言。
存储过程可以：承受输入参数并以输出参数的形式将多个数值返回、包含执行数据库的语句、将查询语句执行结果返回到客户端，其好处有，允许模块化程序设计、改善性能、减少网络流量、可作为安全机制使用。
3.触发器
是有一段由对数据的更改操作引发的自动执行的代码，通常用于保证业务规那么和数据完整性，用户可以编程的方法来实现复杂的处理逻辑和业务规那么，增强了完整性约束的功能。
4.用户定义函数
可以扩展数据操作的功能，期中包括，变量函数和表值函数，标量函数返回单个数据值，表值函数返回一个表，表值函数又分为联表值函数和语句表值函数。
5.游标
游标提供对结果集中的每一行或一局部行进展单独的处理，这在select无法实现的，其具有如下特点：允许定位结构集中的特定行、允许从结果集的当前位置检索一行或多行、支持对结果集中当前行的数据进展修改、为由其他用户对显示在结果集中的数据所做的更改提供不同级别的可见性支持。

二.索引
本章上机练习均利用MySimpleDB数据库实现。写出实现创建满足如下要求的索引的SQL语句，并执行这些语句。
1． 请为以下查询设计一个最适宜的索引，并查看建立索引前后该语句的执行计划，比拟执行效率。
SELECT FirstName,LastName, EmailAddress,Phone
FROM Person.Contact
WHERE Phone BETWEEN '300' AND '350'
CREATENONCLUSTEREDINDEXIX_Person_Contact
ONPerson.Contact(Phone)
INCLUDE(FirstName,LastName,EmailAddress);

建立索引之前： 建立索引后：

2． Production.ProductReview 表包含的列有：ProductID〔int〕、ReviewerName〔nvarchar(50) 〕和 Comments〔nvarchar (3850)〕。假设经常执行以下形式的查询，请为该类查询创建适宜的索引，以最大程度地提高查询效率。
SELECT Comments,ReviewerName
FROM Production.ProductReview
WHERE ProductID >= 937 and ReviewerName like '[a-d]%';
createindexpidxonProduction.ProductReview(ProductID)
include(Comments,ReviewerName)WHEREProductID>= 937

建立索引前： 建立索引后：

3．在Person.Address表上创建具有一个键列〔PostalCode〕和四个非键列〔AddressLine1、AddressLine2、City、StateProvinceID〕的包含列索引。查看索引建立前后以下查询语句的执行计划，观察索引对效率的提高情况。
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'94000' and N'95999';
CREATENONCLUSTEREDINDEXIX_Address_PostalCode
ONPerson.Address(PostalCode)
INCLUDE(AddressLine1,AddressLine2,City,StateProvinceID);

索引使该查询速度变快了

建立索引前： 建立索引后：

4.设经常需要执行以下类型的查询，以统计2003年某一段时间各产品的销售总量。
SELECT ProductID, SUM(sod.OrderQty) AS QtySold
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE 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） 查询每个学生的修课总学分，要求列出学生学号与总学分。
CREATEPROCEDURESELECT_STUDENT
AS
SELECTSno,sum(Credit)as总学分
FROMscjoincourseconco=sco
GroupbySno

EXECSELECT_STUDENT

（2） 查询学生的学号、、修的课程号、课程名、课程学分，将学生所在系作为输入参数，默认值为“计算机系〞。执行此存储过程，并分别指定一些不同的输入参数值，查看执行结果。
CREATEPROCinformation
deptCHAR(20)='计算机系'
AS
SELECTsc$.sno,sname,sc$o,cname,creditFROMstudent$sinnerjoinsc$ON
s.sno=sc$.snoinnerjoincourse$cONco=sc$o
WHEREdept=dept

EXECinformation'信息管理系'

EXECSELECT_STUDENT1'通信工程系',null

（3） 查询指定系的男生人数，其中系为输入参数，人数为输出参数。
CREATEPROCEDURESELECT_STUDENT2
t_Deptnvarchar(20),total_manintOUTPUT
AS
SELECTtotal_man=COUNT(*)FROMstudent
WHEREDept=t_DeptANDSex='男'
PRINTtotal_man

DECLARERE_manint--声明变量
EXECSELECT_STUDENT2'计算机系',RE_manOUTPUT
--PRINT RE_man

（4） 查询指定学生〔〕在指定学期的选课门数和考试平均成绩，要求和学期为输入参数，选课门数和平均成绩用输出参数返回，平均成绩保存到小时点后2位。
createprocedureti_4
namechar(10),semesint,
countCnointoutput,
avgGnumeric(4,2)output
as
selectcountCno=COUNT(*),avgG=avg(Grade*1.00)from
SCjoinCourseconSCo=co
joinStudentsons.Sno=SC.Sno
whereSemester=semes
andSname=name
groupbySCo

declarecint,anumeric(4,2)
execti_4"勇", 2,coutput,aoutput
printc
printa

（5） 查询指定学生〔学号〕的选课门数。如果指定学生不存在，那么返回代码1；如果指定的学生没有选课，那么返回代码2；如果指定学生有选课，那么返回代码0，并用输出参数返回该学生的选课门数。
createprocedureti_5
snochar(7),
Countintoutput
as
ifexists(select*fromStudentwhereSno=sno)
begin
ifexists(select*fromscwheresno=sno)
begin
selectCount=count(Cno)
fromStudentleftjoinsconStudent.Sno=sc.Sno
wherestudent.Sno=sno
return 0
end
else
return 2
end
else
return 1
（6） 删除指定学生〔学号〕的修课记录，如果指定的学生不存在，那么显示提示信息“没有指定学生〞；如果指定的学生没有选课，那么显示提示信息“该学生没有选课〞。学号为输入参数。
createprocedureti_6
snochar(7)
as
ifsnoisnotnullandexists(select*fromStudentwhereSno=sno)
begin
ifexists(select*fromSCwhereSno=sno)
begin
deletefromSCwhereSno=sno
end
else
print'该学生没有选课'
end
else
print'没有指定学生'

（7） 修改指定课程的开课学期。输入参数为：课程号和修改后的开课学期。
createprocedureti_7
cnochar(6),
semint
as
updateCourseset
Semester=sem
whereCno=cno

（8） 在Course表中插入一行数据，课程号、课程名、学分、开课学期均为输入参数。课程号为C100、课程名为操作系统、学分为4、开课学期为4，开课学期的默认值为3。如果学分大于10或者小于1，那么不插入数据，并显示提示信息“学分为1~10间的整数〞。
createprocedureti_8
cnochar(6),
cnamevarchar(20),
credittinyint,
semtinyint
as
ifcreditbetween1and10
insertintoCoursevalues(cno,cname,credit,sem)
else
print'学分为1~10间的整数'

2． 创建满足下述要求的DML触发器〔前触发器、后触发器均可〕，并验证触发器执行情况。
（1） 限制学生所在系的取值围为{计算机系，信息管理系，数学系，通信工程系}
createtriggertri_dept
onStudentafterinsert,update
as
ifexists(select*frominserted
whereSdeptnotin('计算机系','信息管理系','数学系','通信工程系'))
rollback
go

（2） 限制每个学期开设的课程总学分在20~30围。
createTRIGGEReve_total_credit
ONcourseAFTERINSERT
AS
declaretint
SELECTt=SUM(C.Credit)FROMCoursec
JOININSERTEDIONI.Semester=c.Semester
IF (t<20 ort>30)
BEGIN
PRINT'本学期课程学分限制在--30之间！'
ROLLBACK
END

插入正常的数据：

插入超限的数据提醒：

（3） 限制每个学生每学期选课门数不能超过6门〔设只针对单行插入操作〕。
CREATETRIGGEReve_total_cno
ONcourseAFTERINSERT
AS
declaretint
SELECTt=COUNT(*)FROMCoursec
JOININSERTEDIONI.Semester=c.Semester
JOINSCSONSo=Co
IF (t<0 ort>6)
BEGIN
PRINT'本学期选课门数不能超过6门！'
ROLLBACK
END

（4） 限制不能删除有人选的课程。
createtriggertri_delCno
oncourseafterdelete
as
ifexists(select*fromdeleteddwheredoin(selectCnofromsc))
rollback
go

（5） 利用10.2.2例6建立的Teachers表和Depts表，编写实现如下要求的触发器：每当在Teachers表中修改了某个教师的职称时，自动维护Depts表中职称人数统计的一致性。〔考虑同时修改多名教师职称的情况〕
（6） 利用10.2.2例6建立的Teachers表和Depts表，首先为Depts表增加一个记录部门教师人数的列，列名为：DeptCount，类型为整型。然后编写实现如下要求的触发器：每当在Teachers表中插入一行数据或者是删除一行数据时，自动维护Depts表中的相关信息。

四．函数和游标
如无特别说明，以下各题均利用第6章建立的Student、Course和SC表实现。
3． 创建满足下述要求的用户自定义标量函数。
（1） 查询指定学生已经得到的修课总学分〔考试与格的课程才能拿到学分〕，学号为输入参数，总学分为函数返回结果。并写出利用此函数查询0811101学生的、所修的课程名、课程学分、考试成绩以与拿到的总学分的SQL语句。
CREATEFUNCTIONDBO.FIND_XF(SNOCHAR(7))
RETURNSINT
AS
BEGIN
DECLAREXINT
SELECTX=SUM(CREDIT)FROMSTUDENTSJOINSC
ONS.SNO=SC.SNOJOINCOURSECONSCO=CO
wheregrade>=60 ands.sno=SNO
RETURNX
END

SELECTsnameAS,cnameAS课程名,
creditAS课程学分,gradeAS考试成绩,
dbo.find_xf(s.sno)as总学分
fromSTUDENTSJOINSCON
S.SNO=SC.SNOJOINCOURSECONSCO=CO
wheres.sno=0811101

（2） 查询指定系在指定课程(课程号)的考试平均成绩。
CREATEFUNCTIONDBO.FIND_AVG(DEPTCHAR(20),CNOCHAR(6))
RETURNSnumeric(4,2)
AS
BEGIN
DECLAREAVGnumeric(4,2)
SELECTAVG=AVG(GRADE)FROMSCJOIN
studentsonsc.sno=s.sno
joincourseconsco=co
wheresco=cnoanddept=dept
returnAVG
End

selectS.dept,Sco,
dbo.FIND_AVG(S.dept,sco)ASavg_GRADE
fromscjoinstudentsonsc.sno=s.sno
wheredept='信息管理系'

（3） 查询指定系的男生中选课门数超过指定门数的学生人数。
createFUNCTIONDBO.FIND_man(DEPTCHAR(20),CNOint)
returnsint
AS
BEGIN
DECLAREcumint
selectcum=COUNT(*)fromstudentsjoin
scons.sno=sc.sno
wheredept=deptandsex='男'
groupbys.SnohavingCOUNT(*)>cno
returncum
End

selectsname,dept,cname,DBO.FIND_man(dept, 1)as学生人数
fromstudentsjoinscons.sno=sc.sno
joincourseconco=sco
wheredept='计算机系'

4． 创建满足下述要求的用户自定义联表值函数。
（1） 查询选课门数在指定围的学生的、所在系和所选的课程名。
CREATEfunctionfind_1(xint)
returnstable
return (selectsname,dept,cname
fromstudentsjoinscons.sno=sc.sno
joincourseconco=sco
wheres.snoin(
selectsnofromSCgroupbySnohavingCOUNT(*)>x))

--利用函数查询选课门数超过门的情况
select*fromfind_1(3)

（2） 查询指定系的学生考试成绩大于等于90的学生的、所在系、课程名和考试成绩。并写出利用此函数查询计算机系学生考试情况的SQL语句，只列出学生、课程名和考试成绩。
createfunctionfind_2(xchar(20))
returnstable
return (SELECTsname,dept,cname,gradeFROMstudentASs
LEFTJOINscONs.sno=sc.sno
LEFTJOINcourseAScONco=sco
WHEREgrade>= 90 ands.dept=x)

selectsname,cname,gradefromfind_2('计算机系')

5． 创建满足下述要求的用户自定义多语句表值函数。
（1） 查询指定系年龄最大的前2名学生的和年龄，包括并列的情况。
alterFUNCTIONDBO.FIND_AGE(DEPTCHAR(20))
RETURNSRET_FIND_AGETABLE(
SNAMECHAR(10),
AGEINT)
AS
BEGIN
INSERTINTORET_FIND_AGE
SELECTTOP 2 WITHtiessname,YEAR(GETDATE())-YEAR(BirTHDAY)AGE
FROMSTUDENTWHEREDEPT=DEPT
ORDERBYAGEDESC
RETURN
END

SELECTSNAME,AGEfromFIND_AGE('计算机系')

（2） 查询指定学生〔〕的考试情况，列出、所在系、修的课程名和考试情况，其中考试情况列的取值为：如果成绩大于等于90，那么为“优〞；如果成绩在80~89，那么为“良好〞；如果成绩在70~79，那么为“一般〞；如果成绩在60~69，那么为“不太好〞；如果成绩小于60，那么为“很糟糕〞。并写出利用此函数查询勇的考试情况的SQL语句。
createfunctionfind_3(xnchar(5))
returnstable
return (SELECTsname,dept,cname,grade,
case
whengrade>=90 then'优'
whengradebetween 80 and 89 then'良'
whengradebetween 70 and 79 then'一般'
whengradebetween 60 and 69 then'不太好'
whengrade< 60 then'很糟糕'
ENDAS'考试情况'
FROMstudentASs
LEFTJOINscONs.sno=sc.sno
LEFTJOINcourseAScONco=sco
WHERESNAME=x)

SELECT*FROMFIND_3('勇')

6． 创建满足下述要求的游标。
（1） 查询Java课程的考试情况，并按图11-18所示样式显示结果数据。
declarecnamevarchar(20),cnochar(8),snamenchar(5),sexnchar(1),
deptnvarchar(20),gradesmallint
--〔a)声明游标
declarecur_cnocursorfor
selectdistinctamefromCourse
whereoin(selectofromscwheregradeisnotnull
andame='VB')
opencur_cno
--(3)取数据
fetchnextfromcur_cnointocname
whileFETCH_STATUS= 0
begin
print'课程名:'+cname
print' 性别所在系 成绩'
-----------------------------
--(2)对每一门课程，查询选了这门课程的学生(高等数学):用游标实现
--〔a〕声明游标
declarecur_scursorfor
selectsname,sex,dept,gradefromstudentsjoinSCons.Sno=SC.Sno
joincourseconco=sco
whereame=cname
--〔b〕打开游标
opencur_s
--取数据处理
fetchnextfromcur_sintosname,sex,dept,grade
whileFETCH_STATUS= 0
begin
ifgradeisnotnull
printsname+sex+' '+dept+' '+cast(gradeaschar(4))
else
printsname+sex+' '+dept+' null'
fetchnextfromcur_sintosname,sex,dept,grade
end
closecur_s
deallocatecur_s
FETCHNEXTFROMcur_cnoINTOcname
end
closecur_cno
deallocatecur_cno

图11-18 游标〔1〕的显示样式
图11-19 游标〔2〕的显示样式
（2） 统计每个系的男生人数和女生人数，并按图11-19所示样式显示结果数据。
DECLAREcountsINT,deptNVARCHAR(14),sexCHAR(10)
DECLAREc2CURSORFORSELECTdept,sex,count(*)FROMstudent$
GROUPBYsex,dept
OPENc2
FETCHNEXTFROMc2INTOdept,sex,counts
PRINT'系名 性别 人数'
PRINT'==============================='
whilefetch_status=0
begin
PRINTdept+' '+sex+cast(countsASCHAR(4))
FETCHNEXTFROMc2INTOdept,sex,counts
END
CLOSEc2
DEALLOCATEc2

（3） 列出每个系的学生信息，要求首先列出一个系的系名，然后在该系名以下出本系学生的和性别；再列出下一个系名，然后在此系名下再列出该系学生和性别；以此类推，直至列出全部系。要求按图11-20所示样式显示结果数据。
DECLAREsnameCHAR(10),deptCHAR(14)
DECLAREc3CURSORFORSELECTDISTINCTdeptFROMstudent$
OPENc3
FETCHNEXTFROMc3intodept
WHILEfetch_status=0
BEGIN
PRINTdept
DECLAREc4CURSORFORSELECTsname,deptFROMstudent$
WHEREdept=dept
OPENc4
FETCHNEXTFROMc4INTOsname,dept
WHILEfetch_status=0
BEGIN
PRINTsname+dept
FETCHNEXTFROMc4INTOsname,dept
END
CLOSEc4
DEALLOCATEc4
PRINT'====================='
FETCHNEXTFROMc3INTOdept
END
CLOSEc3
DEALLOCATEc3

图11-20 游标〔3〕的显示样式
〔4〕针对11.2.3中例4建立的Job表和数据,用游标对Job表数据进展如下修改：将工作级别一样的工作只保存工作编号较小的一项工作，同时，将这些工作的工作描述拼接为一个工作描述，中间用逗号分隔。修改后的数据示意如表11-3所示。
表11-3 改造后的Job表数据
Jobid
Desc
Level
J01
软件开发，软件测试
10
J02
硬件开发，硬件测试
12
J04
硬件维护
8
DECLAREcur_JobCURSORFORSELECT*FROMJob;
DECLAREojdescVARCHAR(40)
DECLAREjobnoCHAR(4),jdescVARCHAR(40),levelTINYINT
OPENcur_Job
FETCHNEXTFROMcur_JobINTOjobno,jdesc,level
WHILEFETCH_STATUS= 0
BEGIN
SETojdesc=jdesc
DECLAREinjobnoCHAR(4),injdescVARCHAR(40)
DECLAREincur_JobCURSORFORSELECTjobid,decsFROMJobWHERElvl=levelandjobid!=jobno
OPENincur_Job
FETCHNEXTFROMincur_JobINTOinjobno,injdesc
WHILEFETCH_STATUS= 0
BEGIN
SETojdesc=ojdesc+','+injdesc
-- print ojdesc
DELETEFROMJobWHEREJobid=injobno
FETCHNEXTFROMincur_JobINTOinjobno,injdesc
END
PRINTojdesc
CLOSEincur_Job
DEALLOCATEincur_Job
UPDATEJobSETdecs=ojdescWHEREJobid=jobno
SETojdesc=''
FETCHNEXTFROMcur_JobINTOjobno,jdesc,level
END
CLOSEcur_Job
DEALLOCATEcur_Job

〔5〕针对11.2.3中例4建立的Orders表和数据，实现按日统计每日的累计订单总额，处理后结果如图11-21所示。

图11-21 具有日累计订单金额信息的数据
declaresumdecimal(18,2),orderamtdecimal(18,2),orderdatedate,orderdate_olddate
declarecur_sumcursorfor
selectorderamtfrom[order]
declarecur_datecursorfor
selectorderdatefrom[order]
opencur_sum
opencur_date
fetchnextfromcur_sumintoorderamt
fetchnextfromcur_dateintoorderdate
setsum=0
whileFETCH_STATUS=0
begin
if (orderdate!=orderdate_old)setsum=0
setorderdate_old=orderdate
setsum=sum+orderamt
update[order]set[runningtotal]=sum
wherecurrentofcur_sum
fetchnextfromcur_sumintoorderamt
fetchnextfromcur_dateintoorderdate
end
closecur_sum
closecur_date
deallocatecur_sum
deallocatecur_date
select*from[order];

五. 实验总结
本次试验学习了索引，存储过程，存储过程、触发器、用户自定义函数与游标。
通过对索引的学习，知道了索引的作用是加快数据的查询效率。因为建立索引可以改变数据的搜索结构，索引多数情况下是建立在根本表上的，但可以建立在视图上，经过与不建立索引比照，其效率提高不少。
其中存储过程类似于C语言中的函数，把查询语句封装成一个函数，可以传如变量，但是注意的是，变量只能存一个数值，只能用循环方法显示变量，使其显示一组数据。存储过程保存在数据库中，可以随时修改，并加密。
对于触发器，具有不同触发性，要分清时候用前触发还是后触发，一般与判断语句结合，判断是否回滚。
用户自定义函数有两种，一个是标量函数，一个是联表值函数，感觉联表值函数很好用，可以直接打印出多组数据，相应标量函数输出结果比拟单一。
编写游标应注意前后游标的开关，移动，相对来说嵌套游标复杂些，应找好对应关系。
虽然写报告过程中遇到好多问题，还好与时与同学共同讨论并加以解决。
25 / 25