使用者定義函數建議

使用者定義函數建議
本節包含有關使用使用者定義函數的建議和提示，包括有關標量和資料表值函式的資訊、更改架構對函數的可能影響以及用以簡化複雜函數的嵌套函數的使用。

純量涵式的用途
當需要在代碼中的多個位置進行相同的數學計算時，純量涵式十分有用。例如，如果應用程式中遍布基於百分率、本金和年份的利息計算，則可以將它編碼為可調用的函數，如下
所示。

create function calc_interest ( @principal int , @rate numeric(10,5) , @years int )
returns int
as
begin
    declare @interest int
    set @interest = @principal * @rate * @years / 100
    RETURN(@interest)
end

使用系統函數作為構件塊
系統函數可以用作使用者定義函數的構件塊。例如，如果需要計算某個數位四倍值，使用 SQUARE 系統函數來得到該值，而不必從頭編寫整個函數。

將函數嵌套以劃分和簡化複雜函數
允許將函數嵌套；因此，將複雜函數劃分為較簡單的函數，並將這些較簡單的函數一起使用以得到結果可能效果更好。將複雜函數劃分為較小的函數的優點是，該代碼可以在應用
程式中的更多地方重新使用。

例如，假定需要計算一小塊土地的面積，且輸入單位可以是米或英尺，但面積必須始終以平方英尺為單位顯示。可以將任務劃分為兩個函數，而不是編寫一個函數來完成所有工作
：

cnvt_meters_feet 進行從米到英尺的轉換

calc_Area_ft 以英尺為單位計算面積
這樣，便可以在代碼的其它位置使用 cnvt_meters_feet 函數。

USE pubs
GO
CREATE FUNCTION cnvt_meters_feet ( @value numeric(10,3) )
RETURNS numeric(10,3)
AS
BEGIN
    DECLARE @ret_feet numeric(10,3)
    SET @ret_feet = @value * 3.281 ---1 Meter=3.281 Feet
    RETURN(@ret_feet)
END
GO
CREATE FUNCTION calc_area_ft ( @length numeric(10,3), @width numeric(10,3), @Unit char(2) )
RETURNS numeric(10,3)
AS
BEGIN
    DECLARE @area numeric(10,3)
    ---Check for unit, if meters(MT), convert it to feet(FT)
    IF @Unit = 'MT'
    BEGIN
        SET @length = pubs.dbo.cnvt_meters_feet( @length )
        SET @width = pubs.dbo.cnvt_meters_feet (@width )
    END
    ---Calculate Area
    SET @area = @length * @width
    RETURN ( @area )
END
GO
SELECT pubs.dbo.calc_area_ft ( 100.0, 50.0, 'MT') AS 'Area in Feet'
SELECT pubs.dbo.calc_area_ft ( 100.0, 50.0, 'FT') AS 'Area in Feet'
go

避免返回所有行的預設情況
當將函數的輸入參數用作 WHERE 子句中的條件時，應為所有可能的值考慮返回的行數。

例如，如果使用條件"WHERE name like '@value%'"作為唯一條件，並依賴使用者指定起始值，但使用者未指定任何值，那麼此 WHERE 條件轉換為"WHERE name like '%'"，這將返回表
中的所有行。這對於具有數百萬行的表是十分有害的。為避免這種過多的結果集，可以實現一種預設檢查機制，以便當未指定輸入時只返回行的一部分。

考慮對架構更改的影響
如果函數中使用了"SELECT * FROM <table>"，應考慮函數建立後對架構更改的影響。如果函數未用 SCHEMA_BINDING 選項建立，則結果中不反映對架構的更改。

例如，如果在函數建立之後向表添加一個新列，並且該函數不是 SCHEMA 綁定的，則結果集中將不顯示新列。如果在函數建立之後刪除一個列，並且該函數不是 SCHEMA 綁定的，
則結果集中已刪除列處將顯示 NULL 值。

使用子集來合并預存程序和使用者定義函數
可以定義資料表值函式來返回寬的結果集。於是不同的使用者可以使用結果的子集來檢索資料。這可以用來合并多個預存程序或使用者定義函數。

例如，可以建立如下函數：

FunctionA 返回 TableA 的 Col1、Col2、Col3、... Col10

FunctionB 返回 FunctionA 的 Col1、Col3。

FunctionC 返回 FunctionA 的 Col2、Col4。
現在不同的使用者可以通過使用 FunctionB 或 FunctionC 來檢索較小的子集。他們還可以通過使用簡單的 SELECT 語句來選擇 FunctionA 返回的列的子集。
例如：Select Col10 from FunctionA。

消除暫存資料表使用
多語句資料表值函式可用於消除中間結果處理的暫存資料表使用。

何時將預存程序轉換為資料表值函式
評估轉換的原因；不要僅為了一致便將預存程序轉換為資料表值函式。儘管預期會有某些改善，但是請仔細測試轉換以確認例外情況，並檢查是否有不希望的副作用。