Oracle :: 다중 INSERT 하기

 

최근에 맡은 프로젝트에서 다시 Oracle을 쓰게 되었다. (최근까지는 MSSQL을 이용한 프로젝트에 참여했었다)
다중 INSERT를 해야 해서 MSSQL 사용했을 때처럼 했는데
계속 " ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다 " 오류가 발생했다.

구글에 검색을 해보니 오라클에서 다중 INSERT 문을 처리하는 방법들이 나왔다.

첫 번째 방법 🔥
INSERT INTO 테이블 (COL1, COL2, ....) SELECT VAL1, VAL2, ... FROM DUAL UNION ALL
SELECT VAL1, VAL2, ... FROM DUAL

결과
"ORA-00001: 무결성 제약 조건에 위배됩니다"
PK 값에 이전값의 MAX값을 가져와서 넣도록 해놨는데 일괄 삽입이라 그런지 적용이 안되어
무결성 제약조건에 위배되어 데이터가 들어가지 않았다.

두 번째 방법 🔥
BEGIN ~ END 블록 설정해주기
MSSQL 처럼 일단 INSERT 구문을 만들어주고 그 구문들을 BEGIN ~ END; 블록으로 감싸주었다
그러니까 잘 들어가짐

첫번째 방법이 속도면으로는 좋을 것 같아서 다시 구글링을 해본 결과
한 블로그에서 함수를 이용해서 해결하는 방법을 찾았다.
UNION ALL + 함수사용 하기 블로그 참고링크

세번째 방법 🔥
FROM DUAL UNION ALL + 함수 사용
결과
"ORA-04091: 사용 테이블이 변경되어 트리거/함수가 볼 수 없습니다." 
NEXTVAL을 쓰는 것이 아니라 값을 INSERT 하는 테이블에서 PK의 MAX값을 SELECT 해와서 넣어주려고 했는데 해당 테이블에서 INSERT가 일어나다 보니 테이블이 변경되어 함수를 사용할 수 없었다. 
구글링을 더 해보니 "pragma autonomous_transaction;" 구문을 추가해봤지만
"ORA-00001: 무결성 제약 조건에 위배됩니다"이 다시 발생했다.
PK만 저장하는 테이블 따로 만들어서 호출하는 방식으로도 해봤지만 무결성 위배 오류 발생 ㅎㅎ

일단 두번째 방법으로 했다
혹시 지나가시다 좋은 방법 있으면 알려주세요 😂~

회사선배님이 방법을 알려주셨다!! 얏호~ 

*아래의 테이블 및 함수명, 매개변수 등의 디테일 부분은  회사 소스를 직접 가져올 수 없기 때문에 내가 임의로 작성한 것*

TBLASTID 테이블 생성
IDKEY VARCHAR2(50)
LASTNUMBER NUMBER
DESCRIPTION VARCHAR2(2000)

 👉 각종 KEY값들을 저장해두는 테이블을 생성한다
     LASTNUMBER 컬럼은 마지막에 사용된 PK값으로 함수를 이용해 +1된 값을 가져올 예정 

함수생성

CREATE OR REPLACE FUNCTION FNGETLASTNUM(PIDKEY VARCHAR2) RETURN VARCHAR2
 👉 해당 KEY의 LASTID를 변경해주기 위해 PIDKEY를 인수로 넘겨준다

IS 
  RETURNID VARCHAR2(50)  := '';
  LASTIDNUM NUMBER := 0;
  EXISTCOUNT := 0;

BEGIN
  BEGIN
 	 SELECT COUNT(1) INTO EXISTCOUNT FROM TBLASTID WHERE IDKEY = PIDKEY;

    IF EXISTCOUNT > 0 THEN
    	SELECT NVL(LASTNUMBER ,0) INTO LASTIDNUM FROM TBLASTID WHERE IDKEY = PIDKEY;
    ELSE
    	INSERT INTO TBLASTID (IDKEY, LASTNUMBER ) VAKYES (PIDKEY, LASTIDNUM );
    END IF;
     👉 새로 생성한 테이블에서 넘겨준 KEY값에 해당하는 값이 존재하면 함수내에서 선언한 LASTIDNUM에 값을 넣어준다

  	UPDATE TBLASTID SET LASTNUMBER  = LASTIDNUM  + 1 WHERE IDKEY = PIDKEY;
  	COMMIT;
  	👉 무결성제약조건 위배 오류를 막기위해 가져왔던 값에 +1 해준다.

  	RETURNID := LASTIDNUM + 1;

  	EXCEPTION WHEN OTHERS THEN LASTIDNUM := NULL;
  	👉 예외처리를 해준다
  END;

  RETURN  LASTIDNUM;
END;

FROM DUAL UNION ALL + 함수를 사용하니 확실히 속도가 빨라졌다 

 

첫번째가 함수+UNIONALL / 두 번째가 함수 + 그냥 INSERT 한 것