Массовое обновление в Oracle 12.1.0.2?
В настоящее время я работаю над задачей, и у меня есть требование обновить около 2000 до 4000 записей, считывая значения из JSON. Я оптимизирую часть JSON, но в настоящее время я обновляю каждую запись одну за другой. Может ли кто-нибудь предложить лучший подход для обновления всех записей от 2000 до 4000 с помощью одного запроса вместо того, чтобы запускать его от 2000 до 4000 раз? Вот мой пример кода
APEX_JSON.PARSE(V_OUTPUT_DATA);
plan_count := apex_json.get_count('plan');
IF plan_count > 0 THEN
FOR I in 1..plan_count LOOP
activities_count := APEX_JSON.get_count(p_path => 'plan['||i||'].activities');
IF activities_count > 0 THEN
FOR j in 2..(activities_count-1) LOOP
V_TASK_ID := APEX_JSON.get_varchar2(p_path => 'plan['||i||'].activities['||j||'].task_id');
V_SEQ := APEX_JSON.get_number(p_path => 'plan['||i||'].activities['||j||'].sequence');
UPDATE TABLE_NAME
SET ROUTE_SEQUENCE = V_SEQ, UPDATED_BY = 'SYSTEM',UPDATED_ON = SYSTIMESTAMP
WHERE TASK_ID = V_TASK_ID;
END LOOP;
COMMIT;
END IF;
END LOOP;
END IF;
Должен ли я использовать 2D-массив и использовать его для массового обновления, или можно использовать какой-то другой подход?
Добавлен образец JSON
{
"plan": [{
"vehicle_id": "vehicle_1",
"activities": [{
"sequence": 0,
"timestamp": "2017-11-10T09:48:19Z",
"location_id": "depot"
},
{
"sequence": 1,
"timestamp": "2017-11-10T09:50:07Z",
"task_id": "465427",
"location_id": "465427",
"travel_distance": 1099,
"travel_duration": "00:01:48"
},
{
"sequence": 2,
"timestamp": "2017-11-10T09:50:10Z",
"task_id": "443951",
"location_id": "443951",
"travel_distance": 26,
"travel_duration": "00:00:03"
},
{
"sequence": 3,
"timestamp": "2017-11-10T09:50:25Z",
"task_id": "165760",
"location_id": "165760",
"travel_distance": 152,
"travel_duration": "00:00:15"
},
{
"sequence": 4,
"timestamp": "2017-11-10T09:51:34Z",
"task_id": "459187",
"location_id": "459187",
"travel_distance": 705,
"travel_duration": "00:01:09"
}]
}]
}
1 ответ
Предполагая, что ваша таблица похожа на эту:
create table table_name
(
id number(12) primary key,
route_sequence number(12),
updated_by varchar2(30),
updated_on timestamp(9)
)
и что объект json похож на этот:
{
"activities":
[
{"task_id": 1, "sequence" : 10},
{"task_id": 2, "sequence" : 20},
{"task_id": 3, "sequence" : 30},
{"task_id": 4, "sequence" : 40},
{"task_id": 5, "sequence" : 50},
]
}
Вы можете напрямую запрашивать данные json в SQL с помощью оператора sql "JSON_TABLE" (новичок в oracle 12 - см. https://docs.oracle.com/database/121/SQLRF/functions092.htm)... а затем Вы можете воспользоваться этим, используя такой запрос в выражении "слияние":
этот единственный оператор SQL делает то, что вам нужно:
merge into table_name t
using
(
select *
from JSON_TABLE(
'{
"activities":
[
{"task_id": 1, "sequence" : 10},
{"task_id": 2, "sequence" : 20},
{"task_id": 3, "sequence" : 30},
{"task_id": 4, "sequence" : 40},
{"task_id": 5, "sequence" : 50},
]
}',
'$."activities"[*]'
COLUMNS(
V_TASK_ID NUMBER PATH '$.task_id',
V_SEQ NUMBER PATH '$.sequence'
)
)
) json_data
on (json_data.v_task_id = t.id)
when matched then
update set
ROUTE_SEQUENCE = V_SEQ,
UPDATED_BY = 'SYSTEM',
UPDATED_ON = SYSTIMESTAMP
Изменить: теперь, когда вы опубликовали свой фактический пример JSON:
чтобы мой пример работал с вашими данными, вам просто нужно заменить
'$."activities"[*]'
строка с этим:
'$."plan"[0]."activities"[*]'
Ситуация может стать более сложной, если элемент массива "план" содержит более одного элемента, но это все еще можно сделать.
Редактировать 2: как обрабатывать вложенные объекты (то есть: что делать, когда "план" содержит несколько объектов
Допустим, что строка json для обработки это
'{
"plan":
[
{
"vehicle_id": "vehicle_1",
"activities":
[
{
"sequence": 1,
"task_id": "465427"
},
{
"sequence": 2,
"task_id": "443951"
}
]
}
,
{
"vehicle_id": "vehicle_2",
"activities":
[
{
"sequence": 3,
"task_id": "165760"
},
{
"sequence": 4,
"task_id": "459187"
}
]
}
]
}'
(Я не буду повторять это в моих примерах: я просто напишу в коде
Если вы не заинтересованы в чтении поля vehicle_id и хотите получить плоское представление всех подробностей действий (независимо от того, какой объект "плана" содержит их, вы можете просто изменить строку селектора корневого объекта из этого
'$."plan"[0]."activities"[*]'
к этому:
'$."plan"[*]."activities"[*]'
Итак, этот запрос:
select *
from JSON_TABLE
(
<json_string_here>,
'$."plan"[*]."activities"[*]'
COLUMNS(
V_TASK_ID NUMBER PATH '$.task_id',
V_SEQ NUMBER PATH '$.sequence'
)
)
будет проходить все объекты "деятельности" всех объектов плана, но вернет вам только столбцы "task_id" и "sequence".
если вместо этого вы хотите, чтобы соответствующий столбец идентификатора транспортного средства повторялся во всех строках, вы должны перейти на уровень с корневым селектором, используя это выражение
'$."plan"[*]'
и внутри предложения "columnns" вы можете использовать синтаксис "вложенного пути", чтобы сказать, что вы хотите расширить встроенные столбцы подобъекта:
select *
from JSON_TABLE
(
<json_string_here>,
'$."plan"[*]'
COLUMNS
(
VEHICLE varchar2(20) PATH '$."vehicle_id"',
NESTED PATH '$."activities"[*]'
COLUMNS
(
V_TASK_ID NUMBER PATH '$.task_id',
V_SEQ NUMBER PATH '$.sequence'
)
)
)
