Oracle Apex 22.21：嵌套JSON数组同步双表触发器PL/SQL错误排查

问题概述 #

需将ORDERS_LOCAL表中ORDER_ITEMS列的嵌套JSON数组数据，同步提取到ORDER_ITEMS_LOCAL子表。当前创建TR_MAINTAIN_LINES触发器时出现PLS-00049编译错误，提示NEW.ID、OLD.ID为无效绑定变量，同时对JSON解析相关细节存在疑问。

错误原因分析 #

PLS-00049错误的核心原因是：触发器中引用的NEW.ID/OLD.ID并非ORDERS_LOCAL表的字段。从提供的REST JSON响应示例可知，订单主表的主键字段是order_id（字符串类型），而非通用的id字段，因此需将触发器中的ID替换为order_id。

修正后的完整触发器代码 #

CREATE OR REPLACE TRIGGER TR_MAINTAIN_LINES
AFTER INSERT OR UPDATE OR DELETE ON ORDERS_LOCAL
FOR EACH ROW
DECLARE
BEGIN
  -- 处理DELETE操作：删除子表中对应订单的所有行
  IF DELETING THEN
    DELETE FROM ORDER_ITEMS_LOCAL
    WHERE ORDER_ID = :OLD.order_id;
  END IF;

  -- 处理INSERT/UPDATE操作：先删除旧数据（UPDATE时），再插入新解析的行
  IF INSERTING OR UPDATING THEN
    -- 先清理子表中当前订单的旧行（UPDATE场景下）
    IF UPDATING THEN
      DELETE FROM ORDER_ITEMS_LOCAL
      WHERE ORDER_ID = :OLD.order_id;
    END IF;

    -- 解析JSON数组并批量插入子表
    INSERT INTO ORDER_ITEMS_LOCAL (LINE_ID, ORDER_ID, LINE_NUMBER, PRODUCT_ID, QUANTITY, PRICE)
    SELECT SEQ_LINE_ID.NEXTVAL, -- 用序列自动生成LINE_ID
           :NEW.order_id,       -- 关联主表的订单ID
           jt.line_number,
           jt.product_id,
           jt.quantity,
           jt.price
    FROM JSON_TABLE(
           :NEW.order_items,
           '$[*]' COLUMNS (
             line_number NUMBER PATH '$.line_number',
             product_id NUMBER PATH '$.product_id',
             quantity NUMBER PATH '$.quantity',
             price NUMBER PATH '$.price'
           )
         ) jt
    WHERE :NEW.order_items IS NOT NULL; -- 避免JSON为空时插入空行
  END IF;
END;
/

关键疑问解答 #

1. 绑定变量对应关系 #

触发器中的:NEW和:OLD是绑定变量，分别代表触发语句执行后（NEW）和执行前（OLD）的主表行数据。由于主表主键是order_id，因此必须使用:NEW.order_id/:OLD.order_id，而非:NEW.ID/:OLD.ID。

2. Sequence的作用 #

ORDER_ITEMS_LOCAL的LINE_ID要求自动生成，这里使用序列SEQ_LINE_ID的NEXTVAL来生成唯一值。如果你的表是Oracle 12c+版本，也可以将LINE_ID定义为GENERATED ALWAYS AS IDENTITY列，此时插入时无需指定该字段，数据库会自动生成。

3. JSON_TABLE是否需要包含line_id和order_id #

不需要。line_id由序列或IDENTITY列自动生成，order_id来自主表的:NEW.order_id，JSON_TABLE仅需解析嵌套JSON数组中的业务字段（line_number、product_id、quantity、price）即可。

代码优化建议 #

• 空值处理：添加WHERE :NEW.order_items IS NOT NULL判断，避免当ORDER_ITEMS为空时执行无效的插入操作。
• 批量操作：使用JSON_TABLE配合INSERT ... SELECT实现批量插入，比逐行循环解析插入的效率更高，尤其适合包含多行订单明细的场景。
• 触发器时机：使用AFTER INSERT OR UPDATE OR DELETE确保主表数据已完成变更后，再同步子表数据，避免主表数据回滚时子表出现脏数据。
• 异常处理：可根据业务需求添加异常捕获逻辑，比如当product_id在PRODUCTS表中不存在时的处理，避免触发器执行失败导致主表操作回滚。

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