在PL/SQL中怎么使用JSON數組

這篇文章主要介紹“在PL/SQL中怎么使用JSON數組”的相關知識，小編通過實際案例向大家展示操作過程，操作方法簡單快捷，實用性強，希望這篇“在PL/SQL中怎么使用JSON數組”文章能幫助大家解決問題。

就像類一樣，對象類型提供了一個預定義的構造函數來實例化該類型的新實例、靜態方法和成員方法。

以下是您最有可能使用的方法：

一般來說，關于在 PL/SQL 中使用 JSON 元素和 JSON 數組，有幾點需要記住：

錯誤處理行為

默認情況下，如果在為 JSON 數組（或對象）調用成員方法時發生錯誤，則返回 NULL。換句話說，異常不會引發回您的塊。

如果您希望錯誤作為異常從該方法傳播，請調用 ON_ERROR 方法并傳遞一個大于 0 的值。

數組索引

在 PL/SQL 中，您可能知道，嵌套表和變量數組中的索引從 1 開始，而不是 0。使用關聯數組，它可以從您想要的任何位置開始。:-)

JSON 數組索引從 0 開始，這在許多其他編程語言中很常見，我們在 Oracle 數據庫中使用 JSON 數組遵循該約定。所以你不想遍歷一個帶有循環頭的 JSON 數組，如下所示：

FOR indx IN 1 .. my_array.get_size()

相反，你應該這樣寫：

FOR indx IN  0 .. my_array.get_size() - 1

JSON 數組基礎

數組是方括號內以逗號分隔的元素列表，如下所示：

 ["SQL", "PL/SQL"] 

JSON 數組的索引從 0 開始，這與 PL/SQL 集合的規范不同（嵌套表和數組從索引值 1 開始）。

所以上面顯示的數組的元素定義在索引值 0 和 1，而不是 1 和 2。

數組中元素的順序很重要，與對象的順序不同，對象的成員順序并不重要（類似于關系表）。

JSON 數組可以在其中包含標量、對象和數組。這些都是有效的 JSON 數組：

1. 包含單個標量值的數組

 [1] 

2. 包含三個標量的數組

 [1,2,"three"] 

3. 三個 JSON 對象的數組

 [{"object":1},{"inside":2},{"array":3}] 

4. 包含布爾文字、標量數組和對象的數組

 [true,[1,2,3],{"name":"steven"},] 

構建您自己的陣列

有時數組是提供給你的，你需要去探索（參見下面的遞歸循環通過數組）。有時您需要根據表或程序中的數據構造一個數組。

JSON_ARRAY_T 類型為 BYOA（“構建您自己的數組”）提供了許多成員過程：

• APPEND – 在數組末尾追加一個新項目

• APPEND_NULL – 在數組末尾追加一個新項目

• PUT - 在數組中的指定位置添加或修改元素

• PUT_NULL – 將數組中指定位置的元素值設置為 NULL

為了演示 append，我創建了一個“to JSON”包，它將字符串索引關聯數組轉換為 JSON 數組（它還包含其他“to JSON”函數；用這個 LiveSQL 腳本自己嘗試一下）。

返回的 JSON 數組中的每個元素都是表單中的一個 JSON 對象

 {"index-value":"item-value"} 

其中 index-value 是關聯數組中的字符串索引值，item-value 是數組中該位置的項的值。

這是包裝規格；請注意，關聯數組由定義為 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 函數隱藏了從鍵和值構造有效 JSON 對象的所有細節。to_array 函數解釋如下：

• 接受一個關聯數組，返回一個 JSON 數組對象類型實例。

• 由于這是一個字符串索引集合，我不能使用“FOR index IN 1 .. array.COUNT”方法。相反，我從定義的最低索引值開始（在第 13 行通過調用 FIRST 函數檢索）并使用 WHILE LOOP。

• 調用 JSON_OBJECT_T append 成員方法以在 JSON 數組的末尾添加一個元素。我要添加什么？使用 to_json.to_object 函數從關聯數組索引和項構造的 JSON 對象。

• 找到下一個定義的索引值（記住：字符串！）。NEXT 函數在經過最后一個索引值時返回 NULL，這將停止 WHILE 循環。

• 返回 JSON 數組。

是時候運行一些代碼了！

在下面的塊中，我利用了 new-to-18c 限定表達式功能，允許我使用單個表達式初始化字符串索引數組的內容。然后我將它轉換為一個 JSON 數組，并顯示結果，所有這些都在對 DBMS_OUTPUT.put_line 的單個調用中：

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;
/

結果如下：

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 數組中的項目與它們在填充關聯數組的限定表達式中出現的順序不同。這是由于將值放入字符串索引集合時按字符集順序自動排序。

遞歸循環遍歷數組

一些 JSON 數組是標量甚至對象的簡單列表。但是許多數組中還包含其他數組。使用這些帶有嵌套數組的數組，您可能希望遍歷該層次結構中的所有“葉子”。最簡單的方法是使用遞歸。讓我們建立一個過程來做到這一點。

本節中的所有代碼都可以在LiveSQL上找到、運行和使用。

首先，我將創建一個幫助程序來顯示字符串，縮進以顯示其在 JSON 數組層次結構中的位置：

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，對于此過程，它應該是一個數組。“葉動作”參數的實際值是遇到葉時要執行的動態 PL/SQL 塊。你不太可能在生產代碼中使用任何這種通用的東西，但它作為一個實用程序可能非常方便。

定義多個 JSON 對象類型的實例：數組、對象、鍵列表和元素。

將文檔（文本）解析為分層的內存表示。此時，如果 json_document_in 不是有效數組，則會引發以下錯誤：

 ORA-40587: invalid JSON type 

您可以使用以下塊驗證這一點：

DECLARE
   l_doc CLOB := '{"name":"Spider"}';
BEGIN
   json_array_traversal (
      l_doc,
      q'[BEGIN NULL; END;]');
END;

OK，那我就顯示傳入的文檔，利用stringify方法。

遍歷數組中的每個元素。get_size 方法返回數組中元素的數量。請記住，JSON 數組索引以零 (0) 開頭。所以這有效：

 FOR indx IN 0 .. l_array.get_size – 1 

但是通過 PL/SQL 嵌套表與迭代一致的公式，例如：

 FOR indx IN 1 .. l_array.get_size 

很可能會導致這個錯誤：

 ORA-30625: method dispatch on NULL SELF argument is disallowed 

數組中的元素可以是標量、對象或另一個數組。所以我為每種可能性提供了一個 WHEN 子句。嗯，不是每一個。標量的類型比字符串多，但我將 CASE 語句的擴展留給親愛的讀者，以涵蓋所有標量類型。

如果元素是標量字符串，那么我使用本機動態 SQL 來執行提供的 PL/SQL 塊。我傳遞給字符串值（通過調用該索引值的 get_string 方法）和級別（以便條目在輸出中正確縮進）。

對于一個對象，我獲取它的所有鍵，然后對每個鍵值執行葉子操作。注意：這是我選擇為對象執行的操作。在更完整的實現中，您將遍歷對象的值，并根據值的類型采取特定操作。例如，一個對象可以在其中包含一個數組，如下所示：

 {"chicken_noises":["click","clack","cluck"]} 

最后，如果是數組，我遞歸調用遍歷過程，傳遞：

1.這個元素，轉換成數組，然后再轉換回字符串格式。

2.同葉動作動態塊

3. 等級，提升1。

當我調用遍歷過程如下：

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

并通過以下調用：

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

關于“在PL/SQL中怎么使用JSON數組”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識，可以關注億速云行業資訊頻道，小編每天都會為大家更新不同的知識點。