SQL Server函數小結

一、函數分類

　　函數分為純量涵式和資料表值函式。前者返回一個標量值結果，後者以表的形式返回結果。

二、純量涵式

　　純量涵式接受0個或多個輸入參數，並返回一個標量值。因為純量涵式只返回一個值，所以通常在一個select語句的列列表中使用它們，也可以在where子句中使用它們。

　　例：create function [dbo].[ufnGetStock]

　　　　( @ProductID [int])

　　　　returns [int]

　　　　as

　　　　begin

　　　　　　declare @ret int;

　　　　　　select @ret=sum(p.[Quantity]) from [production].[productInventory] p where p.[ProductID]=@productID and p.[locationID]='6';

　　　　　　if(@ret is null)

　　　　　　　　set @ret=0

　　　　　　return @ret

　　　　end;

　　使用函數：

　　　　select *,dbo.ufnGetStock(Production.Product.ProductID) from Production.Product;

　　備忘：1.函數有一些限制，不能使用函數來改變一個資料庫中的任何對象或該資料庫本身的狀態。因此，不能插入、更新或刪除表中的資料，也不能建立、修改或刪除資料庫中的對象。然而，可以建立一個或多個表變數。並對該表變數發出insert、update和delete 語句。

　　2.純量涵式只返回一個值。

三、資料表值函式

　　資料表值函式遵守與純量涵式相同的規則，區別在於資料表值函式返回一個表作為輸出。因此，一般在select語句的from子句中使用它們，並可能與其他表或視圖進行聯結。

　　例：

　　　　create function [dbo].[ufnGetContactInformation]

　　　　(

　　　　　　@ContactID int

　　　　)

　　　　return @retContactInformation table

　　　　(

　　　　　　[ContactID] int primary key not null,

　　　　　　[FirstName] [nvarchar](50) null,

　　　　　　[LastName] [nvarchar](50) null,

　　　　　　[JobTitle] [nvarchar](50) null,

　　　　　　[ContactType] [nvarchar](50) null

　　　　)

　　　　as

　　　　begin

　　　　　　declare

　　　　　　　　@FirstName [nvarchar] (50),

　　　　　　　　@LastName [nvarchar] (50),

　　　　　　　　@JobTitle [nvarchar] (50),

　　　　　　　　@ContactType [nvarchar] (50);

　　　　　　select

　　　　　　　　@ContactID=ContactID,

　　　　　　　　@FirstName=FirstName,

　　　　　　　　@LastName=LastName

　　　　　　from [Person].[Contact]

　　　　　　where [ContactID]=@ContactID;

　　　　　　set @JobTitle=

　　　　　　　　Case

　　　　　　　　　　when exists(select * from [HumanResources].[Employee] e where e.[ContactID]=@ContactID)

　　　　　　　　　　　　then (select [Title] from [HumanResources].[Employee] where [ContactID]=@ContactID)

　　　　　　　　　　when exists(select * from [Purchasing].[VendorContact] vc inner join [Person].[ContactType] ct on vc.[ContactTypeID]=ct.[ContactTypeID] where vc.[ContactID]=@ContactID)

　　　　　　　　　　　　then (select ct.[Name] from [Purchasing].[VendorContact] vc inner join [Person].[ContactType] ct on vc.[ContactTypeID]=ct.[ContactTypeID] where vc.[ContactID]=@ContactID)

　　　　　　　　　　when exists(select * from [Sales].[StoreContact] sc inner join [Person].[ContactType] ct on sc.[ContactTypeID]=ct.[ContactTypeID] where sc.[ContactID]=@ContactID)

　　　　　　　　　　then(select ct.[Name] from [Sales].[StoreContact] sc inner join [Person].[ContactType] ct on sc.[ContactTypeID]=ct.[ContactTypeID] where [ContactID]=@ContactID)

　　　　　　　　　　else null

　　　　　　　　end;

　　　　　　set @ContactType=

　　　　　　　　case

　　　　　　　　　　when exists(select * from [HumanResoures].[Employee] e where e.[ContactID]=@ContactID)

　　　　　　　　　　　　then 'Employee'

　　　　　　　　　　　　else null

　　　　　　　　　　end;

　　　　　　if(@ContactID is not null)

　　　　　　begin

　　　　　　　　insert @retContactInformation

　　　　　　　　select @contactID,@FirstName,@LastName,@JobTitle,@ContactType;

　　　　　　end;

　　　　　　return;

　　end;

　　　　使用函數：

　　　　　　select * from dbo.ufnGetContactInformation(1);

四、確定性函數與非確定性函數

　　1.確定性函數

　　　　對於相同的輸入值集合，每次調用確定性函數都會返回相同的值

　　2.非確定性函數

　　　　每次調用非確定函數時，可能會返回不同的結果。如果一個函數調用一個非確定性函數，或者該函數調用一個擴充的預存程序，則SQL Server也認為該函數是非確定性。

　　3.如果一個函數是非確定的，就不能索引該函數的結果，既不能通過調用該函數的計算資料行上的索引，也不能通過引用該函數的索引檢視表。