数据库技术应用及实验指导高等院校计算机教材系列第5章视图、存储过程和用户自定义函数•5.1视图•5.2存储过程•5.3用户自定义函数5.1视图•5.1.1视图概念•5.1.2定义视图•5.1.3删除视图•5.1.4视图的作用5.1.1视图概念•视图可以被看成是虚拟表。•视图中的数据不物理地存储在数据库内。•SELECT语句的结果集构成了视图的内容。基本表1基本表2视图5.1.2定义视图•语法:CREATEVIEW视图名[(视图列名表)]AS查询语句定义单源表视图•建立信息系学生的视图。CREATEVIEWIS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept='信息系'定义多源表视图•建立信息系选修了‘c01’号课程的学生的视图。CREATEVIEWV_IS_S1(Sno,Sname,Grade)ASSELECTStudent.Sno,Sname,SageFROMStudentJOINSCONStudent.Sno=SC.SnoWHERESdept='信息系'ANDSC.Cno='c01'在已有视图上定义新视图•建立信息系选修了‘c01’号课程且成绩在90分以上的学生的视图。CREATEVIEWV_IS_S2ASSELECTSno,Sname,GradeFROMV_IS_S1WHEREGrade=90定义带表达式的视图•定义一个反映学生出生年份的视图。CREATEVIEWBT_S(Sno,Sname,Sbirth)ASSELECTSno,Sname,2005-SageFROMStudent含分组统计信息的视图•定义一个存放每个学生的学号及平均成绩的视图。CREATEVIEWS_G(Sno,AverageGrade)ASSELECTSno,AVG(Grade)FROMSCGROUPBYSno5.1.3删除视图•格式:DROPVIEW视图名•例.删除前边定义的IS_Student视图。DROPVIEWIS_Student5.1.4视图的作用•简化数据查询语句•使用户能从多角度看到同一数据•提高了数据的安全性•提供了一定程度的逻辑独立性5.2存储过程•存储过程是SQL语句和控制流语句的预编译集合,它以一个名称存储并作为一个单元处理,应用程序可以通过调用的方法执行存储过程。•它使得对数据库的管理和操作更加容易、效率更高。5.2.1存储过程概念•SQL语言是应用程序和SQLServer数据库之间的主要编程接口。使用SQL语言编写代码时,可用两种方法存储和执行代码。•一种是在客户端存储代码,并创建向数据库管理系统发送SQL命令(或SQL语句)并处理返回结果的应用程序;•第二种是将这些发送的SQL语句存储在数据库管理系统中,这些存储在数据库管理系统中的SQL语句就是存储过程。•存储过程与其它程序设计语言中的过程很类似。使用存储过程的好处•允许模块化程序设计•改善性能•减少网络流量•提供了安全机制•简化管理和操作5.2.1创建和执行存储过程•创建存储过程:CREATEPROC[EDURE]存储过程名[{@参数名数据类型}[=default][OUTPUT]]ASSQL语句•执行存储过程:[EXEC[UTE]]存储过程名[实参[,OUTPUT][,…n]]创建不带参数的存储过程•查询计算机系学生的考试成绩,列出学生的姓名、课程名和成绩。CREATEPROCEDUREstudent_grade1ASSELECTSname,Cname,GradeFROMStudentsINNERJOINscONs.sno=sc.snoINNERJOINcoursecONc.cno=sc.cnoWHERESdept='计算机系'创建带输入参数的存储过程•查询某个指定系学生的考试情况,列出学生的姓名、所在系、课程名和考试成绩。CREATEPROCEDUREstudent_grade2@deptchar(20)ASSELECTSname,Sdept,Cname,GradeFROMStudentsINNERJOINscONs.sno=sc.snoINNERJOINcoursecONc.cno=sc.cnoWHERESdept=@dept创建带多个输入参数的存储过程•查询某个学生某门课程的考试成绩,列出学生的姓名、课程名和成绩。CREATEPROCEDUREstudent_grade2@student_namechar(10),@course_namechar(20)ASSELECTSname,Cname,GradeFROMStudentsJOINscONs.sno=sc.snoINNERJOINcoursecONc.cno=sc.cnoWHEREsname=@student_nameANDcname=@course_name参数的传递方式•按参数位置传递值EXECstudent_grade2'刘晨','VB'•按参数名传递值EXECStudent_grade2@student_name='刘晨',@course_name='VB'创建带多个输入参数并有默认值的存储过程•查询某个学生某门课程的考试成绩,若没有指定课程,则默认课程为“数据库基础”。CREATEPROCEDUREstudent_grade3@student_namechar(10),@course_namechar(20)='数据库基础'ASSELECTSname,Cname,GradeFROMStudentsJOINscONs.sno=sc.snoJOINcoursecONc.cno=sc.cnoWHEREsname=@student_nameANDcname=@course_name调用参数有默认值的存储过程EXECstudent_grade3'吴宾'•等价于执行:EXECstudent_grade3'吴宾','数据库基础'创建带有多个输入参数并均指定默认值的存储过程•查询指定系、指定性别的学生中年龄大于等于指定年龄的学生的情况。系的默认值为“计算机系”,性别的默认值为“男生”,年龄的默认值为20。CREATEPROCP_Student@deptchar(20)='计算机系',@sexchar(2)='男',@ageint=20ASSELECT*FROMStudentWHERESdept=@deptANDSsex=@sexANDSage=@age执行•执行1:不提供任何参数值。EXECP_Student•执行2:提供全部参数值。EXECP_Student'信息系','女',19•执行3:只提供第二个参数的值。EXECP_Student@sex='女‘•执行4:只提供第一个和第三个参数的值。EXECP_Student@sex='女',@age=19创建带有输出参数的存储过程•计算两个数的和。CREATEPROCEDUREsum@var1int,@var2int,@var3intoutputAsSet@var3=@var1*@var2•执行此存储过程:Declare@resintExecuteProc15,7,@resoutputPrint@res创建带输入参数和一个输出参数的存储过程•统计指定课程的平均成绩,并将统计的结果用输出参数返回。CREATEPROCEDUREAvgGrade@cnchar(20),@avg_gradeintoutputASSELECT@avg_grade=AVG(Grade)FROMSCJOINCourseCONC.Cno=SC.CnoWHERECname=@cn创建带输入参数和多个输出参数的存储过程•统计指定课程的平均成绩和选课人数,将统计的结果用输出参数返回。CREATEPROCEDUREAvg_Count@cnchar(20),@avg_gradeintoutput,@totalintoutputASSELECT@avg_grade=AVG(Grade),@total=COUNT(*)FROMSCJOINCourseCONC.Cno=SC.CnoWHERECname=@cn创建删除数据的存储过程•删除考试成绩不及格学生的修课记录。CREATEPROCEDUREp_DeleteSCASDELETEFROMscWHEREgrade60创建修改数据的存储过程•将指定课程的学分增加2分。CREATEPROCEDUREp_UpdateCredit@cnvarchar(20)ASUPDATEcourseSETcredit=credit+2WHEREcname=@cn5.3用户自定义函数•5.3.1函数概念•5.3.2创建和调用标量函数•5.3.3创建和定义内嵌表值函数•5.3.4创建和调用多语句表值函数•5.3.5更改和删除函数5.3.1函数概念•函数是由一个或多个SQL语句组成的子程序,它可用于封装代码以提供代码共享的功能。•在概念上类似于一般的程序设计语言中定义的函数。•SQLServer2000支持三种用户自定义函数:•标量函数•内嵌表值函数•多语句表值函数5.3.2创建和调用标量函数•定义标量函数CREATEFUNCTION[拥有者名.]函数名([{@参数名[AS]标量数据类型[=default]}[,...n]])RETURNS返回值类型[AS]BEGIN函数体RETURN标量表达式END创建示例•例1.创建计算立方体的体积的函数。三个输入参数,分别为立方体的长、宽和高,类型均为整型。CREATEFUNCTIONdbo.CubicVolume(@CubeLengthint,@CubeWidthint,@CubeHeightint)RETURNSintASBEGINRETURN(@CubeLength*@CubeWidth*@CubeHeight)END创建示例•例2.创建统计指定课程的选课人数的函数。CREATEFUNCTIONdbo.f_count(@cnamevarchar(20))RETURNSintASBEGINDECLARE@xintSELECT@x=count(*)fromcoursecjoinsconsc.cno=c.cnoWHEREcname=@cnameRETURN@xEND调用标量函数•调用例1所定义的函数,计算长、宽、高分别为4、6、8的立方体的体积。SELECTdbo.CubicVolume(4,6,8)•调用例2所定义的函数,查询“VB”课程的选课人数。SELECTcnameas课程名,dbo.f_count('VB')as选课人数FROMcourseWHEREcname='VB'5.3.3创建和定义内嵌表值函数•定义内嵌表值函数:CREATEFUNCTION[拥有者名.]函数名([{@参数名[AS]标量数据类型[=default]}[,...n]])RETURNSTABLE[AS]RETURN[(]select语句[)]创建示例•例3.创建查询指定系的学生的姓名、年龄和性别的函数。CREATEFUNCTIONdbo.f_sdept(@deptvarchar(20))RETURNSTABLEASRETURN(SELECTsname,sage,ssexfromstudentWHEREsdept=@dept)创建示例•例4.创建查询指定课程中成绩大于指定分数的学生的姓名、所在系和这门课程的考试成绩。CREATEFUNCTIONdbo.f_grade(@cnamevarchar(20),@gradeint)RETURNSTABLEASRETURN(SELECTsname,sdept,gradefromstudentsJOINscONs.sno=sc.snoJOINcoursecONc.cno=sc.cnoWHEREcname=@cnameandgrade@grade)调用内嵌表值函数•利用例3定义的内嵌表值函数,查询“计算机系”学生的信息。SELECT*FROMdbo.f_sdept('计算机系')•利用例4所定义的内嵌表值函数,查询“VB”课程成绩大于70分的学生信息。SE