使用者自訂函數

1. 使用者自訂函數 T.Q. LEE

2. 使用者自訂函數的介紹 • 使用者自訂函數是SQL Sever 2000新增的資料庫物件，它和預存程序、檢視表是很類似的，都是由多行T-SQL敘述句所組成的程式單元。 • 但是它們之間還是有一些非常明顯的差異：

3. 預存程序可以經由參數來傳回資料 (必須將參數設為OUTPUT；檢視表不能有參數)；但使用者自訂函數則只能接收參數，而不能由參數傳回資料。

4. 預存程序只可以傳回一個整數值；檢視表不能傳回值；而使用者自訂函數則可以傳回各種資料型別的值 (但不包括text、ntext、image、cursor及rowversion)，甚至可以傳回sql_variant及table型別。

5. 在預存程序、檢視表和使用者自訂函數中可以做任何的資料異動，例如新增、刪除或修改資料，另外預存程序可以更改資料庫的設定；但檢視表和使用者自訂函數則不允許更改資料庫的狀態或內容。

6. 預存程序必須以EXECUTE來執行，因此不能在運算式之中使用。而使用者自訂函數則可融入運算式中使用，並以傳回值來取代其名稱。

7. 一般而言，使用者自訂函數較適用於擷取或計算資料，然後再將結果傳回到呼叫它的運算式或SQL敘述句的其他子句中使用。

8. 使用者自訂函數的好處 • 1.它可以讓您建立類似檢視表的程式單元，但可以接受參數。 • 2.您可以像執行預存程序一樣地去呼叫使用者自訂函數。 • 3.您可以將它融入運算式中。 • 4.它可以傳回一個資料表，所以可以放置在FROM子句內。

9. 5.它可以讓您在IN運算子內使用使用者自訂函數的結果。5.它可以讓您在IN運算子內使用使用者自訂函數的結果。 • 6.它可以讓您在WHERE子句中將使用者自訂函數當做子查詢來使用。 • 7.它可以讓您合併2個使用者自訂函數的結果，但不需要使用到暫存資料表來儲存中間的結果。 • 8.它可以讓您針對任何特殊的資料庫應用上的需要來建立新的數學函數。

10. 使用者自訂函數的種類 • 1.純量函數(scalar) • 2.內嵌資料表值函數(Inline table_valued) • 3.多陳述式資料表值函數(Multistatement table_valued)

11. 使用純量函數的場合 • 純量函數可以用在任何運算式可以被接受的地方。 • 1.在SELECT敘述句內的SELECT子句，當做是運算式的一部分或者是單純的欄位。 • 2.在任一DML敘述句 (SELECT,UPDATE,INSERT,DELETE) 中的FROM子句，當做是一個單獨欄位和列的導出資料表。

12. 3.在任一DML敘述句中的FROM子句，當做是在ON子句內聯結條件的一部分。3.在任一DML敘述句中的FROM子句，當做是在ON子句內聯結條件的一部分。 • 4.在任一DML敘述句內的WHERE子句。 • 5.在任一DML敘述句內的HAVING子句。 • 6.在GROUP BY子句內，當做是群組條件的一部分。

13. 7.在任一敘述句內的ORDER BY子句，當做是排序的準則。 • 8.在UPDATE敘述句內的SET子句，當做是新增至資料表的一個值。 • 9.當做是一個欄位的一個預設值。 • 10.放置在一個CHECK CONSTRAINT定義內。

14. 11.放在一個CASE運算式內。 • 12.當做是IF或WHILE敘述句的部分條件。 • 13.如果純量函數傳回一個字串，則可放在PRINT敘述句。 • 14.當做是須要計算的欄位的部分定義。

15. 15.當做是呼叫預存程序或另一個使用者自訂函數的參數。15.當做是呼叫預存程序或另一個使用者自訂函數的參數。 • 16.當做是另外一個純量函數傳回的值。 • 17.如果純量函數傳回一個整數值，則可當做預存程序傳回的一個值。

16. 建立純量函數 (Scalar function) • CREATE FUNCTION function_name • ( [ {@param_name scalar_data_type [=default] } [,…n] ] ) • RETURNS scalar_return_data_type • [WITH <function_option> [,] …n] ] • [AS] • BEGIN • function_body • RETURN scalar_expression • END • <function_option> ::= • [ENCRYPTION | SCHEMABINDING]

17. 範例26-4.1 • CREATE FUNCTION dbo.max_two • (@a1 AS int,@a2 AS int) • RETURNS int • AS • BEGIN • RETURN CASE • WHEN @a1 >= @a2 THEN @a1 • WHEN @a2 > @a1 THEN @a2 • ELSE NULL • END • END

18. 呼叫使用者自訂函數 • 當您要參考或叫用 (Invoke) 一個使用者自訂函數時，必須指定函數的名稱，並在後面加上括弧。 • 您可在括弧中指定稱為引數 (Argument) 的運算式，它會提供要傳給參數的資料。 • 呼叫用函數時，不得在引數中指定參數名稱。 • 當您叫用函數時，必須為所有參數提供引數值，指定引數值的順序應與在CREATE FUNCTION陳述式中定義參數的順序相同。

19. 例如範例26-4.1dbo.max_two的函數所傳回的整數，是以兩個整數參數來定義的，則可利用下列運算式來叫用，此例子可以找出兩個整數的最大值。例如範例26-4.1dbo.max_two的函數所傳回的整數，是以兩個整數參數來定義的，則可利用下列運算式來叫用，此例子可以找出兩個整數的最大值。

20. 範例26-4.2 • SELECT dbo.max_two(100,200)

22. 範例26-4.3 • CREATE FUNCTION CheckFax(@Fax nvarchar(24)) • RETURNS nvarchar(24) • AS • BEGIN • DECLARE @temp nvarchar(24) • IF @Fax IS NULL • SET @temp = 'Undefined' • ELSE • SET @temp = @Fax • RETURN @temp • END

23. 範例26-4.4 1.在SELECT敘述句內的SELECT子句，當做是運算式的一部分或者是單純的欄位 • SELECT SupplierID , CompanyName , country , Fax = dbo.CheckFax(Fax) • FROM Suppliers

25. CREATE FUNCTION CheckDiscount(@Discount real) • RETURNS nvarchar(15) • AS • BEGIN • DECLARE @temp nvarchar(24) • SET @temp = CASE @Discount • WHEN 0 THEN 'No Discount' • WHEN 0.01 THEN '1% Discount' • WHEN 0.02 THEN '2% Discount' • WHEN 0.03 THEN '3% Discount' • WHEN 0.04 THEN '4% Discount' • WHEN 0.05 THEN '5% Discount' • WHEN 0.06 THEN '6% Discount' • WHEN 0.1 THEN '10% Discount' • WHEN 0.15 THEN '15% Discount' • WHEN 0.2 THEN '20% Discount' • WHEN 0.25 THEN '25% Discount' • END • RETURN @temp • END

26. SELECT OrderID,ProductID, Quantity,Discount=dbo.CheckDiscount(Discount) • FROM [Order Details]

28. 6.在GROUP BY子句內，當做是群組條件的一部分 • SELECT Discount=dbo.CheckDiscount(Discount) , count(*) Count • FROM [Order Details] • GROUP BY dbo.CheckDiscount(Discount)

30. 範例26-4.5 • CREATE FUNCTION dbo.booktype(@title_id varchar(6)) RETURNS varchar(20) • AS BEGIN • DECLARE @temp varchar(20) • SELECT @temp= • CASE substring(@title_id,1,2) • WHEN 'BU' THEN 'Business' • WHEN 'MC'THEN 'Modern Cooking' • WHEN 'PC' THEN 'Popular Computing' • WHEN 'PS' THEN 'Psychology' • WHEN 'TC' THEN 'Traditional Cooking' • END • RETURN @temp • END

31. 範例26-4.6 1.在SELECT敘述句內的SELECT子句，當做是運算式的一部分或者是單純的欄位 • SELECT title, Booktype=dbo.booktype(title_id),price • FROM titles

32. CREATE FUNCTION fAvgQuantity() • RETURNS numeric(6,2) • AS • BEGIN • RETURN (SELECT avg(Quantity) FROM [Order Details]) • END

33. 1.在SELECT敘述句內的SELECT子句，當做是運算式的一部分或者是單純的欄位1.在SELECT敘述句內的SELECT子句，當做是運算式的一部分或者是單純的欄位 • SELECT OrderID,Quantity,Quantity-dbo.fAvgQuantity() FROM [Order Details] • SELECT OrderID,Quantity,dbo.fAvgQuantity(),Quantity-dbo.fAvgQuantity() FROM [Order Details]

35. 4.在任一DML敘述句內的WHERE子句 • SELECT OrderID,Quantity FROM [Order Details] • WHERE Quantity > dbo.fAvgQuantity()

37. 5.在任一DML敘述句內的HAVING子句 • SELECT OrderID,avg(Quantity) FROM [Order Details] • GROUP BY OrderID • HAVING avg(Quantity) > dbo.fAvgQuantity()

39. 6.在任一DML敘述句內的ORDER BY子句 • SELECT OrderID,Quantity,Quantity-dbo.fAvgQuantity() • FROM [Order Details] • ORDER BY Quantity-dbo.fAvgQuantity()

41. CREATE FUNCTION dbo.fMaxUnitPrice(@CategoryID int) • RETURNS money • AS • BEGIN • RETURN (SELECT max(UnitPrice) FROM Products WHERE CategoryID=@CategoryID) • END • CREATE FUNCTION dbo.fMinUnitPrice(@CategoryID int) • RETURNS money • AS • BEGIN • RETURN (SELECT min(UnitPrice) FROM Products WHERE CategoryID=@CategoryID) • END

42. SELECT ProductID , ProductName , UnitPrice FROM Products • WHERE UnitPrice between dbo.fMinUnitPrice(1) and dbo.fMaxUnitPrice(1)

44. 範例26-4.7 • CREATE FUNCTION CheckEmployeeID(@id int) • RETURNS int • AS • BEGIN • DECLARE @theCount int • SELECT @theCount = count(EmployeeID) FROM Employees • WHERE EmployeeID = @id • RETURN @theCount • END

45. 範例26-4.8 12.當做是IF或WHILE敘述句的部分條件 • DECLARE @id int • SET @id = 8 • IF (dbo.CheckEmployeeID(@id)) = 0 • PRINT 'The Employee ID: ' + convert(varchar(2),@id) +' was not found' • ELSE • PRINT 'The Employee ID: ' + convert(varchar(2),@id) +' is a correct ID'

47. 範例26-4.9 • CREATE FUNCTION CheckShipperID(@id char(4)) • RETURNS int • AS • BEGIN • DECLARE @theCount int • SELECT @theCount = count(ShipperID) FROM Shippers • WHERE ShipperID = @id • RETURN @theCount • END

48. 11.放在一個CASE運算式內13.如果純量函數傳回一個字串，則可放在PRINT敘述句11.放在一個CASE運算式內13.如果純量函數傳回一個字串，則可放在PRINT敘述句 • DECLARE @id int • SET @id = 4 • PRINT 'The Shippers ID: ' + convert(varchar(2),@id) + • CASE (dbo.CheckShipperID(@id)) • WHEN 0 THEN ' was not found' • WHEN 1 THEN ' is a correct ID' • END

50. 使用內嵌資料表值函數的場合 • 內嵌資料表值函數可以用在任何資料表或檢視表可以被接受的地方。 • 1.在SELECT敘述句內的SELECT子句，當做是會傳回一個值 (一個單一列和單一欄位的資料集) 的子查詢的一部分。