Как правильно вставить в таблицу, используя таблицу рекордов и все в pl/sql
Я хочу вставить записи в MY_TABLE с помощью forall, Но нет. записей, которые вставляются в данные, меняется с каждым тестом! Я думаю, что это как-то связано со счетчиком циклов, но я не могу понять. Вот фрагмент кода.
DECLARE
TYPE l_rec_type IS RECORD (
datakey SOURCE_TABLE.datakey%TYPE,
sourcekey SOURCE_TABLE.sourcekey%TYPE,
DESCRIPTION SOURCE_TABLE.DESCRIPTION%TYPE,
dimension_name SOURCE_TABLE.dimension_name%TYPE ,
data_type SOURCE_TABLE.data_type%TYPE
);
TYPE l_table_type IS TABLE OF l_rec_typeINDEX BY PLS_INTEGER;
l_table l_table_type;
l_cntr NUMBER;
BEGIN
FOR rec_dimname IN (SELECT dimension_name FROM dimension_table) LOOP
l_cntr1 := 1
FOR rec_source IN (SELECT * FROM source_table WHERE data_type IS NOT NULL) LOOP
l_table(l_ctr1).datakey := rec_source.datakey;
l_table(l_ctr1).sourcekey := rec_source.sourcekey;
l_table(l_ctr1).DESCRIPTION := rec_source.DESCRIPTION;
l_table(l_ctr1).dimension_name := rec_source.dimension_name;
l_table(l_ctr1).data_type := rec_source.data_type;
l_cntr1 := l_cntr1+1;
END LOOP
FORALL j IN l_table.FIRST..l_table.LAST
INSERT INTO my_table VALUES(l_table(j).datakey,
l_table(j).sourcekey,
l_table(j).DESCRIPTION,
l_table(j).dimension_name,
l_table(j).data_type,
1,
SYSDATE,
login_id
);
END LOOP;
END;
Что я делаю неправильно? Обычное использование цикла for - это вставка 5000 записей. Другая проблема, с которой я сталкиваюсь, заключается в том, как обрабатывать WHEN DUP_VAL_ON_INDEX и WHEN OTHERS исключение, используя forall. В обычном для петли легко. Но я должен использовать FORALL для быстрых вставок. Пожалуйста помоги!
3 ответа
Глядя на ваш код, я вижу, что вы не удаляете данные, хранящиеся в таблице pl/ внутри вашего цикла, и у вас нет order by на ваш запрос. Так что если на первой итерации будет больше данных, то на второй вы получите дублирующиеся данные.
Итак, после инициализации вашего l_cntr1 вар (l_cntr1 := 1) вы должны очистить свой pl/table:
l_table.delete;
Надеюсь, это поможет.
Вот фиксированный код. Плюс СОХРАНИТЬ ИСКЛЮЧЕНИЯ действительно спасли мой день! Вот как я реализовал решение. Спасибо всем за ваше драгоценное время и предложения.
DECLARE
TYPE l_rec_type IS RECORD (
datakey SOURCE_TABLE.datakey%TYPE,
sourcekey SOURCE_TABLE.sourcekey%TYPE,
DESCRIPTION SOURCE_TABLE.DESCRIPTION%TYPE,
dimension_name SOURCE_TABLE.dimension_name%TYPE ,
data_type SOURCE_TABLE.data_type%TYPE
);
TYPE l_table_type IS TABLE OF l_rec_typeINDEX BY PLS_INTEGER;
l_table l_table_type;
l_cntr NUMBER;
ex_dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
login_id NUMBER := -1;
errm VARCHAR2(512);
err_indx NUMBER
BEGIN
FOR rec_dimname IN (SELECT dimension_name FROM dimension_table) LOOP
l_cntr1 := 1;
l_table.DELETE; -- Added
FOR rec_source IN (SELECT * FROM source_table WHERE data_type IS NOT NULL) LOOP
l_table(l_ctr1).datakey := rec_source.datakey;
l_table(l_ctr1).sourcekey := rec_source.sourcekey;
l_table(l_ctr1).DESCRIPTION := rec_source.DESCRIPTION;
l_table(l_ctr1).dimension_name := rec_source.dimension_name;
l_table(l_ctr1).data_type := rec_source.data_type;
l_cntr1 := l_cntr1+1;
END LOOP
FORALL j IN l_table.FIRST..l_table.LAST SAVE EXCEPTIONS
INSERT INTO my_table VALUES(l_table(j).datakey,
l_table(j).sourcekey,
l_table(j).DESCRIPTION,
l_table(j).dimension_name,
l_table(j).data_type,
1,
SYSDATE,
login_id
);
END LOOP;
END LOOP;
EXCEPTION
WHEN ex_dml_errors THEN
l_error_count := SQL%BULK_EXCEPTIONS.count;
DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
errm := SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
err_indx := SQL%BULK_EXCEPTIONS(i).error_index
FOR i IN 1 .. l_error_count LOOP
DBMS_OUTPUT.put_line('Error: ' || i ||
' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
IF errm LIKE '%unique%constraint%violated' THEN -- Insert into my_multiple_entries_tbl on duplicate value on index DATAKEY
INSERT INTO my_multiple_entries_tbl(my_multiple_entries_tbl_seq.NEXTVAL,
l_table(err_indx).datakey,
l_table(err_indx).sourcekey,
l_table(err_indx).data_type,
SYSDATE,
login_id );
ELSE -- Insert into my_other_errors_tbl on other errors
INSERT INTO my_other_errors_tbl ( my_other_errors_tbl_seq.NEXTVAL,
l_table(err_indx).datakey,
l_table(err_indx).sourcekey,
l_table(err_indx).data_type,
SYSDATE,
login_id );
END IF;
END;
Вы, кажется, вставляете одну и ту же вещь несколько раз - вы просто просматриваете count из dimension_table, что означает, что это может быть упрощено до следующего, что будет быстрее. Внизу находится forall версия.
Вы не можете использовать exception when dup_val_on_index с любой версией вы должны делать это построчно. Судя только по тому, что вы опубликовали, я подозреваю, что вы действительно можете достичь того, что вы пытаетесь сделать в одном запросе, и полностью сохранить всю эту проблему (включая работу с дублирующимися значениями).
declare
i integer;
begin
select count(*)
into i
from dimension_table;
for j in 1 .. i loop
insert into my_table (datakey, sourcekey, description
, dimension_name, someother_column
, some_date_column, login_id
select datakey, sourcekey, description, dimension_name
, data_type, 1, sysdate, login_id -- previously missing
from source_table
where data_type is not null;
end loop;
commit;
end;
/
Если, однако, вы действительно хотите использовать forall Вы можете сделать что-то вроде этого:
declare
cursor c_src is
select datakey, sourcekey, description, dimension_name
, data_type, 1, sysdate, login_id -- previously missing
from source_table
where data_type is not null;
type t__src is table of c_src%rowtype index by binary_integer;
t_src t__src;
i integer;
begin
select count(*)
into i
from dimension_table;
for j in 1 .. i loop
open c_src;
loop
fetch c_src bulk collect into t_src;
forall k in t_src.first .. t_src.last
insert into my_table (datakey, sourcekey, description
, dimension_name, someother_column
, some_date_column, login_id
values t_src;
end loop;
close c_src;
end loop;
commit;
end;
/