如何用PL/SQL将Oracle层级JSON数据导入关联表？

针对你遇到的层级JSON导入A、B、C关联表的问题，这里提供两种可行的解决方案：

方法一：利用JSON_TABLE的ORDINALITY伪列区分B对象 #

由于B无业务键，可通过JSON数组元素的位置序号来唯一标识同一个A下的不同B对象，结合批量SQL操作实现高效导入。

步骤说明 #

1. 为B表新增B_ORDINAL字段，存储B在A4数组中的位置序号（同一个A下序号唯一）；
2. 先插入A表并获取A_ID；
3. 解析A4数组时用FOR ORDINALITY获取B的序号，批量插入B表；
4. 基于B表的JSON列（存储B4数组）批量插入C表。

代码示例 #

1. 创建目标表

CREATE TABLE A (
  A_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  A1 VARCHAR2(100),
  A2 VARCHAR2(100),
  A3 VARCHAR2(100)
);

CREATE TABLE B (
  B_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  A_ID NUMBER REFERENCES A(A_ID),
  B1 VARCHAR2(100),
  B_ORDINAL NUMBER -- 标记B在A4数组中的位置，用于唯一区分同A下的B
);

CREATE TABLE C (
  C_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  B_ID NUMBER REFERENCES B(B_ID),
  C1 VARCHAR2(100),
  C2 VARCHAR2(100)
);

2. PL/SQL处理逻辑

DECLARE
  l_json CLOB := '{
    "A1": "val1",
    "A2": "val2",
    "A3": "val3",
    "A4": [
      {
        "B1": "bval1",
        "B4": [{"C1": "cval1", "C2": "cval2"}, {"C1": "cval3", "C2": "cval4"}]
      },
      {
        "B1": "bval2",
        "B4": [{"C1": "cval5", "C2": "cval6"}]
      }
    ]
  }';
  l_a_id NUMBER;
BEGIN
  -- 插入A表并获取A_ID
  INSERT INTO A (A1, A2, A3)
  SELECT jt.A1, jt.A2, jt.A3
  FROM JSON_TABLE(l_json, '$'
    COLUMNS (
      A1 VARCHAR2(100) PATH '$.A1',
      A2 VARCHAR2(100) PATH '$.A2',
      A3 VARCHAR2(100) PATH '$.A3'
    )
  ) jt
  RETURNING A_ID INTO l_a_id;

  -- 批量插入B表，同时记录B的数组序号
  INSERT INTO B (A_ID, B1, B_ORDINAL)
  SELECT l_a_id, jt.B1, jt.b_ordinal
  FROM JSON_TABLE(l_json, '$.A4[*]'
    COLUMNS (
      B1 VARCHAR2(100) PATH '$.B1',
      b_ordinal FOR ORDINALITY, -- 伪列：返回当前元素在数组中的位置（从1开始）
      b4_json CLOB PATH '$.B4' FORMAT JSON -- 保留B的C数组JSON，用于后续插入C表
    )
  ) jt;

  -- 批量插入C表
  INSERT INTO C (B_ID, C1, C2)
  SELECT b.B_ID, jt.C1, jt.C2
  FROM B b
  JOIN JSON_TABLE(b.b4_json, '$[*]'
    COLUMNS (
      C1 VARCHAR2(100) PATH '$.C1',
      C2 VARCHAR2(100) PATH '$.C2'
    )
  ) jt
  WHERE b.A_ID = l_a_id;

  COMMIT;
END;
/

方法二：PL/SQL嵌套循环分层次处理 #

通过将B和C的JSON数组解析为PL/SQL嵌套表，逐层循环处理，确保每个B只插入一次，再处理其下的C数据。

步骤说明 #

1. 插入A表并获取A_ID；
2. 将A4数组解析为B类型的嵌套表；
3. 循环遍历每个B对象，插入B表并获取B_ID；
4. 解析当前B的B4数组为C类型的嵌套表，批量插入C表。

代码示例 #

DECLARE
  l_json CLOB := '{
    "A1": "val1",
    "A2": "val2",
    "A3": "val3",
    "A4": [
      {
        "B1": "bval1",
        "B4": [{"C1": "cval1", "C2": "cval2"}, {"C1": "cval3", "C2": "cval4"}]
      },
      {
        "B1": "bval2",
        "B4": [{"C1": "cval5", "C2": "cval6"}]
      }
    ]
  }';
  l_a_id NUMBER;

  -- 定义B数据的记录和嵌套表类型
  TYPE b_rec_type IS RECORD (
    b1 VARCHAR2(100),
    b4_json CLOB
  );
  TYPE b_tab_type IS TABLE OF b_rec_type;
  l_b_tab b_tab_type;

  -- 定义C数据的记录和嵌套表类型
  TYPE c_rec_type IS RECORD (
    c1 VARCHAR2(100),
    c2 VARCHAR2(100)
  );
  TYPE c_tab_type IS TABLE OF c_rec_type;
  l_c_tab c_tab_type;
BEGIN
  -- 插入A表并获取A_ID
  INSERT INTO A (A1, A2, A3)
  SELECT jt.A1, jt.A2, jt.A3
  FROM JSON_TABLE(l_json, '$'
    COLUMNS (
      A1 VARCHAR2(100) PATH '$.A1',
      A2 VARCHAR2(100) PATH '$.A2',
      A3 VARCHAR2(100) PATH '$.A3'
    )
  ) jt
  RETURNING A_ID INTO l_a_id;

  -- 将A4数组解析为B嵌套表
  SELECT jt.B1, jt.b4_json
  BULK COLLECT INTO l_b_tab
  FROM JSON_TABLE(l_json, '$.A4[*]'
    COLUMNS (
      B1 VARCHAR2(100) PATH '$.B1',
      b4_json CLOB PATH '$.B4' FORMAT JSON
    )
  ) jt;

  -- 循环处理每个B对象
  FOR i IN l_b_tab.FIRST .. l_b_tab.LAST LOOP
    DECLARE
      l_b_id NUMBER;
    BEGIN
      -- 插入B表并获取B_ID
      INSERT INTO B (A_ID, B1)
      VALUES (l_a_id, l_b_tab(i).b1)
      RETURNING B_ID INTO l_b_id;

      -- 解析当前B的C数组为嵌套表
      SELECT jt.C1, jt.C2
      BULK COLLECT INTO l_c_tab
      FROM JSON_TABLE(l_b_tab(i).b4_json, '$[*]'
        COLUMNS (
          C1 VARCHAR2(100) PATH '$.C1',
          C2 VARCHAR2(100) PATH '$.C2'
        )
      ) jt;

      -- 批量插入C表
      FORALL j IN l_c_tab.FIRST .. l_c_tab.LAST
        INSERT INTO C (B_ID, C1, C2)
        VALUES (l_b_id, l_c_tab(j).c1, l_c_tab(j).c2);
    END;
  END LOOP;

  COMMIT;
END;
/

方案选择建议 #

• 方法一以SQL批量操作为主，执行效率更高，适合大数据量场景；
• 方法二逻辑更直观，便于在循环中添加额外业务判断或处理逻辑，适合复杂业务场景。

内容的提问来源于stack exchange，提问作者Thiemo Kellner