本文介绍了如何将错误捕获到Forall PLSQL中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我放置了此异常,以避免在插入某些行时出现任何错误,但我不知道为什么可以运行.
I put this exception to avoid any error inserting some row but I don't know why I can run.
DECLARE
TYPE dataDate IS TABLE OF DATE;
l_dataDate dataDate;
BEGIN
select data1 BULK COLLECT INTO l_dataDate from USER.TABLE_DATA;
FORALL i IN l_dataDate.FIRST..l_dataDate.LAST
BEGIN
INSERT INTO USER.DIMDATE SELECT
to_number(to_char(l_dataDate(i), 'YYYYMMDDHH24MISS')),
to_number(to_char(l_dataDate(i), 'YYYYMMDD')),
l_dataDate(i),
to_number(to_char(l_dataDate(i), 'DD')),
to_char (l_dataDate(i), 'Day'),
to_number(to_char (l_dataDate(i), 'MM')),
to_char (l_dataDate(i), 'Month'),
to_number(to_char(l_dataDate(i), 'YYYY')) FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM USER.DIMDATE WHERE COD_FECHA=to_number(to_char(l_dataDate(i), 'YYYYMMDDHH24MISS')));
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ERROR '||SUBSTR(SQLERRM, 1, 200));
END;
END;
/
可以做这样的事情吗?预先感谢.
Is it posible to do something like this? Thanks in advance.
推荐答案
我假设您要在bulk collect
中放入save exceptions
.
DECLARE
TYPE dataDate IS TABLE OF DATE;
l_dataDate dataDate;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
BEGIN
select data1
BULK COLLECT INTO l_dataDate
from USER.TABLE_DATA;
BEGIN
FORALL i IN l_dataDate.FIRST..l_dataDate.LAST SAVE EXCEPTIONS
INSERT INTO USER.DIMDATE
SELECT
to_number(to_char(l_dataDate(i), 'YYYYMMDDHH24MISS')),
to_number(to_char(l_dataDate(i), 'YYYYMMDD')),
l_dataDate(i),
to_number(to_char(l_dataDate(i), 'DD')),
to_char (l_dataDate(i), 'Day'),
to_number(to_char (l_dataDate(i), 'MM')),
to_char (l_dataDate(i), 'Month'),
to_number(to_char(l_dataDate(i), 'YYYY'))
FROM DUAL
WHERE NOT EXISTS (SELECT 1
FROM USER.DIMDATE
WHERE COD_FECHA=to_number(
to_char(l_dataDate(i),
'YYYYMMDDHH24MISS')));
EXCEPTION
WHEN dml_errors
THEN
FOR i IN 1..sql%bulk_exceptions.count
LOOP
<<do something with the exceptions>>
END LOOP;
END;
尽管如此,似乎根本没有理由使用PL/SQL.如果table_data
有需要记录的无效数据,只需编写一个可能带有DML错误记录的INSERT
语句即可.
For something like this, though, there appears to be no reason to use PL/SQL at all. Just write a single INSERT
statement possibly with DML error logging if table_data
has invalid data that needs to be logged.
这篇关于如何将错误捕获到Forall PLSQL中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!