Oracle Database 12c 第 2 版基于 12.1 SQL/JSON 特性構(gòu)建，添加了許多用于操作 PL/SQL 塊中的 JSON 數(shù)據(jù)的內(nèi)置對(duì)象類(lèi)型（類(lèi)似于面向?qū)ο笳Z(yǔ)言中的類(lèi)）。
在這篇文章中，我探索了一些通過(guò) JSON_ARRAY_T 類(lèi)型及其方法提供的面向數(shù)組的 JSON 特性。
就像類(lèi)一樣，對(duì)象類(lèi)型提供了一個(gè)預(yù)定義的構(gòu)造函數(shù)來(lái)實(shí)例化該類(lèi)型的新實(shí)例、靜態(tài)方法和成員方法。
以下是您最有可能使用的方法：
一般來(lái)說(shuō)，關(guān)于在 PL/SQL 中使用 JSON 元素和 JSON 數(shù)組，有幾點(diǎn)需要記?。?/p>
錯(cuò)誤處理行為
默認(rèn)情況下，如果在為 JSON 數(shù)組（或?qū)ο螅┱{(diào)用成員方法時(shí)發(fā)生錯(cuò)誤，則返回 NULL。換句話說(shuō)，異常不會(huì)引發(fā)回您的塊。
如果您希望錯(cuò)誤作為異常從該方法傳播，請(qǐng)調(diào)用 ON_ERROR 方法并傳遞一個(gè)大于 0 的值。
數(shù)組索引
在 PL/SQL 中，您可能知道，嵌套表和變量數(shù)組中的索引從 1 開(kāi)始，而不是 0。使用關(guān)聯(lián)數(shù)組，它可以從您想要的任何位置開(kāi)始。:-)
JSON 數(shù)組索引從 0 開(kāi)始，這在許多其他編程語(yǔ)言中很常見(jiàn)，我們?cè)?Oracle 數(shù)據(jù)庫(kù)中使用 JSON 數(shù)組遵循該約定。所以你不想遍歷一個(gè)帶有循環(huán)頭的 JSON 數(shù)組，如下所示：
FOR indx IN 1 .. my_array.get_size()相反，你應(yīng)該這樣寫(xiě)：
FOR indx IN 0 .. my_array.get_size() - 1JSON 數(shù)組基礎(chǔ)
數(shù)組是方括號(hào)內(nèi)以逗號(hào)分隔的元素列表，如下所示：
["SQL", "PL/SQL"]
JSON 數(shù)組的索引從 0 開(kāi)始，這與 PL/SQL 集合的規(guī)范不同（嵌套表和數(shù)組從索引值 1 開(kāi)始）。
所以上面顯示的數(shù)組的元素定義在索引值 0 和 1，而不是 1 和 2。
數(shù)組中元素的順序很重要，與對(duì)象的順序不同，對(duì)象的成員順序并不重要（類(lèi)似于關(guān)系表）。
JSON 數(shù)組可以在其中包含標(biāo)量、對(duì)象和數(shù)組。這些都是有效的 JSON 數(shù)組：
1. 包含單個(gè)標(biāo)量值的數(shù)組
[1]
2. 包含三個(gè)標(biāo)量的數(shù)組
[1,2,"three"]
3. 三個(gè) JSON 對(duì)象的數(shù)組
[{"object":1},{"inside":2},{"array":3}]
4. 包含布爾文字、標(biāo)量數(shù)組和對(duì)象的數(shù)組
[true,[1,2,3],{"name":"steven"},]
構(gòu)建您自己的陣列
有時(shí)數(shù)組是提供給你的，你需要去探索（參見(jiàn)下面的遞歸循環(huán)通過(guò)數(shù)組）。有時(shí)您需要根據(jù)表或程序中的數(shù)據(jù)構(gòu)造一個(gè)數(shù)組。
JSON_ARRAY_T 類(lèi)型為 BYOA（“構(gòu)建您自己的數(shù)組”）提供了許多成員過(guò)程：
- APPEND – 在數(shù)組末尾追加一個(gè)新項(xiàng)目
- APPEND_NULL – 在數(shù)組末尾追加一個(gè)新項(xiàng)目
- PUT - 在數(shù)組中的指定位置添加或修改元素
- PUT_NULL – 將數(shù)組中指定位置的元素值設(shè)置為 NULL
為了演示 append，我創(chuàng)建了一個(gè)“to JSON”包，它將字符串索引關(guān)聯(lián)數(shù)組轉(zhuǎn)換為 JSON 數(shù)組（它還包含其他“to JSON”函數(shù)；用這個(gè) LiveSQL 腳本自己嘗試一下）。
返回的 JSON 數(shù)組中的每個(gè)元素都是表單中的一個(gè) JSON 對(duì)象
{"index-value":"item-value"}
其中 index-value 是關(guān)聯(lián)數(shù)組中的字符串索引值，item-value 是數(shù)組中該位置的項(xiàng)的值。
這是包裝規(guī)格；請(qǐng)注意，關(guān)聯(lián)數(shù)組由定義為 VARCHAR2(50) 的子類(lèi)型 INDEX_T 索引。
PACKAGE to_json AUTHID DEFINER
IS
SUBTYPE index_t IS VARCHAR2 (50);
TYPE assoc_array_t IS TABLE OF VARCHAR2 (100)
INDEX BY index_t;
FUNCTION to_object (key_in IN VARCHAR2, value_in IN VARCHAR2)
RETURN json_object_t;
FUNCTION to_array (assoc_array_in IN assoc_array_t)
RETURN json_array_t;
END;這是包體：
PACKAGE BODY to_json
IS
FUNCTION to_object (key_in IN VARCHAR2, value_in IN VARCHAR2)
RETURN json_object_t
IS
BEGIN
RETURN json_object_t ('{"' || key_in || '":"' || value_in || '"}');
END;
FUNCTION to_array (assoc_array_in IN assoc_array_t)
RETURN json_array_t
IS
l_index index_t := assoc_array_in.FIRST;
l_json_array json_array_t := json_array_t ();
BEGIN
WHILE l_index IS NOT NULL
LOOP
DBMS_OUTPUT.put_line (
'Appending ' || l_index || ':' || assoc_array_in (l_index));
l_json_array.append (to_object (l_index, assoc_array_in (l_index)));
DBMS_OUTPUT.put_line ('Watch it grow! ' || l_json_array.get_size ());
l_index := assoc_array_in.NEXT (l_index);
END LOOP;
RETURN l_json_array;
END;
END;to_object 函數(shù)隱藏了從鍵和值構(gòu)造有效 JSON 對(duì)象的所有細(xì)節(jié)。to_array 函數(shù)解釋如下：
- 接受一個(gè)關(guān)聯(lián)數(shù)組，返回一個(gè) JSON 數(shù)組對(duì)象類(lèi)型實(shí)例。
- 由于這是一個(gè)字符串索引集合，我不能使用“FOR index IN 1 .. array.COUNT”方法。相反，我從定義的最低索引值開(kāi)始（在第 13 行通過(guò)調(diào)用 FIRST 函數(shù)檢索）并使用 WHILE LOOP。
- 調(diào)用 JSON_OBJECT_T append 成員方法以在 JSON 數(shù)組的末尾添加一個(gè)元素。我要添加什么？使用 to_json.to_object 函數(shù)從關(guān)聯(lián)數(shù)組索引和項(xiàng)構(gòu)造的 JSON 對(duì)象。
- 找到下一個(gè)定義的索引值（記?。鹤址。EXT 函數(shù)在經(jīng)過(guò)最后一個(gè)索引值時(shí)返回 NULL，這將停止 WHILE 循環(huán)。
- 返回 JSON 數(shù)組。
是時(shí)候運(yùn)行一些代碼了！
在下面的塊中，我利用了 new-to-18c 限定表達(dá)式功能，允許我使用單個(gè)表達(dá)式初始化字符串索引數(shù)組的內(nèi)容。然后我將它轉(zhuǎn)換為一個(gè) JSON 數(shù)組，并顯示結(jié)果，所有這些都在對(duì) DBMS_OUTPUT.put_line 的單個(gè)調(diào)用中：
DECLARE
l_array to_json.assoc_array_t :=
to_json.assoc_array_t (
'yes' => 'you', 'can'=>'in', 'oracledatabase'=>'18c',
'fullstop'=>NULL, 'and then'=>'some');
BEGIN
DBMS_OUTPUT.put_line (to_json.to_array (l_array).to_string ());
END;
/結(jié)果如下：
Appending and then:some
Watch it grow! 1
Appending can:in
Watch it grow! 2
Appending fullstop:
Watch it grow! 3
Appending oracledatabase:18c
Watch it grow! 4
Appending yes:you
Watch it grow! 5
[{"andthen":"some"},{"can":"in"},{"fullstop":""},{"oracledatabase":"18c"},{"yes":"you"}]請(qǐng)注意，JSON 數(shù)組中的項(xiàng)目與它們?cè)谔畛潢P(guān)聯(lián)數(shù)組的限定表達(dá)式中出現(xiàn)的順序不同。這是由于將值放入字符串索引集合時(shí)按字符集順序自動(dòng)排序。
遞歸循環(huán)遍歷數(shù)組
一些 JSON 數(shù)組是標(biāo)量甚至對(duì)象的簡(jiǎn)單列表。但是許多數(shù)組中還包含其他數(shù)組。使用這些帶有嵌套數(shù)組的數(shù)組，您可能希望遍歷該層次結(jié)構(gòu)中的所有“葉子”。最簡(jiǎn)單的方法是使用遞歸。讓我們建立一個(gè)過(guò)程來(lái)做到這一點(diǎn)。
本節(jié)中的所有代碼都可以在LiveSQL上找到、運(yùn)行和使用。
首先，我將創(chuàng)建一個(gè)幫助程序來(lái)顯示字符串，縮進(jìn)以顯示其在 JSON 數(shù)組層次結(jié)構(gòu)中的位置：
CREATE OR REPLACE PROCEDURE put_line (
string_in IN VARCHAR2,
pad_in IN INTEGER DEFAULT 0)
IS
BEGIN
DBMS_OUTPUT.put_line (LPAD (' ', pad_in * 3) || string_in);
END;
/我的 DBMS_OUTPUT.put_line 版本在 json_array_traversal 過(guò)程中的多個(gè)地方使用，如下所示。
CREATE OR REPLACE PROCEDURE json_array_traversal (
json_document_in IN CLOB,
leaf_action_in IN VARCHAR2,
level_in IN INTEGER DEFAULT 0)
AUTHID DEFINER
IS
l_array json_array_t;
l_object json_object_t;
l_keys json_key_list;
l_element json_element_t;
BEGIN
l_array := json_array_t.parse (json_document_in);
put_line ('Traverse: ' || l_array.stringify (), level_in);
FOR indx IN 0 .. l_array.get_size - 1
LOOP
put_line ('Index: ' || indx, level_in);
CASE
WHEN l_array.get (indx).is_string
THEN
EXECUTE IMMEDIATE leaf_action_in
USING l_array.get_string (indx), level_in;
WHEN l_array.get (indx).is_object
THEN
l_object := TREAT (l_array.get (indx) AS json_object_t);
l_keys := l_object.get_keys;
FOR k_index IN 1 .. l_keys.COUNT
LOOP
EXECUTE IMMEDIATE leaf_action_in
USING l_keys (k_index), level_in;
END LOOP;
WHEN l_array.get (indx).is_array
THEN
json_array_traversal (
TREAT (l_array.get (indx) AS json_array_t).stringify (),
leaf_action_in,
level_in + 1);
ELSE
DBMS_OUTPUT.put_line (
'*** No match for type on array index ' || indx);
END CASE;
END LOOP;
END;這是該代碼的敘述性描述：
傳入包含 JSON 文檔的 CLOB，對(duì)于此過(guò)程，它應(yīng)該是一個(gè)數(shù)組。“葉動(dòng)作”參數(shù)的實(shí)際值是遇到葉時(shí)要執(zhí)行的動(dòng)態(tài) PL/SQL 塊。你不太可能在生產(chǎn)代碼中使用任何這種通用的東西，但它作為一個(gè)實(shí)用程序可能非常方便。
定義多個(gè) JSON 對(duì)象類(lèi)型的實(shí)例：數(shù)組、對(duì)象、鍵列表和元素。
將文檔（文本）解析為分層的內(nèi)存表示。此時(shí)，如果 json_document_in 不是有效數(shù)組，則會(huì)引發(fā)以下錯(cuò)誤：
ORA-40587: invalid JSON type
您可以使用以下塊驗(yàn)證這一點(diǎn)：
DECLARE
l_doc CLOB := '{"name":"Spider"}';
BEGIN
json_array_traversal (
l_doc,
q'[BEGIN NULL; END;]');
END;OK，那我就顯示傳入的文檔，利用stringify方法。
遍歷數(shù)組中的每個(gè)元素。get_size 方法返回?cái)?shù)組中元素的數(shù)量。請(qǐng)記住，JSON 數(shù)組索引以零 (0) 開(kāi)頭。所以這有效：
FOR indx IN 0 .. l_array.get_size – 1
但是通過(guò) PL/SQL 嵌套表與迭代一致的公式，例如：
FOR indx IN 1 .. l_array.get_size
很可能會(huì)導(dǎo)致這個(gè)錯(cuò)誤：
ORA-30625: method dispatch on NULL SELF argument is disallowed
數(shù)組中的元素可以是標(biāo)量、對(duì)象或另一個(gè)數(shù)組。所以我為每種可能性提供了一個(gè) WHEN 子句。嗯，不是每一個(gè)。標(biāo)量的類(lèi)型比字符串多，但我將 CASE 語(yǔ)句的擴(kuò)展留給親愛(ài)的讀者，以涵蓋所有標(biāo)量類(lèi)型。
如果元素是標(biāo)量字符串，那么我使用本機(jī)動(dòng)態(tài) SQL 來(lái)執(zhí)行提供的 PL/SQL 塊。我傳遞給字符串值（通過(guò)調(diào)用該索引值的 get_string 方法）和級(jí)別（以便條目在輸出中正確縮進(jìn)）。
對(duì)于一個(gè)對(duì)象，我獲取它的所有鍵，然后對(duì)每個(gè)鍵值執(zhí)行葉子操作。注意：這是我選擇為對(duì)象執(zhí)行的操作。在更完整的實(shí)現(xiàn)中，您將遍歷對(duì)象的值，并根據(jù)值的類(lèi)型采取特定操作。例如，一個(gè)對(duì)象可以在其中包含一個(gè)數(shù)組，如下所示：
{"chicken_noises":["click","clack","cluck"]}
最后，如果是數(shù)組，我遞歸調(diào)用遍歷過(guò)程，傳遞：
1.這個(gè)元素，轉(zhuǎn)換成數(shù)組，然后再轉(zhuǎn)換回字符串格式。
2.同葉動(dòng)作動(dòng)態(tài)塊
3. 等級(jí)，提升1。
當(dāng)我調(diào)用遍歷過(guò)程如下：
DECLARE
l_doc CLOB :=
'["Stirfry",
{"name":"Spider"},
"Mosquitos",
["finger","toe","nose"]
]';
BEGIN
json_array_traversal (
l_doc,
q'[BEGIN put_line ('Leaf: '|| :val, :tlevel); END;]');
END;
/我看到以下輸出：
Traverse: ["Stirfry",{"name":"Spider"},"Mosquitos",["finger","toe","nose"]]
Index: 0
Leaf: Stirfry
Index: 1
Leaf: name
Index: 2
Leaf: Mosquitos
Index: 3
Traverse: ["finger","toe","nose"]
Index: 0
Leaf: finger
Index: 1
Leaf: toe
Index: 2
Leaf: nose并通過(guò)以下調(diào)用：
DECLARE
l_doc CLOB := '["Stirfry",
{"name":"Spider"},
"Mosquitos",
["finger",
"toe",
[{"object":1},{"inside":2},{"array":3}]
],
{"elbow":"tennis"}
]';
BEGIN
json_array_traversal (
l_doc,
q'[BEGIN put_line ('Leaf: '|| :val, :tlevel); END;]');
END;
/我看到這個(gè)輸出：
Traverse: ["Stirfry",{"name":"Spider"},"Mosquitos",["finger","toe",[{"object":1},{"inside":2},{"array":3}]],{"elbow":"tennis"}]
Index: 0
Leaf: Stirfry
Index: 1
Leaf: name
Index: 2
Leaf: Mosquitos
Index: 3
Traverse: ["finger","toe",[{"object":1},{"inside":2},{"array":3}]]
Index: 0
Leaf: finger
Index: 1
Leaf: toe
Index: 2
Traverse: [{"object":1},{"inside":2},{"array":3}]
Index: 0
Leaf: object
Index: 1
Leaf: inside
Index: 2
Leaf: array
Index: 4
Leaf: elbow概括
JSON 數(shù)組被廣泛使用。它們也非常靈活，因?yàn)樗鼈兛梢园瑯?biāo)量、對(duì)象和其他數(shù)組。JSON 數(shù)組的結(jié)構(gòu)越復(fù)雜和嵌套，處理起來(lái)就越具有挑戰(zhàn)性。
JSON_ARRAY_T 對(duì)象類(lèi)型為查詢和構(gòu)造 JSON 數(shù)組提供了一個(gè)干凈、快速的 API。一旦您能夠?qū)?PL/SQL 數(shù)組與 JSON 數(shù)組相關(guān)聯(lián)（例如，糾正索引中的差異），您會(huì)發(fā)現(xiàn)在您的 PL/SQL 代碼中編寫(xiě)代碼來(lái)處理 JSON 數(shù)組很容易。
