Forall用法及其异常捕获

2015-12-09 4 分钟读完 (大约 639 个字)

Oracle从8i开始引入了两个新的数据操纵语言语句：BULK COLLECT和FORALL，前者提供对数据的高速检索，后者则简化了代码同时很大程度上改进了增删改操作的性能。在Oracle EBS开发中经常需要批处理大量记录数据，例如在接口导入当中就使用得很多。下面的代码是我在实际项目开发当中在做客户化订单导入（从A系统导入到EBS）时使用FORALL的部分代码（下面的代码不支持直接Copy Running），在代码层面上简要地展示了FORALL的用法及其异常捕获机制，仅作参考Sample，具体的用法参见末尾的官方文档链接。

工作机制

Forall工作机制图解

Just a Sample code:

DECLARE
  bulk_error EXCEPTION; -->>声明 Forall 异常,代码为 -24381
  PRAGMA EXCEPTION_INIT(bulk_error, -24381);
  TYPE tbl_exc_type IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;

  l_exc_tbl   tbl_exc_type;
  l_error_msg VARCHAR2(2000);
  l_iface_tbl cux_om_ordimp_define_pub.tbl_pub_header_iface;
BEGIN
  l_exc_tbl.delete;
  FORALL idx IN 1 .. l_iface_tbl.count SAVE EXCEPTIONS -->>保存异常
    INSERT INTO cux_om_so_headers_int
    VALUES
      (l_iface_tbl(idx).group_id
      ,l_iface_tbl(idx).source_code
      ,l_iface_tbl(idx).ordered_date
      ,l_iface_tbl(idx).project_number
      ,l_iface_tbl(idx).order_type
      ,l_iface_tbl(idx).customer_number
      ,l_iface_tbl(idx).operation_code
      ,l_iface_tbl(idx).operation_date
      ,l_iface_tbl(idx).batch_id
      ,l_iface_tbl(idx).process_code
      ,l_iface_tbl(idx).process_date
      ,l_iface_tbl(idx).error_msg
      ,l_iface_tbl(idx).currency_code
      ,l_iface_tbl(idx).creation_date
      ,l_iface_tbl(idx).created_by
      ,l_iface_tbl(idx).last_updated_by
      ,l_iface_tbl(idx).last_update_date
      ,l_iface_tbl(idx).last_update_login
      ,l_iface_tbl(idx).attribute_category
      ,l_iface_tbl(idx).attribute1
      ,l_iface_tbl(idx).attribute2
      ,l_iface_tbl(idx).attribute3
      ,l_iface_tbl(idx).attribute4
      ,l_iface_tbl(idx).attribute5
      ,l_iface_tbl(idx).attribute6
      ,l_iface_tbl(idx).attribute7
      ,l_iface_tbl(idx).attribute8
      ,l_iface_tbl(idx).attribute9
      ,l_iface_tbl(idx).attribute10);
EXCEPTION
  WHEN bulk_error THEN
    -->> 捕获异常
    FOR i IN 1 .. SQL%bulk_exceptions.count LOOP
      -->>记录了错误时执行的行
      l_exc_tbl(i) := SQL%BULK_EXCEPTIONS(i).error_index;
      l_error_msg := SQLERRM(-sql%BULK_EXCEPTIONS(i).error_code);
      l_iface_tbl(l_exc_tbl(i)).err_msg := substrb(l_error_msg
                                                  ,1
                                                  ,2000);
    END LOOP;
  
    -->> 回写异常行
    FOR i IN 1 .. l_excp_tbl.count LOOP
      UPDATE cux_ebs_control cec -- ctrl_id is Unique Idx
         SET cec.process_code = 'ERROR'
            ,cec.process_date = SYSDATE
            ,cec.error_msg    = l_iface_tbl(l_exc_tbl(i)).err_msg
       WHERE cec.ctrl_id = l_iface_tbl(l_exc_tbl(i)).ctrl_id;
    END LOOP;
END;