主页 / coding / 问题 / 79429840
Accepted
Abdullah
Asked: 2025-02-11 19:38:10 +0800 CST

在 Oracle 表中插入时如何避免重复记录？

  • 772

我有 Apex 应用程序，插入时出现重复项，

如何避免重复行

这是代码：

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 也返回正确的数字，没有重复

但插入行时会重复

例如，游标 p1 返回 10 行，但插入时每行重复 10 次，总共 100 行。

插入 LAB_PARA_RESULTS 时如何避免循环中出现重复？

plsql

1 个回答

  1. Best Answer

    您没有提供任何示例数据，因此无法进行测试，但一般来说，您可以（并且应该）避免使用游标和循环，并在可能的情况下在单个查询中完成所有操作：

    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,
         sd.TEST_NO,
         p.TEST_NAME,
         NULL, -- REFERENCE RANGE
         p.REFERENCE_RANGE,
         p.TEST_UNIT,
         p.SERIAL,
         NULL, -- EXAMINED_BY
         NULL, -- EXAMINED_DATE
         NULL, -- APPROVED_BY
         NULL, -- APPROVED_DATE 
         a.CUST_NO,
         3 -- SAMPLE_STATUS
  FROM   LAB_TESTS b
         INNER JOIN LAB_SAMPLE_DETAILS sd
         ON sd.TEST_NO = b.TEST_NO
         INNER JOIN LAB_ORDERS o
         ON sd.ORDER_ID = o.ORDER_ID
         INNER JOIN LAB_SAMPLE_HEADER a
         ON a.ORDER_ID = o.ORDER_ID
            AND a.SAMPLE_ID = sd.SAMPLE_ID
         INNER JOIN LAB_TEMPLATE_DETAILS p 
         ON sd.TEST_NO = p.TEST_NO
  WHERE  a.PATIENT_NO = :P60_MRN 
  AND    a.order_id = :P60_ORDER
  AND    ',' || :P60_TEST_NO || ',' LIKE '%,' || B.TEST_NO || ',%';

  COMMIT;
END;

    笔记：

    • 您的COUNT检查是通过加入隐式执行的，LAB_TEMPLATE_DETAILS p因此您不需要明确地重复它。
    • CURSOR para_tests您的查询已经执行了与连接等效的操作，LAB_TEMPLATE_DETAILS p因此在第二个（嵌套）游标中执行此操作是多余的（并且可能是导致问题的原因）。
    • 2

相关问题