浅谈表值函数和标量值函数

表值函数有两种形式：

1.内联表值函数

Create FUNCTION Funtion_name
(  
    --这里定义传入参数以及类型
)
RETURNS TABLE
AS
RETURN
(
    --这里直接写查询语句，返回结果就是查询语句的结果集
)
GO

示例：

CREATE FUNCTION F_BConversion
(  
    @NUM1 INT,
    @NUM2 INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT @NUM1+@NUM2 Addition,@NUM1-@NUM2 Subtration,@NUM1*@NUM2 Multiplication,@NUM1/@NUM2 Division
)
GO

 

这个函数通过传入两个数字分别计算这两个数字加减乘除的结果，表值函数的使用调用方式就相当于表一样，可以直接select * from，也可以和其他表做关联， 只不过后面多个传入参数罢了！

 

2.多语句表值函数

CREATE FUNCTION Funtion_name
(
    --这里定义传入参数及类型
)
RETURNS
@table_name TABLE
(
    --这里定义@table_name的列名
)
AS
BEGIN
    --这里写sql语句并且将最终需要返回的结果集塞到@table_name 这张表里面
    RETURN 
END
GO

示例：

ALTER FUNCTION F_TConversion
(
    @NUM INT
)
RETURNS
@t_table TABLE
(
    [Binary] varchar(64),
    Octal varchar(16),
    Hexadecimal varchar(8)
)
AS
BEGIN
    DECLARE @RESULT2 VARCHAR(500)='',@RESULT8 VARCHAR(500)='',@RESULT16 VARCHAR(500)=''; 
    WITH CTE AS( 
        SELECT @NUM/2 D2,@NUM%2 S2,@NUM/8 D8,@NUM%8 S8,@NUM/16 D16,@NUM%16 S16,1 [INDEX] 
        UNION ALL 
        SELECT D2/2 , D2%2,D8/8 , D8%8,D16/16 , D16%16,[INDEX]+1 FROM CTE WHERE D2>0 
    ) 
    SELECT @RESULT2+=CAST(S2 AS VARCHAR(1))
          ,@RESULT8+=CASE WHEN D8=0 AND S8=0 THEN '' ELSE CAST(S8 AS VARCHAR(1)) END
          ,@RESULT16+=CASE WHEN D16=0 AND S16=0 THEN ''
                           ELSE CASE CAST(S16 AS VARCHAR(5))
                                WHEN '10' THEN 'A' 
                                WHEN '11' THEN 'B' 
                                WHEN '12' THEN 'C' 
                                WHEN '13' THEN 'D' 
                                WHEN '14' THEN 'E' 
                                WHEN '15' THEN 'F' 
                                ELSE CAST(S16 AS VARCHAR(5))
                            END
                        END
    FROM CTE ORDER BY [INDEX] DESC
    INSERT INTO @t_table
    SELECT @RESULT2,@RESULT8,@RESULT16
    RETURN 
END
GO

这个函数是根据我前段时间写的一个关于进制转换的列子改编的，这里通过传入一个十进制的数字，分别返回对应的二进制、八进制、十六进制。

 

标量值函数：

CREATE FUNCTION function_name
(
	--这里定义传入参数及类型
)
RETURNS 
	--这里需要定义返回值的类型
AS
BEGIN
	--定义返回值
	--写sql逻辑
	--返回返回值
END
GO

示例：

CREATE FUNCTION F_Conversion
(
    @NUM INT,
    @TYPE INT
)
RETURNS VARCHAR(100)
AS
BEGIN
    DECLARE @RESULT VARCHAR(500)=''; 
    WITH CTE AS( 
    SELECT @NUM/@type D,@NUM%@type S,1 [INDEX] 
    UNION ALL 
    SELECT D/@type , D%@type,[INDEX]+1 FROM CTE WHERE D>0 
    ) 
    SELECT @RESULT+=CASE CAST(S AS VARCHAR(5)) WHEN '10' THEN 'A' 
            WHEN '11' THEN 'B' 
            WHEN '12' THEN 'C' 
            WHEN '13' THEN 'D' 
            WHEN '14' THEN 'E' 
            WHEN '15' THEN 'F' 
            ELSE CAST(S AS VARCHAR(5)) END 
    FROM CTE ORDER BY [INDEX] DESC 
    RETURN @RESULT
END
GO

这个函数传入两个参数都是int型，分别是整数和返回的进制类型，这个例子将正整数10转换成对应的二进制。