oracle 当DUP_瓦尔_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;

第一次代码插入和第二次退出代码
给予我主键重复的错误
为什么！！
主键是card_seq

使用MERGE语句：

BEGIN   
  MERGE INTO pos_cards_new dst
  USING (
    SELECT *
    FROM   apex_collections
    WHERE  collection_name = 'W'
  ) src
  ON (src.card_seq = dst.card_seq)
  WHEN NOT MATCHED THEN
    INSERT (
      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',
      src.C001,
      ( SELECT curncy_code
        FROM   pos_stp_currencies
        WHERE  curncy_desc_m = src.c003
        AND    comp_id = :p0_comp_id ),
      src.C004,
      src.C005,
      src.C006,
      TO_CHAR (TO_DATE (src.C007, 'YYYYMMDD'),'dd/mm/yyyy'),
      TO_CHAR (TO_DATE (src.C008, 'YYYYMMDD'),'dd/mm/yyyy'),
      src.C009,
      src.C010,
      src.C014,
      :app_user,
      SYSDATE,
      src.C002,
      src.C013,
      src.C015,
      src.C016,
      src.C017
    )
  WHEN MATCHED THEN
    UPDATE
    SET card_amt        = src.C005,
        iuser_id        = :app_user,
        itime_stamp     = SYSDATE,
        valid_from_date = src.C007,
        vald_to_date    = src.C008;
END;
/

注意：这是未经测试的，因为我们没有您的表格或Apex应用程序来测试它。*
注意2：不要将日期存储为字符串。*
注意3：如果您在其他列上有任何唯一索引，那么在主键上合并可能不是错误的来源;然而，你在问题中提供的资料不足，无法确定是否如此。*

oracle 当DUP_瓦尔_ON_INDEX时

1条答案

相关问题

热门标签

最新问答