Oracle Apex插入LAB_PARA_RESULTS时如何避免重复记录？

我有一个Oracle Apex应用，插入数据时出现重复记录。PL/SQL代码如下：

DECLARE
      CURSOR p1 IS
        SELECT DISTINCT 
            sd.TEST_NO,
            b.TEST_NAME_ENG,
            a.PATIENT_NO,
            a.ORDER_ID,
            sd.SAMPLE_ID,
            a.SECTION_ID,
            b.SAMPLE_TYPE,
            b.TEST_CONTAINER,
            b.TEST_VOLUME,
            a.SAMPLE_STATUS,
            a.CUST_NO,
            DECODE(o.ORDER_PRIORITY, 1, 'URGENT', 2, 'ROUTINE') AS ORDER_PRIORITY , 
            p.TEST_NAME,p.REFERENCE_RANGE, p.TEST_UNIT , p.SERIAL
        FROM 
            LAB_SAMPLE_HEADER a,
            LAB_TESTS b,
            LAB_SAMPLE_DETAILS sd,
            LAB_ORDERS o , 
            LAB_TEMPLATE_DETAILS p 

        WHERE 
            sd.TEST_NO = b.TEST_NO
            AND a.ORDER_ID = o.ORDER_ID
            AND sd.ORDER_ID = o.ORDER_ID
            AND a.ORDER_ID = sd.ORDER_ID
            AND a.SAMPLE_ID = sd.SAMPLE_ID
            AND a.PATIENT_NO = :P60_MRN 
            AND sd.TEST_NO = p.TEST_NO 
            AND a.order_id = :P60_ORDER
            AND B.TEST_NO IN (
                SELECT REGEXP_SUBSTR(:P60_TEST_NO, '[^,]+', 1, LEVEL) AS TESTNO
                FROM DUAL
                CONNECT BY LEVEL <= REGEXP_COUNT(:P60_TEST_NO, ',') + 1
            );


    CURSOR para_tests(p_test_no IN NUMBER) IS
    select TEST_NO,TEST_NAME,REFERENCE_RANGE, TEST_UNIT , SERIAL
    from LAB_TEMPLATE_DETAILS para 
    where para.test_no = p_test_no;
  
   v_profile_exists number; -- Variable to check if the TEST_NO belongs to a profile
   v_para_exists number; -- Variable to check if the TEST_NO belongs to a parasitology
BEGIN
    
    FOR i IN p1 LOOP
             -- Check if the current TEST_NO is part of a template
        SELECT COUNT(*) INTO v_para_exists
        FROM LAB_TEMPLATE_DETAILS
        WHERE test_no = i.TEST_NO;
        
        IF v_para_exists > 0 THEN
            -- TEST_NO is part of a parasitology, insert only parasitology tests
            FOR p IN para_tests(i.TEST_NO) LOOP
                INSERT INTO LAB_PARA_RESULTS
                (
                    ORDER_ID, SAMPLE_ID, PATIENT_NO, TEST_NO, TEST_NAME, TEST_RESULT , REFERENCE_RANGE , 
                    TEST_UNIT , SERIAL , EXAMINED_BY, EXAMINED_DATE, APPROVED_BY, APPROVED_DATE, CUST_NO , SAMPLE_STATUS ) 
                    VALUES (
                    i.ORDER_ID, -- ORDER_ID from cursor
                    i.SAMPLE_ID, -- SAMPLE_ID from cursor
                    i.PATIENT_NO, -- PATIENT_NO from cursor
                    p.TEST_NO, -- TEST_NO from para 
                    i.TEST_NAME ,
                    NULL , 
                    i.REFERENCE_RANGE , 
                    i.TEST_UNIT , 
                    i.SERIAL , -- SERIAL
                    NULL , -- EXAMINED_BY
                    NULL, -- EXAMINED_DATE
                    NULL, -- APPROVED_BY
                    NULL , -- APPROVED_DATE 
                    i.CUST_NO, -- CUST_NO from cursor
                    3 -- SAMPLE_STATUS
                                       
                );
            END LOOP;
            end if;
         END LOOP;
         
    COMMIT; -- Commit the transaction
END;

游标p1返回行数正确且无重复，游标para_tests返回行数也正确且无重复，但插入到LAB_PARA_RESULTS表时出现重复：比如p1返回10行，插入后生成了100行（每行重复10次）。需要解决循环插入时的重复问题。

核心问题是**p1游标中存在重复的TEST_NO值**——你所说的"行无重复"是指整行数据唯一，但TEST_NO字段在多行行中可能重复。当p1中有多个行对应同一个TEST_NO时，外层循环会多次处理同一个TEST_NO，每次都触发内层para_tests循环插入相同的一组记录，最终导致重复。

另外，代码中的v_para_exists查询完全多余：因为p1游标已经关联了LAB_TEMPLATE_DETAILS p，能进入p1的行必然满足sd.TEST_NO = p.TEST_NO，也就是TEST_NO一定存在于LAB_TEMPLATE_DETAILS中，这个判断可以直接删除。

方案1：去重处理TEST_NO，避免重复遍历 #

方式A：修改p1游标，确保TEST_NO唯一 #

由于插入时用到的i.ORDER_ID、i.SAMPLE_ID等字段对于同一个TEST_NO应该是相同的（否则业务逻辑本身存在问题），可以修改p1游标，通过DISTINCT确保TEST_NO唯一：

CURSOR p1 IS
  SELECT DISTINCT 
      sd.TEST_NO,
      b.TEST_NAME_ENG,
      a.PATIENT_NO,
      a.ORDER_ID,
      sd.SAMPLE_ID,
      a.CUST_NO,
      p.REFERENCE_RANGE, 
      p.TEST_UNIT, 
      p.SERIAL
  FROM 
      LAB_SAMPLE_HEADER a,
      LAB_TESTS b,
      LAB_SAMPLE_DETAILS sd,
      LAB_ORDERS o , 
      LAB_TEMPLATE_DETAILS p 
  WHERE 
      sd.TEST_NO = b.TEST_NO
      AND a.ORDER_ID = o.ORDER_ID
      AND sd.ORDER_ID = o.ORDER_ID
      AND a.SAMPLE_ID = sd.SAMPLE_ID
      AND a.PATIENT_NO = :P60_MRN 
      AND sd.TEST_NO = p.TEST_NO 
      AND a.order_id = :P60_ORDER
      AND B.TEST_NO IN (
          SELECT REGEXP_SUBSTR(:P60_TEST_NO, '[^,]+', 1, LEVEL) AS TESTNO
          FROM DUAL
          CONNECT BY LEVEL <= REGEXP_COUNT(:P60_TEST_NO, ',') + 1
      );

方式B：记录已处理的TEST_NO，跳过重复项 #

声明集合变量存储已处理的TEST_NO，每次循环前检查是否已处理，避免重复执行内层插入：

DECLARE
  -- 定义存储已处理TEST_NO的集合
  TYPE test_no_tab IS TABLE OF NUMBER;
  v_processed_tests test_no_tab := test_no_tab();
  
  CURSOR p1 IS
    -- 保持原游标定义不变
    SELECT DISTINCT 
        sd.TEST_NO,
        b.TEST_NAME_ENG,
        a.PATIENT_NO,
        a.ORDER_ID,
        sd.SAMPLE_ID,
        a.SECTION_ID,
        b.SAMPLE_TYPE,
        b.TEST_CONTAINER,
        b.TEST_VOLUME,
        a.SAMPLE_STATUS,
        a.CUST_NO,
        DECODE(o.ORDER_PRIORITY, 1, 'URGENT', 2, 'ROUTINE') AS ORDER_PRIORITY , 
        p.TEST_NAME,p.REFERENCE_RANGE, p.TEST_UNIT , p.SERIAL
    FROM 
        LAB_SAMPLE_HEADER a,
        LAB_TESTS b,
        LAB_SAMPLE_DETAILS sd,
        LAB_ORDERS o , 
        LAB_TEMPLATE_DETAILS p 
    WHERE 
        sd.TEST_NO = b.TEST_NO
        AND a.ORDER_ID = o.ORDER_ID
        AND sd.ORDER_ID = o.ORDER_ID
        AND a.ORDER_ID = sd.ORDER_ID
        AND a.SAMPLE_ID = sd.SAMPLE_ID
        AND a.PATIENT_NO = :P60_MRN 
        AND sd.TEST_NO = p.TEST_NO 
        AND a.order_id = :P60_ORDER
        AND B.TEST_NO IN (
            SELECT REGEXP_SUBSTR(:P60_TEST_NO, '[^,]+', 1, LEVEL) AS TESTNO
            FROM DUAL
            CONNECT BY LEVEL <= REGEXP_COUNT(:P60_TEST_NO, ',') + 1
        );

  CURSOR para_tests(p_test_no IN NUMBER) IS
    select TEST_NO,TEST_NAME,REFERENCE_RANGE, TEST_UNIT , SERIAL
    from LAB_TEMPLATE_DETAILS para 
    where para.test_no = p_test_no;
  
BEGIN
  FOR i IN p1 LOOP
    -- 检查当前TEST_NO是否已处理
    IF i.TEST_NO NOT MEMBER OF v_processed_tests THEN
      -- 添加到已处理集合
      v_processed_tests.EXTEND;
      v_processed_tests(v_processed_tests.LAST) := i.TEST_NO;
      
      -- 执行内层插入
      FOR p IN para_tests(i.TEST_NO) LOOP
          INSERT INTO LAB_PARA_RESULTS
          (
              ORDER_ID, SAMPLE_ID, PATIENT_NO, TEST_NO, TEST_NAME, TEST_RESULT , REFERENCE_RANGE , 
              TEST_UNIT , SERIAL , EXAMINED_BY, EXAMINED_DATE, APPROVED_BY, APPROVED_DATE, CUST_NO , SAMPLE_STATUS ) 
              VALUES (
              i.ORDER_ID,
              i.SAMPLE_ID,
              i.PATIENT_NO,
              p.TEST_NO,
              i.TEST_NAME_ENG,
              NULL , 
              p.REFERENCE_RANGE,
              p.TEST_UNIT,
              p.SERIAL,
              NULL ,
              NULL,
              NULL,
              NULL ,
              i.CUST_NO,
              3
          );
      END LOOP;
    END IF;
  END LOOP;
  COMMIT;
END;

方案2：用单条INSERT语句替代嵌套循环（推荐） #

嵌套循环效率低且容易出错，直接通过关联查询生成要插入的数据，一次性插入，从根源避免重复：

BEGIN
  INSERT INTO LAB_PARA_RESULTS
  (
      ORDER_ID, SAMPLE_ID, PATIENT_NO, TEST_NO, TEST_NAME, TEST_RESULT, REFERENCE_RANGE, 
      TEST_UNIT, SERIAL, EXAMINED_BY, EXAMINED_DATE, APPROVED_BY, APPROVED_DATE, CUST_NO, SAMPLE_STATUS
  )
  SELECT DISTINCT
      a.ORDER_ID,
      sd.SAMPLE_ID,
      a.PATIENT_NO,
      para.TEST_NO,
      b.TEST_NAME_ENG,
      NULL,
      para.REFERENCE_RANGE,
      para.TEST_UNIT,
      para.SERIAL,
      NULL,
      NULL,
      NULL,
      NULL,
      a.CUST_NO,
      3
  FROM 
      LAB_SAMPLE_HEADER a
      JOIN LAB_SAMPLE_DETAILS sd ON a.ORDER_ID = sd.ORDER_ID AND a.SAMPLE_ID = sd.SAMPLE_ID
      JOIN LAB_TESTS b ON sd.TEST_NO = b.TEST_NO
      JOIN LAB_ORDERS o ON a.ORDER_ID = o.ORDER_ID
      JOIN LAB_TEMPLATE_DETAILS p ON sd.TEST_NO = p.TEST_NO
      JOIN LAB_TEMPLATE_DETAILS para ON sd.TEST_NO = para.TEST_NO
  WHERE 
      a.PATIENT_NO = :P60_MRN 
      AND a.order_id = :P60_ORDER
      AND B.TEST_NO IN (
          SELECT REGEXP_SUBSTR(:P60_TEST_NO, '[^,]+', 1, LEVEL) AS TESTNO
          FROM DUAL
          CONNECT BY LEVEL <= REGEXP_COUNT(:P60_TEST_NO, ',') + 1
      );
  
  COMMIT;
END;

这种方式用JOIN替代嵌套循环，通过DISTINCT确保插入的记录唯一，同时执行效率更高。

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