В начале я использовал приведенный ниже код для загрузки данных из файла 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 ВМЕСТЕ!