sql代码重构—— 创建自定义函数（二）

接着上一篇的内容《sql代码重构——使用自定义函数（一）》咱们精彩继续。

上一篇咱们讲到，什么是自定义函数、为什么使用自定义函数，以及其使用场景。今天，咱们一起看看。sqlserver下，自定义函数的使用。

四、如何使用自定义函数?

在使用之前，说清楚一件事儿很重要。那就是，在sqlserver 中有两种主要的 UDF 类型：一类是返回标量值的 UDF ，另一类则是返回表值的 UDF。

1、标量UDF：

   返回标量值的 UDF 最类似于许多编程语言所引用的作为函数的内容。它们返回由标量数据类型（例如，integer、varchar(n)、char(n)、money、datetime、bit，等等）组成的单一值。如果用户定义的数据类型 (UDDT) 基于标量数据类型，UDF 也可以返回这些数据类型。使用返回内联或多语句表的 UDF，可以通过表数据类型返回行集。然而，并非所有的数据类型都可以从 UDF 中返回。例如，UDF 无法返回下列数据类型中任何一个的值：text、ntext、image、cursor、或 timestamp。

   使用场景：

   UDF 可以用于多种情况，以使代码具有更好的可维护性、可重用性和更少的复杂性。当 T-SQL 代码的相同段在几个地方（可能由几个存储过程和批 SQL 语句）使用时，这会非常有用。例如，假定一个应用程序中的几个部分都需要查找产品是否必须重新订购。在每个需要此操作的地方，代码可以检查重新订购等级，并将它与库存量加订购量的和相比较。然而，因为这个代码在几个地方用到，所以可以改为使用 UDF 以减少代码块，并使得万一需要更改时维护函数更加容易。

  创建自定义函数的模板：

<span style="font-family:KaiTi_GB2312;font-size:18px;"> create function [架构名].[函数名]（[参数名 参数类型]，[参数名 参数类型]） returns 参数类型  as     begin      declare something you want      // edit your code herer     return something alse  end </span>

（示例代码）

<span style="font-family:KaiTi_GB2312;font-size:18px;">USE [School]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION (@studentId INT)RETURNS DECIMAL(3,2)ASBEGINDECLARE @avg DECIMAL(3,2);SELECT @avg = avg(Grade) FROM StudentGrade WHERE StudentID = @studentId;RETURN @avg;END</span>

   调用自定义的函数：

<span style="font-family:KaiTi_GB2312;font-size:18px;">use [shcool]go select    studentName,   studentClass,   sex,   [dbo].[AvgStudentGrade](007) as AvgStudentGradefrom  StudentGradewhere studentid = '007'</span>

2、表值UDF：

  表值 UDF 的类别中有两种子类型：返回内联表值的 UDF 和返回多语句表值的 UDF。返回内联表的 UDF 通过 SQL Server 表数据类型返回一个行集。它们使用构成函数体的单一 SELECT 语句进行定义。返回内联表值的 UDF 不能在定义它将返回的表的 SQL SELECT 语句之外包含其他 T-SQL 逻辑。然而，它们比返回多语句表的 UDF 要容易创建，因为它们不必定义要返回的确切表结构。返回内联表的 UDF 从 SELECT 语句本身推断行集的结构。因此，UDF 将返回的列由 SELECT 列表中的列确定。

  返回内联表值类型的UDF示例：

<span style="font-family:KaiTi_GB2312;font-size:18px;">CREATE FUNCTION fnGetEmployeesByCity (@sCity VARCHAR(30))    RETURNS TABLEASRETURN    (        SELECT    FirstName, LastName, Address        FROM    Employees        WHERE    City = @sCity     )GO</span>

调用示例：

<span style="font-family:KaiTi_GB2312;font-size:18px;">SELECT * FROM dbo.fnGetEmployeesByCity('seattle')</span>

注意：嵌套的UDF：

   UDF 可以互相嵌套，只要其中的 UDF 是先创建的即可。使用嵌套函数的一个 catch 是非确定性内置函数（例如 getdate 函数），不能在另一个 UDF 内嵌套（否则会引发 SQL Server 错误）。非确定性函数是用完全相同的参数调用多次时可能返回不同结果的函数。getdate 函数属于这一类，因为每次调用时，它会返回新的当前日期和时间。另一个常用的非确定性内置函数是 NewID 函数。它也是非确定性的，因为它总是返回唯一的 GUID，所以 NewID 函数同样不允许在 UDF 内嵌套。

五：对比（后续更新中，敬请期待……）

 1、sqlserver 函数和自定义函数

 2、sqlserver 中的函数和存储过程

参考文章：《如何使用自定义的函数在sqlserver中》