文档结构  
翻译进度:已翻译     翻译赏金:0 元 (?)    ¥ 我要打赏

1. 介绍

SQL server中的存储过程和函数是SQL程序的编译单元,用来执行特定的某个任务。在SQL Server中用户定义的函数不像存储过程,它可以在SQLSelect查询中使用。在这篇文章,我将演示在SQL Server中如何创建使用 “返回值函数”(UDF)。

文中的例子是用微软提供的来自Pubs数据库的Titles表。在这个网站的SQL2005小节,你可以下载SQL脚本并创建PUbs和NorthWnd数据库。开始前,我们提供的Title表的数据内容,以便你在通读全文的时候可以参考。

图1. Title表的内容

第 1 段(可获 2 积分)

2. 简单的返回值函数

像存储过程一样,返回值函数也是执行一个特定的任务,并且返回数值。 在函数体里,返回值是确定的。一旦函数创建,就可以像SQLServer的内置函数一样使用。

下面一张图展示了一系列T-SQL语句,用来创建用户自定义的TotalBooks函数。此函数计算了PUPs库中titles表中的条数,然后返回条数数值。

Simple Scalar-Valued Function Example
图2. 简单的返回值函数用例

第 2 段(可获 2 积分)

就像存储过程,关键字 “Create Function” 用来创建函数(标记为1)和函数名(标记为4)。当函数返回一个值,关键字 “Returns”用来指定函数返回值的类型。在例子中, TotalBooks 函数返回了一个整型(标记为2)。

函数体里,函数功能以及该返回什么值是确定的。函数体是写在“ BeginEnd ”里的。在例子中,变量 BookCount (标记为5)定位一个整型。title表里的总数是有聚合计算函数算出的,算出结果被赋给内部成员(标记为6) @BookCount. ,然后返回 @BookCount 值(标记为3和7).注意标2和标3中的 “Return” 经常会弄错。一旦函数创建,你可以将数据库 “Programmability” 结点下的 “Scalar-Valued Function” 结点展开,以此验证。如下图所示。

第 3 段(可获 2 积分)

图3. 数据库树形目录中的用户函数

图4.执行返回值函数

以上讨论的创建和执行函数的视频链接如下所示。

Video 1: Simple Scalar-Valued Function

3. 带有参数和默认参数的返回值函数

在以上章节中,我们已经创建了一个简单的返回值函数。返回值函数也可以带参。这个章节中,我们将创建一个函数并返回属于某个书本类型的titles数目。为了实现这个功能,在调用函数中命名 “Book Section”为参数。看下下面的例子。

第 4 段(可获 2 积分)

图5. 带参的返回值函数

这边我们创建了一个名为 TotalPrice的函数,含有一个名为“Type”的参数(标为1)。当参数没有赋值时,其值为NULL。注意参数类型需要与数据表 Title 里的 “Type” 列的数据类型匹配,因为我们可能将表中 “Type” 列的值赋给参数。因为总价格来自Title表中的Price列,返回值类型定义为money类型(标为1),与对应表中的Price列相对应。 “Null on Null Input” 语句说明当参数中含有一个或多个null值时,函数会立即返回(标为2)。

第 5 段(可获 2 积分)

函数中的其他部分与我们之前章节中所述类似。然而你可以看到在函数体里,参数是怎么传递的。在函数体里参数通过select查询语句中的where子句传递(标为3)。这样,指定参数书目类别的总价格能计算出来,并赋给局部变量 @PriceTotal. 然后函数返回此变量值

4. 给返回值函数传递参数

在上面的例子中,你可能会认为,让参数列为空会赋予默认null值。但在SQL2005中这是错的。看看下面的查询和错误信息:

第 6 段(可获 2 积分)

查询: Select Dbo.TotalPrice();

结果 Msg 313, Level 16, State 2, Line 1 An insufficient number of arguments were supplied for the procedure or function Dbo.TotalPrice.

正确赋值默认参数如下所示

Query: Select Dbo.TotalPrice(Default);

Result: Null

上面的例子返回Null是因为它含有null参数。看一下标2部分的例子。其他参数的传递方式如下图所示:


图6. 运行带参的返回值函数

在上图第一个着重部分,你可以看到通过字符变量 mod_cook 和abusiness参数,函数被调用了两次。函数返回了 mod_cook 部分和 business 部分的总价格。

第 7 段(可获 2 积分)

图中第二个着重部分显示了将Table自身的数值作为传递参数。这里Title表中的Type列被作为函数的参数。

图中第三个着重部分显示了执行查询的结果。查询结果返回6行,也就是有6中不同的类型。因此函数被调用6此,每次将“Title”表中的type列的值赋给参数。

视频2 Scalar-Valued function with Parameter (带参数的返回值)

 

脚本清单

Listing 1.1

 
--Simple Function
Create Function TotalBooks()
returns int as
Begin
     declare @BookCount as int;
     Select @BookCount = Count(Title_id)
     from Titles;
     return @BookCount;
 
end;
 
Listing 1.2

 
--Function with Parameter
Create Function TotalPrice(@Type char(12)= null)
returns money
with returns null on null input as
Begin
     declare @PriceTotal money;
     Select @PriceTotal=SUM(Price)
     From Titles
     Where Type=@Type;
     return @PriceTotal;
 
end;

 

第 8 段(可获 2 积分)

文章评论