plsql - How I can avoid duplicate records while inserting in Oracle table? - Stack Overflow

admin管理员组

文章数量:1415111

I have apex application and I got duplicates when Inserting ,

How can I avoid duplicate rows

This is the code :

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;

the cursor p1 return correct number of rows without duplicates

the cursor para_tests also return correct number without duplicates

but when inserting rows its duplicated

for example cursor p1 returns 10 rows but when inserting its duplicated each row 10 time with total of 100 rows .

How to avoid duplicates in the loop when INSERT INTO LAB_PARA_RESULTS ?

I have apex application and I got duplicates when Inserting ,

How can I avoid duplicate rows

This is the code :

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;

the cursor p1 return correct number of rows without duplicates

the cursor para_tests also return correct number without duplicates

but when inserting rows its duplicated

for example cursor p1 returns 10 rows but when inserting its duplicated each row 10 time with total of 100 rows .

How to avoid duplicates in the loop when INSERT INTO LAB_PARA_RESULTS ?

• plsql
• oracle-apex

Share Improve this question Follow asked Feb 11 at 11:38 AbdullahAbdullah 1,0331414 silver badges2929 bronze badges 1

• 1 you have a double loop. For every row in p1 you are running para_tests which generates additional rows. I suggest you join these two sources together into a single INSERT SELECT statement and not use PL/SQL at all. – Paul W Commented Feb 11 at 13:28

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

You have not provided any sample data so it is impossible to test but, in general, you can (and should) avoid cursors and loops and do everything in a single query if possible:

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;

Note:

• Your COUNT check is implicitly performed by joining LAB_TEMPLATE_DETAILS p so you do not need to explicitly duplicate it.
• Your query already performs the equivalent of CURSOR para_tests by joining LAB_TEMPLATE_DETAILS p so doing it in a second (nested) cursor is redundant (and is probably the cause of your issue).

本文标签： plsqlHow I can avoid duplicate records while inserting in Oracle tableStack Overflow