《存储过程和函数》课件.ppt

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语句显示用户定义的消息和变量语句显示用户定义的消息和变量的值的值 如果变量不是字符串类型，要通过如果变量不是字符串类型，要通过conv

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

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

6、所引用值为变量赋值变量赋值 为局部变量赋值为局部变量赋值幻灯片幻灯片6（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据 多行注释多行注释 /*.*/单行注释单行注释 -注释注释幻灯片幻灯片7（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据 以下规则适用于批处理：以下规则适用于批处理：CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER 和和 CREATE VIEW 语句

7、不能在批处理中与其语句不能在批处理中与其他语句组合使用。批处理必须以他语句组合使用。批处理必须以 CREATE 语句开始。所语句开始。所有跟在该批处理后的其他语句将被解释为第一个有跟在该批处理后的其他语句将被解释为第一个 CREATE 语句定义的一部分。语句定义的一部分。不能在同一个批处理中更改表，然后引用新列。不能在同一个批处理中更改表，然后引用新列。如果如果 EXECUTE 语句是批处理中的第一句，则不需要语句是批处理中的第一句，则不需要 EXECUTE 关键字。如果关键字。如果 EXECUTE 语句不是批处理中语句不是批处理中的第一条语句，则需要的第一条语句，则需要 EXECUTE 关键

8、字。关键字。创建批处理的指导方针创建批处理的指导方针幻灯片幻灯片8（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据课间思考课间思考下面哪个语句可以被用在批处理中下面哪个语句可以被用在批处理中?1.CREATE FUNCTION 2.CREATE RULE 3.DECLARE答案：答案：3.DECLARE幻灯片幻灯片9（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据允许你使用下面的结构来控制语句的流程：允许你使用下面的结构来控制语句的流程：IFELSE 语句语句CA

9、SE 语句语句WHILE 语句语句程序块语句程序块语句幻灯片幻灯片10（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据语法：语法：BEGIN sql_statement|statement_block END 在处理时，整个语句块看作是一条在处理时，整个语句块看作是一条SQL语句。语句。程序块语句程序块语句Begin.end幻灯片幻灯片11（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据IFELSE 语句：语句：基于布尔表达式的结果执行特定的操作基于布尔表达式的结

10、果执行特定的操作语法：语法：IF boolean_expression sql_statement|statement_block ELSE boolean_expression sql_statement|statement_block让我们看看如何让我们看看如何使用结构（续）使用结构（续）幻灯片幻灯片12（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据DECLARE Rate moneySELECT Rate=Rate FROM HumanResources.EmployeePayHistoryWHERE EmployeeI

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

12、ession.ELSE expressionEND让我们看看如何让我们看看如何使用结构（续）使用结构（续）幻灯片幻灯片14（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据SELECT EmployeeID,Marital Status=CASE MaritalStatusWHEN M THEN MarriedWHEN S THEN SingleELSE Not specifiedENDFROM HumanResources.Employee幻灯片幻灯片15（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server

13、 2005 查询和管理数据查询和管理数据WHILE 语句：语句：重复执行只要给定的条件为真重复执行只要给定的条件为真使用使用BREAK 和和 CONTINUE语句来控制语句来控制WHILE循环中的语句循环中的语句语法：语法：WHILE boolean_expression sql_statement|statement_blockBREAKsql_statement|statement_blockCONTINUEBREAK 导致从导致从WHILE 循环中退出。将执行出现在循环中退出。将执行出现在 END 关键字关键字（循环结束的标记）后面的任何语句。（循环结束的标记）后面的任何语句。CONTI

14、NUE 使使 WHILE 循环重新开始执行，忽略循环重新开始执行，忽略 CONTINUE 关键字关键字后面的任何语句。后面的任何语句。使用结构（续）使用结构（续）幻灯片幻灯片16（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据Example:(WHILE)WHILE(SELECT AVG(Rate)+1 from HumanResources.EmployeePayHistory)127BREAKELSECONTINUEEND幻灯片幻灯片17（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查

15、询和管理数据查询和管理数据SQL Server 中的错误可以用以下两种方式处理：中的错误可以用以下两种方式处理：使用使用TRY-CATCH 结构结构 使用使用RAISERROR语句语句处理错误和异常处理错误和异常幻灯片幻灯片18（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据在以下情形下使用在以下情形下使用TRY-CATCH 结构：结构：Try 块包含一组块包含一组T-SQL语句。如果语句。如果TRY块的语句中发块的语句中发生任何错误，控制将传递给生任何错误，控制将传递给CATCH块。块。CATCH块包含另外一组语句，这些语句在

16、错误发生块包含另外一组语句，这些语句在错误发生时执行。时执行。BEGIN TRY sql_statement|statement_block END TRY BEGIN CATCH sql_statement|statement_block END CATCH ;TRY-CATCH在以前的版本中是没有的。在以前的版本中是没有的。处理错误和异常（续）处理错误和异常（续）幻灯片幻灯片19（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据 在在CATCH块中，可以使用以下的系统函数来确块中，可以使用以下的系统函数来确定关于错误的信息：定

17、关于错误的信息：ERROR_NUMBER()返回错误号。返回错误号。ERROR_MESSAGE()返回错误消息的完整文本。此返回错误消息的完整文本。此文本包括为任何可替换参数（如长度、对象名称或时文本包括为任何可替换参数（如长度、对象名称或时间）提供的值。间）提供的值。ERROR_SEVERITY()返回错误严重性。返回错误严重性。ERROR_STATE()返回错误状态号。返回错误状态号。ERROR_LINE()返回导致错误的例程中的行号。返回导致错误的例程中的行号。ERROR_PROCEDURE()返回出现错误的存储过程返回出现错误的存储过程或触发器的名称。或触发器的名称。处理错误和异常（续

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

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

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

21、息的级别。从定制信息的级别。从 0 to 25，19-25 是重大错误代码。是重大错误代码。state 呈现导致错误的状态，不在呈现导致错误的状态，不在SQL内部使用。内部使用。argument 定义在错误信息中的可以替换的值。定义在错误信息中的可以替换的值。WITH 有三个选项有三个选项:WITH LOG 纪录错误。只能用于级别高于纪录错误。只能用于级别高于19的错误。的错误。WITH NOWAIT 将错误立刻发送到客户端将错误立刻发送到客户端 WITH SETERROR sets ERROR to the value specified by msg_id,regardless of se

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

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

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

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

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

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

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

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

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

31、过程，可省略略EXEC。创建存储过程创建存储过程幻灯片幻灯片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使用使用SQ

32、L Server 2005 查询和管理数据查询和管理数据ALTER PROCEDURE prcDeptASBEGINSELECT DepartmentID,Name FROM HumanResources.DepartmentEND幻灯片幻灯片36（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据课间思考课间思考你将使用哪个命令修改过程你将使用哪个命令修改过程?答案：答案：ALTER PROCEDURE 幻灯片幻灯片37（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数

33、据课间思考课间思考哪个系统定义的表存储所有存储过程的名称哪个系统定义的表存储所有存储过程的名称?答案：答案：sysobjects幻灯片幻灯片38（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据带参数的存储过程：带参数的存储过程：被用于在运行时传递值到存储过程被用于在运行时传递值到存储过程这些值通过标准变量被传递。传递值的参数这些值通过标准变量被传递。传递值的参数被定义为输入参数被定义为输入参数参数用于在存储过程和函数以及调用存储过参数用于在存储过程和函数以及调用存储过程或函数的应用程序或工具之间交换数据程或函数的应用程序或工具之

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

35、yee title char(50)AS BEGINPRINT List of EmployeesSELECT EmployeeID,LoginID,TitleFROM HumanResources.Employee WHERE Title=titleENDExecute by passing a parameterEXECUTE prcListEmployee Tool Designer幻灯片幻灯片40（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据存储过程：存储过程：也可以从过程返回作为输出的值也可以从过程返回作为输出的值为

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

37、E 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 HumanResources.EmployeeDepartmentH

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

39、一个过程被调用的时候你必须使用过程被在另一个过程被调用的时候你必须使用EXEC关键字关键字 让我们看看如何让我们看看如何补充：补充：ROWCOUNT：返回语句影响的行数。：返回语句影响的行数。从另一个过程调用一个过程从另一个过程调用一个过程幻灯片幻灯片43（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据演示：创建存储过程演示：创建存储过程问题描述：问题描述：你是你是AdventureWorks,Inc的数据库开发人员。的数据库开发人员。人力资源部门需要修改员工的支付详情。你需人力资源部门需要修改员工的支付详情。你需要创建一个过程

40、，它得到百分比值，你需要通要创建一个过程，它得到百分比值，你需要通过这个值增加支付率。而且，你需要确保仅修过这个值增加支付率。而且，你需要确保仅修改那些员工支付率在最近六个月没有修改的员改那些员工支付率在最近六个月没有修改的员工的薪水。工的薪水。幻灯片幻灯片44（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据解决方案：解决方案：为了解决上述问题，你需要完成下面的任务：为了解决上述问题，你需要完成下面的任务：1.创建一个存储过程。创建一个存储过程。2.执行存储过程。执行存储过程。3.验证结果。验证结果。演示：创建存储过程（续）演示

41、：创建存储过程（续）幻灯片幻灯片45（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据在在 SQL Server 中使用用户定义函数有以下优点：中使用用户定义函数有以下优点：允许模块化程序设计。允许模块化程序设计。只需创建一次函数并将其存储在数据库中，以后便可以在程只需创建一次函数并将其存储在数据库中，以后便可以在程序中调用任意次。序中调用任意次。执行速度更快。执行速度更快。与存储过程相似，与存储过程相似，Transact-SQL 用户定义函数通过缓存计用户定义函数通过缓存计划并在重复执行时重用它来降低划并在重复执行时重用它来降低

42、 Transact-SQL 代码的编译代码的编译开销。这意味着每次使用用户定义函数时均无需重新解析和开销。这意味着每次使用用户定义函数时均无需重新解析和重新优化，从而缩短了执行时间。重新优化，从而缩短了执行时间。减少网络流量。减少网络流量。基于某种无法用单一标量的表达式表示的复杂约束来过滤数基于某种无法用单一标量的表达式表示的复杂约束来过滤数据的操作，可以表示为函数。然后，此函数便可以在据的操作，可以表示为函数。然后，此函数便可以在 WHERE 子句中调用，以减少发送至客户端的数字或行数。子句中调用，以减少发送至客户端的数字或行数。函数函数幻灯片幻灯片46（共（共31张）张）第第11章章Ver

43、.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据标量函数标量函数用户定义标量函数返回在用户定义标量函数返回在 RETURNS 子子句中定义的类型的单个数据值句中定义的类型的单个数据值 表值函数表值函数用户定义表值函数返回用户定义表值函数返回 table 数据类型数据类型 注意：注意：函数不能对具有函数外作用域（例如数函数不能对具有函数外作用域（例如数据库表的修改）的资源状态的任何永久据库表的修改）的资源状态的任何永久性更改性更改 函数类型函数类型幻灯片幻灯片47（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和

44、管理数据标量函数包含以下成分：标量函数包含以下成分：带可选模式带可选模式/拥有者名称的函数名拥有者名称的函数名输入参数名和数据类型输入参数名和数据类型可应用与输入参数的选项可应用与输入参数的选项返回参数数据类型和选项名返回参数数据类型和选项名应用与返回参数的选项应用与返回参数的选项一个或多个一个或多个T-SQL语句语句使用使用CREATE FUNCTION 语句创建标量函语句创建标量函数。数。创建创建 UDFs幻灯片幻灯片48（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据语法：语法：CREATE FUNCTION schema

45、_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_expressionEND;让我们看看如何让我们看看如何创建创建 UDFs（续）（续）幻灯片幻灯片49（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据表值函数：表值函数：返回一个表作为输出，它可以来自返回一个表作为输

46、出，它可以来自SELECT语句的语句的一部分一部分可以替代视图可以替代视图,视图受限于单个视图受限于单个 SELECT 语句，语句，而用户定义函数可包含更多语句而用户定义函数可包含更多语句 使用表数据类型存储一系列行使用表数据类型存储一系列行有以下两种类型：有以下两种类型：内联表值函数内联表值函数对于内联表值函数，没有函数主体对于内联表值函数，没有函数主体 多语句表值函数多语句表值函数 对于多语句表值函数，在对于多语句表值函数，在 BEGIN.END 语句块中定语句块中定义的函数体包含一系列义的函数体包含一系列 Transact-SQL 语句，这些语语句，这些语句可生成行并将其插入将返回的表中

47、句可生成行并将其插入将返回的表中 创建创建 UDFs（续）（续）幻灯片幻灯片50（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据确定函数和非确定函数确定函数和非确定函数根据函数返回的结果集，分为根据函数返回的结果集，分为确定性函数确定性函数如果对于一组特定的输入值和如果对于一组特定的输入值和数据库状态，函数始终返回相同的结果，则该数据库状态，函数始终返回相同的结果，则该用户定义的函数就是确定的。用户定义的函数就是确定的。非确定性函数非确定性函数使用同一组输入值重复调使用同一组输入值重复调用非确定性函数，返回的结果可能会不同用非确

48、定性函数，返回的结果可能会不同 Getdate()幻灯片幻灯片51（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据课间思考课间思考哪个函数类型返回单一值哪个函数类型返回单一值?答案：答案：标量函数标量函数幻灯片幻灯片52（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据问题描述：问题描述：作为作为AdventureWorks,Inc.的数据库开发人员，的数据库开发人员，你需要创建一个函数，它接收员工的员工你需要创建一个函数，它接收员工的员工ID并并且返回下面的详情：

49、且返回下面的详情：Employee ID员工的姓名员工的姓名员工的职务员工的职务在员工下面工作的其他员工的数量在员工下面工作的其他员工的数量你将如何创建函数你将如何创建函数?演示：创建函数演示：创建函数 幻灯片幻灯片53（共（共31张）张）第第11章章Ver.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据解决方案：解决方案：为了解决上述问题，你需要完成下面的任务：为了解决上述问题，你需要完成下面的任务：1.创建一个函数。创建一个函数。2.执行函数以验证结果。执行函数以验证结果。演示：创建函数（续）演示：创建函数（续）幻灯片幻灯片54（共（共31张）张）第第11章章V

50、er.1.0使用使用SQL Server 2005 查询和管理数据查询和管理数据 一般来说，存储过程实现的功能要复杂一点，而一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。函数的实现的功能针对性比较强。函数中有些系函数中有些系统函数不可以使用，如统函数不可以使用，如getdate()对于存储过程来说可以返回任意个输出参数，而函对于存储过程来说可以返回任意个输出参数，而函数只能返回值或者表对象数只能返回值或者表对象,且只有一个。且只有一个。存储过程一般是作为一个独立的部分来执行，而函存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数数