SQL Server函数小结
时间：2010-09-23 来源：moss_tan_jun
一、函数分类
函数分为标量函数和表值函数。前者返回一个标量值结果，后者以表的形式返回结果。
二、标量函数
标量函数接受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.如果一个函数是非确定的，就不能索引该函数的结果，既不能通过调用该函数的计算列上的索引，也不能通过引用该函数的索引视图。
相关阅读 更多 +
