顾名思义:表值函数返回的是表,而标量值函数可以返回基类型
一、表值函数
用户定义表值函数返回 table 数据类型,表是单个 SELECT 语句的结果集。
以下示例:
CREATE FUNCTION F3_GetEmployeeSalary
(
@EmployeeID VARCHAR(50) --参数
)
RETURNS TABLE --返回类型为表
AS
RETURN
(
SELECT * FROM dbo.TEmployee
WHERE EmployeeID = @EmployeeID --通过一条sql查询语句获取表中数据
)
--函数调用
SELECT * FROM F3_GetEmployeeSalary('1')
注意:
(1)只能返回Table,returns后边一定是table
(2)as后面没有begin/end,只有一个return语句返回特定的记录
对于多语句表值函数,在 BEGIN...END 语句块中定义的函数体包含一系列 Transact-SQL 语句,这些语句可生成行并将其插入将返回的表中。
以下示例:
CREATE FUNCTION [dbo].[F_SQLSERVER_SPLIT](@Long_str varchar(8000),@split_str varchar(100))
RETURNS @tmp TABLE(
ID int IDENTITY PRIMARY KEY,
short_str varchar(8000)
)
AS
BEGIN
DECLARE @long_str_Tmp varchar(8000),@short_str varchar(8000),@split_str_length int
SET @split_str_length = LEN(@split_str)
IF CHARINDEX(@split_str,@Long_str)=1
SET @long_str_Tmp=SUBSTRING(@Long_str,@split_str_length+1,LEN(@Long_str)-@split_str_length)
ELSE
SET @long_str_Tmp=@Long_str
IF CHARINDEX(REVERSE(@split_str),REVERSE(@long_str_Tmp))>1
SET @long_str_Tmp=@long_str_Tmp+@split_str
ELSE
SET @long_str_Tmp=@long_str_Tmp
IF CHARINDEX(@split_str,@long_str_Tmp)=0
Insert INTO @tmp select @long_str_Tmp
ELSE
BEGIN
WHILE CHARINDEX(@split_str,@long_str_Tmp)>0
BEGIN
SET @short_str=SUBSTRING(@long_str_Tmp,1,CHARINDEX(@split_str,@long_str_Tmp)-1)
DECLARE @long_str_Tmp_LEN INT,@split_str_Position_END int
SET @long_str_Tmp_LEN = LEN(@long_str_Tmp)
SET @split_str_Position_END = LEN(@short_str)+@split_str_length
SET @long_str_Tmp=REVERSE(SUBSTRING(REVERSE(@long_str_Tmp),1,@long_str_Tmp_LEN-@split_str_Position_END))
IF @short_str<>'' Insert INTO @tmp select @short_str
END
END
RETURN
END
--函数调用
select * from F_SQLSERVER_SPLIT('a,b,c',',')
注意:
(1)returns后面直接定义返回的表类型,首先是定义表名,表明前面要加@,然后是关键字TABLE,最后是表的结构。(实质上是定义了一个表变量)
(2)在begin/end语句块中,直接将需要返回的结果insert到returns定义的表中就可以了,在最后return时,会将结果返回。
(3)最后只需要return,return后面不跟任何变量。(返回的是表变量里面的数据)
二、标量值函数
以下示例:
CREATE FUNCTION dbo.F3_GetMax
(
@a INT, --函数的两个参数
@b INT
)
RETURNS INT --返回类型为INT
AS
BEGIN
DECLARE @max INT;
IF(@a>=@b)
BEGIN
SET @max = @a;
END
ELSE
BEGIN
SET @max = @b
END
RETURN @max; --最后return返回@max中的值
END
--调用函数
SELECT dbo.F3_GetMax(2,3);
注意:(1)必须使用两部分组成函数的名字来调用函数,即所有者.对象名,如dbo.F3_GetMax(2,3)
(2)所有的传入参数前必须加@
(3)不要写漏和写错关键字,如as,returns,return
(4)returns后面不是跟一个变量,而是跟变量的返回值类型
(5)在begin/end语句块中,使用的是return