[ORACLE 에서 다중 INSERT 할때 잘못된 예제]
<insert id="queryId" parameterType="map">
/*SystemDAO.queryId */
<!-- oracle 문법에 적합하지 않음: 시퀀스가 nextval 되지않는 문제가 있음-->
INSERT INTO SYS_TABLE
( COL_A, --컬럼_A -> PK
COL_B, --컬럼_B -> PK
COL_C, --컬럼_C
COL_D, --컬럼_D
COL_E, --컬럼_E
COL_F, --컬럼_F
COL_G, --컬럼_G
COL_H, --컬럼_H
COL_I, --컬럼_I
COL_J, --컬럼_J
COL_K --컬럼_K
) values
<foreach collection="list" item="item" separator=" , ">
(
SQ_SYS.NEXTVAL,
#{colB},
#{item.colC},
#{item.colD},
#{item.colE},
#{item.colF},
#{item.colG},
TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
#{colI},
TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
#{colK}
)
</foreach>
</insert>Oracle에서 위와 같이 사용할 때 두가지 문제점이 있다.
1. 사용한 시퀀스가 nextval 이 되지 않는 문제
2. 문법적으로 맞지않아 오류발생
첫번째 문제는 사실, 해당 시퀀스를 사용하는 컬럼이 테이블에서 PK가 아니라면 문제가 되지않겠지만,
내가 사용한 테이블에서는 해당 컬럼이 PK라서 결국 해당 쿼리를 그냥 서비스에서 반복문으로 처리하였다.
두번째 문제는 보통 Mysql 이나 다른 sql 문법에서는 위와 같이 사용해서 ORACLE에서도 비슷하게 사용하다가 오류를 만난다고 한다. 오라클에서는 위와같은 방법이 아니라 INSERT ALL을 이용한다.
구분자는 콤마가 아닌 공백(" ")으로 처리된다.
foreach 자체 기능을 통해 open에 insert all을 해주고 마지막 select * from dual로 처리해준다.
만약 위의 쿼리를 시퀀스를 사용하는 컬럼이 해당 테이블에서 PK가 아니고 ORACLE에서 사용한다면 다음과 같이 변경이 가능하다 (두방법 모두 시퀀스 값이 동일한 값이 들어감)
방법1) values 가 정해져있을때
<insert id="queryId" parameterType="map">
/*SystemDAO.queryId */
INSERT ALL
INTO SYS_TABLE VALUES
(
SQ_SYS.NEXTVAL,
#{valA_1},
#{valB_1},
#{valC_1},
#{valD_1},
#{valE_1},
#{valF_1},
TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
#{valG_1},
TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
#{valH_1}
)
INTO SYS_TABLE VALUES
(
SQ_SYS.NEXTVAL,
#{valA_2},
#{valB_2},
#{valC_2},
#{valD_2},
#{valE_2},
#{valF_2},
TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
#{valG_2},
TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
#{valH_2}
)
SELECT * FROM DUAL;
</insert>
방법2) mybatis foreach 를 활용
<update id="queryId" parameterType="map">
/*SystemDAO.queryId */
<!-- oracle 문법에 적합하지 않음: 시퀀스가 nextval 되지않는 문제가 있음-->
<foreach collection="list" item="item" open="INSERT ALL" close="SELECT * FROM DUAL" separator=" ">
INTO SYS_TABLE
VALUES
(
SQ_SYS.NEXTVAL,
#{colB},
#{item.colC},
#{item.colD},
#{item.colE},
#{item.colF},
#{item.colG},
TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
#{colI},
TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
#{colK}
)
</foreach>
</update>* 주의내용: 해당방법을 사용시 mapper 형식을 insert(<insert id="" >)로 하면 sql command not properly ended 라는 에러발생. update(<update id="" 어쩌구)로 변경해서 사용해야함.
자, 여기까지는 두번째 문제를 해결한 것이다. 오라클의 문법에 맞춰 다중 insert 를 한것.
위에 발견된 두가지 문제를 모두 해결하려면 가장간단하게 서비스에서 반복문으로 여러번 insert를 시켜도 된다.
하지만, 그렇게 하면 connection이 여러번 요청되고, foreach는 한번의 요청으로 원하는 데이터를 insert할 수 있기때문에 foreach를 사용해서 해결 해보고 싶어 다음과 같이 작성해보았다.
당시엔, 서비스로 반복문으로 해결하고 넘어가서 아래에 작성한방법으로는 실제 DB에서 작동을 해보진 않았다.
<update id="queryId" parameterType="map">
/*SystemDAO.queryId */
<!-- oracle 문법에 적합하지 않음: 시퀀스가 nextval 되지않는 문제가 있음-->
INSERT INTO SYS_TABLE
( COL_A, --컬럼_A -> PK
COL_B, --컬럼_B -> PK
COL_C, --컬럼_C
COL_D, --컬럼_D
COL_E, --컬럼_E
COL_F, --컬럼_F
COL_G, --컬럼_G
COL_H, --컬럼_H
COL_I, --컬럼_I
COL_J, --컬럼_J
COL_K --컬럼_K
)
SELECT SQ_SYS.NEXTVAL AS sysSeq
, A.*
From
(
<foreach collection="list" item="item" index="index" separator="UNION ALL">
SELECT
#{colB},
#{item.colC},
#{item.colD},
#{item.colE},
#{item.colF},
#{item.colG},
TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
#{colI},
TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
#{colK}
FROM DUAL
</foreach>
) A
</update>시범적으로 작동 시켜볼 기회가 있길 바라는중이다.
