存储过程与函数

数据库技术与应用袁宝库yuanbaoku@视图、存储过程和用户自定义函数视图存储过程用户自定义函数视图视图概念定义视图删除视图视图的作用

视图概念视图可以被看成是虚拟表。视图中的数据不物理地存储在数据库内。SELECT语句的结果集构成了视图的内容。基本表1基本表2视图定义视图语法：

CREATEVIEW<视图名>[（视图列名表）] AS

查询语句定义单源表视图建立信息系学生的视图。

CREATEVIEWIS_Student AS SELECTSno,Sname,Sage FROMStudentWHERESdept='信息系'定义多源表视图建立信息系选修了‘c01’号课程的学生的视图CREATEVIEWV_IS_S1(Sno,Sname,Grade)ASSELECT Student.Sno,Sname,GradeFROM StudentJOINSCON Student.Sno=SC.SnoWHERE Sdept='信息系' ANDSC.Cno='c01'在已有视图上定义新视图建立信息系选修了‘c01’号课程且成绩在90分以上的学生的视图。CREATEVIEWV_IS_S2AS SELECTSno,Sname,GradeFROMV_IS_S1 WHEREGrade>=90定义带表达式的视图定义一个反映学生出生年份的视图。

CREATEVIEWBT_S (Sno,Sname,Sbirth) ASSELECT Sno,Sname,2012-SageFROM Student含分组统计信息的视图定义一个存放每个学生的学号及平均成绩的视图。

CREATEVIEWS_G(Sno,AverageGrade) ASSELECT Sno,AVG(Grade)FROM SC GROUPBY Sno删除视图格式：DROPVIEW<视图名>例．删除前边定义的IS_Student视图。

DROPVIEWIS_Student视图的作用简化数据查询语句使用户能从多角度看到同一数据提高了数据的安全性提供了一定程度的逻辑独立性存储过程存储过程是SQL语句和控制流语句的预编译集合，它以一个名称存储并作为一个单元处理，应用程序可以通过调用的方法执行存储过程。它使得对数据库的管理和操作更加容易、效率更高。存储过程概念SQL语言是应用程序和SQLServer数据库之间的主要编程接口。使用SQL语言编写代码时，可用两种方法存储和执行代码。一种是在客户端存储代码，并创建向数据库管理系统发送SQL命令（或SQL语句）并处理返回结果的应用程序；第二种是将这些发送的SQL语句存储在数据库管理系统中，这些存储在数据库管理系统中的SQL语句就是存储过程。

存储过程与其它程序设计语言中的过程很类似。使用存储过程的好处允许模块化程序设计改善性能减少网络流量提供了安全机制简化管理和操作创建和执行存储过程创建存储过程：CREATEPROC[EDURE]存储过程名

[{@参数名数据类型}[=default][OUTPUT]]ASSQL语句执行存储过程：

[EXEC[UTE]]存储过程名

[实参[,OUTPUT][,…n]]创建不带参数的存储过程查询计算机系学生的考试成绩，列出学生的姓名、课程名和成绩。CREATEPROCEDUREstudent_grade1ASSELECTSname,Cname,GradeFROMStudentsINNERJOINscONs.sno=sc.snoINNERJOINcoursecONo=oWHERESdept='计算机系'创建带输入参数的存储过程查询某个指定系学生的考试情况，列出学生的姓名、所在系、课程名和考试成绩。CREATEPROCEDUREstudent_grade2 @deptchar(20)ASSELECTSname,Sdept,Cname,GradeFROMStudentsINNERJOINscONs.sno=sc.snoINNERJOINcoursecONo=oWHERESdept=@dept创建带多个输入参数的存储过程查询某个学生某门课程的考试成绩，列出学生的姓名、课程名和成绩。CREATEPROCEDUREstudent_grade2@student_namechar(10),@course_namechar(20)ASSELECTSname,Cname,GradeFROMStudentsJOINscONs.sno=sc.snoINNERJOINcoursecONo=oWHEREsname=@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.snoJOINcoursecONo=oWHEREsname=@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创建带有输出参数的存储过程计算两个数的积。CREATEPROCEDUREProc1@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_DeleteSCASDELETEFROMscWHEREgrade<60创建修改数据的存储过程将指定课程的学分增加2分。CREATEPROCEDUREp_UpdateCredit@cnvarchar(20)ASUPDATEcourseSETcredit=credit+2WHEREcname=@cn用户自定义函数函数概念创建和调用标量函数创建和定义内嵌表值函数创建和调用多语句表值函数更改和删除函数函数概念函数是由一个或多个SQL语句组成的子程序，它可用于封装代码以提供代码共享的功能。在概念上类似于一般的程序设计语言中定义的函数。SQLServer支持三种用户自定义函数：

标量函数内嵌表值函数多语句表值函数

创建和调用标量函数定义标量函数CREATEFUNCTION[拥有者名.]函数名

([{@参数名[AS]标量数据类型[=default]}[,...n]])RETURNS返回值类型

[AS]BEGIN

函数体

RETURN标量表达式

END创建示例创建计算立方体的体积的函数。三个输入参数，分别为立方体的长、宽和高，类型均为整型。CREATEFUNCTIONdbo.CubicVolume(@CubeLengthint,@CubeWidthint,@CubeHeightint)RETURNSintASBEGINRETURN(@CubeLength*@CubeWidth*@CubeHeight)END创建示例创建统计指定课程的选课人数的函数。CREATEFUNCTIONf_count(@cnamevarchar(20))RETURNSintASBEGINDECLARE@xintSELECT@x=count(*)fromcoursecjoinscono=oWHEREcname=@cnameRETURN@xEND调用标量函数

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

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

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

([{@参数名[AS]标量数据类型[=default]}[,...n]])RETURNSTABLE[AS]RETURN[(]select语句[)]创建示例创建查询指定系的学生的姓名、年龄和性别的函数。CREATEFUNCTIONdbo.f_sdept(@deptvarchar(20))RETURNSTABLEASRETURN(SELECTsname,sage,ssexfromstudentWHEREsdept=@dept)创建示例创建查询指定课程中成绩大于指定分数的学生的姓名、所在系和这门课程的考试成绩。CREATEFUNCTIONdbo.f_grade(@cnamevarchar(20),@gradeint)RETURNSTABLEASRETURN(SELECTsname,sdept,gradefromstudentsJOINscONs.sno=sc.snoJOINcoursecONo=oWHEREcname=@cnameandgrade>@grade)调用内嵌表值函数

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

利用前边所定义的内嵌表值函数，查询“VB”课程成绩大于70分的学生信息。SELECT*FROMdbo.f_grade('VB',70)创建和调用多语句表值函数

定义多语句表值函数：CREATEFUNCTION[拥有者名.]函数名

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

函数体

RETURNEND

<表定义>：：=({列定义|表约束}[,...n])创建示例创建返回考试成绩为指定层次的学生的学号、姓名、课程名和考试成绩的多语句表值函数，如果指定的层次为“优秀”，则返回的表中的内容为成绩大于等于90的学生的信息；如果指定的层次为“非优秀”，则返回的表中的内容为成绩小于90的学生的信息。创建示例CREATEFUNCTIONdbo.f_GradeLevel(@levelchar(6))RETURNS@GradeLevelTABLE(snochar(7),snamechar(8),cnamevarchar(20),gradeint)ASBEGINIF@level='优秀‘INSERTINTO@GradeLevelSELECTs.sno,sname,cname,gradeFROMstudentsJOINscONs.sno=sc.snoJOINcoursecONo=oWHEREgrade>=