iT邦幫忙

1

詢問SQL寫法概念_依照一張table,分別兩個欄位內容作為條件,呼叫兩個不同的Function

如同標題,我想要寫一段SQL,首先定義一個變數較做@json,以及table變數@tempTable,並透過FunctionGetValuesJson@json丟進去以後回傳結果至table變數@tempTable中。
程式與結果如下:

    declare @json nvarchar(max)
    set @json = 
    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"]
        }
    }'
    
    declare @tempTable table (topKey nvarchar(4000), [key] nvarchar(4000), [value] nvarchar(4000), IsType bit, IsList bit)

	insert @tempTable 
	select * from GetValuesJson(@json,default)

https://ithelp.ithome.com.tw/upload/images/20190308/20115336y7574YuLCa.png

依照這張表的結果,
當我的IsType=1的時候,我想要呼叫GetValuesJson('{"' + [key] + '":' + [value] + '}',IsList);
當我的IsType=0我想要呼叫insertToTableA(topKey,[key])

FunctionGetValuesJson是將傳入的Json字串做解析,透過join其他表回傳一個table。
FunctioninsertToTable是將傳入的值insert到TableA,並回傳一個@id。

若先不看詳細方法內容,不知道用SQL撰寫的概念為何? 有辦法達成嗎? 謝謝!

你的MSSQL版本?該不會是2000版的吧?
anniecat iT邦新手 4 級 ‧ 2019-03-08 16:50:18 檢舉
我的MSSQL版本是2017版本的

1 個回答

4
張小馬~
iT邦新手 4 級 ‧ 2019-03-08 17:21:45
最佳解答

先確認幾個問題:
FunctionGetValuesJson,做出來的table欄位是固定的嗎?就是這function是建1個table還是建多個table?還是這也只是在做insert?(拍謝看不太懂原題目)
FunctionGetValuesJson 和 FunctioninsertToTable是兩個動作各做一次,還是輪流一直做?各做一次,那就簡單了,輪流一直做,那不建議用SQL寫...(但用SQL還是辦得到...)

換句話說,如果你是兩個分開的動作,
先FunctionGetValuesJson建好固定欄位的table,
再FunctioninsertToTable做資料插入,那OK,SQL還算簡單。
(而不是從第一列第一筆資料開始有順序的遇到IsType=1時建table,遇到IsType=0時insert,就是一下要建table、一下要insert、來來回回這樣,這種就不建議用SQL做。然後每次建的table欄位還都不一樣,那確實蠻複雜的,但還是辦得到。)

依照你的例子,我建議順序如下:

1.先隨便建出一個和你預期要做的table一模一樣的欄位,假設叫table_setof。

2.建FunctionGetValuesJson
CREATE OR REPLACE FUNCTION GetValuesJson()
RETURNS SETOF table_setof AS
$BODY$
BEGIN
--把你要做的事情用SQL寫好
END
$BODY$
LANGUAGE;

3.建FunctioninsertToTable
CREATE OR REPLACE FUNCTION insertToTable()
RETURNS SETOF void AS
$BODY$
BEGIN
--把你要做的事情用SQL寫好
END
$BODY$
LANGUAGE;

4.然後執行兩個function
select * from GetValuesJson();
select insertToTable();

--
可參考我SQL迴圈實作的系列文,對SQL function不熟的,通常是卡在不知道要return什麼,和控制項不知道該怎麼塞,這個就多多google囉(當然也可以再問我啦),不過你的例子簡單來看是上面這樣(至少起手式是這樣)。若更複雜困難也是做得到,有順序有規則就做得到。

--
2019/3/11補充:
看懂了,確實是我定義的外迴圈,以下順序說明:

0:建好function GetValuesJson()

1:你手上有原始資料t1_source_data共1000筆。

2:拿t1_source_dataGetValuesJson(),跑第一次,假設跑完有100筆的【IsType=0】、900筆的【IsType=1】。把這1000筆存成t2_processing這個table。當然,因為我不確定你GetValuesJson()實際做了什麼事,總之這邊先保留所有GetValuesJson()執行後的資料。

3:把100筆的【IsType=0】建一個table,這table的名稱是t3_goalwarehouse(目標倉庫),且裡面有二個欄位(topKey, Key)。

4(1):接著把原始的t1_source_data砍掉,把剩下900筆的【IsType=1】命名為t1_source_data。你會發現這個步驟和第1步驟有點類似,都是要取得t1_source_data,表示從這裡開始,就是迴圈(重複做事情)的範圍。

接著重複1~3步驟,一直跑到再也找不到【IsType=1】的資料(資料筆數=0)。這邊,你必須再建立一個控制項,也就是再建一個返回整數的function寫在WHILE的位置。

除了第一次跑完的1~3步驟必須人工手動執行(當作建立空氣品質優良的迴圈環境),以下是迴圈內1~3要做的事情:

1.砍掉原本的t1_source_data,把舊t2_processing中【IsType=1】的資料當作新的t1_source_data建上去。
2.對t1_source_data執行GetValuesJson(),並存成新t2_processing(當然要先砍掉原本的舊t2_processing)。
3.把新t2_processing中【IsType=0】的資料insert進t3_goalwarehouse,然後繼續1.砍掉原本的...

如下圖:
https://ithelp.ithome.com.tw/upload/images/20190311/20111566SnKuIDMJgv.png

因為我不是很確定你說的「一個sql query似乎不能回傳兩個結果再分別做兩件事情」,所以我直接秀它最後會呈現的長相:

----------以下開始insertToTable()的設定----------
DROP FUNCTION IF EXISTS insertToTable();
CREATE OR REPLACE FUNCTION insertToTable()
RETURNS VOID as
$BODY$
BEGIN
----------以下開始【控制項】設定----------
WHILE IsType=1的資料筆數() > 0
----------以上結束【控制項】設定----------
----------以下開始【迴圈內容】設定----------
LOOP
--t1
DROP TABLE IF EXISTS t1_source_data; 
CREATE TABLE t1_source_data as
select * from t2_processing
where IsType=1
;
--t2
DROP TABLE IF EXISTS t2_processing; 
CREATE TABLE t2_processing as 
拿t1_source_data執行GetValuesJson()

;
--t3
INSERT into t3_goalwarehouse
select topKey, Key
from t2_processing
where IsType=0;
END LOOP
----------以上結束【迴圈內容】設定----------
;
END
$BODY$
LANGUAGE 'plpgsql';
----------以上結束insertToTable()的設定----------

而上面一整個其實就是在設定insertToTable()。這個時候你才真的有把所有必備的function給建立完。

所以完整的執行過程如下,先手動執行完1~3(第一次的1~3),接著

select insertToTable();

這時候所有的事情就做完了。

再去查看t3_goalwarehouse,

select * from t3_goalwarehouse

t3_goalwarehouse裡面就會是最終你想要的結果了。

select insertToTable()確實做到了「一個sql query可以回傳兩個結果再分別做兩件事情」,只是背後有很大的一番功夫要下就是了。

看更多先前的回應...收起先前的回應...
anniecat iT邦新手 4 級 ‧ 2019-03-08 18:04:15 檢舉

謝謝您耐心看完我的問題,然後回覆您詢問的部分 :

  1. FunctionGetValuesJson,做出來的table欄位是固定的嗎?
    ->是的,此方法做完後會回傳1個table結果
    table的長相同我所定義的@tempTable

  2. FunctionGetValuesJson和 FunctioninsertToTable是兩個動作各做一次,還是輪流一直做?
    ->會輪流一直做

目前因需求所以需要用SQL來做,然後希望可以做到是,
一開始會先有一個字串@json,然後丟到FunctionGetValuesJson後,它的結果會是(長相如同@tempTable) :
https://ithelp.ithome.com.tw/upload/images/20190308/20115336iWpb9ugWcP.png
所以當我發現IsType=1的時候,就要呼叫GetValuesJson('{"' + [key] + '":' + [value] + '}',IsList)繼續做解析,
當我的IsType=0的時候就要呼叫insertToTableA(topKey,[key]),並希望他回傳的值到一個變數,若無法回傳,可能就寫在另外一個方法裡

只是目前尚未找到方法,也有點沒頭緒...
因此嘗試提問看能否有什麼建議!

我問更詳細一點:
你希望GetValuesJson實際做什麼事情?
1.建立table:CREATE TABLE name AS select...
2.把資料寫入某table:INSERT into table_name select...

我問題在於,單一function(非包覆式的)只能做一件事,如果它做的是1.建立table,又要輪流一直做,那它會建出很多張table,只是這不同的table_name擁有著相同的欄位,是這意思嗎?

https://ithelp.ithome.com.tw/upload/images/20190308/20111566QBBHtFHACh.png
你是不是想做類似上圖的事?

這個不是三言兩語講得完的XD
還是貼我自己SQL迴圈實作 -6.英文文字雲的處理工廠1這文章給你參考,兩篇而已,看有沒有機會融會貫通。

概念大致是:先把你打算要做到的最終table的格式給做出來(可以先不必有資料),當作倉庫的概念,然後透過幾個function,去把資料塞進倉庫裡面,function過程必須包含:處理【即將被處理的資料】、將【處理完畢】的資料塞入【倉庫】、把【處理完畢】後的【剩餘資料】取代回原本【即將被處理的資料】、處理【即將被處理的資料】,然後迴圈或做控制項從第1列跑到max列後停止。

anniecat iT邦新手 4 級 ‧ 2019-03-11 11:13:14 檢舉

感謝您耐心回覆,重新整理一下問題,並將每一筆結果顯示,如附圖。

https://ithelp.ithome.com.tw/upload/images/20190311/20115336JrHo3z2PCm.png

而每一次的結果都需要分開做事情,所以先不考慮使用遞迴的方式一次解析完再儲存至資料庫中,不知道有辦法在同一個sql query中,做兩件事情嗎?

另外,我看一個sql query似乎不能回傳兩個結果再分別做兩件事情,所以想說回傳一個結果作為判斷的依據,不知道這樣的理解是對的嗎?

再次感謝!

https://ithelp.ithome.com.tw/upload/images/20190311/201153366AildXuFmH.png

我要發表回答

立即登入回答