这个问题是后续问题另一个问题 https://stackoverflow.com/questions/75192510/oracle-apex-rest-data-source-nested-json-array-sync-two-tables-where-to.
我实际上已经重新创建了上一个问题的表格。更新后的 JSON 响应可以在这个问题的底部找到。
ORDERS_LOCAL table
ORDERS_LOCAL表数据。ORDER_ITEMScolumn 是我需要提取到的 JSON 数组ORDER_ITEMS_LOCAL table.
ORDER_ITEMS_LOCAL table. LINE_ID列应该自动创建。ORDER_ID列是一个外键ORDERS_LOCAL table. PRODUCT_ID列是一个外键PRODUCTS table. LINE_NUMBER只是订单行号（第 1 行 = 产品 1、价格、数量 | 第 2 行 = 产品 2、价格、数量等...）我相信它被称为sequence type?
PRODUCTS table
PRODUCTS表数据
根据卡斯滕的回答，我为ORDERS表从对象浏览器.
然后我输入了上一个问题中 Carsten 的 PLSQL 代码。他确实提到这是伪代码。所以我尝试更新一下..
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 ORDER_ITEMS_LOCAL ( line_id, order_id, line_number, product_id, quantity, price)
( select :new.id,
seq_lines.nextval,
j.line_number,
j.product_id,
j.quantity,
j.price
from json_table(
:new.order_items,
'$[*]' columns (
line_number for ordinality,
product_id number path '$.product_id',
quantity number path '$.quantity',
price number path '$.price' ) ) );
elsif deleting then
delete ORDER_ITEMS_LOCAL
where order_id = :old.id;
elsif updating then
delete ORDER_ITEMS_LOCAL
where order_id = :old.id;
--
-- handle the update case here.
-- I would simply delete and re-insert LINES rows.
end if;
end;
我收到以下错误
Compilation failed, line 4 (08:38:57) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers.
PLS-00049: bad bind variable 'NEW.ID'Compilation failed, line 19 (08:38:57) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers.
PLS-00049: bad bind variable 'OLD.ID'Compilation failed, line 22 (08:38:57) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers.
PLS-00049: bad bind variable 'OLD.ID'
我相信这是由于触发代码中缺少列造成的，但我不确定。
我是 PLSQL 新手，解析 JSON 有点令人困惑......尤其是下面。请看我的评论。
if inserting then
insert into ORDER_ITEMS_LOCAL ( line_id, order_id, line_number, product_id, quantity, price)
( select :new.id, -- is this new id for `line_id`
order_id -- how to insert order_id foreign key
seq_lines.nextval, -- not sure what this is for?
j.line_number, -- I changed 'lines' to 'order_items' so should this be seq_order_items.nextval, ?
j.product_id,
j.quantity,
j.price
from json_table(
:new.order_items, -- I changed 'lines' to 'order_items' so I changed this from :new.lines,
'$[*]' columns ( -- Would I include 'line_id' and 'order_id' in here as well?
line_number for ordinality,
product_id number path '$.product_id',
quantity number path '$.quantity',
price number path '$.price' ) ) );
更新的 JSON 响应
[
{
"order_id": "HO9b6-ahMY-B2i9",
"order_number": 34795,
"order_date": "2022-11-02",
"store_id": 2,
"full_name": "Ronda Perfitt",
"email": "[email protected] /cdn-cgi/l/email-protection",
"city": "Fresno",
"state": "California",
"zip_code": "93762",
"credit_card": "5108758574719798",
"order_items": [
{
"line_number": 1,
"product_id": 2,
"quantity": 1,
"price": 3418.85
},
{
"line_number": 2,
"product_id": 7,
"quantity": 1,
"price": 4070.12
}
]
},
{
"order_id": "RFvUC-sN8Y-icJP",
"order_number": 62835,
"order_date": "2022-10-09",
"store_id": 1,
"full_name": "Wash Rosenfelt",
"email": "[email protected] /cdn-cgi/l/email-protection",
"city": "Chicago",
"state": "Illinois",
"zip_code": "60646",
"credit_card": "5048372443777103",
"order_items": [
{
"line_number": 1,
"product_id": 1,
"quantity": 1,
"price": 3349.05
},
{
"line_number": 2,
"product_id": 3,
"quantity": 1,
"price": 4241.29
},
{
"line_number": 3,
"product_id": 1,
"quantity": 1,
"price": 3560.03
}
]
},
]
我很抱歉让这件事变得混乱。我真的很想学习如何正确地做到这一点。非常感谢您的支持。谢谢。
