[DataBase] Oracle + MyBatis SQL

📝 개요

Spring Boot + MyBatis + Oracle DB 로 프로젝트를 진행하면서 Oracle 의 쿼리 문법은 기본적으로 좀 더 엄격하거나, 특이한 부분이 많다고 느겼다. MySQL 에서는 당연하게 되었던 것들이 Oracle 로 변경되면서 새롭게 알아가는 것도 있었고, 새로운 문법을 익혀야 하는것도 있었다.

다음은 Groupware 백엔드를 구현하면서 알아보았던 Oracle DB 의 SQL 문법과 MyBatis 에서 Oracle 을 사용했을 때의 특징과 사용에 대해서 설명한다.

🚀 Oracle vs MySQL

1️⃣ 시퀀스(Sequence) vs AUTO_INCREMENT

MySQL

PK 자동 증가 컬럼이 테이블에 존재한다.
INSERT 시 값 입력을 따로 하지 않으면 AUTO_INCREMENT 설정대로 값이 증가한다.
# USERS 테이블에 PK 값인 id 에 대해서 AUTO_INCREMENT 설정 CREATE TABLE USERS ( id INT AUTO_INCREMENT PRIMARY KEY, ... );

Oracle

AUTO_INCREMENT 없음
별도의 SEQEUNCE 객체를 만들어서 시퀀스 값을 꺼내와 INSERT 시 PK 컬럼에 넣어줘야 함
# SEQUENCE SEQ_USERS 객체에서 값을 하나 꺼내옴 SELECT SEQ_USERS.nextval FROM DUAL # 시퀀스 생성 CREATE SEQUENCE 시퀀스명 [START WITH 시작값] -- 시퀀스 번호 시작 값 지정 [INCREMENT BY 증가값] -- 한번 조회 시 시퀀스를 얼마나 증가시킬지 지정 [NOCACHE | CACHE n] -- 시퀀스 번호를 미리 얼마나 캐싱할지 지정 [NOCYCLE | CYCLE]; -- 최대값에 도달하면 다시 처음부터 재사용할지 여부

[ SEQUENCE 객체 ]

Oracle 테이블 자체에는 AUTO_INCREMENT 가 존재하지 않는다. RDB 설계 사상에서, “번호 생성은 테이블이 아니라 별도의 시퀀스 객체가 관리” 동시성, 트랜잭션 분리/관리의 이유 때문이 시퀀스 객체를 사용한다.

시퀀스 객체는 여러 테이블이 한 시퀀스를 사용해도 되고 번호값을 유연하게 관리가 가능하다. MySQL 은 PK 별로만 증가하지만, Oracle 은 여러 테이블에서 공유가 가능하다.

동시성 관리
- 다수의 사용자가 동시에 INSERT 요청을 보낼때, MySQL AUTO_INCREMENT 는 테이블의 마지막 PK 를 기준으로 증가시킴
- 동시에 여러 트랜잭션이 INSERT 를 시도하면 LOCK 이 걸릴 수 있음
- Oracle 시퀀스는 “독립 개체” 로 여러 트랜잭션/세션이 동시에 nextval 을 요청해도 시퀀스 자체에서 “번호만 관리” 하기 때문에 테이블에 LOCK 이 걸리지 않음
- 대용량/초고속 트랜잭션 환경에서 “PK 충돌, 지연, 락” 이슈가 획기적으로 적음
트랜잭션 분리/관리
- 테이블의 PK 와 “번호 생성” 을 분리하면 트랜잭션과 별도로 동작하게 됨
- 어떤 트랜잭션이 시퀀스에서 nextval 받아서 INSERT 를 시도 했을 때, 중간에 트랜잭션이 롤백(실패) 되면, 번호는 소비(스킵) 되지만 다시 사용되지는 않음
관리 편의성/유연성
- 시퀀스 객체는 재사용, 공유, 복수 테이블에 적용 가능
- 필요에따라 여러 테이블이 같은 시퀀스를 사용해도 무방
- 테이블 구조에 영향 없이 번호 정책만 따로 관리/변경 가능
- 시퀀스 값 초기화, 증가 폭, 캐싱 등 유연하게 커스터마이징 가능

[ DUAL 테이블 ]

SELECT 1 + 1 FROM DUAL;               -- 결과: 2
SELECT SYSDATE FROM DUAL;             -- 오늘 날짜
SELECT SEQUENCE_SEQ.NEXTVAL FROM DUAL;  -- 시퀀스 값 추출

오라클에서는 SELECT 결과는 무조건 테이블에서 반환하게 되어있다. 즉, 아무값이든 SELECT 하려면 FROM 테이블 구분이 반드시 반드시 필요하다. 그래서 오라클은 임시로 1행을 반환하는 DUAL 테이블을 사용한다. DUAL 테이블을 조회하면 아무런 값도 들어있지 않은 더미 행을 리턴한다.

[ MyBatis 에서 SEQUENCE 사용 ]

<insert id="insertSomething" parameterType="SomeVO">
    <selectKey keyProperty="id" resultType="long" order="BEFORE">
        SELECT MY_SEQ.nextval FROM DUAL
    </selectKey>
    INSERT INTO MY_TABLE (
        ID, NAME, ...
    ) VALUES (
        #{id}, #{name}, ...
    )
</insert>

MyBatis 에서 Oracle 의 SEQUENCE 값을 가져오는 방식은 다음과 같다. <insert> 쿼리 태그 안에 <selectKey> 를 정의해서 SEQUENCE 로 부터 값을 가져오고 INSERT 시에 그 값을 넣어준다. keyProperty 에는 시퀀스에서 가져온 PK 값을 저장할 자바 객체의 필드명이 되고 order=”BEFORE” 의 설정 값은 INSERT 전에 시퀀스값을 미리 세팅한다는 의미를 가진다.

[ selectKey 사용 시 여러개의 SEQUENCE 조회 ]

<selectKey keyProperty="id1, id2" resultType="map" order="BEFORE">
    SELECT SEQ1.nextval AS id1, SEQ2.nextval AS id2 FROM DUAL
</selectKey>

만약에 SEQUENCE 를 통해서 가져와야 하는 PK 의 값이 여러개라면 다음과 같이 keyProperty 를 콤마로 구분짓고, SELECT 를 통해서 동시에 여러 시퀀스를 조회하여 가져온다. 하지만 동시에 여러 시퀀스 값을 가져오는 경우는 거의 없다. 복합 PK 라면 보통 한 시퀀스에서 하나의 번호만 가져오고, 나머지는 조합해서 PK 를 만든다.

2️⃣ 페이징 쿼리 (LIMIT vs OFFSET/FETCH)

[ MySQL ]

SELECT *
  FROM TABLE_NAME
 ORDER BY CREATED_AT DESC
 LIMIT @count, OFFSET @offset

LIMIT 과 OFFSET 의 값을 넘겨받고 해당 값으로 페이징을 진행한다. LIMIT 의 카운트에 해당하는 값은 SELECT 결과로부터 몇개의 ROW 를 가져올지 지정하고, OFFSET 의 경우 몇 번째 ROW 를 가져올지 지정한다.

[ Oracle ]

-- Oracle 12c 이상 (OFFSET/FETCH)
SELECT *
  FROM TABLE_NAME
 ORDER BY CREATED_AT DESC
 OFFSET @offset ROWS FETCH NEXT @size ROWS ONLY;

-- Oracle 11g 이하 (ROWNUM + 인라인 뷰)
SELECT * FROM (
    SELECT t.*, ROWNUM rn
    FROM (SELECT * FROM TABLE_NAME ORDER BY CREATED_AT DESC) t
    WHERE ROWNUM <= 30
)
WHERE rn > 10;

Oracle 12c 이상의 버전에서는 OFFSET @offset ROWS FETCH NEXT @size ROWS ONLY 문법을 사용해서 MySQL 과 유사한 구조를 갖는다. OFFSET 의 값을 지정해서 몇 번째 ROW 를 가져올지 정하고 ROWS FETCH 값을 지정해서 그 다음 가져올 행의 수를 지정한다. Oracle 11g 이하의 버전에서는 인라인 뷰(서브쿼리) 를 사용해서 해당 테이블의 조회 결과에 ROWNUM 조건을 부여하여 처리한다. ROWNUM 은 결과 생성 순간에 부여되기 때문에 무조건 인라인 뷰로 먼저 정렬한 후 ROWNUM 을 지정한다.

FETCH NEXT n ROWS ONLY 와 FETCH FIRST n ROWS ONLY 는 동일하게 도작한다. 상위 n 개의 행만 가져온다는 의미를 가진다.

3️⃣ 문자열 연결 (CONCAT vs ||)

[ MySQL ]

SELECT CONCAT('A', 'B', 'C'); -- 결과 : 'ABC'

MySQL 은 CONCAT 함수만 사용 가능
|| (파이프 두 개)는 논리 연산자로 취급됨 (기본 SQL 모드에서 문자열 연결로 쓰지 않음)
여러 개의 문자열을 한 번에 이어붙일 수 있음 (CONCAT(’A’, ‘B’, ‘C’ … ))

[ Oracle ]

SELECT 'A' || 'B' || 'C' FROM DUAL; -- 결과 : 'ABC'

CONCAT 함수 지원이 되지만 2개의 문자열만 연결 가능
여러 개 연결 시 || (파이프 두 개) 연산자 사용
MyBatis 에서는 LIKE ‘%’ || #{keyword} || ‘%’ 로 사용되기도 하지만 LIKE CONCAT (’%’, #{keyword}, ‘%’) 로도 사용 가능하다.

4️⃣ NVL vs NULL

[ MySQL ]

SELECT IFNULL(column, '기본값')

column 이 NULL 이면 ‘기본값’ 반환
NULL 이 아니면 원래 값 반환
컬럼에 null 허용이 많을 때, 기본값 처리용으로 사용

[ Oracle ]

SELECT NVL(column, '기본값') FROM DUAL

column 이 NULL 이면 ‘기본값’ 반환
NULL 이 아니면 원래 값 반환
상수/함수 테스트 등을 진행할때는 DUAL 테이블을 붙임
함수 이름만 다름, 동작 원리는 동일함

[ 프로젝트 예시 ]

<!-- 근속 1년 미만 사용자 잔여 정보 조회 -->
<select id="selectUsersUnderOneYear" parameterType="int" resultType="VacationBalanceVO">
    SELECT VB.VACATION_BALANCE_ID,
           VB.USER_ID,
           VB.YEAR,
           VB.VACATION_GRANT_DAYS,
           VB.VACATION_USED_DAYS,
           VB.VACATION_REMAINING_DAYS,
           VB.UPDATED_AT
    FROM VACATION_BALANCE VB
        JOIN USERS U ON VB.USER_ID = U.USER_ID
    WHERE VB.YEAR = #{currentYear}
      AND MONTHS_BETWEEN(SYSDATE, U.USER_HIRE_DATE) &lt; 12
      AND NVL(U.IS_DELETE, 'N') = 'N'
      AND (U.USER_RESIGN_DATE IS NULL OR U.USER_RESIGN_DATE > SYSDATE)
</select>

근속 1년 미만 사용자 잔여정보 조회 시 NVL 을 사용하여 USERS 테이블의 삭제여부 필드를 ‘N’ 으로 지정

5️⃣ 날짜 / 계층 / 집합 연산 (CONNNECT BY, LEVEL, MULTISET)

[ 프로젝트 코드 ]

<!-- 사용자 휴가 날짜 조회 (범위) -->
<select id="findVacationDatesByUserAndRange" parameterType="map" resultType="java.time.LocalDate">
    SELECT COLUMN_VALUE AS VACATION_DATE
    FROM TABLE (
        CAST(
            MULTISET (
                SELECT VACATION_START_DATE + LEVEL - 1
                FROM VACATION
                WHERE USER_ID = #{userId}
                  AND IS_DELETED = 'N'
                  AND VACATION_STATUS = 'APPROVED_SECOND'
                  AND VACATION_START_DATE &lt;= #{endDate}
                  AND VACATION_END_DATE &gt;= #{startDate}
                  AND ROWNUM &lt; 1000
                CONNECT BY LEVEL &lt;= VACATION_END_DATE - VACATION_START_DATE + 1
                AND VACATION_ID = PRIOR VACATION_ID
                AND PRIOR SYS_GUID() IS NOT NULL
            ) AS SYS.ODCIDATELIST
        )
    )
</select>

FROM TABLE (…)
- TABLE 함수 : 오라클에서 컬렉션(배열, 집합) 타입을 ‘행’ 으로 펼쳐주는 함수
- 배열로 반환 된 값을 SQL 결과의 행(row) 로 반환, MULTISET, CAST, SYS.ODCIDATELIST 등으로 뽑은 여러 날짜(배열) 을 일반 SELECT 결과처럼 한 줄씩 보여주기 위해 사용
CAST ( … AS SYS.ODIDATELIST)
- SQL 표준 변환 함수 : 타입을 명확하게 변환(캐스팅) 할 때 사용
- AS SYS.ODCIDATELIST : 오라클 내장 “날짜 배열 타입” 으로 반환 / OBJECT COLLECTION 타입
- MULTISET 결과 (날짜 집합) 를 오라클이 인식할 수 있는 날짜 배열 타입으로 바꿔 TABLE 함수에서 사용할 수 있게 함
MULTISET ( SELECT.. )
- 서브쿼리 결과 여러 행을 집합(배열) 로 만들어주는 오라클 함수
- 일반 SELECT 행(row) 으로 리턴되지만, MULTISET 은 그 결과를 한번에 배열(Collection) 으로 만듦
- VACATION 에서 구한 여러 날짜(행)를 날짜 집합(배열)으로 만들어서 한꺼번에 처리 가능하게 함
SELECT VACATION_START_DATE + LEVEL - 1
- CONNECTION BY 구문에서만 사용 가능한 1부터 시작하는 재귀 깊이(계층) 숫자
- 반복문 처럼 증가하며, 휴가 시작일에서 LEVEL(1, 2, 3 … ) 을 더해 휴가 전체 날짜를 하루씩 나열
- VACATION_START_DATE + LEVEL - 1 : 휴가 시작일로부터 종료일까지 날짜 전부를 구하는 공식
CONNECT BY
- 오라클 전용 계층/반복 쿼리 구문, 여기서는 하나의 행을 LEVEL 값만큼 반복적으로 확장
- LEVEL ≤ VACATION_END_DATE - VACATION_START_DATE + 1 : 휴가일이 3일이면 LEVEL 이 1, 2, 3 까지만 생성(날짜 개수만큼 반복)
- VACATION_ID = PRIOR VATION_ID : 각 휴가 행별로 반복(확장) 할 때, 이전 행의 VACATION_ID 와 같은 값에서만 확장 (즉, 각 휴가별로 별도로 반복)
- AND PRIOR SYS_GUID() IS NOT NULL : 오라클 CONNECT BY 쿼리의 “사이클 방지용 관용구”
- 재귀/반복이 꼬이지 않게, 무한루프 방지 (PRIOR 바로 직전 행을 가리킴)
AND ROWNUM < 1000
- 반복 확장 시 쿼리 폭주/오류 방지 (휴가기간이 비정상적으로 길 때 무한루프/폭주 방지용)
SYS.ODCIDATELIST
- 오라클이 내장 제공하는 DATE 배열 타입(Object Collection)
- 여러 날짜(배열)를 한 번에 다룰 때 표준적으로 사용함
- MULTISET 으로 집합을 만든 후 TABLE 에서 한 행씩 꺼낼 때 필요
COLUMN_VALUE
- TABLE() 함수로 펼쳐진 배열/집합의 각 값 : 오라클에서는 COLUMN_VALUE 라는 가상의 컬럼으로 각 배열의 값을 꺼냄

6️⃣ JDBC 타입 명시 (jdbcType)

[ MySQL ]

<select id="selectUser" parameterType="String" resultType="UserVO">
    SELECT * FROM USERS WHERE USER_ID = #{userId}
</select>

대부분의 상황에서 별도 타입 명시 없이도 잘 동작함
MyBatis 가 Java 타입 ↔ MySQL 타입 자동 매핑 (String, Integer, Date 등)
jdbcType 명시가 거의 필요하지 않음/NOT NULL 컬럼에 null 을 넣거나 일부 드라이버 호환 시 명시

[ Oracle ]

 <!-- 사용자 등록 -->
<insert id="insertUser" parameterType="UserVO">
    INSERT INTO USERS(USER_ID,
                      ... 
                      USER_RESPONSIBILITY,
                      USER_POSITION,
                      USER_POSITION_RANK,
                      USER_HIRE_DATE,
                      USER_RESIGN_DATE,
                      USER_PROMOTION_DATE
                      ... )
    VALUES (
            ...
            #{userResponsibility, jdbcType=VARCHAR},
            #{userPosition},
            #{userPositionRank},
            #{userHireDate, jdbcType=DATE},
            #{userResignDate, jdbcType=DATE},
            #{userPromotionDate, jdbcType=DATE},
            ...)
</insert>

타입 매핑이 엄격함
- 날짜, 숫자, CLOB, VARCHAR2 등 명확하게 맞추면 오류가 나지 않음
- NULLABLE 한 필드에 NULL 값을 넣을 때, DATE/CLOB 등 타입에 민감하게 반응
- 위와같이 NULL 값을 넣을 때 jdbcType 을 명시하지 않으면 부적합한 열 유형 오류가 발생함

'Project > Database' 카테고리의 다른 글

[Groupware] Database : Oacle DB 세팅 (1)	2025.06.19

📝 개요

🚀 Oracle vs MySQL

1️⃣ 시퀀스(Sequence) vs AUTO_INCREMENT

2️⃣ 페이징 쿼리 (LIMIT vs OFFSET/FETCH)

3️⃣ 문자열 연결 (CONCAT vs ||)

4️⃣ NVL vs NULL

5️⃣ 날짜 / 계층 / 집합 연산 (CONNNECT BY, LEVEL, MULTISET)

6️⃣ JDBC 타입 명시 (jdbcType)

'Project > Database' 카테고리의 다른 글

티스토리툴바