存储过程和函数(二)

《存储过程和函数(二)》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、第14章存储过程和函数(二)课程回顾根据你的理解，什么是存储过程？存储过程的优点有哪些？存储过程的参数哪些？如何自定义存储过程中的错误信息？本章节授课目标了解用户定义函数（UDF）的分类掌握用户定义函数什么是函数有没有这样一种方法，它既能像存储过程那样封装一些复杂的T-SQL代码，并根据需要设置参数，同时又能返回程序所需的值呢？在SQLSERVER2005中，函数是用于封装频繁执行的逻辑的例程。任何必须执行的代码都可调用函数，而无需重复所有的函数逻辑函数是由一条或多条T-SQL语句组成的例程，可用于封装代码以便进行重用。函数接受零个或多个输

2、入参数，并返回标量值或表。函数不支持输出参数函数的分类T-SQL用户定义函数(UDF)分成3类：标量函数：标量函数返回单个数据值，并且其类型是在RETURNS子句中定义的内联表值函数：内联表值函数返回一个表，该表是单个SELECT语句的结果。它类似于视图，但是比视图提供了更多的灵活性，因为可向函数提供参数多语句表值函数：语句表值函数返回由一条或多条T-SQL语句构建的表并且类似于存储过程。与存储过程不同的是，多语句表值函数可以在SELECT语句的FROM子句中进行引用，就像它是视图或表一样创建标量用户定义函数3-1CREATEFUNCTIO

3、N[schema_name.]function_name([{@parameter_name[AS][type_schema_name.]parameter_data_type[=default][READONLY]}[,...n]])RETURNSreturn_data_type[WITH[,...n]][AS]BEGINfunction_bodyRETURNscalar_expressionEND[;]标量用户定义函数接受0个或多个参数并返回一个值，标量UDF通常用于将当前的值转换为新值，或者根据某些参

4、数执行其他复杂的查找，语法如下：声明参数声明返回值类型函数体创建标量用户定义函数3-2用代码创建一个标量函数，它累加了OSS数据库中某件特定产品的所有销售量，并将总计作为int型数据返回创建标量函数dbo.SumSold，并接受一个varchar型的输入参数@MerchandiseID，一个int型的返回值创建标量用户定义函数3-3--创建标量用户定义函数，返回指定商品的销售总量CREATEFUNCTIONdbo.SumSold(@MerchandiseIDvarchar(20))RETURNSintASBEGIN--声明变量@ret，以存

5、储指定商品的总销售量DECLARE@retint;SELECT@ret=SUM(Num)FROMdbo.TMe_OrderDetailsInfoWHEREMerchandiseID=@MerchandiseID;--如果@ret为空，则重新赋值给@ret--如果@ret不为空，则返回变量值IF(@retISNULL)SET@ret=0RETURN@retEND参考实现如下创建函数SumSold对于返回标量值的用户定义函数，允许相同数据类型的标量表达式在任何地方进行调用可使用标量函数的位置：查询中：作为SELECT语句中的select_lis

6、t的expression；作为WHERE或HAVING子句中的expression；作为UPDATE语句中SET子句中的expression表定义中：CHECK约束。函数只能引用同一表中的列T-SQL语句中：CASE表达式中；PRINT语句中（只适用于字符串函数）；作为存储过程的RETURN语句（只适用于返回整数的标量函数）函数和存储过程中：作为用户定义函数的RETURN子句，前提是被调用的用户函数返回的值可隐式转换为进行调用的函数的返回数据类型标量用户定义函数的使用场合用代码执行一个SELECT语句，该语句检索OSS数据库中的每条商品记录

7、的MerchandiseID,MerchandiseName及SumSold标量函数的结果标量用户定义函数调用SELECTMerchandiseIDAS商品编码,MerchandiseNameAS商品名称,dbo.SumSold(MerchandiseID)AS销售总数FROMdbo.TMe_MerchandiseInfo;创建内联用户定义函数5-1表值函数遵守与标量函数相同的规则，区别在于表值函数返回一个表作为输出内联UDF返回表数据类型，在UDF定义中，你不需要显式定义返回的表，只需要使用一个SELECT语句来定义返回的行和列内联UDF

8、使用一个或多个参数并使用一个SELECT语句返回数据内联UDF和视图非常相似，它们能在FROM字句中引用，然而和视图不同的是，UDF可以接受参数，然后将它们用在函数的SELECT