第14章存储过程和函数(二).pptVIP

* * * * 。 * * * * 。 * * * * 。 * * 。 * 。 * 。 * * 第14章 课程回顾 根据你的理解，什么是存储过程？ 存储过程的优点有哪些？ 存储过程的参数哪些？ 如何自定义存储过程中的错误信息？ 本章节授课目标 了解用户定义函数（UDF）的分类 掌握用户定义函数 什么是函数 有没有这样一种方法，它既能像存储过程那样封装一些复杂的T-SQL 代码，并根据需要设置参数，同时又能返回程序所需的值呢？ 在SQL SERVER 2005 中，函数是用于封装频繁执行的逻辑的例程。任何必须执行的代码都可调用函数，而无需重复所有的函数逻辑 函数是由一条或多条T-SQL 语句组成的例程，可用于封装代码以便进行重用。函数接受零个或多个输入参数，并返回标量值或表。函数不支持输出参数 函数的分类 T-SQL用户定义函数(UDF)分成3类： 标量函数：标量函数返回单个数据值，并且其类型是在RETURNS子句中定义的 内联表值函数：内联表值函数返回一个表，该表是单个SELECT 语句的结果。它类似于视图，但是比视图提供了更多的灵活性，因为可向函数提供参数 多语句表值函数：语句表值函数返回由一条或多条T-SQL 语句构建的表并且类似于存储过程。与存储过程不同的是，多语句表值函数可以在 SELECT 语句的FROM 子句中进行引用，就像它是视图或表一样 创建标量用户定义函数3-1 CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] [ READONLY ] } [ ,...n ] ] ) RETURNS return_data_type [ WITH function_option [ ,...n ] ] [ AS ] BEGIN function_body RETURN scalar_expression END [ ; ] 标量用户定义函数接受0个或多个参数并返回一个值，标量UDF通常用于将当前的值转换为新值，或者根据某些参数执行其他复杂的查找，语法如下： 声明参数 声明返回值类型 函数体 创建标量用户定义函数3-2 用代码创建一个标量函数，它累加了OSS数据库中某件特定产品的所有销售量，并将总计作为int 型数据返回 创建标量函数 dbo.SumSold ，并接受一个varchar 型的输入参数@MerchandiseID ，一个int型的返回值 创建标量用户定义函数3-3 -- 创建标量用户定义函数，返回指定商品的销售总量 CREATE FUNCTION dbo.SumSold(@MerchandiseID varchar(20)) RETURNS int AS BEGIN -- 声明变量@ret ，以存储指定商品的总销售量 DECLARE @ret int; SELECT @ret = SUM(Num) FROM dbo.TMe_OrderDetailsInfo WHERE MerchandiseID = @MerchandiseID; -- 如果@ret为空，则重新赋值给@ret -- 如果@ret不为空，则返回变量值 IF (@ret IS NULL) SET @ret = 0 RETURN @ret END 参考实现如下 创建函数SumSold 对于返回标量值的用户定义函数，允许相同数据类型的标量表达式在任何地方进行调用 可使用标量函数的位置： 查询中：作为SELECT 语句中的select_list的expression；作为WHERE 或 HAVING子句中的expression；作为 UPDATE 语句中SET子句中的expression 表定义中：CHECK约束。函数只能引用同一表中的列 T-SQL 语句中：CASE 表达式中；PRINT 语句中（只适用于字符串函数）；作为存储过程的RETURN语句（只适用于返回整数的标量函数） 函数和存储过程中：作为用户定义函数的RETURN 子句，前提是被调用的用户函数返回的值可隐式转换为进行调用的函数的返回数据类型 标量用户定义函数的使用场合 用代码执行一个SELECT语句，该语句检索OSS数据库中的每条商品记录的MerchandiseID, MerchandiseName及SumSold标量函数的结果 标量用户定义函数调用 SELECT MerchandiseID AS 商品编码,Mer