存储过程和函数.ppt

《存储过程和函数.ppt》由会员分享，可在线阅读，更多相关《存储过程和函数.ppt（57页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、幻灯片幻灯片1（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据批处理：批处理：有助于减少网络堵塞有助于减少网络堵塞（network traffic)多个命令一起被提交和执行多个命令一起被提交和执行 批处理批处理(batch)是一组一起提交给是一组一起提交给SQL Server执执行的行的SQL语句语句在结尾使用在结尾使用GO命令以发送命令以发送SQL语句到一个语句到一个SQL Server实例实例当编译器读取到当编译器读取到GO语句时，会把语句时，会把GO前面的语前面的语句当成一个批处理，并把这些语句打包发送给句当成一个批处理，

2、并把这些语句打包发送给服务器。服务器。特点：批一次性的发送到特点：批一次性的发送到SQL SERVER服务器服务器执行，服务器将批处理语句编译成一个可执行执行，服务器将批处理语句编译成一个可执行单元，这种单元称为单元，这种单元称为执行计划执行计划（execution plan)举例举例创建批处理创建批处理幻灯片幻灯片2（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据说明：说明：如果批处理中出现任何编译错误如果批处理中出现任何编译错误(compilation error)，导致执行计划无法编，导致执行计划无法编译，从而批处理中的任

3、何语句都无法执行。译，从而批处理中的任何语句都无法执行。如果编译完成，在运行中出错，如果编译完成，在运行中出错，(run-time error)则执行停止，出错之前执行的语句则执行停止，出错之前执行的语句不受影响。不受影响。幻灯片幻灯片3（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据变量变量使用变量存储值使用变量存储值输入数据，中间值，最后结果输入数据，中间值，最后结果使用使用PRINT语句显示用户定义的消息和变量的语句显示用户定义的消息和变量的值值如果变量不是字符串类型，要通过如果变量不是字符串类型，要通过convert函数

4、进函数进行转换行转换变量类型：变量类型：全局变量全局变量（Global variables）记录服务器的活动状态记录服务器的活动状态是事先定义好的变量，不能由用户参与定义，是事先定义好的变量，不能由用户参与定义，只读只读用用标记标记(举例）举例）幻灯片幻灯片4（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据创建局部变量创建局部变量DECLAREvariable_namedata_typeDECLAREMyCounterint多个变量，用，隔开多个变量，用，隔开DECLARELastNamenvarchar(30),FirstNa

5、menvarchar(20),Statenchar(2)变量的作用域从声明变量的地方开始到声明变变量的作用域从声明变量的地方开始到声明变量的批处理或存储过程的结尾。量的批处理或存储过程的结尾。创建局部变量创建局部变量幻灯片幻灯片5（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据第一次声明变量时，其值设置为第一次声明变量时，其值设置为NULL赋值的方法：赋值的方法：SET语句语句 SET avgscore=60.0SELECT语句的选择列表中当前所引用值为语句的选择列表中当前所引用值为变量赋值变量赋值 为局部变量赋值为局部变量赋值

6、幻灯片幻灯片6（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据多行注释多行注释/*.*/单行注释单行注释-注释注释幻灯片幻灯片7（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据以下规则适用于批处理：以下规则适用于批处理：CREATEDEFAULT、CREATEFUNCTION、CREATEPROCEDURE、CREATERULE、CREATETRIGGER和和CREATEVIEW语句不能在批处理中与其他语句组合语句不能在批处理中与其他语句组合使用。批处理必须以使用

7、。批处理必须以CREATE语句开始。所有跟在该批语句开始。所有跟在该批处理后的其他语句将被解释为第一个处理后的其他语句将被解释为第一个CREATE语句定义语句定义的一部分。的一部分。不能在同一个批处理中更改表，然后引用新列。不能在同一个批处理中更改表，然后引用新列。如果如果EXECUTE语句是批处理中的第一句，则不需要语句是批处理中的第一句，则不需要EXECUTE关键字。如果关键字。如果EXECUTE语句不是批处理中语句不是批处理中的第一条语句，则需要的第一条语句，则需要EXECUTE关键字。关键字。创建批处理的指导方针创建批处理的指导方针幻灯片幻灯片9（共（共31张）张）第第11章章Ver.

8、1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据允许你使用下面的结构来控制语句的流程：允许你使用下面的结构来控制语句的流程：IFELSE 语句语句CASE 语句语句WHILE 语句语句程序块语句程序块语句幻灯片幻灯片10（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据语法：语法：BEGINsql_statement|statement_blockEND在处理时，整个语句块看作是一条在处理时，整个语句块看作是一条SQL语句。语句。程序块语句程序块语句Begin.end幻灯片幻灯片11（共（共31张）张）第

9、第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据IFELSE 语句：语句：基于布尔表达式的结果执行特定的操作基于布尔表达式的结果执行特定的操作语法：语法：IF boolean_expression sql_statement|statement_block ELSE boolean_expression sql_statement|statement_block让我们看看如何让我们看看如何使用结构（续）使用结构（续）幻灯片幻灯片12（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据DEC

10、LARE Rate moneySELECT Rate=Rate FROM HumanResources.EmployeePayHistoryWHERE EmployeeID=23IF Rate 15PRINT Review requiredELSEBEGINPRINT Review not required PRINT your rate=PRINT RateEND幻灯片幻灯片13（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据CASE 语句：语句：评估条件的一个列表并且返回可能结果中的一个评估条件的一个列表并且返回可能结果中的

11、一个语法：语法：CASE WHEN boolean_expression THEN expressionWHEN boolean_expression THEN expression.ELSE expressionEND让我们看看如何让我们看看如何使用结构（续）使用结构（续）幻灯片幻灯片14（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据SELECT EmployeeID,Marital Status=CASE MaritalStatusWHEN M THEN MarriedWHEN S THEN SingleELSE Not

12、specifiedENDFROM HumanResources.Employee幻灯片幻灯片15（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据WHILE 语句：语句：重复执行只要给定的条件为真重复执行只要给定的条件为真使用使用BREAK 和和 CONTINUE语语句来控制句来控制WHILE循循环环中的中的语语句句语法：语法：WHILE boolean_expression sql_statement|statement_blockBREAKsql_statement|statement_blockCONTINUEBREAK导致

13、从导致从WHILE循环中退出。将执行出现在循环中退出。将执行出现在END关键字关键字（循环结束的标记）后面的任何语句。（循环结束的标记）后面的任何语句。CONTINUE使使WHILE循环重新开始执行，忽略循环重新开始执行，忽略CONTINUE关键字关键字后面的任何语句。后面的任何语句。使用结构（续）使用结构（续）幻灯片幻灯片16（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据Example:(WHILE)WHILE(SELECT AVG(Rate)+1 from HumanResources.EmployeePayHistory

14、)127BREAKELSECONTINUEEND幻灯片幻灯片17（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据SQL Server 中的错误可以用以下两种方式处理：中的错误可以用以下两种方式处理：使用使用TRY-CATCH 结构结构 使用使用RAISERROR语句语句处理错误和异常处理错误和异常幻灯片幻灯片18（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据在以下情形下使用在以下情形下使用TRY-CATCH 结构：结构：Try 块包含一组块包含一组T-SQL语句

15、。如果语句。如果TRY块的语句中发块的语句中发生任何错误，控制将传递给生任何错误，控制将传递给CATCH块。块。CATCH块包含另外一组语句，这些语句在错误发生块包含另外一组语句，这些语句在错误发生时执行。时执行。BEGINTRYsql_statement|statement_blockENDTRYBEGINCATCHsql_statement|statement_blockENDCATCH;TRY-CATCH在以前的版本中是没有的。在以前的版本中是没有的。处理错误和异常（续）处理错误和异常（续）幻灯片幻灯片19（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 200

16、5 查询和管理数据查询和管理数据在在CATCH块中，可以使用以下的系统函数来确块中，可以使用以下的系统函数来确定关于错误的信息：定关于错误的信息：ERROR_NUMBER()返回错误号。返回错误号。ERROR_MESSAGE()返回错误消息的完整文本。此返回错误消息的完整文本。此文本包括为任何可替换参数（如长度、对象名称或时文本包括为任何可替换参数（如长度、对象名称或时间）提供的值。间）提供的值。ERROR_SEVERITY()返回错误严重性。返回错误严重性。ERROR_STATE()返回错误状态号。返回错误状态号。ERROR_LINE()返回导致错误的例程中的行号。返回导致错误的例程中的行号

17、。ERROR_PROCEDURE()返回出现错误的存储过程返回出现错误的存储过程或触发器的名称。或触发器的名称。处理错误和异常（续）处理错误和异常（续）幻灯片幻灯片20（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据RAISERROR：被用于返回消息到商业程序被用于返回消息到商业程序使用与系统错误或数据库引擎产生的警告消息使用与系统错误或数据库引擎产生的警告消息相同的格式相同的格式也可能返回用户定义的错误消息也可能返回用户定义的错误消息让我们看看如何让我们看看如何处理错误和异常（续）处理错误和异常（续）幻灯片幻灯片21（共（共3

18、1张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据错误由错误由表维护。每一个错误代码都有相应的表维护。每一个错误代码都有相应的级别和描述。级别和描述。错误定义的级别从错误定义的级别从0到到25。20以上的错误代以上的错误代表重大错误，通常意味着该错误会导致存储表重大错误，通常意味着该错误会导致存储进程立刻终止，并且所有的客户连接都要重进程立刻终止，并且所有的客户连接都要重新初始化。新初始化。非关键性错误只是禁止掉当前运行的程序行，非关键性错误只是禁止掉当前运行的程序行，并继续执行。并继续执行。处理错误和异常（续）处理错误和异常（续）幻灯片幻

19、灯片22（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据语法语法RAISERROR(msg_idmsg_str,severity,state,argument,.n)WITHoption,.n幻灯片幻灯片23（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据课间思考课间思考msg_id定制消息的错误代码。定制消息的错误代码。.RAISERROR接受任何大于接受任何大于13000的的数字数字,但是定制信息但是定制信息msg_id要大于等于要大于等于50000。msg_

20、str定制信息的文本。定制信息的文本。severity定制信息的级别。从定制信息的级别。从0to25，19-25是重大错误代码。是重大错误代码。state呈现导致错误的状态，不在呈现导致错误的状态，不在SQL内部使用。内部使用。argument定义在错误信息中的可以替换的值。定义在错误信息中的可以替换的值。WITH有三个选项有三个选项:WITHLOG纪录错误。只能用于级别高于纪录错误。只能用于级别高于19的错误。的错误。WITHNOWAIT将错误立刻发送到客户端将错误立刻发送到客户端WITHSETERRORsetsERRORtothevaluespecifiedbymsg_id,regardl

21、essofseveritylevel.幻灯片幻灯片24（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据课间思考课间思考哪个系统函数返回错误消息的文本当用在哪个系统函数返回错误消息的文本当用在CATCH块中的时候块中的时候?答案：答案：ERROR_MESSAGE()幻灯片幻灯片25（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据课间思考课间思考你如何在批处理中返回用户定义的错误消息你如何在批处理中返回用户定义的错误消息?答案：答案：使用使用RAISERROR语句语句

22、幻灯片幻灯片26（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据批批处处理是一系列一起提交到服理是一系列一起提交到服务务器器执执行的行的SQL语语句。句。你可以使用你可以使用变变量存量存储储一个一个临时值临时值。你可以使用你可以使用PRINT语句来显示用户定义消息或屏幕上一语句来显示用户定义消息或屏幕上一个变量的内容。个变量的内容。你可以在批处理中使用注释实体给代码写注释。你可以在批处理中使用注释实体给代码写注释。你可以使用你可以使用IFELSE语句以条件执行语句以条件执行SQL语句。语句。CASE语句求一系列条件的值并且返回各

23、种可能结果中语句求一系列条件的值并且返回各种可能结果中的一个。的一个。你可以在批处理中使用你可以在批处理中使用WHILE语句以允许一系列语句以允许一系列T SQL语句重复执行只要给定条件为真。语句重复执行只要给定条件为真。BREAK语句导致从语句导致从WHILE循环中退出。循环中退出。小结（续）小结（续）幻灯片幻灯片27（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据小结（续）小结（续）CONTINUE语句导致语句导致WHILE循环重启，忽略循循环重启，忽略循环中环中CONTINUE语句后的任何语句。语句后的任何语句。在批处理中

24、处理错误的两种方法是：在批处理中处理错误的两种方法是：TRY-CATCH 结构结构RAISERROR 语句语句幻灯片幻灯片28（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据存储过程：存储过程：（stored procedurre)是数据库中保存的预编译的数据库对象是数据库中保存的预编译的数据库对象批处理是临时的批处理是临时的存储过程可以极大的减少网络需求存储过程可以极大的减少网络需求 系统存储过程：系统存储过程：sp_ 不要以不要以sp_为前缀创建任何存储过程。为前缀创建任何存储过程。sp_前缀是前缀是SQLServer用来指

25、定系统存储过程用来指定系统存储过程的的存储过程存储过程幻灯片幻灯片29（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据存储过程的优点：存储过程的优点：存储过程只在创造时进行编译，以后每次执行存储存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般过程都不需再重新编译，而一般SQL语句每执行一次语句每执行一次就编译一次就编译一次,所以使用存储过程可提高数据库执行速所以使用存储过程可提高数据库执行速度。度。当对数据库进行复杂操作时当对数据库进行复杂操作时(如对多个表进行如对多个表进行Update,Insert,Q

26、uery,Delete时），可将此复杂操作用时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起存储过程封装起来与数据库提供的事务处理结合一起使用。使用。存储过程可以重复使用存储过程可以重复使用,可减少数据库开发人员的工可减少数据库开发人员的工作量作量安全性高安全性高,可设定只有某此用户才具有对指定存储过可设定只有某此用户才具有对指定存储过程的使用权程的使用权存储过程存储过程幻灯片幻灯片30（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据存储过程：存储过程：使用使用CREATE PROCEDURE语句创建存储过程

27、语句创建存储过程使用使用EXECUTE 语句实现存储过程语句实现存储过程语法：语法：CREATE PROCEDURE proc_nameASBEGINsql_statement1sql_statement2END让我们看看如何让我们看看如何创建存储过程创建存储过程幻灯片幻灯片31（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据CREATE PROCEDURE prcDeptASBEGINSELECT Name FROMHumanResources.DepartmentENDExecutingtheprocedureEXECUTE

28、 prcDept幻灯片幻灯片32（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据存储过程编译的过程：存储过程编译的过程：解析：组件被解析解析：组件被解析分解：检查引用对象的存在性分解：检查引用对象的存在性存储过程的名字存储在存储过程的名字存储在sysobjects中，代码中，代码存储在存储在syscomments表中表中编译存储过程，创建执行计划，并保存在缓编译存储过程，创建执行计划，并保存在缓存中存中首次执行的时候，读取执行计划并优化，运首次执行的时候，读取执行计划并优化，运行。以后执行直接从缓存中读取，提高了性行。以后执行直

29、接从缓存中读取，提高了性能。能。创建存储过程创建存储过程幻灯片幻灯片33（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据存储过程的依赖关系：存储过程的依赖关系：sp_dependssp_dependsobject_name过程代码：过程代码：sp_helptext注意：注意：不能在单个批处理中将不能在单个批处理中将create procedure与与其它其它SQL语句一起使用语句一起使用执行存储过程：执行存储过程：EXEC proc_name如果在批处理的第一行执行存储过程，可省如果在批处理的第一行执行存储过程，可省略略EXEC

30、。创建存储过程创建存储过程幻灯片幻灯片34（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据存储过程：存储过程：使用使用 ALTER PROCEDURE语句修改存储过程语句修改存储过程语法：语法：ALTER PROCEDURE proc_name使用使用DROP PROCEDURE 语句删除存储过程语句删除存储过程语法：语法：DROP PROCEDURE proc_name让我们看看如何让我们看看如何创建存储过程（续）创建存储过程（续）幻灯片幻灯片35（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 20

31、05 查询和管理数据查询和管理数据ALTER PROCEDURE prcDeptASBEGINSELECT DepartmentID,Name FROM HumanResources.DepartmentEND幻灯片幻灯片38（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据带参数的存储过程：带参数的存储过程：被用于在运行时传递值到存储过程被用于在运行时传递值到存储过程这些值通过标准变量被传递。传递值的参数这些值通过标准变量被传递。传递值的参数被定义为输入参数被定义为输入参数参数用于在存储过程和函数以及调用存储过参数用于在存储过程

32、和函数以及调用存储过程或函数的应用程序或工具之间交换数据程或函数的应用程序或工具之间交换数据输入参数允许调用方将数据值传递到存储过程或输入参数允许调用方将数据值传递到存储过程或函数。函数。输出参数允许存储过程将数据值传递回调用方。输出参数允许存储过程将数据值传递回调用方。每个存储过程向调用方返回一个整数返回代码。每个存储过程向调用方返回一个整数返回代码。如果存储过程没有显式设置返回代码的值，则返如果存储过程没有显式设置返回代码的值，则返回代码为回代码为0。创建带参数的存储过程创建带参数的存储过程幻灯片幻灯片39（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005

33、 查询和管理数据查询和管理数据CREATE PROC prcListEmployee title char(50)AS BEGINPRINT List of EmployeesSELECT EmployeeID,LoginID,TitleFROM HumanResources.Employee WHERE Title=titleENDExecutebypassingaparameterEXECUTE prcListEmployee Tool Designer幻灯片幻灯片40（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据存储过程

34、：存储过程：也可以从过程返回作为输出的值也可以从过程返回作为输出的值为了指定一个参数为输出参数，你可以使用为了指定一个参数为输出参数，你可以使用OUTPUT关键字关键字可用可用return返回整形值，缺省情况下，返回整形值，缺省情况下，0代表成功，非零代表失代表成功，非零代表失败。败。语法：语法：CREATE PROCEDURE procedure_name parameter data_type OUTPUTAS sql_statement.n让我们看看如何让我们看看如何从存储过程返回值从存储过程返回值幻灯片幻灯片41（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server

35、 2005 查询和管理数据查询和管理数据CREATE PROCEDURE prcGetEmployeeDetail EmpId int,DepId int OUTPUT,DepName char(50)OUTPUT,ShiftId int OUTPUTASBEGINIF EXISTS(SELECT*FROM HumanResources.Employee WHERE EmployeeID=EmpId)BEGINSELECT DepId=d.DepartmentID,DepName=Name,ShiftId=ShiftIDFROM HumanResources.Department d JOIN

36、 HumanResources.EmployeeDepartmentHistory hON d.DepartmentID=h.DepartmentIDWHERE EmployeeID=EmpIdRETURN 0ENDELSERETURN 1END幻灯片幻灯片42（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据存储过程：存储过程：可能需要在另一个过程中使用一个过程返回的值可能需要在另一个过程中使用一个过程返回的值调用或执行另一个过程的过程被称为调用过程调用或执行另一个过程的过程被称为调用过程被调用或被调用过程执行的过程被称为被调用

37、过程被调用或被调用过程执行的过程被称为被调用过程说明：说明：过程被在另一个过程被调用的时候你必须使用过程被在另一个过程被调用的时候你必须使用EXEC关键字关键字让我们看看如何让我们看看如何补充：补充：ROWCOUNT：返回语句影响的行数。：返回语句影响的行数。从另一个过程调用一个过程从另一个过程调用一个过程幻灯片幻灯片45（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据在在SQLServer中使用用户定义函数有以下优点：中使用用户定义函数有以下优点：允许模块化程序设计。允许模块化程序设计。只需创建一次函数并将其存储在数据库中，以

38、后便可以在程只需创建一次函数并将其存储在数据库中，以后便可以在程序中调用任意次。序中调用任意次。执行速度更快。执行速度更快。与存储过程相似，与存储过程相似，Transact-SQL用户定义函数通过缓存计划用户定义函数通过缓存计划并在重复执行时重用它来降低并在重复执行时重用它来降低Transact-SQL代码的编译开代码的编译开销。这意味着每次使用用户定义函数时均无需重新解析和重销。这意味着每次使用用户定义函数时均无需重新解析和重新优化，从而缩短了执行时间。新优化，从而缩短了执行时间。减少网络流量。减少网络流量。基于某种无法用单一标量的表达式表示的复杂约束来过滤数基于某种无法用单一标量的表达式表

39、示的复杂约束来过滤数据的操作，可以表示为函数。然后，此函数便可以在据的操作，可以表示为函数。然后，此函数便可以在WHERE子句中调用，以减少发送至客户端的数字或行数。子句中调用，以减少发送至客户端的数字或行数。函数函数幻灯片幻灯片46（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据标量函数标量函数用户定义标量函数返回在用户定义标量函数返回在RETURNS子子句中定义的类型的单个数据值句中定义的类型的单个数据值表值函数表值函数用户定义表值函数返回用户定义表值函数返回table数据类型数据类型注意：注意：函数不能对具有函数外作用域（

40、例如数函数不能对具有函数外作用域（例如数据库表的修改）的资源状态的任何永久据库表的修改）的资源状态的任何永久性更改性更改函数类型函数类型幻灯片幻灯片47（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据标量函数包含以下成分：标量函数包含以下成分：带可选模式带可选模式/拥有者名称的函数名拥有者名称的函数名输入参数名和数据类型输入参数名和数据类型可应用与输入参数的选项可应用与输入参数的选项返回参数数据类型和选项名返回参数数据类型和选项名应用与返回参数的选项应用与返回参数的选项一个或多个一个或多个T-SQL语句语句使用使用CREATE

41、FUNCTION 语句创建标量函语句创建标量函数。数。创建创建 UDFs幻灯片幻灯片48（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据语法：语法：CREATE FUNCTION schema_name.function_name(parameter_name AS type_schema_name.parameter_data_type =default ,.n)RETURNS return_data_type WITH ,.n AS BEGIN function_body RETURN scalar_expressionEN

42、D;让我们看看如何让我们看看如何创建创建 UDFs（续）（续）幻灯片幻灯片49（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据表值函数：表值函数：返回一个表作为输出，它可以来自返回一个表作为输出，它可以来自SELECT语句的语句的一部分一部分可以替代视图可以替代视图,视图受限于单个视图受限于单个SELECT语句，语句，而用户定义函数可包含更多语句而用户定义函数可包含更多语句使用表数据类型存储一系列行使用表数据类型存储一系列行有以下两种类型：有以下两种类型：内联表值函数内联表值函数对于内联表值函数，没有函数主体对于内联表值函数，没

43、有函数主体多语句表值函数多语句表值函数对于多语句表值函数，在对于多语句表值函数，在BEGIN.END语句块中定语句块中定义的函数体包含一系列义的函数体包含一系列Transact-SQL语句，这些语语句，这些语句可生成行并将其插入将返回的表中句可生成行并将其插入将返回的表中创建创建 UDFs（续）（续）幻灯片幻灯片50（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据确定函数和非确定函数确定函数和非确定函数根据函数返回的结果集，分为根据函数返回的结果集，分为确定性函数确定性函数如果对于一组特定的输入值和如果对于一组特定的输入值和数据

44、库状态，函数始终返回相同的结果，则该数据库状态，函数始终返回相同的结果，则该用户定义的函数就是确定的。用户定义的函数就是确定的。非确定性函数非确定性函数使用同一组输入值重复调使用同一组输入值重复调用非确定性函数，返回的结果可能会不同用非确定性函数，返回的结果可能会不同Getdate()幻灯片幻灯片51（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据课间思考课间思考哪个函数类型返回单一值哪个函数类型返回单一值?答案：答案：标量函数标量函数幻灯片幻灯片52（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据问题描述：问题描述：作为作为AdventureWorks,Inc.的数据库开发人员，的数据库开发人员，你需要创建一个函数，它接收员工的员工你需要创建一个函数，它接收员工的员工ID并并且返回下面的详情：且返回下面的详情：Employee ID员工的姓名员工的姓名员工的职务员工的职务在员工下面工作的其他员工的数量在员工下面工作的其他员工的数量你将如何创建函数你将如何创建函数?演示：创建函数演示：创建函数