在PL/SQL中如何使用JSON数组？在PL/SQL中JSON数组使用的方法！

级高速公路ETC识别机携带者 2021-09-24 15:49:15 浏览数 (4663)
反馈

Oracle Database 12c 第 2 版基于 12.1 SQL/JSON 特性构建，添加了许多用于操作 PL/SQL 块中的 JSON 数据的内置对象类型（类似于面向对象语言中的类）。

在这篇文章中，我探索了一些通过 JSON_ARRAY_T 类型及其方法提供的面向数组的 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

概括

JSON 数组被广泛使用。它们也非常灵活，因为它们可以包含标量、对象和其他数组。JSON 数组的结构越复杂和嵌套，处理起来就越具有挑战性。

JSON_ARRAY_T 对象类型为查询和构造 JSON 数组提供了一个干净、快速的 API。一旦您能够将 PL/SQL 数组与 JSON 数组相关联（例如，纠正索引中的差异），您会发现在您的 PL/SQL 代码中编写代码来处理 JSON 数组很容易。


SQL

0 人点赞

©w3cschool编程狮闽ICP备15016281号-3

违法和不良信息举报电话：173-0602-2364|举报邮箱：jubao@eeedong.com