iT邦幫忙

2

【MSSQL】使用遞迴,依據條件式判斷呼叫不同的方法

我想詢問一個遞迴的概念寫法,附上圖輔助描述

我有一張表,這張表是透過方法PraseJson丟入變數後回傳的表。

https://ithelp.ithome.com.tw/upload/images/20190315/20115336tpyBfya2wZ.png

我的目的是想要寫一段SQL
isTerminal = 0時,我就要呼叫方法PraseJson,當isTerminal = 1時,我就要呼叫方法InsertToDB
並在InsertToDB做完以後,將其回傳的@Id,存放在暫存@Table中。

https://ithelp.ithome.com.tw/upload/images/20190319/20115336NRathBTurU.png
目前已經寫出以下遞迴,如果符合isTerminal = 0的條件,就一直呼叫方法PraseJson,程式寫法與執行完後的長相如下 :
方法PraseJson是使用Sql的openjson的語法去做字串分解的

--定義變數
declare @DeclaringType nvarchar(max)=N'Book'
declare 
	@json nvarchar(max)=N'{
    "Book":{
        "IssueDate":"02-15-2019"
        , "Detail":{
            "Type":"Any Type"
            , "Author":{
                "Name":"Annie"
                , "Sex":"Female"
            }
        }
        , "Chapter":[
            {
                "Section":"1.1"
                , "Title":"Hello world."
            }
            ,
            {
                "Section":"1.2"
                , "Title":"Be happy."
            }       
        ]
        , "Sponsor":["A","B","C"]
    }
}'	
    
--遞迴
;with cte as (
		select 
			* 
		from 
			PraseJson(@json, 0)
	)	
    select 
        x.* 
    from 
        cte x
    union all
    select 
        z.* 
    from 
        cte y
    cross apply PraseJson('{"' + Key + '":' + Value + '}',isList) z
    where y.isTerminal = 0

https://ithelp.ithome.com.tw/upload/images/20190319/201153362GJMOEZBVh.png

但我想不出來要如何將以下條件加入我的遞迴寫法中呢?
isTerminal = 0時,我就要呼叫方法PraseJson
isTerminal = 1時,我就要呼叫方法InsertToDB
並在InsertToDB做完以後,將其回傳的@targetId,存放在暫存Table中

InsertToDB之後table的長相
https://ithelp.ithome.com.tw/upload/images/20190319/20115336fmsvTHOiyK.png

select出暫存Table的長相
https://ithelp.ithome.com.tw/upload/images/20190319/20115336KxLQpxyr9z.png

目前因方法裡面牽涉的table與呼叫其他方法較多,因此先沒有詳細敘述此部分,想詢問一個概念上的寫法,謝謝!

--補充--
PraseJson是Function,其作用是將傳入的Json字串使用sql的openJson分解成key、value(2016以上的版本才有),並透過isJson判斷是不是Terminal的值,以供後續判斷value值是否要再解析,最後回傳一個table變數。

InsertToDB是預存程序,其作用是將key、value做insert到table中,會回傳一個存入DB後自動生成的id。

暫存Table的長相會是許多InsertToDB執行完成後回傳的id,會使用暫存Table是因為我後續要再做其他事情。

我的目標是要透過丟入一個Json字串,解析他以後存入table,希望最後可以select出存入DB後的id。所以簡單來說是想詢問,當我有A方法與B方法時,會依照條件重複的呼叫這兩個方法,此時要怎麼運用SQL去撰寫遞迴呢?

看更多先前的討論...收起先前的討論...
我只想問你@json丟出來的資料表會長怎樣?
你好像沒把預期結果的資料秀出來?(測試資料的結果資料表)
anniecat iT邦新手 5 級 ‧ 2019-03-19 10:27:43 檢舉
請問您說的 : @json丟出來的資料表會長怎樣 是什麼意思呢 ?
anniecat iT邦新手 5 級 ‧ 2019-03-19 10:48:07 檢舉
不好意思因近幾日生病,現在才看到各位的回覆,已有更新發問內容~
thwu iT邦新手 5 級 ‧ 2019-03-19 11:57:07 檢舉
常在解答問題的人曾經一定有遇過直接回答提問者的問題後,才發現是提問者一開始的方向有問題,才會引發別的問題出來。
所以也都習慣先了解提問人的 "目的" 或 "結果",因為大家應該都希望能解對問題,不要白費功夫。
建議樓主明確標註一下目的和希望的結果是什麼,別讓各位熱心的大神再繼續猜測吧。
anniecat iT邦新手 5 級 ‧ 2019-03-19 13:20:55 檢舉
好的,謝謝您的指正,不好意思初期發問問題還抓不太到方法...
目前我有修改我的問題,不知道那樣的敘述還缺少什麼嗎?
fysh711426 iT邦研究生 4 級 ‧ 2019-03-19 13:38:37 檢舉
PraseJson、InsertToDB 這兩個函數的輸入輸出和裡面的邏輯還不太清楚。
例如 PraseJson 第三個參數的用途
還有 PraseJson 解析陣列時如何帶入 topKey
ex: 最後的結果 Section 的 topKey 是 Chapter,這部分不知道函數中是如何帶入
恩..我意思~文字上都明白你的說法~
但你可以畫個產生結果的表格(用圖表示)~這樣表達訴求~
會比較知道@json最後產生的資料表是什麼樣子~
anniecat iT邦新手 5 級 ‧ 2019-03-19 14:25:50 檢舉
謝謝@fysh711426與@純真的人
我有更新我的問法了
我發問的困難點在於那兩個函數內容所牽涉的table以及額外呼叫的函數較多
較無法都放上去 因為是專案的內容,所以用簡單敘述的方式說明
原本想要忽略函數的內容,詢問外框的寫法,但看起來這樣是不行的,會再想想怎麼發問我的問題!
真的再一次謝謝你們!
fysh711426 iT邦研究生 4 級 ‧ 2019-03-19 14:58:26 檢舉
沒關係~
程式我有更新在下面回覆
和你的寫法其實蠻類似
thwu iT邦新手 5 級 ‧ 2019-03-19 17:48:53 檢舉
意思是這樣嗎?
當 PraseJson 解 json 到這條支線最末端時,會設定 isTerminal = 1,此時就要呼叫 InsertToDB。
若未到最末端,則 isTerminal = 0,此時會繼續做 PraseJson。
這樣做的目的是為了能在每一次到最末端時,不僅能將 key, value 寫入 db,同時InsertToDB 還能回傳一筆 id 到暫存 Table,是嗎?

如果是這樣,因為我的資料庫版本太舊,沒有辦法用 OPENJSON,只能用提問的方式來給你參考。
既然 CTE 裡無法寫條件來判斷呼叫那一個 fn,不知道可不可以改成都呼叫,但呼叫後要不要實際執行,則是將 isTerminal 也一併傳入 fn,在 fn 裡判斷。
anniecat iT邦新手 5 級 ‧ 2019-03-20 11:02:11 檢舉
嗯嗯,是的!
您是指我可以一樣呼叫PraseJson,在這個方法裡面再判斷是不是Terminal 值,決定是否insert資料
但我依據條件可能會回傳id或table呢...
這部分我今天也研究一下!
真的很感謝您~
thwu iT邦新手 5 級 ‧ 2019-03-20 14:01:35 檢舉
所以若寫成 PraseJson , InsertToDB 都同時呼叫呢? (我也不確定語法可不可以這樣寫,你可能要試試看)
isTerminal 都會傳入到這兩個 fn 裡,但 PraseJson 只有在 isTerminal =0 時會有執行,而 InsertToDB 則只有在 isTerminal = 1 時才執行。

例如
if (isTerminal==0) PraseJson();
else InsertToDB();
改成這樣
PraseJson(isTerminal); // 回傳 Table,為了繼續跑遍歷
InsertToDB(isTerminal); // 回傳 id 寫入 暫存 Table => 直接做到 fn 裡,變成不用回傳 id
anniecat iT邦新手 5 級 ‧ 2019-03-20 16:22:20 檢舉
PraseJson , InsertToDB 都同時呼叫這部分也是我正在研究的部分,之前曾試過用select case依照條件去各別呼叫,但因為我的InsertToDB 是預存程序,因此不適用,若都是Function則適用。
另外,您提到的觀點,我覺得多了一個思考的點,謝謝您~

2 個回答

3
fysh711426
iT邦研究生 4 級 ‧ 2019-03-16 14:59:29
最佳解答

不確定是不是這樣,給你參考

PraseJson

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[PraseJson](@json NVARCHAR(MAX), @key NVARCHAR(1000) NULL)
RETURNS @TVP TABLE 
(
	[Key] NVARCHAR(1000),
	isType BIT,
	isList BIT,
	isTermital BIT,
	[Value] NVARCHAR(MAX)
)
AS
BEGIN
	IF @key IS NULL
		INSERT INTO @TVP
		SELECT [key] AS [Key],
		   CASE WHEN [type] >= 4 THEN 1 ELSE 0 END AS isType,
		   CASE WHEN [type] = 4 THEN 1 ELSE 0 END AS isList,
		   CASE WHEN [type] <= 3 THEN 1 ELSE 0 END AS isTermital,
		   [value] AS [Value]
		FROM OPENJSON(@json)
    ELSE
		INSERT INTO @TVP
        SELECT [key] AS [Key],
		   CASE WHEN [type] >= 4 THEN 1 ELSE 0 END AS isType,
		   CASE WHEN [type] = 4 THEN 1 ELSE 0 END AS isList,
		   CASE WHEN [type] <= 3 THEN 1 ELSE 0 END AS isTermital,
		   [value] AS [Value]
		FROM OPENJSON(@json, '$.' + @key)
	RETURN 
END

程式

DECLARE @json NVARCHAR(4000) = N'{
    "Book":{
        "IssueDate":"02-15-2019"
        , "Detail":{
            "Type":"Any Type"
            , "Author":{
                "Name":"Annie"
                , "Sex":"Female"
            }
        }
        , "Chapter":[
            {
                "Section":"1.1"
                , "Title":"Hello world."
            }
            ,
            {
                "Section":"1.2"
                , "Title":"Be happy."
            }       
        ]
        , "Sponsor":["A","B","C"]
    }
}'

SET @json = REPLACE(REPLACE(REPLACE(@json,' ',''),CHAR(10),''),CHAR(13),'')

;WITH CTE AS
(
	SELECT *
	FROM dbo.PraseJson(@json, 'Book')
	UNION ALL
	SELECT B.*
	FROM (
		SELECT * FROM CTE WHERE isTermital=0 AND isList=0
	) AS A
	CROSS APPLY dbo.PraseJson(A.[Value], NULL) AS B
)

SELECT * FROM CTE
WHERE isTermital=1

結果

   Key      isType     isList      isTermital      Value
---------|----------|----------|--------------|-------------
IssueDate	  0	          0	           1	     02-15-2019
Type	      0	          0	           1	     AnyType
Name	      0	          0	           1	     Annie
Sex	          0	          0	           1	     Female

2019/03/19 更新:

因為不知道 PraseJson 真實邏輯,所以結果不太一樣,但邏輯大概是這樣~

PraseJson

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[PraseJson](@json NVARCHAR(MAX), @key NVARCHAR(1000))
RETURNS @TVP TABLE 
(
    topKey NVARCHAR(1000),
	[Key] NVARCHAR(1000),
	isType BIT,
	isList BIT,
	isTermital BIT,
	[Value] NVARCHAR(MAX)
)
AS
BEGIN
	INSERT INTO @TVP
        SELECT @key AS topKey,
		   [key] AS [Key],
		   CASE WHEN [type] >= 4 THEN 1 ELSE 0 END AS isType,
		   CASE WHEN [type] = 4 THEN 1 ELSE 0 END AS isList,
		   CASE WHEN [type] <= 3 THEN 1 ELSE 0 END AS isTermital,
		   [value] AS [Value]
		FROM OPENJSON(@json, '$."' + @key + '"')
	RETURN 
END

程式

DECLARE @json NVARCHAR(4000) = N'{
    "Book":{
        "IssueDate":"02-15-2019"
        , "Detail":{
            "Type":"Any Type"
            , "Author":{
                "Name":"Annie"
                , "Sex":"Female"
            }
        }
        , "Chapter":[
            {
                "Section":"1.1"
                , "Title":"Hello world."
            }
            ,
            {
                "Section":"1.2"
                , "Title":"Be happy."
            }       
        ]
        , "Sponsor":["A","B","C"]
    }
}'	

SET @json = REPLACE(REPLACE(REPLACE(@json,' ',''),CHAR(10),''),CHAR(13),'')

;WITH CTE AS
(
	SELECT *
	FROM dbo.PraseJson(@json, 'Book')
	UNION ALL
	SELECT B.*
	FROM (
		SELECT * FROM CTE WHERE isTermital=0
	) AS A
	CROSS APPLY dbo.PraseJson('{"' + A.[Key] + '":' + A.[Value] + '}', A.[Key]) AS B
)

--最後執行 InsertToDB 

SELECT B.targetId
FROM (
	SELECT * FROM CTE WHERE isTermital=1
) AS A
CROSS APPLY dbo.InsertToDB() AS B

遞迴的結果

https://ithelp.ithome.com.tw/upload/images/20190319/20106865ZNobLh3Pw7.jpg


2019/03/20 更新-函數版本:

需另外新增 InsertToDB 和 Loop 函數

InsertToDB

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[InsertToDB](@key NVARCHAR(1000), @value NVARCHAR(MAX))
RETURNS NVARCHAR(1000)
AS
BEGIN
	RETURN @key + ':' + @value
END
GO

Loop

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Loop](@json NVARCHAR(MAX), @key NVARCHAR(1000))
RETURNS @TVP TABLE 
(	
	[targetid] NVARCHAR(1000)
)
AS
BEGIN
	DECLARE @Temp TABLE
	(
		topKey NVARCHAR(1000),
		[Key] NVARCHAR(1000),
		isType BIT,
		isList BIT,
		isTermital BIT,
		[Value] NVARCHAR(MAX)
	)
	--先將JSON轉換後的結果存入@Temp
	INSERT INTO @Temp
	SELECT * FROM dbo.PraseJson(@json, @key)

	INSERT INTO @TVP
	--如果isTermital=1呼叫InsertToDB
	SELECT dbo.InsertToDB(A.[Key], A.[Value]) AS targetid
	FROM (
		SELECT * FROM @Temp WHERE isTermital=1
	) AS A
	--CROSS APPLY dbo.InsertToDB(A.[Key]) AS B --如果InsertToDB是資料表函數
	UNION ALL
	--如果isTermital=0繼續遞迴
	SELECT B.targetid AS targetid
	FROM (
		SELECT * FROM @Temp WHERE isTermital=0
	) AS A
	CROSS APPLY dbo.[Loop]('{"' + A.[Key] + '":' + A.[Value] + '}', A.[Key]) AS B

	RETURN 
END

程式

DECLARE @json NVARCHAR(4000) = N'{
    "Book":{
        "IssueDate":"02-15-2019"
        , "Detail":{
            "Type":"Any Type"
            , "Author":{
                "Name":"Annie"
                , "Sex":"Female"
            }
        }
        , "Chapter":[
            {
                "Section":"1.1"
                , "Title":"Hello world."
            }
            ,
            {
                "Section":"1.2"
                , "Title":"Be happy."
            }       
        ]
        , "Sponsor":["A","B","C"]
    }
}'	

SET @json = REPLACE(REPLACE(REPLACE(@json,' ',''),CHAR(10),''),CHAR(13),'')

SELECT * FROM dbo.[Loop](@json, 'Book')

結果

https://ithelp.ithome.com.tw/upload/images/20190320/20106865SoYlqZuh0d.jpg

看更多先前的回應...收起先前的回應...
anniecat iT邦新手 5 級 ‧ 2019-03-19 10:34:10 檢舉

不好意思,可能我沒有表達的很詳盡,PraseJson與InsertToDB這兩個方法已經寫好了,只是要將它們組起來的時候,不知道怎麼組,所以想說詢問一個概念上的寫法,但真的非常感謝您提供的方法!

anniecat iT邦新手 5 級 ‧ 2019-03-19 15:59:16 檢舉

不好意思,有辦法將
--最後執行 InsertToDB
合併到上面的遞迴之中嗎?

很感謝您的耐心回覆與解答~

fysh711426 iT邦研究生 4 級 ‧ 2019-03-20 11:20:58 檢舉

不行~
CTE 遞迴和函數遞迴不太一樣
每次 UNION ALL 的結果會成為下次遞迴的起始資料
因此不能把 PraseJson 和 InsertToDB 的結果放在一起
最多只能把最後 InsertToDB 的部分再包成另一個 CTE
要寫在一起的話函數遞迴才能辦到,函數版本我更新在上面回覆。

fysh711426 iT邦研究生 4 級 ‧ 2019-03-20 11:42:35 檢舉

不過函數遞迴有32層限制,所以要慎用~~

DECLARE @json NVARCHAR(4000) = '{"0":{"1":{"2":{"3":{"4":{"5":{"6":{"7":{"8":{"9":{"10":{"11":{"12":{"13":{"14":{"15":{"16":{"17":{"18":{"19":{"20":{"21":{"22":{"23":{"24":{"25":{"26":{"27":{"28":{"29":{"30":{"31":{"32":1}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}'

SELECT * FROM dbo.[Loop](@json, '0')

超過預存程序、函數、觸發程序或檢視巢狀層級的最大值 (上限為 32)。

anniecat iT邦新手 5 級 ‧ 2019-03-20 16:16:20 檢舉

謝謝您,我有看懂您的範例了!
不過若我將InsertToDB這隻改成預存程序就無法執行

'InsertToDB' 不是可辨識的 內建函數名稱。

因為會在InsertToDB之中有insert的操作,不知道怎麼樣調整比較好呢?

fysh711426 iT邦研究生 4 級 ‧ 2019-03-21 09:45:13 檢舉

函數內不能呼叫預存程序,這個沒辦法~ /images/emoticon/emoticon06.gif

anniecat iT邦新手 5 級 ‧ 2019-03-21 10:00:53 檢舉

好的,這個部份我想看看能不能把SP的東西拔出來放到外面,謝謝您花這麼多時間在幫助解決問題,也讓我懂了更多~!
/images/emoticon/emoticon12.gif

1
海綿寶寶
iT邦超人 1 級 ‧ 2019-03-16 07:43:42

我看了你問的三個問題
其實是同一個問題
就是「把 JSON 資料存進 SQL Table」
更精確一點地說
是「把一整個 JSON 剖析成 key-value pair存進單一個 Table」

我實在想不出有什麼這麼做的必要性或實際應用的案例經驗
也可能因為如此,不會有什麼人回答這問題

不過我還是 Google 到了這篇
參考看看合不合用
dbo.JSONHierarchy

祝你好運

anniecat iT邦新手 5 級 ‧ 2019-03-19 10:37:11 檢舉

不好意思讓您費心替我Google,我覺得那個方法符合您所說的「把 JSON 資料存進 SQL Table」,我在撰寫PraseJson這個方法時也是使用openjson的語法,此部分已完成。
但目前我的寫法還是要先分別做解析字串與儲存值的動作,而PraseJson與InsertToDB這兩個方法我已經寫好了,現在需要將它們串起來,但不太知道怎麼組,所以想說詢問一個概念上的寫法,但還是非常感謝您提供的方法!

我不明白你最後要的結果是什麼(What)
也不明白你這麼做的目的(Why)
只能說
Good Luck.

我要發表回答

立即登入回答