Как использовать Forall для массовой вставки из CSV-файла в APEX 5

Андрей Васенин

Андрей Васенин

Автор статьи. Сфера интересов: ИТ-специалист (программирование, администрирование, DBA). Кандидат экономических наук. Подробнее .

В начале я использовал приведенный ниже код для загрузки данных из файла csv в таблицу db с использованием APEX 5, но производительность была очень медленная, так как CSV-файл очень большой. Как я могу использовать forall-оператор в приведенном ниже коде для быстрого выполнения запроса? Я попытался заменить операцию немедленного выполнения на forall, но это не сработало. Итак, как же я вышел из этой ситуации? Вам интересно?

 
DECLARE 
v_blob_data BLOB; 
v_blob_len NUMBER; 
v_position NUMBER; 
v_raw_chunk RAW(10000); 
v_char CHAR(1); 
c_chunk_len NUMBER := 1; 
v_line VARCHAR2 (32767):= NULL; 
v_data_array wwv_flow_global.vc_arr2; 
v_rows NUMBER; 
v_sr_no NUMBER := 1; 
v_filename VARCHAR2(2000) := apex_util.get_session_state('P4_FILE_BROWSE 9; ); 
v_idx NUMBER; 
v_first_line_done BOOLEAN := false; 
v_error_cd NUMBER :=0; 
BEGIN 

OALFIN.OALEXM_COST_CENTER_MANAGER_PKG.OALEXM_COST_ CENTER_MGR_PROC; 

SELECT blob_content 
INTO v_blob_data 
FROM apex_application_temp_files 
WHERE name = v_filename; 
v_blob_len := dbms_lob.getlength(v_blob_data); 
v_position := 1; 
--wwv_flow.debug('v_position'||v_position); 
LOOP 
v_idx := dbms_lob.instr( v_blob_data, '0A', v_position ); 
-- wwv_flow.debug('v_idx'||v_idx); 
IF v_idx = 0 THEN 
wwv_flow.debug('in if'); 
v_line := utl_raw.cast_to_varchar2( dbms_lob.substr( v_blob_data, 32767, v_position ) ); 
ELSE 
wwv_flow.debug('in else'); 
v_line := utl_raw.cast_to_varchar2( dbms_lob.substr( v_blob_data, v_idx - v_position, v_position ) ); 
END IF; 
wwv_flow.debug('v_line'||v_line); 
EXIT 
WHEN v_idx = 0; 
v_position := v_idx + 1; 
v_line := REPLACE (v_line, ',', ':'); 
v_data_array := wwv_flow_utilities.string_to_table (v_line); 
IF(v_first_line_done != true) THEN 
v_first_line_done := true; 

wwv_flow.debug('v_data_array(5) '||v_data_array(5) ); 
IF v_data_array(1) = 'COMPANY_CODE' AND v_data_array(2) = 'COST_CENTER_CODE' AND v_data_array(3) = 'USER_NAME' 
AND v_data_array(4) = 'EFFECTIVE_START_DATE' --AND v_data_array(5) = 'EFFECTIVE_END_DATE' 
THEN 
v_error_cd := 0; 
ELSE 
v_error_cd := 1; 
END IF; 

ELSIF(v_first_line_done = true AND v_error_cd = 0) THEN 

EXECUTE IMMEDIATE 'insert into OALFIN.OALEXM_COST_CENTER_MANAGER(company_code,cos t_center,user_name,effective_start_date,effective_end_Date ) 
values (:1,:2,:3,:4,:5)' USING v_data_array(1), v_data_array(2), v_data_array(3), v_data_array(4), v_data_array(5); 
END IF; 

v_line := NULL; v_sr_no := v_sr_no + 1; 
END LOOP; 
EXCEPTION 
WHEN OTHERS THEN 
raise_application_error(-20000,'Error'||apex_util.get_session_state ('P4_FILE_BROWSE 9;)||sqlerrm); 
END; 

Прежде всего мы должны переделать свою процедуру так, чтобы LOOP заполнил несколько коллекций, по одному для каждого столбца, который нужно вставить, и когда вы заполнили партию значений, например, содержащую 100 элементов в каждой коллекции, то вы выполним оператор FORALL INSERT следующим образом:

FORALL indx IN 1 .. v_coll_company_code.COUNT 
INSERT INTO OALFIN.OALEXM_COST_CENTER_MANAGER(company_code,cos t_center,user_name,effective_start_date,effective_end_Date ) 
VALUES ( 
v_coll_company_code (indx), 
v_coll_cost_center (indx), 
v_coll_user_name (indx), 
v_coll_effective_start_date (indx), 
v_coll_effective_end_date (indx) 
); 
 

Нам не нужно использовать  EXECUTE IMMEDIATE, потому что здесь нет динамического оператора SQL. Вместо того, чтобы заполнять отдельную коллекцию для каждого столбца, вы можете определить запись, содержащую все 5 столбцов, и затем объявить одну единственную коллекцию этого типа записи, например:

 
TYPE my_rec IS RECORD ( company_code ? , 
cost_center ? , 
user_name ? , 
start_date ? , 
end_date ); 

TYPE v_coll_rec IS TABLE OF my_rec INDEX BY PLS_INTEGER; 

и т.д.

и тогда уже используем оператор FORALL:

 
FORALL indx IN 1 .. v_coll_rec.COUNT 
INSERT INTO 
( SELECT company_code, 
cost_center, 
user_name , 
start_date, 
end_date 
FROM OALFIN.OALEXM_COST_CENTER_MANAGER 
) 
VALUES v_coll_rec (indx) ; 

Индекс цикла FORALL используется для перебора элементов (ов) коллекции, которые соответствуют вставленным ROWS, то есть для каждого значения индекса будет вставлена ​​одна строка.

После выполнения одного оператора FORALL вы удалите все элементы всех коллекций и будет продолжать заполнять их снова, вплоть до конца вашего основного цикла.

Затем, после END LOOP, вы должны добавить последнюю инструкцию FORALL, которая вставляет последнюю партию из 100 строк (или, возможно, меньше).

В изначальном коде Я пытались поместить различные столбцы в коллекцию с 5 элементами, что совсем не правильно.

Вы можете сделать что-то подобное, если все ваши столбцы имеют один и тот же тип данных (например, VARCHAR2), но тогда вам понадобится двухуровневая коллекция (коллекция, индекс которой итерации по строкам, состоящий из другой коллекции, индекс которой (от 1 до 5) итерации по столбцам) Это можно сделать, но полученный код будет, вероятно, менее читаемым.


Всего несколько идей, которые, возможно, помогут вам. В моем случае время загрузки данных в таблицу базы данных Oracle из CSV-файла уменьшилось в разы! Учимся писать грамотный код на Apex ВМЕСТЕ!

Вас заинтересует / Intresting for you:

Apex Oracle обучающий видеокур...
Apex Oracle обучающий видеокур... 8805 просмотров Дэн Sun, 05 Aug 2018, 16:36:33
Oracle IDE: JDeveloper, SQL De...
Oracle IDE: JDeveloper, SQL De... 4522 просмотров Ольга Потемкина Tue, 21 Nov 2017, 13:18:46
Значения NULL в PL/SQL Oracle
Значения NULL в PL/SQL Oracle 4805 просмотров Дэн Wed, 01 Jul 2020, 08:10:56
Oracle и Java: использование P...
Oracle и Java: использование P... 5749 просмотров sepia Tue, 08 May 2018, 08:52:34
Войдите чтобы комментировать

apv аватар
apv ответил в теме #8722 6 года 5 мес. назад
При написании кода, конечно, нужно усиленно морщить лоб. Спасибо за элегантный пример программирования на Oracle Apex