Oracle Apex - REST 数据源 - 嵌套 JSON 数组 - 同步两个表 - 在哪里编写 SQL

这个问题是后续问题另一个问题 https://stackoverflow.com/questions/75183126/oracle-apex-22-21-rest-data-source-nested-json-array-discovery.

摘要：我有一个返回嵌套 JSON 数组的 API。正在通过 APEX 提取数据REST 数据源. The 行选择器 in the 数据简介被设定为 ”。” （选择“根节点”）。

The lines数组已手动添加到列 (LINES) 中数据简介, set 数据类型 to JSON文档，并使用lines as the selector.

SAMPLE JSON RESPONSE FROM API
[ {
  "order_number": "so1223",
  "order_date": "2022-07-01",
  "full_name": "Carny Coulter",
  "email": "[email protected] /cdn-cgi/l/email-protection",
  "credit_card": "3545556133694494",
  "city": "Myhiya",
  "state": "CA",
  "zip_code": "12345",
  "lines": [
    {
      "product": "Beans - Fava, Canned",
      "quantity": 1,
      "price": 1.99
    },
    {
      "product": "Edible Flower - Mixed",
      "quantity": 1,
      "price": 1.50
    }
  ]
},
{
  "order_number": "so2244",
  "order_date": "2022-12-28",
  "full_name": "Liam Shawcross",
  "email": "[email protected] /cdn-cgi/l/email-protection",
  "credit_card": "6331104669953298",
  "city": "Humaitá",
  "state": "NY",
  "zip_code": "98670",
  "lines": [
    {
      "order_id": 5,
      "product": "Beans - Green",
      "quantity": 2,
      "price": 4.33
    },
    {
      "order_id": 1,
      "product": "Grapefruit - Pink",
      "quantity": 5,
      "price": 5.00
    }
  ]
},
]

订单属性已同步到本地表（表名：SOTEST_LOCAL）

该表有正确的数据。如下所示，LINES列包含 JSON 数组。

然后我创建了一个ORDER_LINES从中提取 JSON 的子表LINES栏目中的SOTEST_LOCAL桌子。 （抱歉表名......我应该将表命名为ORDERS_LOCAL and ORDER_LINES_LOCAL)

CREATE TABLE  "SOTEST_ORDER_LINES_LOCAL" 
   (    "LINE_ID" NUMBER, 
    "ORDER_ID" NUMBER, 
    "LINE_NUMBER" NUMBER, 
    "PRODUCT" VARCHAR2(200) COLLATE "USING_NLS_COMP", 
    "QUANTITY" NUMBER, 
    "PRICE" NUMBER, 
     CONSTRAINT "SOTEST_ORDER_LINES_LOCAL_PK" PRIMARY KEY ("LINE_ID")
  USING INDEX  ENABLE
   )  DEFAULT COLLATION "USING_NLS_COMP"
/
ALTER TABLE  "SOTEST_ORDER_LINES_LOCAL" ADD CONSTRAINT "SOTEST_ORDER_LINES_LOCAL_FK" FOREIGN KEY ("ORDER_ID")
      REFERENCES  "SOTEST_LOCAL" ("ORDER_ID") ON DELETE CASCADE ENABLE
/

QuickSQL version..

SOTEST_ORDER_LINES_LOCAL
    LINE_ID /pk
    ORDER_ID /fk SOTEST_LOCAL references ORDER_ID
    LINE_NUMBER
    PRODUCT
    QUANTITY
    PRICE

因此，根据 Carsten 在上一个问题中的回答，我可以编写 SQL 来从LINES栏目中的SOTEST_LOCAL表到子表SOTEST_ORDER_LINES_LOCAL.

我的问题分为两部分。

• SQL具体应该写在哪里呢？我会把它写在SQL Workshop in SQL Commands?

• REST 数据正在同步以每小时发出一次请求。那么我是否需要编写一个在每次合并新数据时运行的函数？

有多种选择：

1. 在本地同步表上创建触发器

   您可以在 ORDERS 表上创建一个触发器，该触发器运行AFTER INSERT, UPDATE or DELETE在您的 ORDERS 表上，并维护 LINES 表。这个的好处是子表的维护独立于 APEX 或 REST 同步；如果您只是使用普通 SQL*Plus 插入行，它也可以工作。

   下面是一些关于触发器的伪代码。

create or replace trigger tr_maintain_lines
after insert or update or delete on ORDERS_LOCAL
for each row
begin
    if inserting then
        insert into SOTEST_ORDER_LINES_LOCAL ( order_id, line_id, line_number, product, quantity, price) 
        ( select :new.id,
                 seq_lines.nextval,
                 j.line#,
                 j.product,
                 j.quantity,
                 j.price
            from json_table( 
                     :new.lines,
                     '$[*]' columns (
                         line# for ordinality,
                         product  varchar2(255) path '$.product',
                         quantity number        path '$.quantity',
                         price    number        path '$.price' ) ) );
    elsif deleting then
        delete SOTEST_ORDER_LINES_LOCAL
         where order_id = :old.id;
    elsif updating then
        -- 
        -- handle the update case here.
        -- I would simply delete and re-insert LINES rows.
    end if;
end;

2. 在 APEX 本身中处理子表维护。

   您可以关闭scheduleREST 源同步，并且仅在使用 APEX_REST_SOURCE_SYNC.SYNCHRONIZE_DATA 调用时才运行（https://docs.oracle.com/en/database/oracle/apex/22.1/aeapi/SYNCHRONIZE_DATA-Procedure.html#GUID-660DE4D1-4BAF-405A-A871-6B8C201969C9 https://docs.oracle.com/en/database/oracle/apex/22.1/aeapi/SYNCHRONIZE_DATA-Procedure.html#GUID-660DE4D1-4BAF-405A-A871-6B8C201969C9）。 然后创建一个埃派克斯自动化，它按照您想要的时间表运行，并且此自动化有两个Actions。一种是 REST 源同步，另一种是调用 PL/SQL 代码来维护子表。

   看看这篇博客文章，其中讨论了一些更复杂的同步场景（尽管它确实适合场景）：https://blogs.oracle.com/apex/post/synchronize-parent-child-rest-sources https://blogs.oracle.com/apex/post/synchronize-parent-child-rest-sources

我希望这有帮助