Function Table MS SQL

SQL Server Function Table 應用

蔡宗勳 Tsunghsun Tsai 2020/12/31 23:44:22
4642

一、前言

      資料庫的資料處理常見為View、StoreProcedure，但MS SQL 有個與其他資料庫不一樣的功能「Function Table」，其不但可進行各項邏輯運算，且可將其視為Table，可對其進行同一般 Table 之存取作業。

 

二、特性

(1).可直接進行 select, where, join..等作業

(2).可依據需求自訂查詢結果欄位

(3).重複存取該Function Table時，在一定條件下將重複使用同樣的資源快取

(4).可直接針對欲回傳的資料表進行資料邏輯運算

此回將針對第(4)點進一步做說明

 

三、範例說明「可直接針對欲回傳的資料表進行資料邏輯運算」

(1).目的條件：利用行政區(戶籍地/居住地)、性別、年齡與訂閱項目之條件，篩選出符合條件的名單

(2).作法目標：

  A. 複合查詢條件的應用

  B. 解省資源，避免大量資料重複存取運算

  C. 可直接做 select 存取以進行下一步作業

(3).SQL參考：

重點作法說明：

  A. 直接於該 Function Table 之 Return Table 做資料邏輯運算

  B. 為避免喪失 index 故不於 where 條件做 case when 之等於判斷式

  C. 函式應用：

    i. isjson : 判斷該資料是否為Json格式

    ii.JSON_VALUE : 取得Json字串中該Tag欄位的值

    iii.string_split : 指定分隔符號切割字串(SQL 2016以上版本支援)

  D. Function Table 內容 SQL

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<依據條件取得名單>
-- =============================================
CREATE FUNCTION [dbo].[Fun_GetNotificationList]
(
	@Condition varchar(500)  --查詢條件
)
RETURNS @ReturnTable Table
(
	MemberSeq int,
	ACID varchar(22),
	UID varchar(20),
	FECID varchar(64),
	Gender varchar(1),
	Birthday varchar(8),
	RecidentDistrictCode varchar(7),
	CurrentDistrictCode varchar(7)
)
AS
BEGIN

    if not (isnull(@Condition,'')='' or isjson(@Condition)=0)
    begin
	    /*解析查詢條件*/
		Declare @Gender varchar(1)
		Declare @BirthdayS varchar(8)
		Declare @BirthdayE varchar(8)
		Declare @District varchar(max)
		Declare @NotificationMainCategoryCode varchar(3) 
		Declare @NotificationSubCategoryCode varchar(5)
		Declare @DistrictRowcount int
		Declare @NeedDataFirst int=1
		Declare @HasData bit=0

		select @Gender=(JSON_VALUE(@Condition,'$.gender'))
			,@BirthdayS=(JSON_VALUE(@Condition,'$.birthday[0]'))
			,@BirthdayE=(JSON_VALUE(@Condition,'$.birthday[1]'))
			,@District=trim(replace(replace(replace(JSON_QUERY(@Condition,'$.district'),char(34),''),char(91),''),char(93),''))
			,@NotificationMainCategoryCode=(JSON_VALUE(@Condition,'$.itemNo'))
			,@NotificationSubCategoryCode=(JSON_VALUE(@Condition,'$.itemSubNo'))
        
		/*解析行政區*/
		Declare @DistrictTable table (
			District varchar(7) primary key
		)
		insert into @DistrictTable(District)
		select trim(value)
		from string_split(@District,',')
		where value<>''
			and len(trim(value))=7

		set @DistrictRowcount=(select count(1) from @DistrictTable)
		
		/*全無限制，直接回應全部資料*/
		if (isnull(@Gender,'')='' and isnull(@BirthdayS,'')='' 
		    and isnull(@BirthdayE,'')='' and isnull(@DistrictRowcount,0)=0
			and isnull(@NotificationMainCategoryCode,'')='' and isnull(@NotificationSubCategoryCode,'')='')
		begin
		    insert into @ReturnTable
			select
				Seq MemberSeq
				,Account ACID
				,ID UID
				,HashID
				,Gender
				,Birthday
				,RecidentDistrictCode
				,CurrentDistrictCode
			from MemberInfo mi
			return
		end
		
	    /*行政區*/
	    if (@DistrictRowcount>0)
        begin
			set @NeedDataFirst=0

			insert into @ReturnTable
			select
				Seq MemberSeq
				,Account ACID
				,ID UID
				,HashID
				,Gender
				,Birthday
				,RecidentDistrictCode
				,CurrentDistrictCode
			from MemberInfo mi
			inner join @DistrictTable dt on dt.District=mi.CurrentDistrictCode
			union
			select
				Seq MemberSeq
				,Account ACID
				,ID UID
				,HashID
				,Gender
				,Birthday
				,RecidentDistrictCode
				,CurrentDistrictCode
			from MemberInfo mi
			inner join @DistrictTable dt on dt.District=mi.RecidentDistrictCode
	    end

	    /*訂閱項目 - 細項*/
	    set @HasData=isnull((select top 1 1 from @ReturnTable),0)
	    if(isnull(@NotificationSubCategoryCode,'')<>'')
	    begin
	        if (@NeedDataFirst=1)
		    begin
		        set @NeedDataFirst=0

		        insert into @ReturnTable
		        select
					mi.Seq MemberSeq
					,Account ACID
					,ID UID
					,HashID
					,Gender
					,Birthday
					,RecidentDistrictCode
					,CurrentDistrictCode
				from MemberInfo mi
				inner join SubscribeManagement sm on mi.Seq=sm.MemberInfo_Seq
			    inner join NotificationSubCategory sub on sub.Code=@NotificationSubCategoryCode
			        and charindex(char(44)+cast(sub.Seq as varchar)+char(44),char(44)+sm.Content+char(44),1)>1
            end
			else if (@HasData=1)
			begin
				delete tmp
				from @ReturnTable tmp
				left join SubscribeManagement sm on tmp.MemberSeq=sm.MemberInfo_Seq
				left join NotificationSubCategory sub on sub.Code=@NotificationSubCategoryCode
					and charindex(char(44)+cast(sub.Seq as varchar)+char(44),char(44)+sm.Content+char(44),1)>1
				where sub.Seq is null
			end
	    end

		/*訂閱項目 - 大項*/
		set @HasData=isnull((select top 1 1 from @ReturnTable),0)
		if(isnull(@NotificationMainCategoryCode,'')<>'')
		begin
			if (@NeedDataFirst=1)
			begin
			    set @NeedDataFirst=0

				;with subm as (
					select 
						MemberInfo_Seq,value
					from SubscribeManagement
					cross apply STRING_SPLIT(Content, ',')
				),
				member as (
					select distinct
						MemberInfo_Seq
					from subm
					inner join NotificationMainCategory main on main.Code=@NotificationMainCategoryCode
					inner join NotificationSubCategory sub on sub.seq=subm.value
						and main.Seq=sub.NotificationMainCategory_Seq
				)
				insert into @ReturnTable
				select
				    mi.Seq MemberSeq
					,Account ACID
					,ID UID
					,HashID
					,Gender
					,Birthday
					,RecidentDistrictCode
					,CurrentDistrictCode
				from member
				inner join MemberInfo mi on mi.Seq=member.MemberInfo_Seq
			end
			else if (@HasData=1)
			begin
				;with subm as (
					select 
						MemberInfo_Seq,value
					from SubscribeManagement
					cross apply STRING_SPLIT(Content, ',')
				),
				member as (
					select distinct
						MemberInfo_Seq
					from subm
					inner join NotificationMainCategory main on main.Code=@NotificationMainCategoryCode
					inner join NotificationSubCategory sub on sub.seq=subm.value
						and main.Seq=sub.NotificationMainCategory_Seq
				)
				delete tmp 
				from @ReturnTable tmp
				left join member on member.MemberInfo_Seq=tmp.MemberSeq
				where member.MemberInfo_Seq is null
			end
		end

	    /*生日*/
		set @HasData=isnull((select top 1 1 from @ReturnTable),0)
		if (isnull(@BirthdayS,'')<>'')
		begin
			if (@NeedDataFirst=1)
			begin
			    set @NeedDataFirst=0

			    insert into @ReturnTable
				select
					Seq MemberSeq
					,Account ACID
					,ID UID
					,HashID
					,Gender
					,Birthday
					,RecidentDistrictCode
					,CurrentDistrictCode
				from MemberInfo
				where isnull(Birthday,'')<>''
				    and Birthday>=@BirthdayS
			end
			else if (@HasData=1)
			begin
				delete @ReturnTable where Birthday is null
			    delete @ReturnTable where Birthday<@BirthdayS
			end
		end

		set @HasData=isnull((select top 1 1 from @ReturnTable),0)
		if (isnull(@BirthdayE,'')<>'')
		begin
		    if (@NeedDataFirst=1)
			begin
			    set @NeedDataFirst=0

				insert into @ReturnTable
				select
					Seq MemberSeq
					,Account ACID
					,ID UID
					,HashID
					,Gender
					,Birthday
					,RecidentDistrictCode
					,CurrentDistrictCode
				from MemberInfo
				where isnull(Birthday,'')<>''
				    and Birthday<=@BirthdayE
			end
			else if (@HasData=1)
			begin
				delete @ReturnTable where Birthday is null
				delete @ReturnTable where Birthday>@BirthdayE
			end
		end

		/*性別*/
		set @HasData=isnull((select top 1 1 from @ReturnTable),0)
		if (isnull(@Gender,'')<>'')
		begin
		    if (@NeedDataFirst=1)
			begin
			    set @NeedDataFirst=0

				insert into @ReturnTable
				select
					Seq MemberSeq
					,Account ACID
					,ID UID
					,HashID
					,Gender
					,Birthday
					,RecidentDistrictCode
					,CurrentDistrictCode
				from MemberInfo
				where Gender=@Gender
			end
			else if (@HasData=1)
			begin
			    delete from @ReturnTable where isnull(Gender,'')<>@Gender
			end
		end
    end
	
	RETURN 
END

 

  F. 執行結果

    i. 直接取得清單

/*
{
  "gender": "F",
  "age": [
    "19800506",
    "20131231"
  ],
  "area": [
    "6400600"
  ],
  "itemNo": "T01",
  "itemSubNo": "T0106"
}
*/
declare @Condition varchar(max)='{"gender":"F","age":["19800506","20131231"],"area":["6400600"],"itemNo":"","itemSubNo":""}'
select
    MemberSeq  '會員編號'
	,ACID  '會員卡號'
from dbo.Fun_GetNotificationList(@Condition)

 

 

    ii. 進一步直接計算 : 符合名單之會員數

/*
{
  "gender": "F",
  "age": [
    "19800506",
    "20131231"
  ],
  "area": [
    "6400600"
  ],
  "itemNo": "T01",
  "itemSubNo": "T0106"
}
*/
declare @Condition varchar(max)='{"gender":"F","age":["19800506","20131231"],"area":["6400600"],"itemNo":"","itemSubNo":""}'
select count(1) 符合名單之會員數
from dbo.Fun_GetNotificationList(@Condition)

 

(4).摘要結論：

  A. 達到複合條件排列組合且避免喪失index

  B. 無須重複存取資料，解省系統資源

  C. 查詢之結果資料無須轉接，即可直接做進一步篩選應用

 

 

蔡宗勳 Tsunghsun Tsai
相關文章
Ora2pg組態設定(下)
Ora2pg組態設定(上)
SQL Server 2019 資料庫鏡像設定實戰
Neo4j 因果叢集實作簡介
使用CTE遞迴實作階層式報表
SQL Server Function Table 應用
最新文章
Visual Regression Test
快速生成API測試案例 - Keploy
Visual Testing 原理簡介
API安全風險大揭密 (下集) - OWASP Top 10 API Security Risk 2023
探索性測試指南
前端爬蟲 Puppeteer 介紹