Oracle Database 12c 第 2 版基于 12.1 SQL/JSON 特性構(gòu)建，添加了許多用于操作 PL/SQL 塊中的 JSON 數(shù)據(jù)的內(nèi)置對象類型（類似于面向?qū)ο笳Z言中的類）。
在這篇文章中，我探索了一些通過 JSON_ARRAY_T 類型及其方法提供的面向數(shù)組的 JSON 特性。
就像類一樣，對象類型提供了一個預(yù)定義的構(gòu)造函數(shù)來實例化該類型的新實例、靜態(tài)方法和成員方法。
以下是您最有可能使用的方法：
一般來說，關(guān)于在 PL/SQL 中使用 JSON 元素和 JSON 數(shù)組，有幾點需要記?。?/p>
錯誤處理行為
默認情況下，如果在為 JSON 數(shù)組（或?qū)ο螅┱{(diào)用成員方法時發(fā)生錯誤，則返回 NULL。換句話說，異常不會引發(fā)回您的塊。
如果您希望錯誤作為異常從該方法傳播，請調(diào)用 ON_ERROR 方法并傳遞一個大于 0 的值。
數(shù)組索引
在 PL/SQL 中，您可能知道，嵌套表和變量數(shù)組中的索引從 1 開始，而不是 0。使用關(guān)聯(lián)數(shù)組，它可以從您想要的任何位置開始。:-)
JSON 數(shù)組索引從 0 開始，這在許多其他編程語言中很常見，我們在 Oracle 數(shù)據(jù)庫中使用 JSON 數(shù)組遵循該約定。所以你不想遍歷一個帶有循環(huán)頭的 JSON 數(shù)組，如下所示：
FOR indx IN 1 .. my_array.get_size()相反，你應(yīng)該這樣寫：
FOR indx IN 0 .. my_array.get_size() - 1JSON 數(shù)組基礎(chǔ)
數(shù)組是方括號內(nèi)以逗號分隔的元素列表，如下所示：
["SQL", "PL/SQL"]
JSON 數(shù)組的索引從 0 開始，這與 PL/SQL 集合的規(guī)范不同（嵌套表和數(shù)組從索引值 1 開始）。
所以上面顯示的數(shù)組的元素定義在索引值 0 和 1，而不是 1 和 2。
數(shù)組中元素的順序很重要，與對象的順序不同，對象的成員順序并不重要（類似于關(guān)系表）。
JSON 數(shù)組可以在其中包含標(biāo)量、對象和數(shù)組。這些都是有效的 JSON 數(shù)組：
1. 包含單個標(biāo)量值的數(shù)組
[1]
2. 包含三個標(biāo)量的數(shù)組
[1,2,"three"]
3. 三個 JSON 對象的數(shù)組
[{"object":1},{"inside":2},{"array":3}]
4. 包含布爾文字、標(biāo)量數(shù)組和對象的數(shù)組
[true,[1,2,3],{"name":"steven"},]
構(gòu)建您自己的陣列
有時數(shù)組是提供給你的，你需要去探索（參見下面的遞歸循環(huán)通過數(shù)組）。有時您需要根據(jù)表或程序中的數(shù)據(jù)構(gòu)造一個數(shù)組。
JSON_ARRAY_T 類型為 BYOA（“構(gòu)建您自己的數(shù)組”）提供了許多成員過程：
- APPEND – 在數(shù)組末尾追加一個新項目
- APPEND_NULL – 在數(shù)組末尾追加一個新項目
- PUT - 在數(shù)組中的指定位置添加或修改元素
- PUT_NULL – 將數(shù)組中指定位置的元素值設(shè)置為 NULL
為了演示 append，我創(chuàng)建了一個“to JSON”包，它將字符串索引關(guān)聯(lián)數(shù)組轉(zhuǎn)換為 JSON 數(shù)組（它還包含其他“to JSON”函數(shù)；用這個 LiveSQL 腳本自己嘗試一下）。
返回的 JSON 數(shù)組中的每個元素都是表單中的一個 JSON 對象
{"index-value":"item-value"}
其中 index-value 是關(guān)聯(lián)數(shù)組中的字符串索引值，item-value 是數(shù)組中該位置的項的值。
這是包裝規(guī)格；請注意，關(guān)聯(lián)數(shù)組由定義為 VARCHAR2(50) 的子類型 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 對象的所有細節(jié)。to_array 函數(shù)解釋如下：
- 接受一個關(guān)聯(lián)數(shù)組，返回一個 JSON 數(shù)組對象類型實例。
- 由于這是一個字符串索引集合，我不能使用“FOR index IN 1 .. array.COUNT”方法。相反，我從定義的最低索引值開始（在第 13 行通過調(diào)用 FIRST 函數(shù)檢索）并使用 WHILE LOOP。
- 調(diào)用 JSON_OBJECT_T append 成員方法以在 JSON 數(shù)組的末尾添加一個元素。我要添加什么？使用 to_json.to_object 函數(shù)從關(guān)聯(lián)數(shù)組索引和項構(gòu)造的 JSON 對象。
- 找到下一個定義的索引值（記住：字符串?。?。NEXT 函數(shù)在經(jīng)過最后一個索引值時返回 NULL，這將停止 WHILE 循環(huán)。
- 返回 JSON 數(shù)組。
是時候運行一些代碼了！
在下面的塊中，我利用了 new-to-18c 限定表達式功能，允許我使用單個表達式初始化字符串索引數(shù)組的內(nèi)容。然后我將它轉(zhuǎn)換為一個 JSON 數(shù)組，并顯示結(jié)果，所有這些都在對 DBMS_OUTPUT.put_line 的單個調(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"}]請注意，JSON 數(shù)組中的項目與它們在填充關(guān)聯(lián)數(shù)組的限定表達式中出現(xiàn)的順序不同。這是由于將值放入字符串索引集合時按字符集順序自動排序。
遞歸循環(huán)遍歷數(shù)組
一些 JSON 數(shù)組是標(biāo)量甚至對象的簡單列表。但是許多數(shù)組中還包含其他數(shù)組。使用這些帶有嵌套數(shù)組的數(shù)組，您可能希望遍歷該層次結(jié)構(gòu)中的所有“葉子”。最簡單的方法是使用遞歸。讓我們建立一個過程來做到這一點。
本節(jié)中的所有代碼都可以在LiveSQL上找到、運行和使用。
首先，我將創(chuàng)建一個幫助程序來顯示字符串，縮進以顯示其在 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 過程中的多個地方使用，如下所示。
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，對于此過程，它應(yīng)該是一個數(shù)組?！叭~動作”參數(shù)的實際值是遇到葉時要執(zhí)行的動態(tài) PL/SQL 塊。你不太可能在生產(chǎn)代碼中使用任何這種通用的東西，但它作為一個實用程序可能非常方便。
定義多個 JSON 對象類型的實例：數(shù)組、對象、鍵列表和元素。
將文檔（文本）解析為分層的內(nèi)存表示。此時，如果 json_document_in 不是有效數(shù)組，則會引發(fā)以下錯誤：
ORA-40587: invalid JSON type
您可以使用以下塊驗證這一點：
DECLARE
l_doc CLOB := '{"name":"Spider"}';
BEGIN
json_array_traversal (
l_doc,
q'[BEGIN NULL; END;]');
END;OK，那我就顯示傳入的文檔，利用stringify方法。
遍歷數(shù)組中的每個元素。get_size 方法返回數(shù)組中元素的數(shù)量。請記住，JSON 數(shù)組索引以零 (0) 開頭。所以這有效：
FOR indx IN 0 .. l_array.get_size – 1
但是通過 PL/SQL 嵌套表與迭代一致的公式，例如：
FOR indx IN 1 .. l_array.get_size
很可能會導(dǎo)致這個錯誤：
ORA-30625: method dispatch on NULL SELF argument is disallowed
數(shù)組中的元素可以是標(biāo)量、對象或另一個數(shù)組。所以我為每種可能性提供了一個 WHEN 子句。嗯，不是每一個。標(biāo)量的類型比字符串多，但我將 CASE 語句的擴展留給親愛的讀者，以涵蓋所有標(biāo)量類型。
如果元素是標(biāo)量字符串，那么我使用本機動態(tài) SQL 來執(zhí)行提供的 PL/SQL 塊。我傳遞給字符串值（通過調(diào)用該索引值的 get_string 方法）和級別（以便條目在輸出中正確縮進）。
對于一個對象，我獲取它的所有鍵，然后對每個鍵值執(zhí)行葉子操作。注意：這是我選擇為對象執(zhí)行的操作。在更完整的實現(xiàn)中，您將遍歷對象的值，并根據(jù)值的類型采取特定操作。例如，一個對象可以在其中包含一個數(shù)組，如下所示：
{"chicken_noises":["click","clack","cluck"]}
最后，如果是數(shù)組，我遞歸調(diào)用遍歷過程，傳遞：
1.這個元素，轉(zhuǎn)換成數(shù)組，然后再轉(zhuǎn)換回字符串格式。
2.同葉動作動態(tài)塊
3. 等級，提升1。
當(dāng)我調(diào)用遍歷過程如下：
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并通過以下調(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;
/我看到這個輸出：
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ù)組被廣泛使用。它們也非常靈活，因為它們可以包含標(biāo)量、對象和其他數(shù)組。JSON 數(shù)組的結(jié)構(gòu)越復(fù)雜和嵌套，處理起來就越具有挑戰(zhàn)性。
JSON_ARRAY_T 對象類型為查詢和構(gòu)造 JSON 數(shù)組提供了一個干凈、快速的 API。一旦您能夠?qū)?PL/SQL 數(shù)組與 JSON 數(shù)組相關(guān)聯(lián)（例如，糾正索引中的差異），您會發(fā)現(xiàn)在您的 PL/SQL 代碼中編寫代碼來處理 JSON 數(shù)組很容易。
