第5章视图存储过程与函数

数据库技术应用及实验指导高等院校计算机教材系列第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_Student AS SELECTSno,Sname,Sage FROMStudentWHERESdept='信息系'定义多源表视图

建立信息系选修了‘c01’号课程的学生的视图。CREATEVIEWV_IS_S1(Sno,Sname,Grade)AS SELECTStudent.Sno,Sname,SageFROMStudentJOINSCONStudent.Sno=SC.SnoWHERESdept='信息系'ANDSCo='c01'在已有视图上定义新视图

建立信息系选修了‘c01’号课程且成绩在90分以上的学生的视图。CREATEVIEW

V_IS_S2AS SELECTSno,Sname,GradeFROMV_IS_S1

WHEREGrade>=90

定义带表达式的视图

定义一个反映学生出生年份的视图。

CREATEVIEW

BT_S(Sno,Sname,Sbirth) ASSELECTSno,Sname,2005-SageFROMStudent

含分组统计信息的视图

定义一个存放每个学生的学号及平均成绩的视图。

CREATEVIEWS_G(Sno,erageGrade) ASSELECTSno,G(Grade)FROMSC GROUPBYSno5.1.3删除视图格式：DROPVIEW<视图名>例．删除前边定义的IS_Student视图。

DROPVIEWIS_Student

5.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,ame,GradeFROMStudentsINNERJOINscONs.sno=sc.snoINNERJOINcoursecONco=scoWHERESdept='计算机系'

创建带输入参数的存储过程

查询某个指定系学生的考试情况，列出学生的姓名、所在系、课程名和考试成绩。

CREATEPROCEDUREstudent_grade2 @deptchar(20)ASSELECTSname,Sdept,ame,GradeFROMStudentsINNERJOINscONs.sno=sc.snoINNERJOINcoursecONco=scoWHERESdept=@dept

创建带多个输入参数的存储过程

查询某个学生某门课程的考试成绩，列出学生的姓名、课程名和成绩。CREATEPROCEDUREstudent_grade2@student_namechar(10),@course_namechar(20)ASSELECTSname,ame,GradeFROMStudentsJOINscONs.sno=sc.snoINNERJOINcoursecONco=scoWHEREsname=@student_nameANDame=@course_name参数的传递方式

按参数位置传递值

EXECstudent_grade2'','VB'按参数名传递值

EXECStudent_grade2@student_name='',@course_name='VB'创建带多个输入参数并有默认值的存储过程

查询某个学生某门课程的考试成绩，若没有指定课程，则默认课程为“数据库基础”。CREATEPROCEDUREstudent_grade3@student_namechar(10),@course_namechar(20)='数据库基础'ASSELECTSname,ame,GradeFROMStudentsJOINscONs.sno=sc.snoJOINcoursecONco=scoWHEREsname=@student_nameANDame=@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@resintExecuteProc1outputPrint@res

创建带输入参数和一个输出参数的存储过程

统计指定课程的平均成绩，并将统计的结果用输出参数返回。CREATEPROCEDUREgGradechar(20),@g_gradeintoutputASSELECT@g_grade=G(Grade)FROMSCJOINCourseCONCo=SCoWHEREame=创建带输入参数和多个输出参数的存储过程

统计指定课程的平均成绩和选课人数，将统计的结果用输出参数返回。CREATEPROCEDUREg_Countchar(20),@g_gradeintoutput,@totalintoutputASSELECT@g_grade=G(Grade),@total=COUNT(*)FROMSCJOINCourseCONCo=SCoWHEREame=

创建删除数据的存储过程

删除考试成绩不及格学生的修课记录。CREATEPROCEDUREp_DeleteSCASDELETEFROMscWHEREgrade<60

创建修改数据的存储过程

将指定课程的学分增加2分。CREATEPROCEDUREp_UpdateCreditvarchar(20)ASUPDATEcourseSETcredit=credit+2WHEREame=5.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.CubicVolumeint,@CubeWidthint,@CubeHeightint)RETURNSintASBEGINRETURN(@CubeLength*@CubeWidth*@CubeHeight)END创建示例例2．创建统计指定课程的选课人数的函数。CREATEFUNCTIONdbo.f_count(amevarchar(20))RETURNSintASBEGINDECLARE@xintSELECT@x=count(*)fromcoursecjoinsconsco=coWHEREame=ameRETURN@xEND调用标量函数

调用例1所定义的函数，计算长、宽、高分别为4、6、8的立方体的体积。

SELECT

dbo.CubicVolume(4,6,8)调用例2所定义的函数，查询“VB”课程的选课人数。SELECTameas课程名,dbo.f_count('VB')as选课人数

FROMcourseWHEREame='VB'5.3.3创建和定义内嵌表值函数定义内嵌表值函数：CREATEFUNCTION[拥有者名.]函数名

([{@参数名[AS]标量数据类型[=default]}[,...n]])RETURNSTABLE[AS]RETURN[(]select语句[)]

创建示例例3．创建查询指定系的学生的姓名、年龄和性别的函数。CREATEFUNCTIONdbo.f_sdeptvarchar(20))RETURNSTABLEASRETURN(SELECTsname,sage,ssexfromstudentWHEREsdept=@dept)创建示例例4．创建查询指定课程中成绩大于指定分数的学生的姓名、所在系和这门课程的考试成绩。CREATEFUNCTIONdbo.f_grade(amevarchar(20),@gradeint)RETURNSTABLEASRETURN(SELECTsname,sdept,gradefromstudentsJOINscONs.sno=sc.snoJOINcoursecONco=scoWHEREame=ameandgrade>@grade)

调用内嵌表值函数

利用例3定义的内嵌表值函数，查询“计算机系”学生的信息。SELECT*FROMdbo.f_sdept('计算机系')

利用例4所定义的内嵌表值函数，查询“VB”课程成绩大于70分的学生信息。SELECT*FROMdbo.f_grade('VB',70)

5.3.4创建和调用多语句表值函数定义多语句表值函数：CREATEFUNCTION[拥有者名.]函数名

([{@参数名[AS]标量数据类型[=default]}[,...n]])RETURNS返回变量TABLE<表定义>[AS]BEGIN

函数体

RETURNEND<表定义>：：=({列定义|表约束}[,...n])创建示例例5．创建返回考试成绩为指定层次的学生的学号、姓名、课程名和考试成绩的多语句表值函数，如果指定的层次为“优秀”，则返回的表中的内容为成绩大于等于90的学生的信息；如果指定的层次为“非优秀”，则返回的表中的内容为成绩小于90的学生的信息。CREATEFUNCTIONchar(6))RETURNS@GradeLevelTABLE(snochar(7),snamechar(8),amevarchar(20),gradeint)AS示例（续）BEGINIF@level='优秀'INSERTINTO@GradeLevelSELECTs.sno,snameame,gradeFROMstudentsJOINscONs.sno=sc.snoJOINcoursecONco=scoWHEREgrade>=90ELSEIF