[SQL] 使用者自訂函數應用

論壇問題

要在預設值（default）和計算欄位（Computered Column）內，把欄位名稱當成變數傳入使用者自訂函數（udf）內來填值

以下利用 TSQL 測試該情況

使用 AdventureWorks2014 DB，在內建立 Orders 和 OrdersDetail Table

USE [AdventureWorks2014]
GO

IF OBJECT_ID('Orders') IS NOT NULL
    DROP TABLE Orders

IF OBJECT_ID('OrdersDetail') IS NOT NULL
    DROP TABLE OrdersDetail

CREATE TABLE [dbo].[Orders](
    [OrderNO] [char](11) NOT NULL,
    [DetailCount] [int] NOT NULL, -- 之後要設定 udf(ColumnName) 用
    CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED([OrderNO] ASC)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrdersDetail](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [OrderNO] [char](11) NOT NULL CONSTRAINT [DF_OrdersDetail_Orders]  DEFAULT (''),
    [Product] [nchar](30) NOT NULL CONSTRAINT [DF_OrdersDetail_Product]  DEFAULT (''),
    [UnitPrice] [money] NOT NULL CONSTRAINT [DF_OrdersDetail_UnitPrice]  DEFAULT ((0)),
    [Qty] [int] NOT NULL CONSTRAINT [DF_OrdersDetail_Qty]  DEFAULT ((0)),
    CONSTRAINT [PK_OrdersDetail] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]
GO

建立使用者自訂函數

• uspGetDetailCount：用來取的 detail 內有多少筆資料
• uspGetTotalPrice：用來加總 detail 內金額

IF OBJECT_ID(N'dbo.uspGetDetailCount', N'FN') IS NOT NULL
    DROP FUNCTION dbo.uspGetDetailCount
GO

IF OBJECT_ID(N'dbo.uspGetTotalPrice', N'FN') IS NOT NULL
    DROP FUNCTION dbo.uspGetTotalPrice
GO

CREATE FUNCTION uspGetDetailCount(@OrderNO char(11))
RETURNS int
BEGIN
    RETURN
    (
        SELECT count(*)
        FROM OrdersDetail
        WHERE OrderNO = @OrderNO
    )
END
GO

CREATE FUNCTION uspGetTotalPrice(@OrderNO char(11))
RETURNS int
BEGIN
    RETURN
    (
        SELECT sum(UnitPrice * Qty)
        FROM OrdersDetail
        WHERE OrderNO = @OrderNO
    )
END

測試：設定 Orders DetailCount 欄位的 deafult 為 uspGetDetailCount

ALTER TABLE Orders 
    ADD CONSTRAINT [DF_Orders_DetailCount] 
    DEFAULT ( dbo.uspGetDetailCount(Order_NO) ) FOR DetailCount
GO

這個內容中不允許有名稱 "OrderNO"。有效的運算式包括常數、常數運算式及 (某些內容中) 變數。不允許使用資料行名稱。

MSDN CREATE TABLE 內的 default 說明

DEFAULT 定義中的 constant_expression 無法參考資料表中的另一個資料行，也無法參考其他資料表、檢視表或預存程序。

測試：在 Orders Table 內新增 TotalPrice 計算欄位，並設定利用 uspGetTotalPrice 為計算公式

-- 新增一個欄位、設定計算欄位，並插入資料來確認
ALTER TABLE dbo.Orders
    ADD TotalPrice AS dbo.uspGetTotalPrice(OrderNO)

-- 插入測試資料
INSERT INTO Orders (OrderNO,DetailCount) VALUES('20160519001',0)
INSERT INTO OrdersDetail (OrderNO,Product,UnitPrice,Qty) VALUES('20160519001',N'產品1',10,10)
INSERT INTO OrdersDetail (OrderNO,Product,UnitPrice,Qty) VALUES('20160519001',N'產品2',11,10)
INSERT INTO OrdersDetail (OrderNO,Product,UnitPrice,Qty) VALUES('20160519001',N'產品3',9,10)

-- 顯示計算欄位效果
SELECT * FROM Orders
SELECT * FROM OrdersDetail

延伸閱讀
• [SQL] 使用者自訂函數

參考資料
• 論壇問題
• CREATE TABLE