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

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

反饋

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>

錯誤處理行為

默認(rèn)情況下，如果在為 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() - 1

JSON 數(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 對象的所有細(xì)節(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 限定表達(dá)式功能，允許我使用單個表達(dá)式初始化字符串索引數(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ù)組的限定表達(dá)式中出現(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ù)組很容易。

SQL

0 人點贊

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