在PL/SQL中如何使用JSON數(shù)組？在PL/SQL中JSON數(shù)組使用的方法！

級(jí)高速公路ETC識(shí)別機(jī)攜帶者 2021-09-24 15:49:15 瀏覽數(shù) (4879)

反饋

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)需要記住：

錯(cuò)誤處理行為

默認(rèn)情況下，如果在為 JSON 數(shù)組（或?qū)ο螅┱{(diào)用成員方法時(shí)發(fā)生錯(cuò)誤，則返回 NULL。換句話(huà)說(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() - 1

JSON 數(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è)定義的索引值（記住：字符串?。?。NEXT 函數(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)型為查詢(xún)和構(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ù)組很容易。

SQL

0 人點(diǎn)贊