Oracle Apex REST数据源嵌套JSON同步双表：SQL位置与自动执行方案

问题1：提取JSON至子表的SQL应该放在哪里？ #

• 若只是一次性初始化子表数据，SQL Workshop的SQL Commands完全可用，直接执行解析JSON的语句即可。
• 若要和REST数据同步联动（比如父表SOTEST_LOCAL更新时自动同步子表），更合适的方案是：
  • 数据库触发器：给SOTEST_LOCAL创建AFTER INSERT/UPDATE触发器，每次订单数据新增或更新时，自动解析lines列的JSON到SOTEST_ORDER_LINES_LOCAL。
  • REST数据源的Post-Processing脚本：在REST Data Source配置中找到Post-Processing选项，将解析语句放入，每次同步完成后自动执行。

示例解析SQL（用MERGE处理新增和更新）：

MERGE INTO SOTEST_ORDER_LINES_LOCAL t
USING (
    SELECT 
        s.id AS order_id,
        j.line_id,
        j.product_name,
        j.quantity
    FROM SOTEST_LOCAL s,
         JSON_TABLE(s.lines, '$[*]'
             COLUMNS(
                 line_id NUMBER PATH '$.lineId',
                 product_name VARCHAR2(100) PATH '$.productName',
                 quantity NUMBER PATH '$.quantity'
             )
         ) j
) src
ON (t.order_id = src.order_id AND t.line_id = src.line_id)
WHEN MATCHED THEN
    UPDATE SET 
        t.product_name = src.product_name,
        t.quantity = src.quantity
WHEN NOT MATCHED THEN
    INSERT (order_id, line_id, product_name, quantity)
    VALUES (src.order_id, src.line_id, src.product_name, src.quantity);

问题2：自动同步时是否需要编写函数？ #

不需要单独编写复杂函数，用以下两种方式即可：

• REST数据源Post-Processing：直接将上述MERGE语句（需处理删除可追加后续DELETE语句）放到REST Data Source的Post-Processing中，每次自动同步完成后会自动执行，确保子表数据与父表JSON数组一致。
• 触发器方案：若使用触发器，每次父表数据变动时自动触发解析逻辑，触发器内直接调用MERGE语句即可，无需额外函数。

若需清理子表中已不存在于父表JSON的行，可在MERGE后追加：

DELETE FROM SOTEST_ORDER_LINES_LOCAL t
WHERE NOT EXISTS (
    SELECT 1 
    FROM SOTEST_LOCAL s,
         JSON_TABLE(s.lines, '$[*]' COLUMNS(line_id NUMBER PATH '$.lineId')) j
    WHERE s.id = t.order_id AND j.line_id = t.line_id
);

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