『DataBase』 Oracle SQL & MyBatis Mapper 부먹

복잡한 결재 시스템을 구축한다고 가정하고, Oracle SQL과 MyBatis Mapper를 조합해서 다양한 쿼리들을 구현해 보자.

간단한 예제 쿼리로는 복잡한 비즈니스 로직이 녹아있는 실무의 쿼리를 이해할 수 없기에 조금 복잡한 내용들을 다루어 보려고 한다.

 

우선 쿼리를 작성하려면 테이블이 존재해야 되기 때문에 아래와 같이 4개의 예제 테이블을 정의하겠다.

 

하나의 사용자는 여러 결재 요청을 생성할 수 있어 둘 사이에는 1대 N(One-to-Many) 관계가 성립한다.

 

동시에 결재 단계 테이블을 매개로 하나의 결재 요청은 여러 사용자에게 결재를 받을 수 있고, 한 사용자도 여러 결재 요청의 승인자가 될 수 있어 결재 단계와 사용자 사이에는 N대 N(Many-to-Many) 관계가 성립한다.

 

또한 사용자는 한 부서에 소속되므로 N대 1(Many-to-One) 관계이며, 부서는 자기 자신을 참조해 상위-하위 부서로 이루어지는 계층 구조를 가진다.

 

 

복잡한 예제

1. CASE, DECODE + 동적 조건 (if)

• 결재 요청 상태를 CASE/DECODE로 변환
• startDate, endDate, statusList(여러 상태 코드) 파라미터에 따라 동적으로 WHERE 절 조합
• statusList는 <foreach>를 활용

 

Oracle SQL 문법/함수

CASE	CASE ... WHEN ... THEN ... END 구문을 사용하여 특정 조건을 만족할 때 문자열이나 값을 반환한다. 여기서는 approval_status가 'P'일 땐 'Pending', 'C'일 땐 'Completed'로 변환한다. CASE문은 가독성이 좋아서 여러 분기 처리를 직관적으로 표현할 때 유용하다.
DECODE	DECODE는 Oracle에서만 지원하는 함수로, CASE와 유사하지만 함축적으로 작성할 수 있다. DECODE(a.approval_status, 'P', '대기중', 'C', '완료', 'R', '반려', '기타') 처럼 상태 코드에 맞춰 결과를 변환할 수 있다. CASE에 비해 코드가 짧지만, 복잡한 로직은 CASE가 더 읽기 편할 수 있다.
BETWEEN ... AND + TO_DATE	a.created_at BETWEEN TO_DATE(#{startDate}, 'YYYY-MM-DD') AND TO_DATE(#{endDate}, 'YYYY-MM-DD') 특정 날짜 범위(예: 2024-01-01 ~ 2024-01-31) 안에 있는지 확인한다. TO_DATE 함수를 사용해서 문자열을 날짜 형식으로 변환한다.
WHERE 1=1	흔히 동적 쿼리에서 사용되는 기법으로, 조건을 유연하게 추가하거나 제거할 때 쓰인다. 뒤이어 <if> 문법으로 조건을 더할 때, AND 키워드 앞에 불필요한 구문오류가 생기지 않도록 하는 역할이다.

 

MyBatis 문법

<if test="startDate != null and endDate != null">	파라미터에 startDate, endDate가 모두 있을 때만 해당 조건을 SQL에 추가한다. 없으면 그 부분이 아예 제거되어 전체 기간 조회가 가능하다.
<if test="statusList != null and statusList.size() > 0"> + <foreach>	상태 리스트(예: ['P','C'])가 존재하고 1개 이상이면 IN (...) 절을 동적으로 생성한다. <foreach>로 컬렉션을 순회해 쉼표로 구분된 상태 코드 목록을 만든다.
parameterType="map", resultType="Approval"	파라미터는 Map 형태(키-값)로 전달되며, 쿼리 결과는 Java의 Approval 객체 리스트로 매핑된다는 뜻이다.

 

 

2. 계층구조(Connect By), LAG/LEAD, 동적 부서 필터

• 부서 계층 구조를 조회
• departmentId 파라미터가 있으면 해당 부서 이하 계층만 조회
• LAG, LEAD 윈도우 함수로 이전/다음 부서명 참조

 

Oracle SQL 문법/함수

WITH ... AS (CTE: Common Table Expression)	WITH DeptHierarchy AS (...) 구문으로 서브쿼리를 미리 정의하고, 아래에서 SELECT * FROM DeptHierarchy로 결과를 재활용한다. 복잡한 쿼리를 단계적으로 작성할 수 있어 가독성이 좋아진다.
LEVEL	계층형 쿼리(Connect By)에서 트리 깊이를 나타내는 의사 컬럼이다. 루트 노드는 LEVEL=1, 그 자식은 LEVEL=2 식으로 표시된다.
CONNECT BY / START WITH	테이블에 자기참조(부서 → 상위 부서) 구조가 있을 때 계층형 쿼리를 작성하는 문법이다. START WITH d.parent_department_id IS NULL는 최상위 부서를 찾고, CONNECT BY PRIOR d.department_id = d.parent_department_id는 상위 부서(부모)와 하위 부서(자식)를 연결한다.
윈도우 함수(LAG, LEAD)	LAG(col) OVER (ORDER BY ...)는 이전 행의 값을 가져오고, LEAD(col) OVER (ORDER BY ...)는 다음 행의 값을 가져온다. 여기서는 이전/다음 부서 이름을 미리 확인할 수 있게 한다.

 

MyBatis 문법

<if test="departmentId != null">	파라미터에 departmentId가 주어지면 AND department_id = #{departmentId} 조건이 추가되어, 해당 부서만 필터링한다. 부서 ID가 없으면 전체 계층 구조를 조회한다.
resultType="DepartmentHierarchy"	결과를 매핑받을 DTO/VO 클래스(예: DepartmentHierarchy)가 있다고 가정한다.

 

 

3. 집계/분석: PIVOT, RANK + 동적 기간/부서 조건

• 특정 기간 내 결재 건수를 부서별/월별 집계하고 PIVOT
• RANK로 상위 부서 뽑기
• minCount 파라미터로 특정 이상 건수인 부서만 조회
• departmentIdList 파라미터가 있을 경우 해당 부서만 필터링

 

Oracle SQL 문법/함수

EXTRACT(MONTH FROM a.created_at)	날짜 컬럼에서 ‘월’만 추출한다. EXTRACT 함수는 YEAR, MONTH, DAY 등 특정 부분을 뽑을 수 있어 날짜 집계 시 유용하다.
GROUP BY	GROUP BY u.department_id, EXTRACT(MONTH FROM a.created_at)로 부서와 월별로 결재 건수를 합산. COUNT(*) AS approval_count처럼 건수를 센다.
PIVOT	행 형태로 누적된 데이터를 열(Column) 형태로 바꾸는 기능이다. FOR month_num IN (1 AS "M1", 2 AS "M2", 3 AS "M3") 부분이 핵심. 1월, 2월, 3월에 해당하는 값들을 각각 "M1", "M2", "M3"라는 열로 생성한다.
RANK() OVER (ORDER BY ...)	윈도우 함수로, 정렬 기준(여기서는 M1+M2+M3 합계)에 따라 순위를 매긴다. 합계가 높은 부서가 rank 1이 된다.
NVL	NVL(expr, default_value) : expr가 NULL이면 default_value를 사용한다. 여기서는 "M1", "M2", "M3"가 NULL이면 0으로 바꿔 합산한다.

 

MyBatis 문법

<if test="startDate != null and endDate != null">	기간이 주어지면 BETWEEN 조건을 추가해 그 기간만 조회한다.
<if test="departmentIdList != null and departmentIdList.size() > 0">	여러 부서 ID가 전달되면, IN ( ... ) 구문을 생성한다. 비어있으면 조건이 빠져서 전체 부서를 대상으로 한다.
<if test="minCount != null">	최종 쿼리에서 M1+M2+M3 >= minCount 필터를 걸어, 일정 이상 건수가 있는 부서만 보여준다.

 

 

4. WITH 절(CTE), 서브쿼리 팩터링 + 동적 조건

• 완료된 결재들만 추출하는 CTE
• 사용자 정보와 JOIN 후 부서별 완료 건수 집계
• completeOnly 파라미터가 true일 경우 완료 결재만 필터링
• excludeDeptId 파라미터로 특정 부서 제외

 

Oracle SQL 문법/함수

WITH (CTE) 여러 단계	FilteredApprovals : 조건에 따라 승인완료(C)된 결재만 필터링. JoinedData : 위에서 필터된 결재와 users 테이블 JOIN. CTE를 연속적으로 선언하면, 여러 중간 데이터를 단계적으로 생성할 수 있다.
JOIN	FROM FilteredApprovals f JOIN users u ON f.requester_id = u.user_id 결재 요청 정보와 사용자 정보를 합쳐서 한 번에 가져온다.
COUNT(*) + GROUP BY	최종적으로 부서(department_id)별 결재 건수를 센다. GROUP BY j.department_id로 부서별 합계를 낸다.

 

MyBatis 문법

<if test="completeOnly == true">	completeOnly 파라미터가 참일 때만 approval_status='C' 조건이 추가되어, 완료된 결재만 걸러낸다.
<if test="excludeDeptId != null">	특정 부서를 제외하고 싶으면 j.department_id != #{excludeDeptId} 조건을 넣는다. 파라미터가 null이면 조건이 없다.

 

 

5. 데이터 조합(UNION, INTERSECT, MINUS) + 동적 파라미터

• 이번 달/지난달 승인 건을 UNION ALL로 결합
• comparePrevious 파라미터가 true일 경우 지난달 데이터 포함, 아니면 이번 달만
• approverId 파라미터로 특정 결재자를 포함하는 결재만 필터링(동적으로 if문 적용)

 

Oracle SQL 문법/함수

TRUNC(SYSDATE, 'MONTH')	현재 날짜(SYSDATE)의 월 부분만 남기고 일/시/분/초를 1일 00:00:00로 만든다. 예: 2024-03-15 14:20:00 → 2024-03-01 00:00:00. 이번 달의 시작일을 구할 때 유용하다.
ADD_MONTHS(date, -1)	인자로 받은 날짜에서 한 달을 뺀다. ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), -1)는 지난 달 시작일이 된다.
UNION ALL	두 SELECT 결과를 합친다(중복된 행도 그대로 포함). 여기선 이번 달 건 + 지난 달 건을 합쳐서 비교하는 로직.
IN (SELECT ...)	서브쿼리를 통해 특정 조건을 만족하는 approval_id 목록만 필터링. 여기서는 approval_steps 테이블에서 approver_id가 일치하는 행만 추린다.

 

MyBatis 문법

<if test="comparePrevious == true"> ... <if test="comparePrevious == false"> ...	한 쿼리 안에서 두 가지 시나리오를 분기한다. comparePrevious가 true면 이번 달 + 지난 달, false면 이번 달만 검색.
<if test="approverId != null">	결재자 ID(approverId)가 주어지면 서브쿼리로 필터를 건다. 파라미터가 null이면 추가 조건이 없다(모든 승인자 포함).

 

 

6. 문자열/날짜 처리: NVL/NVL2, REGEXP, EXTRACT + 동적 키워드 검색

• 결재 제목에서 REGEXP_LIKE로 키워드 필터(keyword 파라미터)
• NVL/NVL2로 NULL 처리
• yearFilter 파라미터가 있으면 특정 연도만 필터
• useNvl2 파라미터에 따라 NVL2 사용 여부 동적 적용

 

Oracle SQL 문법/함수

NVL2(expr, value_if_not_null, value_if_null)	expr가 NOT NULL이면 value_if_not_null, NULL이면 value_if_null 반환. NVL2(a.approval_title, 'Title Exists', 'No Title') → approval_title이 있으면 "Title Exists", 없으면 "No Title".
DECODE	DECODE(a.approval_title, NULL, 'No Title', 'Title Exists') approval_title이 NULL이면 'No Title', 그렇지 않으면 'Title Exists'를 반환.
REGEXP_LIKE	정규 표현식을 사용해 문자열 일치를 검사. 'i'는 대소문자를 구분하지 않는다는 뜻. REGEXP_LIKE(a.approval_title, #{keyword}, 'i') → approval_title에 keyword가 있으면 true.
EXTRACT(YEAR FROM a.created_at)	날짜에서 연도만 추출. EXTRACT(MONTH, EXTRACT(DAY 등으로 달, 일을 구할 수도 있다.
NVL((SELECT SUM(amount) ...), 0)	서브쿼리에서 금액이 NULL이면 0으로 대체. 결재 건에 budget 정보가 없으면 0을 반환한다.

 

MyBatis 문법

<if test="useNvl2 == true"> ... <if test="useNvl2 == false">	useNvl2 값에 따라 NVL2 또는 DECODE 중 하나를 사용한다. 동적으로 SQL 구문을 바꿀 수 있어 유연한 로직 구현 가능.
<if test="keyword != null and keyword != ''">	검색어(keyword)가 없으면 조건 미적용, 있으면 REGEXP_LIKE 추가.
<if test="yearFilter != null">	특정 연도로 필터링하고 싶으면 EXTRACT(YEAR FROM a.created_at) = yearFilter 조건을 넣는다. null이면 전체 연도 조회.