如何将错误捕获到Forall PLSQL中 [英] How to catch an error into 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中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！