[Oracle] 오라클 INSERT 할 때 중복 제외하는 방법

오라클에서 INSERT 할 때 PK가 중복되어서 오류가 발생하는 경우가 많다. 이럴 경우 중복을 무시하거나 중복을 제외하고 INSERT를 하거나, 미리 중복을 체크하여 INSERT가 되지 않도록 해야 한다.

 

ORA-00001 : 유일성 제약조건에 위배됩니다 (unique constraint violated)

 

아래의 3가지 방법을 사용하여 INSERT 할 때 중복을 제외하고 데이터를 입력하면 오류를 방지할 수 있다.

​

NOT EXISTS 연산자를 사용하여 중복 제외 MERGE 절을 사용하여 중복 제외 PRODEDURE를 사용하여 중복 입력 시 예외처리

 

NOT EXISTS 연산자를 사용하여 중복 제외

INSERT INTO emp (empno, ename, job, hiredate)
SELECT 8000
     , 'GENT'
     , 'BLOGGER'
     , TRUNC(SYSDATE)
  FROM dual
 WHERE NOT EXISTS (SELECT 1
                     FROM emp
                    WHERE empno = 8000)

 

 

NOT EXISTS 연산자를 사용하여 입력할 테이블에 PK 조건으로 조회하여 존재하지 않을 때만 INSERT를 한다.

사원번호(empno)가 PK이기 때문에 emp 테이블에서 존재하지 않을 때만 emp 테이블에 INSERT 한다.

 

MERGE 절을 사용하여 중복 제외

MERGE 
 INTO emp
USING dual
   ON (empno = 8001)
 WHEN NOT MATCHED THEN
      INSERT (empno, ename, job, hiredate)
      VALUES (8001, 'GENT', 'BLOGGER', TRUNC(SYSDATE));

 

 

MERGE 절을 사용하면 사원번호(empno)가 존재하지 않을 때만 INSERT를 할 수 있다.

WHEN MATCHED THEN 조건절을 추가한 후 데이터가 존재하면 UPDATE 구문을 추가로 수행할 수 있다.

 

PRODEDURE를 사용하여 중복 입력 시 예외처리

CREATE OR REPLACE PROCEDURE pc_insert_emp
(
    p_empno    IN NUMBER,
    p_ename    IN VARCHAR2,
    p_job      IN VARCHAR2,
    p_hiredate IN DATE
)
IS

BEGIN

    INSERT INTO emp (empno, ename, job, hiredate)
    VALUES (p_empno, p_ename, p_job, p_hiredate);

EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        NULL;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE: ' || SQLERRM);

END pc_insert_emp;

 

 

PROCEDURE를 사용하여 INSERT를 한다면 EXCEPTION 절에 WHEN DUP_VAL_ON_INDEX THEN을 추가해 놓고 NULL을 반환하면 중복으로 입력될 경우 오류가 발생하지 않는다. NULL 대신 UPDATE 구문을 작성해 놓으면, 데이터가 존재할 경우 UPDATE를 수행할 수도 있다.

[Oracle] 오라클 MERGE INTO 사용법 & 노하우 정리

[Oracle] 오라클 INSERT 방법 & 노하우 정리 (데이터 입력)