SQL-Server-2012数据库开发教程第8章.ppt

《SQL-Server-2012数据库开发教程第8章.ppt》由会员分享，可在线阅读，更多相关《SQL-Server-2012数据库开发教程第8章.ppt（97页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、SQLServer2012SQLServer2012数据库开发教程数据库开发教程数据库开发教程数据库开发教程（第（第（第（第33版）版）版）版）授课教师：授课教师：职务：职务：第第8章章存储过程、函数和触发器存储过程、函数和触发器课程描述课程描述课程描述课程描述存储过程、函数和触发存储过程、函数和触发存储过程、函数和触发存储过程、函数和触发器实际上都是使用器实际上都是使用器实际上都是使用器实际上都是使用Transact-SQLTransact-SQLTransact-SQLTransact-SQL语言编写语言编写语言编写语言编写的程序。存储过程和函的程序。存储过程和函的程序。存储过程和函的程序

2、。存储过程和函数需要显式调用才能执数需要显式调用才能执数需要显式调用才能执数需要显式调用才能执行，而触发器则在满足行，而触发器则在满足行，而触发器则在满足行，而触发器则在满足指定条件时自动执行。指定条件时自动执行。指定条件时自动执行。指定条件时自动执行。了解它们的工作原理是了解它们的工作原理是了解它们的工作原理是了解它们的工作原理是编写存储过程、函数和编写存储过程、函数和编写存储过程、函数和编写存储过程、函数和触发器的前提。触发器的前提。触发器的前提。触发器的前提。本章知识点本章知识点8.1 8.1 存储过程存储过程 8.2 8.2 用户定义函数用户定义函数 8.3 8.3 触发器触发器 8.

3、1存储过程存储过程8.1.1 8.1.1 什么是存储过程什么是存储过程 8.1.2 8.1.2 创建存储过程创建存储过程 8.1.3 8.1.3 执行不带参数的存储过程执行不带参数的存储过程 8.1.4 带参数的存储过程8.1.5 8.1.5 存储过程的返回值存储过程的返回值 8.1.6 8.1.6 获取存储过程信息获取存储过程信息 8.1.7 8.1.7 修改和重命名存储过程修改和重命名存储过程 8.1.8 8.1.8 删除存储过程删除存储过程 8.1.9 8.1.9 系统存储过程系统存储过程 8.1.1什么是存储过程什么是存储过程p存储过程是存储过程是Transact-SQLTransac

4、t-SQL语句的预编译集语句的预编译集合，这些语句在一个名称下存储并作为一合，这些语句在一个名称下存储并作为一个单元进行处理。个单元进行处理。SQL ServerSQL Server提供了一系提供了一系列存储过程以管理列存储过程以管理SQL ServerSQL Server、显示有关、显示有关数据库和用户的信息。数据库和用户的信息。SQL ServerSQL Server提供的提供的存储过程称为系统存储过程。存储过程称为系统存储过程。p存储过程由参数、编程语句和返回值组成。存储过程由参数、编程语句和返回值组成。可以通过输入参数向存储过程中传递参数可以通过输入参数向存储过程中传递参数值，也可以通

5、过输出参数向调用者传递多值，也可以通过输出参数向调用者传递多个输出值；个输出值；使用使用SQLServer中的存储过程的优势中的存储过程的优势1 1允许模块化程序设计允许模块化程序设计 2 2允许更快执行允许更快执行3 3减少网络流量减少网络流量4 4可作为安全机制使用可作为安全机制使用 8.1.2创建存储过程创建存储过程1 1使用使用CREATE PROCEDURECREATE PROCEDURE语句语句2 2使使用用SQL SQL Server Server Management Management StudioStudio中中的菜单命令的菜单命令1使用使用CREATEPROCEDURE

6、语句语句pCREATE PROCEDURECREATE PROCEDURE语句的作用是创建存语句的作用是创建存储过程，它的语法结构如下：储过程，它的语法结构如下：CREATE PROC EDURE CREATE PROC EDURE 存储过程名存储过程名 parameter data_type parameter data_type VARYING =default VARYING =default OUTPUT OUTPUT AS SQLAS SQL语句语句 .n .n 参数说明参数说明(1)p存储过程的名称必须符合标识符规则，而且对于数据库存储过程的名称必须符合标识符规则，而且对于数据库及

7、其所有者必须是唯一的。如果要创建局部临时过程，可及其所有者必须是唯一的。如果要创建局部临时过程，可以在存储过程名前面加一个编号符（以在存储过程名前面加一个编号符（#procedure_nameprocedure_name），），如果要创建全局临时过程，可以在存储过程名前面加两个如果要创建全局临时过程，可以在存储过程名前面加两个编号符（编号符（#procedure_nameprocedure_name）。完整的名称（包括）。完整的名称（包括#或或#）不能超过）不能超过128128个字符。指定过程所有者的名称是可选的。个字符。指定过程所有者的名称是可选的。p parameteparamete表示过

8、程中的参数。在表示过程中的参数。在CREATE PROCEDURECREATE PROCEDURE语句语句中可以声明一个或多个参数。用户必须在执行过程时提供中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。使每个所声明参数的值（除非定义了该参数的默认值）。使用用 符号作为第一个字符来指定参数名称。参数名称必须符符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其他过程中。在默认情况下，参数同的参数名称可以用在其他过程中。在默认情况

9、下，参数只能代替常量，而不能用于代替表名、列名或其他数据库只能代替常量，而不能用于代替表名、列名或其他数据库对象的名称。对象的名称。参数说明参数说明(2)pdata_typedata_type表示参数的数据类型。所有数据类型表示参数的数据类型。所有数据类型（包括（包括texttext、ntextntext和和imageimage）都可以用作存储过）都可以用作存储过程的参数。程的参数。pVARYINGVARYING指定作为输出参数支持的结果集，仅适指定作为输出参数支持的结果集，仅适用于游标参数。用于游标参数。pdefaultdefault指定参数的默认值。如果定义了默认值，指定参数的默认值。如果

10、定义了默认值，不必指定该参数的值即可执行过程。默认值必须不必指定该参数的值即可执行过程。默认值必须是常量或是常量或NULLNULL。如果过程中对该参数使用。如果过程中对该参数使用LIKELIKE关关键字，那么默认值中可以包含通配符（键字，那么默认值中可以包含通配符（%、_ _、和和）。）。pOUTPUTOUTPUT表明参数是返回参数。使用表明参数是返回参数。使用OUTPUTOUTPUT参数可参数可将信息返回给调用过程。将信息返回给调用过程。TextText、ntextntext和和imageimage参参数可用作数可用作OUTPUTOUTPUT参数。参数。pASAS关键字指定过程要执行的操作。

11、关键字指定过程要执行的操作。p过程中可以包含的任意数目和类型的过程中可以包含的任意数目和类型的Transact-Transact-SQLSQL语句语句【例例8-1】p创建存储过程创建存储过程IncreaseWageIncreaseWage，功能是将表，功能是将表EmployeesEmployees中所有员工的工资数据增加中所有员工的工资数据增加10%10%，具体语句如下：具体语句如下：USE HrSystemUSE HrSystemGOGOCREATE PROCEDURE IncreaseWageCREATE PROCEDURE IncreaseWageASASUPDATE Employees

12、 SET Wage=Wage*1.1UPDATE Employees SET Wage=Wage*1.1GOGO【例例8.2】p为了进一步增强存储过程的可用性，在为了进一步增强存储过程的可用性，在【例例8.18.1】的基的基础上添加参数信息。由用户通过参数动态地设置工资增础上添加参数信息。由用户通过参数动态地设置工资增长的比例。示例代码如下：长的比例。示例代码如下：USE HrSystemUSE HrSystemGOGOCREATE PROCEDURE IncreaseWage1CREATE PROCEDURE IncreaseWage1IncRate SMALLINTIncRate SMAL

13、LINTASASUPDATE Employees SET Wage=Wage*(1+IncRate/UPDATE Employees SET Wage=Wage*(1+IncRate/100)100)GOGOp与与IncreaseWageIncreaseWage相比，此存储过程多了一个参数相比，此存储过程多了一个参数IncRateIncRate。在调用。在调用IncreaseWage1IncreaseWage1时，可以将需要上调时，可以将需要上调工资的比例通过参数告知存储过程，存储过程再根据参工资的比例通过参数告知存储过程，存储过程再根据参数动态调整工资数额。数动态调整工资数额。2使用使用SQ

14、LServerManagementStudio中中的菜单命令的菜单命令p在在SQL Server SQL Server Management Management StudioStudio中，展开中，展开要创建存储过程要创建存储过程的数据库，例如的数据库，例如HrSystemHrSystem。右击。右击“可编程性可编程性”下下面的面的“存储过程存储过程”项，在弹出菜项，在弹出菜单中选择单中选择“新建新建存储过程存储过程”命令，命令，打开新建存储过打开新建存储过程页面，如图程页面，如图8-8-1 1所示。所示。8.1.3执行不带参数的存储过程执行不带参数的存储过程p使用使用Transact-SQ

15、LTransact-SQL语言的语言的EXCUTEEXCUTE语句可以执行存储过程。语句可以执行存储过程。p【例例8-38-3】执行存储过程执行存储过程IncreaseWageIncreaseWage的命令如下：的命令如下：USE HrSystemUSE HrSystemGOGOEXEC IncreaseWageEXEC IncreaseWageGOGOp运行结果为：运行结果为：（所影响的行数为（所影响的行数为 9 9行）行）p读者可以在读者可以在SQL Server Management StudioSQL Server Management Studio中，查看表中，查看表Employee

16、sEmployees的变化，检验存储过程的变化，检验存储过程WageIncreaseWageIncrease的执行的执行情况。情况。8.1.4带参数的存储过程带参数的存储过程p在定义存储过程时，可以同时指定参数，在定义存储过程时，可以同时指定参数，格式如下：格式如下：参数名参数名 数据类型数据类型 =默认值默认值 OUTPUT,OUTPUT,.n.np如果参数后面使用如果参数后面使用OUTPUTOUTPUT关键字，则表明关键字，则表明它是输出参数。它是输出参数。【例例8-4】p创建存储过程创建存储过程add_procadd_proc，实现计算两个参数之和并将其，实现计算两个参数之和并将其输出的

17、功能，具体语句如下：输出的功能，具体语句如下：CREATE PROCEDURE add_procCREATE PROCEDURE add_procnum1 INT=0,num1 INT=0,num2 INT=0num2 INT=0ASASDECLARE num3 INTDECLARE num3 INTSET num3=num1+num2SET num3=num1+num2PRINT num3PRINT num3p存储过程存储过程add_procadd_proc定义了两个参数定义了两个参数num1num1和和num2num2，它们，它们都是输入参数，参数类型为都是输入参数，参数类型为INTINT

18、，默认值为，默认值为0 0。【例例8-4】p在在SQL Server Management StudioSQL Server Management Studio中运行上面的命令，中运行上面的命令，创建存储过程创建存储过程add_procadd_proc，并执行如下命令，运行存储过，并执行如下命令，运行存储过程：程：EXEC add_procEXEC add_procGOGOp运行结果为运行结果为0 0。因为在执行存储过程时没有带参数，所。因为在执行存储过程时没有带参数，所以两个参数的值都被设置为默认值以两个参数的值都被设置为默认值0 0，它们的和等于，它们的和等于0 0。p在运行存储过程时使用

19、参照在运行存储过程时使用参照1313和和2525，代码如下：，代码如下：EXEC add_proc 13,25EXEC add_proc 13,25GOGO运行结果为运行结果为3838。【例例8-5】p看一个使用输出参数的例子。创建存储过程看一个使用输出参数的例子。创建存储过程add_proc1add_proc1，它的功能，它的功能是计算两个参数之和，并将结果使用输出参数返回，具体语句如是计算两个参数之和，并将结果使用输出参数返回，具体语句如下：下：CREATE PROCEDURE add_proc1CREATE PROCEDURE add_proc1num1 INT=0,num1 INT=0

20、,num2 INT=0,num2 INT=0,num3 INT OUTPUTnum3 INT OUTPUTASASSET num3=num1+num2SET num3=num1+num2p在在SQL Server Management StudioSQL Server Management Studio中执行如下代码，可以将存储中执行如下代码，可以将存储过程过程add_proc1add_proc1的输出参数值保存到的输出参数值保存到numnum变量中。变量中。DECLARE num AS INTDECLARE num AS INTEXEC add_proc1 12,23,num OUTPUTE

21、XEC add_proc1 12,23,num OUTPUTPRINT numPRINT nump运行结果为运行结果为3838。【例例8-6】p创建存储过程创建存储过程AvgWageAvgWage，它的功能是根据给定的部门名称计算平均工资，并将结果，它的功能是根据给定的部门名称计算平均工资，并将结果使用输出参数返回，具体语句如下：使用输出参数返回，具体语句如下：CREATE PROCEDURE AvgWageCREATE PROCEDURE AvgWagedepname varchar(100),depname varchar(100),wage float OUTPUTwage float

22、OUTPUTASASDECLARE depid intDECLARE depid intSET depid=0SET depid=0-根据参数中指定的部门名称根据参数中指定的部门名称depname,depname,获取部门编号获取部门编号SELECT depid=Dep_Id FROM Departments WHERE Dep_Name=depnameSELECT depid=Dep_Id FROM Departments WHERE Dep_Name=depnameIF depid=0IF depid=0 BEGIN BEGIN SET wage=0 SET wage=0 PRINT PR

23、INT 指定的部门记录不存在指定的部门记录不存在 END ENDELSEELSE BEGIN BEGIN SELECT wage=AVG(Wage)FROM EmployeesSELECT wage=AVG(Wage)FROM Employees WHERE Dep_Id=depid WHERE Dep_Id=depid GROUP BY Dep_Id GROUP BY Dep_Id END ENDGOGO 【例例8-6】p执行如下代码，可以将存储过程执行如下代码，可以将存储过程AvgWageAvgWage的输出参数值（即办公室的平均工资）保的输出参数值（即办公室的平均工资）保存到存到wage

24、wage变量中，并打印平均工资数据。变量中，并打印平均工资数据。DECLARE wage floatDECLARE wage floatEXEC AvgWage EXEC AvgWage 办公室办公室,wage OUTPUT,wage OUTPUTPRINT wagePRINT wage8.1.5存储过程的返回值存储过程的返回值p可以在存储过程中使用可以在存储过程中使用RETURNRETURN语句返回一语句返回一个状态值，返回值只能是整数。个状态值，返回值只能是整数。【例例8-7】p创建存储过程创建存储过程AvgWage1AvgWage1，它的功能是根据给定的部门名称计算平均工资，它的功能是根

25、据给定的部门名称计算平均工资，并将结果使用输出参数返回。如果指定的部门存在，则返回并将结果使用输出参数返回。如果指定的部门存在，则返回1 1；否则返回；否则返回0 0。具体语句如下：具体语句如下：USE HrSystemUSE HrSystemGOGOCREATE PROCEDURE AvgWage1CREATE PROCEDURE AvgWage1depname varchar(100),depname varchar(100),wage float OUTPUTwage float OUTPUTASASDECLARE depid intDECLARE depid intSET depid=

26、0SET depid=0-根据参数中指定的部门名称根据参数中指定的部门名称depname,depname,获取部门编号获取部门编号SELECT depid=Dep_Id FROM Departments WHERE Dep_Name=depnameSELECT depid=Dep_Id FROM Departments WHERE Dep_Name=depnameIF depid=0IF depid=0 RETURN 0 RETURN 0ELSEELSE BEGIN BEGIN SELECT wage=AVG(Wage)FROM EmployeesSELECT wage=AVG(Wage)FR

27、OM Employees WHERE Dep_Id=depid WHERE Dep_Id=depid GROUP BY Dep_Id GROUP BY Dep_Id RETURN 1 RETURN 1 END ENDGOGO 【例例8-7】p执行如下脚本，可以将存储过程执行如下脚本，可以将存储过程AvgWage1AvgWage1的输的输出参数值保存到出参数值保存到wagewage变量中。变量中。DECLARE wage floatDECLARE wage floatDECLARE result intDECLARE result intEXEC result=AvgWage1 EXEC res

28、ult=AvgWage1 办公室办公室,wage,wage OUTPUTOUTPUT-检查返回值检查返回值IF result=1IF result=1 PRINT wage PRINT wageELSEELSE PRINT PRINT 没有对应的记录没有对应的记录 8.1.6获取存储过程信息获取存储过程信息p从系统视图从系统视图INFORMATION_SCHEMA.ROUTINESINFORMATION_SCHEMA.ROUTINES中获取存储过程信息。中获取存储过程信息。INFORMATION_SCHEMA.ROUTINESINFORMATION_SCHEMA.ROUTINES中各列的含义如

29、表中各列的含义如表8-18-1所示。所示。属性描述ROUTINE_CATALOG 存储过程或函数所属的数据库ROUTINE_SCHEMA 存储过程或函数所属的构架ROUTINE_NAME存储过程或函数名ROUTINE_TYPE为存储过程返回 PROCEDURE；为函数返回 FUNCTIONDATA_TYPE 函数返回值的数据类型NUMERIC_PRECISION 返回值的数字精度NUMERIC_PRECISION_RADIX返回值的数字精度基数NUMERIC_SCALE 返回值的小数位数DATETIME_PRECISION如果返回值是 datetime 类型，则表示秒的小数精度。否则，返回 N

30、ULLROUTINE_BODY 对于 Transact-SQL 函数，返回 SQL；对于外部编写的函数，返回 EXTERNALROUTINE_DEFINITION 如果函数或存储过程未加密，返回函数或存储过程的定义文本最前面的 4000 字符。否则，返回 NULLCREATED 创建例程的时间LAST_ALTERED 最后一次修改函数的时间查看查看INFORMATION_SCHEMA.ROUTINES的数据的数据8.1.7修改和重命名存储过程修改和重命名存储过程p1 1使用使用SQL Server Management StudioSQL Server Management Studio修改存

31、储过程修改存储过程p2 2使用使用SQL Server Management StudioSQL Server Management Studio重命名存储过程重命名存储过程p3 3使用使用ALTER PROCEDUREALTER PROCEDURE语句修改存储过语句修改存储过程程p4 4使用使用sp_renamesp_rename重命名存储过程重命名存储过程 1使用使用SQLServerManagementStudio修修改存储过程改存储过程p在在SQL Server Management StudioSQL Server Management Studio中选择存储过程所在中选择存储过程所

32、在的数据库，依次选择的数据库，依次选择“可编程性可编程性”/“存储过程存储过程”。在。在右侧的窗口中，列出了选择数据库的所有存储过程，如右侧的窗口中，列出了选择数据库的所有存储过程，如图图8-38-3所示。所示。编辑存储过程的窗口编辑存储过程的窗口p右键单击要修改的存储过程，在弹出菜单中选右键单击要修改的存储过程，在弹出菜单中选择择“修改修改”，打开编辑存储过程的窗口，如图，打开编辑存储过程的窗口，如图8-48-4所示。编辑完成后，单击工具栏中的保存图所示。编辑完成后，单击工具栏中的保存图标。标。2使用使用SQLServerManagementStudio重重命名存储过程命名存储过程p在在SQ

33、L Server Management StudioSQL Server Management Studio中右键中右键单击要重命名的存储过程，选择单击要重命名的存储过程，选择“重命名重命名”菜单项，就可以在当前位置上修改存储菜单项，就可以在当前位置上修改存储过程的名字。过程的名字。3使用使用ALTERPROCEDURE语句修改存储语句修改存储过程过程p使用使用ALTER PROCEDUREALTER PROCEDURE语句可以修改存储过程的属性。语句可以修改存储过程的属性。p【例例8-88-8】修改存储过程修改存储过程IncreaseWage1IncreaseWage1，在参数中增加，在参数

34、中增加Dep_IdDep_Id，对指定的部门按照指定的比例上调工资。使，对指定的部门按照指定的比例上调工资。使用的代码如下：用的代码如下：USE HrSystemUSE HrSystemGOGOALTER PROCEDURE IncreaseWage1ALTER PROCEDURE IncreaseWage1IncRate SMALLINT,Dep_Id SMALLINTIncRate SMALLINT,Dep_Id SMALLINTASASUPDATE Employees SET Wage=Wage*(1+IncRate/UPDATE Employees SET Wage=Wage*(1+I

35、ncRate/100)100)WHERE Dep_id=Dep_IdWHERE Dep_id=Dep_IdGOGO4使用使用sp_rename重命名存储过程重命名存储过程p【例例8-98-9】要将存储过程要将存储过程WageIncreaseWageIncrease重重命名为命名为WageIncWageInc，则可以使用以下命令：，则可以使用以下命令：EXEC sp_rename IncreaseWage,EXEC sp_rename IncreaseWage,WageIncWageIncGOGO8.1.8删除存储过程删除存储过程p可以在可以在SQL Server Management Stud

36、ioSQL Server Management Studio中，右中，右击要删除的存储过程，在弹出菜单中选择击要删除的存储过程，在弹出菜单中选择“删删除除”命令，并确认删除。也可以使用命令，并确认删除。也可以使用DROP DROP PROCEDUREPROCEDURE语句来删除存储过程。语句来删除存储过程。pDROP PROCEDUREDROP PROCEDURE语句的语法结构如下：语句的语法结构如下：DROP PROCEDURE DROP PROCEDURE p【例例8-108-10】删除存储过程删除存储过程WageIncWageInc，可以使用以，可以使用以下命令：下命令：DROP PRO

37、CEDURE WageIncDROP PROCEDURE WageIncGOGO8.1.9系统存储过程系统存储过程p1 1sp_databasessp_databasesp2 2sp_tablessp_tablesp3 3xp_logininfoxp_logininfop4 4xp_msverxp_msver1sp_databasesp存储过程存储过程sp_tablessp_tables可以列出当前环境下可查询可以列出当前环境下可查询的所有对象列表。执行此存储过程，结果如图的所有对象列表。执行此存储过程，结果如图8-68-6所示。所示。2sp_tablesp存储过程存储过程sp_tabless

38、p_tables可以列出当前环境下可查询可以列出当前环境下可查询的所有对象列表。执行此存储过程，结果如图的所有对象列表。执行此存储过程，结果如图8-68-6所示。所示。【例例8-11】p使用使用sp_tablessp_tables存储过程返回所有用户表，可以使用如下命令：存储过程返回所有用户表，可以使用如下命令：USE HrSystemUSE HrSystemGOGOEXEC sp_tables table_type=TABLEEXEC sp_tables table_type=TABLEGOGO结果如图结果如图8-78-7所示。所示。3xp_logininfop系统存储过程系统存储过程xp_

39、logininfoxp_logininfo可以列出当前账户的基本信可以列出当前账户的基本信息，包括账户类型、账户的特权级别、账户的映射登录息，包括账户类型、账户的特权级别、账户的映射登录名和账户访问名和账户访问 SQL ServerSQL Server的权限路径等信息。的权限路径等信息。p执行此存储过程，结果如图执行此存储过程，结果如图8-88-8所示。所示。4xp_msverp列出当前列出当前SQL ServerSQL Server的版本信息，除了服务器的版本信息，除了服务器的内部版本号外，还包括多种环境信息。执行的内部版本号外，还包括多种环境信息。执行此存储过程的结果如图此存储过程的结果如

40、图8-98-9所示。所示。8.2用户定义函数用户定义函数p8.2.1 8.2.1 什么是用户定义函数什么是用户定义函数p8.2.2 8.2.2 创建标量值函数创建标量值函数p8.2.3 8.2.3 创建内联表值函数创建内联表值函数p8.2.4 8.2.4 创建多语句表值函数创建多语句表值函数p8.2.5 8.2.5 修改和重命名用户定义函数修改和重命名用户定义函数p8.2.6 8.2.6 删除用户定义函数删除用户定义函数8.2.1什么是用户定义函数什么是用户定义函数p可以在可以在SQL Server Management StudioSQL Server Management Studio中，

41、右中，右击要删除的用户定义函数，在弹出菜单中选择击要删除的用户定义函数，在弹出菜单中选择“删除删除”命令，并确认删除。命令，并确认删除。p也可以使用也可以使用DROP FUNCTIONDROP FUNCTION语句来删除用户定义语句来删除用户定义函数。函数。DROP PROCEDUREDROP PROCEDURE语句的语法结构如下：语句的语法结构如下：DROP FUNCTION DROP FUNCTION p【例例8-168-16】删除存储过程删除存储过程GetDepartmentAvgWageGetDepartmentAvgWage，可以使用以下命令：，可以使用以下命令：DROP FUNCT

42、ION GetDepartmentAvgWageDROP FUNCTION GetDepartmentAvgWage8.2.2创建标量值函数创建标量值函数p使用使用CREATE FUNCTIONCREATE FUNCTION语句可以创建标量语句可以创建标量值函数，它的语法结构如下：值函数，它的语法结构如下：CREATE FUNCTION CREATE FUNCTION ()RETURNS RETURNS AS AS 【例8-12】p创建标量值函数创建标量值函数GetDepartmentAvgWageGetDepartmentAvgWage，它的功能是获取表，它的功能是获取表EmployeesE

43、mployees中指定部门的平均工资，具体语句如下：中指定部门的平均工资，具体语句如下：USE HrSystemUSE HrSystemGOGOCREATE FUNCTION GetDepartmentAvgWage(dep_id int)CREATE FUNCTION GetDepartmentAvgWage(dep_id int)RETURNS floatRETURNS floatASASBEGINBEGINDECLARE avgWage floatDECLARE avgWage floatSELECT avgWage=AVG(Wage)FROM Employees SELECT avgW

44、age=AVG(Wage)FROM Employees WHERE Dep_id=dep_idWHERE Dep_id=dep_idGROUP BY Dep_idGROUP BY Dep_id RETURN avgWageRETURN avgWageENDENDGOGO调用标量值函数调用标量值函数GetDepartmentAvgWage()p执行下面的语句调用标量值函数执行下面的语句调用标量值函数GetDepartmentAvgWageGetDepartmentAvgWage()()获取编号为获取编号为1 1的部门的平均工资。的部门的平均工资。USE HrSystemUSE HrSystemG

45、OGOSELECT dbo.GetDepartmentAvgWage(1)SELECT dbo.GetDepartmentAvgWage(1)GOGOp执行结果为执行结果为4166.666666666674166.66666666667。在在SQLServerManagementStudio中创建中创建标量值函数标量值函数p展开要创建标量值函数的数据库，例如展开要创建标量值函数的数据库，例如HrSystemHrSystem。右击。右击“可编程性可编程性”下面的下面的“函数函数”/“标量值函数标量值函数”项，在项，在弹出菜单中选择弹出菜单中选择“新建标量值函数新建标量值函数”命令，打开新建函命令

46、，打开新建函数页面，如图数页面，如图8-108-10所示。所示。8.2.3创建内联表值函数创建内联表值函数p使用使用CREATE FUNCTIONCREATE FUNCTION语句还可以创建语句还可以创建内联表值函数，它的语法结构如下：内联表值函数，它的语法结构如下：CREATE FUNCTION CREATE FUNCTION ()RETURNS TABLERETURNS TABLEASASRETURN SELECTRETURN p函数返回函数返回SELECTSELECT语句的结果集。语句的结果集。【例例8-13】p创建内联表值函数创建内联表值函数DepartmentWageTableDep

47、artmentWageTable，它的功能是获取表它的功能是获取表EmployeesEmployees中指定部门的中指定部门的员员工工资信息，具体语句如下：工工资信息，具体语句如下：USE HrSystemUSE HrSystemGOGOCREATE FUNCTION DepartmentWageTable(dep_id int)CREATE FUNCTION DepartmentWageTable(dep_id int)RETURNS TABLERETURNS TABLEASASRETURNRETURN(SELECT Emp_name,Sex,Title,Wage FROM Employee

48、s SELECT Emp_name,Sex,Title,Wage FROM Employees WHERE Dep_id=dep_idWHERE Dep_id=dep_id)GOGO调用函数调用函数DepartmentAvgWageTableUSE HrSystemUSE HrSystemGOGOSELECT*FROM dbo.DepartmentWageTable(1)SELECT*FROM dbo.DepartmentWageTable(1)GOGO8.2.4创建多语句表值函数创建多语句表值函数p使用使用CREATE FUNCTIONCREATE FUNCTION语句还可以创建多语语句还可

49、以创建多语句表值函数，它的语法结构如下：句表值函数，它的语法结构如下：CREATE FUNCTION CREATE FUNCTION ()RETURNS RETURNS TABLE TABLEASASBEGINBEGIN SQL RETURN RETURN ENDEND【例例8-14】p创建多语句表值函数创建多语句表值函数uf_FindReportsuf_FindReports，它的功能是获取表，它的功能是获取表EmployeesEmployees中指定员工的下属员工中指定员工的下属员工信息，具体语句如下：信息，具体语句如下：USE HrSystemUSE HrSystemGOGOCREATE

50、 FUNCTION dbo.uf_FindReports(InEmpID INT)CREATE FUNCTION dbo.uf_FindReports(InEmpID INT)RETURNS retFindReports TABLE RETURNS retFindReports TABLE(EmployeeID int primary key NOT NULL,EmployeeID int primary key NOT NULL,Name nvarchar(50)NOT NULL,Name nvarchar(50)NOT NULL,Title nvarchar(50)NOT NULL,Tit