[database] 오라클 다중 insert 문

1. insert all

create table test(
    person VARCHAR(255) PRIMARY KEY,
    salary INT
);

insert into test values ('one', 1000),('two', 2000); -- 에러!
-- SQL 오류: ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다

 

mysql(maria db)에서 사용하는 형식의 다중 삽입문이 오라클에서는 SQL 명령어가 올바르게 종료되지 않았다는 에러가 뜨며 동작하지 않는다. 그래서 다른 블로그들을 참고했더니 첫 번째로 이 해결책을 제시했다. 

insert all 
    into test (person, salary) values ('one', 1000)
    into test (person, salary) values ('two', 2000)
select 1 from dual; -- 성공!
-- 2개 행 이(가) 삽입되었습니다.

 

여기서 values(값1, 값2 ...) 뒤에 콤마를 쓰거나, select 문을 함께 쓰지 않을 경우 SQL 오류: ORA-00928: 누락된 SELECT 키워드 오류가 발생한다. 다중 테이블 insert 문을 사용할 때 반드시  select 서브 쿼리가 동반해야 하기 때문에 select 절을 써야 한다는 것 같다. [https://thebook.io/006696/0204/, https://stackoverflow.com/questions/39576/best-way-to-do-multi-row-insert-in-oracle]

 

그런데 유의해야 할 점은 select의 from 절에서 데이터를 삽입할 대상 테이블이 아니라 dual을 썼다는 점이다. dual 대신 현재 작업 중인 test 테이블을 입력할 경우 오류는 발생하지 않지만 "0개 행 이(가) 삽입되었습니다." 만 출력될 뿐이다. 그래서 위 구문대로 dual로 해야지 제대로 행이 삽입된다. 

 

2. union all 

insert into test (person, salary) 
    select 'one', 1000 from dual union all
    select 'two', 2000 from dual union all
    select 'three', 3000 from dual; -- 성공!
-- 3개 행 이(가) 삽입되었습니다.

 

union all 구문을 사용할 때도 from dual을 사용해야 하며 삽입하고자 하는 데이터를 select 절로 묶은 다음 union all로 연결한다. 이때 마지막 select 절에서는 union all을 사용하지 않는다. 그리고 각각의 데이터들은 괄호 없이 콤마로만 연결한다.

 

링크에 걸어둔 스택 오버플로우를 보면 이외에도 프로시저나 loop 등으로도 처리할 수 있는 듯하다.