SQL Server自帶的用戶自定義函數
2024-08-31 00:53:08
供稿：網友
使用自定義函數
打開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_為前綴的系統函數時，需要加帶前綴符::。
