SQL Server - 函数用法

一、简单介绍 

SQL Server自定义函数分为三种类型： 标量函数(Scalar Function)、内嵌表值函数(Inline Function)、多语句表值函数(Multi-Statement Function)。

注意：SQL中，不能declare一个变量来存储筛选出的表，但可以定义一个表变量来分别存储筛选出来的字段。

1、标量函数

    标量函数是对单一值操作，返回单一值。能够使用表达式的地方，就可以使用标量函数。像我们经常使用的left、getdate等，都属于标量函数。系统函数中的标量函数包括：数学函数、日期和时间函数、字符串函数、数据类型转换函数等。

 

2、表值函数

1）内嵌表值函数

    内嵌表值函数的功能相当于一个参数化的视图。它返回的是一个表，内联表值型函数没有由BEGIN-END 语句括起来的函数体。其返回的表由一个位于RETURN 子句中的SELECT 命令段从数据库中筛选出来。

2）多语句表值函数

    多语句表值函数可以看作标量型和内嵌表值型函数的结合体。它的返回值是一个表，但它和标量型函数一样有一个用BEGIN-END 语句括起来的函数体，返回值的表中的数据是由函数体中的语句插入的。由此可见，它可以进行多次查询，对数据进行多次筛选与合并，弥补了内联表值型函数的不足。
————————————————
详请参见：https://blog.csdn.net/qq_36330228/article/details/88917455

 

 二、案例展示

1、查询供应商的情况

1.1 内嵌表值函数

-- 查询某指定供应商的情况

CREATE FUNCTION request1_1(@supplier_code nvarchar(9)) 
RETURNS TABLE 
AS
RETURN 
(
	SELECT [项目名称], [项目编码], [本期贷方]
	FROM CT1
	WHERE [供应商档案编码] = @supplier_code
)
GO

-- 调用函数
SELECT dbo.request1_1('13579')
SELECT [项目名称] From dbo.request1_1('13579')

1.2 多语句表值函数

-- 查询某指定供应商“连续三年应付账款余额是否为0”

CREATE FUNCTION request1_2(@supplier_code nvarchar(9)) 

RETURNS @result TABLE (
	[2019年度期末余额] decimal(12,2),
	[应付账款连续情况] nvarchar(50)
)
AS
BEGIN

	declare @panduan varchar(30)

	declare @year_one decimal(12,2)
	declare @year_two decimal(12,2)
	declare @year_three decimal(12,2)
 
	SELECT @year_one=SUM([期末余额])	FROM CT1	WHERE [供应商档案编码] = @supplier_code AND [年份] = YEAR(GETDATE())	GROUP BY [年份]
	SELECT @year_two=SUM([期末余额])	FROM CT1	WHERE [供应商档案编码] = @supplier_code AND [年份] = YEAR(GETDATE())-1	GROUP BY [年份]
	SELECT @year_three=SUM([期末余额])	FROM CT1	WHERE [供应商档案编码] = @supplier_code AND [年份] = YEAR(GETDATE())-2	GROUP BY [年份]

	if(@year_one>0 AND @year_two>0 AND @year_three>0)
	begin
		set @panduan='该供应商连续三年应付账款余额不为0'
	end
	else
	begin
		set @panduan='NULL'
	end

	insert into @result([2019年度期末余额],[应付账款连续情况]) values (@year_one, @panduan)
	
	RETURN

END
GO

2、查询用户的情况 

2.1 内嵌表值函数

-- 查询[已关联用户]中含有特定字样的用户整体情况

CREATE FUNCTION request2_1(@u_name nvarchar(6))
RETURNS TABLE 
AS
RETURN 
(
	SELECT [职责名称],[角色编码],[角色名称],[所属组织]
	FROM CT2
	WHERE [已关联用户] LIKE '%'+@u_name+'%'    -- '%'+@u_name+'%'：这样才能在函数中正确用like匹配
)

2.2 多语句表值函数

-- 查询某特定用户职责设置是否合理
/*
[职责名称]不能同时为：
1）'SG_FI_ZJGL_001'和'SG_FI_ZJGL_002' 
2）'SG_FI_ZJGL_010'和'SG_FI_ZJGL_011' 
3）'SG_FI_ZJGL_013'和'SG_FI_ZJGL_014'
*/


CREATE FUNCTION request2_1(@u_name nvarchar(6)) 

RETURNS @result TABLE (
	[用户] nvarchar(6) not null,
	[权限分配情况] nvarchar(10) not null
)
AS
BEGIN 

	DECLARE @count_001 int
	DECLARE @count_002 int
	DECLARE @count_010 int
	DECLARE @count_011 int
	DECLARE @count_013 int
	DECLARE @count_014 int
	DECLARE @permission nvarchar(10)

	SELECT @count_001 = count(*) FROM CT2 WHERE [已关联用户] LIKE '%'+@u_name+'%' AND [职责名称]='SG_FI_ZJGL_001'
	SELECT @count_002 = count(*) FROM CT2 WHERE [已关联用户] LIKE '%'+@u_name+'%' AND [职责名称]='SG_FI_ZJGL_002'
	SELECT @count_010 = count(*) FROM CT2 WHERE [已关联用户] LIKE '%'+@u_name+'%' AND [职责名称]='SG_FI_ZJGL_010'
	SELECT @count_011 = count(*) FROM CT2 WHERE [已关联用户] LIKE '%'+@u_name+'%' AND [职责名称]='SG_FI_ZJGL_011'
	SELECT @count_013 = count(*) FROM CT2 WHERE [已关联用户] LIKE '%'+@u_name+'%' AND [职责名称]='SG_FI_ZJGL_013'
	SELECT @count_014 = count(*) FROM CT2 WHERE [已关联用户] LIKE '%'+@u_name+'%' AND [职责名称]='SG_FI_ZJGL_014'


	IF( (@count_001*@count_002<>0) OR (@count_010*@count_011<>0) OR (@count_013*@count_014<>0) )
	BEGIN
		SET @permission='不合理'
	END
	ELSE
	BEGIN
		SET @permission='合理'
	END

	INSERT INTO @result([用户], [权限分配情况]) VALUES (@u_name, @permission)

	RETURN

END
GO