PL/SQL代码二次执行触发DUP_VAL_ON_INDEX错误原因排查

首次执行以下PL/SQL代码可正常将数据插入pos_cards_new表（主键为card_seq），但第二次执行时触发DUP_VAL_ON_INDEX主键重复错误，具体代码如下：

BEGIN                                                    
    FOR r1
        IN (SELECT *
              FROM apex_application_temp_files f,
                   TABLE (
                       apex_data_parser.parse (
                           p_content           => f.blob_content,
                           p_add_headers_row   => 'Y',
                           p_file_name         => f.filename)) p
             WHERE f.name = :p24_upload AND line_number > 1)
    LOOP
        apex_collection.add_member (
            p_collection_name   => 'W',
            p_c001              => NVL (REPLACE (r1.col001, '-', ''), NULL),
            p_c002              => NVL (REPLACE (r1.col002, '-', ''), NULL),
            p_c003              => NVL (REPLACE (r1.col003, '-', ''), NULL),
            p_c004              => NVL (REPLACE (r1.col004, '-', ''), NULL),
            p_c005              => NVL (REPLACE (r1.col005, '-', ''), NULL),
            p_c006              => NVL (REPLACE (r1.col006, '-', ''), NULL),
            p_c007              => NVL (REPLACE (r1.col007, '-', ''), NULL),
            p_c008              => NVL (REPLACE (r1.col008, '-', ''), NULL),
            p_c009              => NVL (REPLACE (r1.col009, '-', ''), NULL),
            p_c010              => NVL (REPLACE (r1.col010, '-', ''), NULL),
            p_c011              => NVL (REPLACE (r1.col011, '-', ''), NULL),
            p_c012              => NVL (REPLACE (r1.col012, '-', ''), NULL),
            p_c013              => NVL (REPLACE (r1.col013, '-', ''), NULL),
            p_c014              => NVL (REPLACE (r1.col014, '-', ''), NULL),
            p_c015              => NVL (REPLACE (r1.col015, '-', ''), NULL),
            p_c016              => NVL (REPLACE (r1.col016, '-', ''), NULL),
            p_c017              => NVL (REPLACE (r1.col017, '-', ''), NULL),
            p_c018              => NVL (REPLACE (r1.col018, '-', ''), NULL),
            p_c019              => NVL (REPLACE (r1.col019, '-', ''), NULL),
            p_c020              => NVL (REPLACE (r1.col020, '-', ''), NULL),
            p_c021              => NVL (REPLACE (r1.col021, '-', ''), NULL),
            p_c022              => NVL (REPLACE (r1.col022, '-', ''), NULL),
            p_c023              => NVL (REPLACE (r1.col023, '-', ''), NULL),
            p_c024              => NVL (REPLACE (r1.col024, '-', ''), NULL),
            p_c025              => NVL (REPLACE (r1.col025, '-', ''), NULL));
    END LOOP;
END;                                                       

DECLARE
    CURSOR c2
    IS
        (SELECT *
           FROM apex_collections
          WHERE collection_name = 'W');
          
BEGIN   

    FOR i IN c2
    LOOP
        BEGIN                                              
            INSERT INTO pos_cards_new (comp_id,
                                       card_seq,
                                       curncy_code,
                                       curr_rate,
                                       card_amt,
                                       card_base_amt,
                                       valid_from_date,
                                       vald_to_date,
                                       card_points,
                                       card_balance,
                                       card_isvalid,
                                       iuser_id,
                                       itime_stamp,
                                       card_id,
                                       customer_code,
                                       employee_cridet_limit,
                                       employee_cridet_curr_balance,
                                       is_admin)
                 VALUES (
                            'IPOS',
                            I.C001,
                            (SELECT curncy_code
                               FROM pos_stp_currencies
                               WHERE curncy_desc_m = i.c003
                               AND comp_id = :p0_comp_id),
                            I.C004,
                            I.C005,
                            I.C006,
                            TO_CHAR (TO_DATE (I.C007, 'YYYYMMDD'),'dd/mm/yyyy'),
                            TO_CHAR (TO_DATE (I.C008, 'YYYYMMDD'),'dd/mm/yyyy'),
                            I.C009,
                            I.C010,
                            I.C014,
                            :app_user,
                            SYSDATE,
                            I.C002,
                            I.C013,
                            I.C015,
                            I.C016,
                            I.C017);
        EXCEPTION
            WHEN DUP_VAL_ON_INDEX
            THEN
                FOR i IN c2
                LOOP
                    UPDATE pos_cards_new
                       SET card_amt = I.C005,
                           iuser_id = :app_user,
                           itime_stamp = SYSDATE,
                           valid_from_date = I.C007,
                           vald_to_date = I.C008
                     WHERE card_seq = I.C001;
                END LOOP;
        END;                                              
    END LOOP;

END;

错误原因分析 #

1. Apex Collection未清理，重复插入历史数据 #

第一次执行时，上传的数据被存入Apex集合W，执行完成后集合未被删除。第二次执行时，新上传的数据会追加到集合中，导致集合同时包含第一次的历史数据（这些数据的card_seq已经存在于pos_cards_new表）和新数据。循环插入时，历史数据的主键与表中现有数据冲突，触发DUP_VAL_ON_INDEX错误。

2. 异常处理逻辑存在严重问题 #

• 捕获DUP_VAL_ON_INDEX异常后，嵌套了一个遍历游标c2的循环，且循环变量名与外层循环变量i冲突，导致变量引用混乱。
• 内层循环会遍历整个集合，对所有记录执行UPDATE操作，不仅完全没必要（只需要更新当前触发异常的那条记录），还会导致重复执行无效操作，甚至可能引发其他逻辑错误。
• 异常处理仅处理了当前记录的错误，但后续循环仍会插入集合中的其他历史数据，继续触发主键冲突。

3. 日期转换逻辑冗余（潜在问题） #

代码中使用TO_CHAR(TO_DATE(I.C007, 'YYYYMMDD'),'dd/mm/yyyy')将日期字符串转成DATE再转回字符串，如果valid_from_date和vald_to_date是DATE类型，这种转换会导致隐式类型转换，可能引发数据类型错误或性能问题，但这不是当前主键错误的直接原因。

修复建议 #

1. 每次执行前清空Apex集合 #

在第一个PL/SQL块开始前，添加集合清空逻辑，确保集合只包含当前上传的数据：

BEGIN
    apex_collection.delete_collection(p_collection_name => 'W'); -- 新增：清空历史数据
    FOR r1 IN (...) LOOP
        -- 原add_member逻辑
    END LOOP;
END;

2. 修正异常处理逻辑 #

去掉内层循环，仅更新当前触发异常的记录，同时避免变量名冲突：

EXCEPTION
    WHEN DUP_VAL_ON_INDEX
    THEN
        UPDATE pos_cards_new
           SET card_amt = i.C005,
               iuser_id = :app_user,
               itime_stamp = SYSDATE,
               valid_from_date = TO_DATE(i.C007, 'YYYYMMDD'), -- 直接转为DATE类型（如果目标列是DATE）
               vald_to_date = TO_DATE(i.C008, 'YYYYMMDD')
         WHERE card_seq = i.C001;

3. 优化日期转换 #

如果valid_from_date和vald_to_date是DATE类型，直接使用TO_DATE(i.C007, 'YYYYMMDD')转换，无需先转成字符串，避免隐式转换问题。

内容的提问来源于stack exchange，提问作者user20983281