~楓花雪岳~: [SQL] 使用者自訂函數

星期五, 2月 28, 2014

[SQL] 使用者自訂函數

整理使用者自訂函數的應用方式

純量值函數（Scalar Function）
資料表值函數（Table-Valued Function）
- 內嵌資料表值函數（Inline Table-Valued Function）
- 多種陳述式資料表值函數（MultiStatement Table-Valued Function）

純量值函數（SF）

使用自定函數來進行字串連結

USE [AdventureWorks2012]
GO

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

-- 建立 FUNCTION
CREATE FUNCTION getCommaString(@SalesOrderID int) 
RETURNS varchar(500)  -- 500若不夠，請加大 
BEGIN
    DECLARE @string varchar(500)  -- 500若不夠，請加大 
    SET @string = ''
  
    SELECT @string = @string + CAST(ProductID AS VARCHAR(50)) + ','
    FROM [Sales].[SalesOrderDetail]
    WHERE SalesOrderID = @SalesOrderID
    ORDER BY ProductID 
  
    SET @string = LEFT(@string,LEN(@string)-1) -- 把最後面的逗號後刪除
    RETURN @string
END
GO

-- 使用自定函數來進行字串連結 
SELECT
    SalesOrderID ,
    dbo.getCommaString(SalesOrderID) AS Lists
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate = '20050701'

使用自定函數來計算訂單平均

USE [AdventureWorks2012]
GO

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

CREATE FUNCTION dbo.ProdAVGUnitPrice(@ProductID int) 
RETURNS smallmoney
BEGIN
    RETURN
        (
            SELECT ISNULL(AVG(UnitPrice * (1 - UnitPriceDiscount)),0) AS AVGUnitPrice
            FROM [Sales].[SalesOrderDetail]
            WHERE ProductID = @ProductID
        )
END
GO

SELECT 
    ProductID , 
    Name , 
    dbo.ProdAVGUnitPrice(ProductID) AS AVGUnitPrice
FROM [Production].[Product]
ORDER BY AVGUnitPrice DESC

內嵌資料表值函數（ITVF）

內嵌資料表值函數像是帶有參數的 VIEW

USE [AdventureWorks2012]
GO

IF OBJECT_ID(N'dbo.CustomerOrder') IS NOT NULL
    DROP FUNCTION dbo.CustomerOrder
GO

CREATE FUNCTION dbo.CustomerOrder(@customerid int,@year int)
RETURNS TABLE
AS
RETURN
  (
      SELECT 
          SOH.CustomerID AS 客戶編號,
          YEAR(SOH.OrderDate) AS 年份,
          SUM(ROUND(SOD.OrderQty * SOD.UnitPrice * (1 - SOD.UnitPriceDiscount),0)) AS 總金額
      FROM [Sales].[SalesOrderHeader] AS SOH 
          JOIN [Sales].[SalesOrderDetail] AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID  
      WHERE SOH.CustomerID  = @customerid
          AND YEAR(OrderDate) = @year
      GROUP BY SOH.CustomerID,YEAR(OrderDate)
  ) 
GO

SELECT * FROM dbo.CustomerOrder(30117,2005)
GO

多種陳述式資料表值函數（MSTVF）

利用 Table-Valued Parameter (TVP) 來當成回傳值，可以建立 Clustered Index，SQL Server 2014 開始可以在 TVP 上建立 nonclustered index

USE [AdventureWorks2012]
GO

IF OBJECT_ID('dbo.EmployLists') IS NOT NULL
    DROP FUNCTION dbo.EmployLists
GO

CREATE FUNCTION dbo.EmployLists (@type varchar(30))
RETURNS @TVP TABLE 
    (
      EmpID int Primary key , 
      EmpNameName nvarchar(100)
    )
AS
  BEGIN
      IF @type = 'LongName'
          INSERT INTO @TVP (EmpID,EmpNameName)
          SELECT 
              E.BusinessEntityID ,
              CONCAT(P.LastName,'-',P.FirstName)
          FROM [HumanResources].[Employee] AS E
              JOIN [Person].[Person] P ON E.[BusinessEntityID] = P.[BusinessEntityID]
      ELSE
          INSERT INTO @TVP (EmpID,EmpNameName)
          SELECT 
              E.BusinessEntityID ,
              P.LastName
          FROM [HumanResources].[Employee] AS E
              JOIN [Person].[Person] P ON E.[BusinessEntityID] = P.[BusinessEntityID]   

      RETURN
  END
GO

SELECT * FROM dbo.EmployLists('LongName')