如何使用1个查询通过JSON数据更新Oracle数据库

Question

我正在尝试将下面的示例json数据更新到Oracle版本19的表中。(我想用1个查询更新json的1000行(：

create table jt_test (
CUST_NUM int, SORT_ORDER int, CATEGORY varchar2(100)
);

[
{"CUST_NUM": 12345, "SORT_ORDER": 1, "CATEGORY": "ICE CREAM"}
{"CUST_NUM": 12345, "SORT_ORDER": 2, "CATEGORY": "ICE CREAM"}
{"CUST_NUM": 12345, "SORT_ORDER": 3, "CATEGORY": "ICE CREAM"}
]

我使用本教程和json中的插入行，效果非常好。但对于更新行，我不知道。我该怎么办？注意：我使用Oracle19C，并使用cx_Oracle模块python连接和插入数据库。

通过json插入Oracle列的代码：

DECLARE 
myJSON  varchar2(1000) := '[
{"CUST_NUM": 12345, "SORT_ORDER": 1, "CATEGORY": "ICE CREAM"},
{"CUST_NUM": 12345, "SORT_ORDER": 2, "CATEGORY": "ICE CREAM"},
{"CUST_NUM": 12345, "SORT_ORDER": 3, "CATEGORY": "ICE CREAM"}
]';
BEGIN
insert into jt_test
select * from json_table ( myjson, '$[*]'
columns ( 
CUST_NUM, SORT_ORDER, CATEGORY
)
);
END; 

Answer 1

在SQL Developer中使用以下代码：

MERGE INTO jt_test destttt  using(  
SELECT  CUST_NUM,SORT_ORDER,CATEGORY FROM json_table  (
'[
{"CUST_NUM": 12345, "SORT_ORDER": 1, "CATEGORY": "ICE CREAM"},
{"CUST_NUM": 12345, "SORT_ORDER": 2, "CATEGORY": "ICE CREAM"},
{"CUST_NUM": 12345, "SORT_ORDER": 3, "CATEGORY": "ICE CREAM"}
]'
,'$[*]'
COLUMNS  
CUST_NUM int PATH '$.CUST_NUM ',
SORT_ORDER int PATH '$.SORT_ORDER ',
CATEGORY varchar2  PATH '$.CATEGORY ' ) )  srccccc

ON ( destttt.CUST_NUM= srccccc.CUST_NUM)
WHEN MATCHED THEN UPDATE SET destttt.CATEGORY=srccccc.CATEGORY
WHEN NOT MATCHED THEN INSERT ( CUST_NUM,SORT_ORDER,CATEGORY) VALUES (srccccc.CUST_NUM,srccccc.SORT_ORDER,srccccc.CATEGORY);

---

在带有cx_Oracle的python中使用以下代码：

long_json_string = '''[
{"CUST_NUM": 12345, "SORT_ORDER": 1, "CATEGORY": "ICE CREAM"},
{"CUST_NUM": 12345, "SORT_ORDER": 2, "CATEGORY": "ICE CREAM"},
{"CUST_NUM": 12345, "SORT_ORDER": 3, "CATEGORY": "ICE CREAM"}
]'''
sql = '''
DECLARE      jsonvalue  CLOB :=  :long_json_string     ;  
begin  
MERGE INTO jt_test destttt  using(  
SELECT  CUST_NUM,SORT_ORDER,CATEGORY FROM json_table  (jsonvalue

,'$[*]'
COLUMNS  
CUST_NUM int PATH '$.CUST_NUM',
SORT_ORDER int PATH '$.SORT_ORDER',
CATEGORY varchar2  PATH '$.CATEGORY' ) )  srccccc


ON ( destttt.CUST_NUM= srccccc.CUST_NUM)
WHEN MATCHED THEN UPDATE SET destttt.CATEGORY=srccccc.CATEGORY

WHEN NOT MATCHED THEN INSERT ( CUST_NUM,SORT_ORDER,CATEGORY) VALUES (srccccc.CUST_NUM,srccccc.SORT_ORDER,srccccc.CATEGORY);
'''
cursor.execute(sql, long_json_string=long_json_string)

注1：不要忘记最终使用commit。

注意2：确保用作比较的列不会在json中重复并导致死锁。

注意3：有区分大小写的json键，即CUST_NUM不同于cust_num和CUST_num，并且。。。

错误：CUST_NUM int PATH '$.CUST_num' or CUST_NUM int PATH '$.cusr _num'

好：CUST_NUM int PATH '$.CUST_NUM'