一、简单介绍
SQL Server自定义函数分为三种类型: 标量函数(Scalar Function)、内嵌表值函数(Inline Function)、多语句表值函数(Multi-Statement Function)。
注意:SQL中,不能declare一个变量来存储筛选出的表,但可以定义一个表变量来分别存储筛选出来的字段。
1、标量函数
标量函数是对单一值操作,返回单一值。能够使用表达式的地方,就可以使用标量函数。像我们经常使用的left、getdate等,都属于标量函数。系统函数中的标量函数包括:数学函数、日期和时间函数、字符串函数、数据类型转换函数等。
2、表值函数
1)内嵌表值函数
内嵌表值函数的功能相当于一个参数化的视图。它返回的是一个表,内联表值型函数没有由BEGIN-END 语句括起来的函数体。其返回的表由一个位于RETURN 子句中的SELECT 命令段从数据库中筛选出来。
2)多语句表值函数
多语句表值函数可以看作标量型和内嵌表值型函数的结合体。它的返回值是一个表,但它和标量型函数一样有一个用BEGIN-END 语句括起来的函数体,返回值的表中的数据是由函数体中的语句插入的。由此可见,它可以进行多次查询,对数据进行多次筛选与合并,弥补了内联表值型函数的不足。
————————————————
二、案例展示
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