sqlserver 中的表值函数和标量函数

目录

一、表值函数

1.内联表值函数

1.创建函数

2.调用函数

3.返回结果

2.多语句的表值函数

2.调用函数

3.返回结果

3.内联表值函数和多语句的表值函数的区别

1.语法上

2.结构上

二、标量函数

1.创建函数

2.调用函数

2.返回结果

总结

---

一、表值函数

表值函数是返回一个Table类型 Table类型相当与一张存储在内存中的一张虚拟表，

表值函数 又分为内联表值函数和多语句表值函数

1.内联表值函数

创建内联表值函数很简单。内联表值函数的内容是一条带参数的SELECT语句。返回数据类型永远是表，不过返回表的结构由来SELECT语句的结构定义

    1.创建函数

        代码如下（示例）：

CREATE FUNCTION test
(	
 @id  int --入参
)
RETURNS TABLE  --返回类型为表
AS
RETURN 
(	
	
	  select * from (   SELECT '1' as id, '小王' as name
	   union
	    SELECT '2'  as id,'小李' as name
		  union
		SELECT '3'  as id,'小周' as name
		  union
	    SELECT '4'  as id,'小赵' as name ) temp
		where id=@id  --返回指定条件的数据源
)
GO

    2.调用函数

        代码如下（示例）：

select * from test(1)

   3.返回结果

  

2.多语句的表值函数

多语句表值函数允许多条语句来创建表的内容。多语句表值函数可以用来替换使用多个步骤来构建结果集的存储过程。

多语句表值函数允许开发人员使用多个步骤动态地填充表，这一点与存储过程类似，不过它们可以在SELECT语句中像表那样被引用。

使用多语句表值函数时，表的结构必须在函数头定义。要为表使用一个变量名，并且所有修改数据的操作只能引用表变量。

     1.创建函数

代码如下 (示例)：

CREATE FUNCTION test1
(	
 @id  int
)
RETURNS @t TABLE --定义表
(
	-- Add the column definitions for the TABLE variable here
	 name  nvarchar(50)--定义字段
)
AS
BEGIN
      insert into @t --向表插入数据

	  select name from (   SELECT '1' as id, '小王' as name
	   union
	    SELECT '2'  as id,'小李' as name
		  union
		SELECT '3'  as id,'小周' as name
		  union
	    SELECT '4'  as id,'小赵' as name ) temp
		where id=@id	--查询符合条件的数据
	 RETURN --返回数据源
END
GO

      

2.调用函数

        代码如下（示例）：

select * from test1(1)

3.返回结果

    

    

3.内联表值函数和多语句的表值函数的区别

   1.语法上

   内联表值函数 as后面没有begin/end，只有一个return语句返回特定的记录

   语句表值函数 as 在begin/end语句块中，直接将需要返回的结果insert到returns定义的表中就可     以了，在最后return时，会将结果返回

    2.结构上

    内联表返回的表结构由来SELECT语句的结构定义

    多语句表值函数返回 内部定义的表结构进行返回

二、标量函数

标量值函数返回一个确定类型的标量值，其返回类型除text、ntext、image、cursor、timestamp、和table类型外的其他数据类型，函数体语句定义在begin-end内部。在returns（注意有带s）语句后定义返回值的数据类型，并且函数的最后一条语句必须为return

1.创建函数

例如：

CREATE FUNCTION 函数名（参数）
RETURNS 返回类型
AS
BEGIN
	SQL语句
	RETURN;
END

    例如 获取两个时间差，返回相差几天几小时几分几秒

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION test2
(
	@Stime datetime,
	@Etime datetime
)
RETURNS nvarchar(20)
AS
BEGIN

declare @day int
declare @hour int
declare @min int
declare @sec int
declare @alls int
declare @result  nvarchar(20)
set @alls=datediff(s,@Stime,@Etime)   --时间@Etime与时间@Stime之间的差
set @day=@alls/86400
set @hour=(@alls-@day*86400)/3600
set @min=(@alls-@day*86400-@hour*3600)/60
set @sec=@alls-@day*86400-@hour*3600-@min*60
set @result=CAST(@day as varchar(5))+'天'+CAST(@hour as varchar(2))+'小时'+CAST(@min as varchar(2))+'分'+CAST(@sec as varchar(2))+'秒'

return @result
END
GO

2.调用函数

必须使用两部分组成函数的名字来调用函数，即架构.对象名

例如 计算2008-08-08 12:00 到现在相差多少天

select dbo.test2('2008-08-08 12:00',GETDATE())

2.返回结果

如下

---

总结

表值函数看作返回一个虚拟的表，可以看作带有入参的视图

标量函数理解来说 返回一个类型的标量值