4.4.1 SQL Server自带的用户自定义函数

4.4  使用自定义函数

打开Microsoft SQL Server Management Studio，展开任意一个数据库节点下的【可编程性】节点，如图4.27所示。读者会发现该节点下，除【函数】节点还没有介绍以外，其他【可编程性】功能已全部介绍过了。本节着重对SQL Server提供的函数功能进行介绍。

图4.27 【函数】节点

用户自定义函数(User Defined Functions，UDFs)是SQL Server提供的另一强大功能。借助UDFs，数据库开发人员可以实现复杂的运算操作，例如实现功能更加强大的聚合运算。UDFs返回的结果可以是一个单一数值(即标量值函数)，也可以是一个数据集(即表值函数)。实际上，前面介绍的存储过程和触发器概念都可以被归为用户自定义函数。

SQL Server从其2000版本开始就已向数据库开发人员提供了用户自定义函数功能。开发人员可以使用Transact-SQL根据需要开发自己所需的自定义函数。而进入SQL Server 2005后，与存储过程、触发器以及自定义类型一样，自定义函数也可以由CLR实现，即通过高级语言实现自定义函数。由此可见，CLR功能极大地扩展了SQL Server数据的编程能力。

虽然用户自定义函数与存储过程和触发器有很多共同点，但是与它们相比，自定义函数具有某些更加灵活的特点。例如，从上面的示例可以看出，虽然一个存储过程可以返回一组数据集，但是存储过程却不能作为其他查询表达式的一部分。简单地说，存储过程不能出现在查询语句中的FROM之后。而下面介绍的用户自定义函数则可以出现在一个查询语句的FROM之后。如果自定义函数返回一组数据集，那么仍然可以在该数据集的基础上进一步执行查询操作。除此之外，开发人员还可以设计一个只返回特定字段的数据集，这一功能类似于一个视图，但与一个简单的视图不同，采用自定义函数实现的视图，可以根据需要接受相应的参数。

4.4.1  SQL Server自带的用户自定义函数

SQL Server 2005为数据库开发人员提供了一些自带的用户自定义函数。其中，一些用户自定义函数由SQL Server数据库服务器自身以及SQL Server Management Studio等数据库管理工具，数据库服务器以及客户端所使用。下面简要介绍一些常见的用户自定义函数。与大部分存储过程不一样，用户无法修改这些用户自定义函数。很多情况下，用户甚至无法使用存储过程sp_help或sp_helptext来查看这些函数的Transact-SQL脚本代码。SQL Server为用户自定义函数提供了专门的系统数据表syscomments来保存这些用户自定义函数的定义信息。

SQL Server提供的内置系统函数的前缀分为两类，即dm_和fn_。其中fn_为常见的函数(function)缩写，而前缀dm_则是SQL Server 2005新添加的功能——动态管理(Dynamic Management)功能的缩写。SQL Server 2005提供的动态管理功能包括两类，即动态管理视图(Dynamic Management View，DMV)和动态管理函数(Dynamic Management Function，DMV)。

【示例6】dm_exec_cursors函数应用。

例如，dm_exec_cursors就是DMF中的一个。该动态管理函数用于返回当前SQL Server数据库中正处于执行状态中的游标信息，该函数的应用过程如下。

(1) 在【查询编辑器】中输入以下SQL脚本代码：

USE Northwind
GO
SELECT * FROM sys.dm_exec_cursors(0)
GO

DECLARE cEmploy CURSOR STATIC
FOR
SELECT EmployeeID,FirstName
FROM Employees
WHERE TitleOfCourtesy = 'Mr.'
ORDER BY EmployeeID
GO

SELECT * FROM sys.dm_exec_cursors(0)
GO

OPEN cEmploy
GO

SELECT * FROM sys.dm_exec_cursors(0)
GO

CLOSE cEmploy
GO

SELECT * FROM sys.dm_exec_cursors(0)
GO

DEALLOCATE cEmploy
GO

SELECT * FROM sys.dm_exec_cursors(0)
GO

(2) 单击【执行】按钮，执行后的结果如图4.28所示。

图4.28 运行结果

技巧    
上述SQL脚本代码中的

SELECT * FROM sys.dm_exec_cursors(0)

语句用于返回当前用户进程的游标信息。该游标的参数为spid，即当前用户进程的会话ID。开始时，由于还没有创建任何游标，因此使用上述命令时返回的记录为空。接下来声明了一个在第一章中创建的游标，再次使用上述命令查看当前的游标使用状态时，将返回一条与该游标相关的信息记录。当执行完Deallocate命令释放该游标后，再次执行上述SQL脚本，此时由于游标已被释放，因此会再次返回空记录。 

提示    
比较CLOSE cEmploy和DEALLOCATE cEmploy两条Transact-SQL语句的执行效果可以发现，关闭一个游标并没有彻底地释放游标，实际上该语句的功能只是释放了该游标所关联的数据库资源，如数据集等。如果想彻底释放一个游标占有的所有资源，应使用DEALLOCATE命令。详细内容在第1章中已进行了详细介绍。 

【示例7】动态管理函数dm_exec_sessions的应用。

动态管理函数dm_exec_sessions用于返回当前与SQL Server数据库服务器连接的会话信息。例如，如果想列出与登录当前SQL Server数据库服务器的所有账号相关的会话连接信息，可按照下面步骤进行设置。

(1) 在【查询编辑器】中输入下面的Transact-SQL脚本代码：

SELECT login_name, 

COUNT(session_id) as session_count FROM sys.dm_exec_sessions GROUP BY login_name

(2) 单击【执行】按钮，其结果如图4.29所示。

图4.29 运行结果

(3) 由于当前只有账号sa登录了SQL Server数据库服务器，因此只有一条记录。如果想进一步查看与账号sa相关的所有会话的详细信息，可以在【查询编辑器】中输入下面的Transact-SQL脚本代码：

SELECT *  FROM sys.dm_exec_sessions

(4) 单击【执行】按钮，其结果如图4.30所示。

硕图4.30 运行结果

提示    
利用SQL Server 2005提供的动态管理视图和函数可以方便地查看SQL Server 2005中各实例的相关信息，便于数据库管理人员了解当前数据库进程的阻塞信息(使用sys.dm_exec_requests)以及用户登录情况(sys.dm_exec_sessions)等，详细内容请参见Microsoft提供的联机帮助文档。 

【示例8】fn_dblog函数应用。
除上面介绍的动态管理函数之外，SQL Server还提供了大量常规的函数，例如，函数fn_dblog可以将SQL Server的事务日志以数据表记录的形式返回给用户。该函数的使用形式如下所示。

::fn_dblog(@StartingLSN, @EndingLSN)

其中，该函数的两个参数分别表示起始的LSN号和结束的LSN号。默认情况下，可以使用空值。如果将@StartingLSN设置为空，则表示从首日志记录开始查询，如果@EndingLSN为空值，则表示一直查询到日志的尾记录为止。

提示    
LSN，是Log Sequence Number，即日志序列号的简称，SQL Server使用事务日志来记录发生在数据库服务器的事务。 

如果想查看与数据库Northwind相关的日志记录，可以按照下面步骤进行设置。
(1) 在【查询编辑器】中输入下面的Transact-SQL语句：

USE Northwind
SELECT * FROM ::fn_dblog('', '')
GO

(2) 单击【执行】按钮，与数据库Northwind相关的每条日志信息将以数据集的形式返回，如图4.31所示。

图4.31 日志信息

(3) 此外，也可以像设计其他普通查询语句那样设置查询条件，如下所示：

USE Northwind
SELECT * FROM ::fn_dblog('', '') WHERE [Begin Time] >= '02/01/07'
GO

(4) 单击【执行】按钮，其运行结果如图4.32所示。

图4.32 运行结果

提示    
使用fn_dblog可以监视诸如数据表更新、插入和删除等事务操作。因此执行这些操作后，数据库事务日志中会有相应的记录出现。 

【示例9】fn_get_sql函数的应用。

对于DBA(数据库管理员)来说，SQL Server 2005提供的另外一个强大的函数是fn_get_sql。通过该函数，数据库管理员和开发人员可以很轻松地获取正在被一个SQL进程执行的SQL文本。因此，如果需要诊断或调试出现的某种死锁或阻塞问题，使用该函数将显得非常有用。fn_get_sql的语法约定如下所示。

fn_get_sql ( SqlHandle )

其中SqlHandle为指定的SQL句柄。函数将返回指定SQL句柄的文本。SQL Server 2005的系统视图Sys.Sysprocesses中包含着当前Microsoft SQL Server实例中运行的所有进程信息。其中包含了cmd字段，即当前执行的命令，以及sqlhandle字段，即当前正在执行的批处理语句或对象的句柄。除此之外，SQL Server中还提供了一个名为sys.dm_exec_requests的动态管理视图。该视图中包含当前SQL Server的每一个请求的相关信息，其中包括字段sql_handle。通过访问这两个视图Sys.Sysprocesses和sys.dm_exec_requests，就可以获取与指定sql_handle相关的所有信息，具体设置过程如下。

(1) 在【查询编辑器】中输入以下SQL脚本代码：

SELECT *
FROM master.dbo.sysprocesses

(2) 单击【执行】按钮，执行上述SQL脚本，其结果如图4.33所示。

图4.33 运行结果

(3) 继续在【查询编辑器】中输入下面的SQL脚本代码：

SELECT * 
FROM sys.dm_exec_requests

(4) 单击【执行】按钮后的结果如图4.34所示。

(5) 由图4.33和图4.34可以看出系统视图Sys.Sysprocesses和动态管理视图sys.dm_exec_requests中都提供了与当前进程相关的sql_handle，以及与该进程相关的命令。因此，可通过从这两个视图中获取所需的sql_handle，然后再使用系统函数fn_get_sql来获取该进程正在执行的SQL文本。在【查询编辑器】中输入下列的SQL脚本代码：

DECLARE @Handle VARBINARY(64);
SELECT @handle = sql_handle
    FROM master.dbo.sysprocesses
    WHERE spid = @@SPID
SELECT * FROM ::fn_get_sql(@Handle);
GO

图4.34 运行结果

提示    
上述代码中变量@@SPID为当前正在执行的SPID，显然，该SPID应指向查询语句SELECT @handle = sql_handle FROM master.dbo.sysprocesses WHERE spid =@@SPID。在通过上述查询语句获得一个sql_handle后，就可以使用系统函数fn_get_sql来获得该sql_handle所指定的进程的SQL文本了，如图4.35所示。

图4.35 运行结果

提示    
仔细观察上面的系统函数使用方法就会发现，使用fn_为前缀的系统函数时，需要加带前缀符::。