sqlserver 表值函数与标量值函数

除了在我们常用的程序开发中要用到函数外，在sql语句中也常用到函数，不论哪种，思想都没有变，都是为了封装，可复用。

创建的方法和整体结构都大体相同，都少不了函数名，函数的形参，返回值等这些。

一、表值函数

从名字可知，表值函数，是将表作为值进行返回的函数。请看本人项目中的一个表值函数：

USE [cnpc]
GO
/****** Object:  UserDefinedFunction [dbo].[FUN_EaScoreDetail]    Script Date: 2019/7/1 星期一 下午 3:50:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,zhengwei>
-- Create date: <Create Date,20190624,>
-- Description:    <Description,立项考核明细表，给立项考核统计提供最基本的数据，很多地方会用到这个函数，不要轻易修改,>
-- =============================================
CREATE FUNCTION [dbo].[FUN_EaScoreDetail] (@unitcode nvarchar(50),@startdate datetime,@enddate datetime)
RETURNS 
@scoreResult TABLE 
(
EaId int,
Createdtime datetime,
ApplyUnitCode nvarchar(50),
updateG int,
ReturnG int,
AdjustG int,
TerminatedG int,
Score float
)
AS
BEGIN
insert into @scoreResult
    select s.EaId EaId,min(e.createdtime) Createdtime,e.unit_code ApplyUnitCode,
sum(case ScoreType when 'Upload' then 1 else 0 end) as updateG,
sum(case ScoreType when 'Reply' then 1 else 0 end) as ReturnG,
sum(case ScoreType when 'Adjust' then 1 else 0 end) as AdjustG,
sum(case ScoreType when 'Terminated' then 1 else 0 end) as TerminatedG,
(case min(s.IncreaseOrReduceScore) when 1 then 1 else (1+min(s.IncreaseOrReduceScore)) end) as Score
 from EaScoreDetail s
 inner join Ea e on e.id=s.EaId
 inner join unitinfo u on e.unit_code = u.dm
  where e.createdtime  BETWEEN @startdate and @enddate
  and e.unit_code like @unitcode+'%'
 group by s.EaId,e.unit_code
    
    RETURN 
END

      表值函数的返回结果为一个表，那么首先就是要创建一个表@scoreResult ，并声明了表中一些字段的，最后将查询的结果插入这个表中，注意，插入结果中select后面字段的顺序要与声明表进字段的顺序相同。
二、标量值函数

从名字可知，表值函数，是将一个值进行返回的函数。请看本人项目中的一个标量值函数：

USE [cnpc]
GO
/****** Object:  UserDefinedFunction [dbo].[FUN_getPassportQualityScore]    Script Date: 2019/7/1 星期一 下午 3:58:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create function [dbo].[FUN_getPassportQualityScore] ( 
@approvalCount float,
@successApprovalCount float,
@borrowCount float,
@overtimeUnReturnCount float,
@unBorrowVisaCount float,
@unBorrowForTK float
)
returns nvarchar(100)  
as begin
declare @passportQualityScore float,
@collectionRate float,
@legalRate float,
@passRate float
set @collectionRate=0
set @legalRate =0
set @passRate =0
---- 收缴率
if(@borrowCount>0)
set @collectionRate = 1- @overtimeUnReturnCount/@borrowCount

---- 合规借出率
if((@unBorrowVisaCount+@unBorrowForTK+@borrowCount)>0)
set @legalRate = @borrowCount/(@unBorrowVisaCount+@unBorrowForTK+@borrowCount)

---- 一次办理合格率
if(@approvalCount>0)
set @passRate = @successApprovalCount/@approvalCount

----质量总分（也就是最终要返回的结果）
set @passportQualityScore = (@collectionRate + @legalRate+@passRate)/0.03
return round(@passportQualityScore,2)
end

标量值函数返回的值为一个数字，也就是将传入的参数通过计算得到一个结果。

三、表值函数与标量值函数的使用

1、在存储过程中使用表值函数与使用数据库中的表是一样的。直接调用并传入需要的参数就可，如下：

2、在存储过程中使用标量值函数正如程序中使用方法一下，传入指定的参数就可，如图中的标量值函数是用select中每列的值作为参数进行调用的。

 

 

 

有需要微信投票的朋友可以找我，WX:18963948278