Oracle存储过程插入图片时旧数据丢失，需保留旧数据并处理同名图片更新

原存储过程在插入新图片时，会删除同一IMG_ID下所有Panaromic类型的旧记录，导致所有旧行丢失。现在需要调整为：保留未重复的旧行，仅对**同一IMG_ID+IMG_TYPE下同名（IMG_NAME相同）**的图片执行更新/替换操作，新的不同名图片直接插入。

方案1：循环内处理单条记录的替换 #

修改原代码，去掉批量删除逻辑，改为在插入单张图片前仅删除同名旧记录：

IF TBL_INSERT > 0 THEN
    IF P_PANORAMIC_IMAGES IS NOT NULL THEN
        FOR PMULTIFIELDS IN (
            SELECT REGEXP_SUBSTR(P_PANORAMIC_IMAGES,'[^,]+', 1, LEVEL) AS IMAGES 
            FROM DUAL
            CONNECT BY REGEXP_SUBSTR(P_PANORAMIC_IMAGES, '[^,]+', 1, LEVEL) IS NOT NULL
        )
        LOOP
            -- 仅删除当前要插入的同名旧记录
            DELETE FROM TBL_VSAT_IMAGE_DETAIL 
            WHERE IMG_ID = TBL_INSERT 
              AND IMG_TYPE = 'Panaromic' 
              AND IMG_NAME = PMULTIFIELDS.IMAGES;
            
            -- 插入新记录
            INSERT INTO TBL_VSAT_IMAGE_DETAIL (IMG_ID, IMG_NAME, IMG_TYPE, IMG_UPLOADED_DATE, UPLOADED_BY)
            VALUES (TBL_INSERT, PMULTIFIELDS.IMAGES, 'Panaromic', SYSDATE, P_CREATED_BY);
        END LOOP;
    END IF;
END IF;

改动说明 #

• 删掉了原代码里批量清空同一IMG_ID下所有Panaromic记录的逻辑，不再误删未重复的旧行。
• 每次插入前只清理当前要插入的同名旧记录，保证其他不同名的旧数据保留。
• 逻辑简单直观，适合小批量图片处理。

方案2：用MERGE实现高效Upsert #

如果图片数量较多，用MERGE语句一次性处理所有记录，性能更优：

IF TBL_INSERT > 0 AND P_PANORAMIC_IMAGES IS NOT NULL THEN
    MERGE INTO TBL_VSAT_IMAGE_DETAIL t
    USING (
        SELECT TBL_INSERT AS IMG_ID,
               REGEXP_SUBSTR(P_PANORAMIC_IMAGES,'[^,]+', 1, LEVEL) AS IMG_NAME,
               'Panaromic' AS IMG_TYPE,
               SYSDATE AS IMG_UPLOADED_DATE,
               P_CREATED_BY AS UPLOADED_BY
        FROM DUAL
        CONNECT BY REGEXP_SUBSTR(P_PANORAMIC_IMAGES, '[^,]+', 1, LEVEL) IS NOT NULL
    ) s
    ON (t.IMG_ID = s.IMG_ID 
        AND t.IMG_TYPE = s.IMG_TYPE 
        AND t.IMG_NAME = s.IMG_NAME)
    WHEN MATCHED THEN
        UPDATE SET t.IMG_UPLOADED_DATE = s.IMG_UPLOADED_DATE,
                   t.UPLOADED_BY = s.UPLOADED_BY
    WHEN NOT MATCHED THEN
        INSERT (IMG_ID, IMG_NAME, IMG_TYPE, IMG_UPLOADED_DATE, UPLOADED_BY)
        VALUES (s.IMG_ID, s.IMG_NAME, s.IMG_TYPE, s.IMG_UPLOADED_DATE, s.UPLOADED_BY);
END IF;

改动说明 #

• 用MERGE替代循环操作，一次完成所有图片的更新或插入。
• 匹配条件为IMG_ID、IMG_TYPE、IMG_NAME三者都相同：
  • 匹配到旧记录：直接更新上传日期和上传人字段（实现更新效果）。
  • 没匹配到：插入新记录。
• 无需手动删除旧数据，逻辑更简洁，批量处理时性能更好。

额外说明 #

• 表中ID字段作为主键，两种方案都会自动生成新的主键值（如果是序列自增的话），符合业务需求。
• 如果必须严格“删除旧记录再插入新记录”，可以把MERGE的WHEN MATCHED THEN分支改成DELETE，再插入新记录，但通常更新字段的方式更高效。

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