[데이터베이스 시스템 7판 5장] JDBC, 트리거, 재귀 질의, 윈도우 함수까지 | 데이터베이스 고급 SQL 한 번에 정리

1. 왜 범용 프로그래밍 언어로 DB에 접근할까

SQL은 데이터베이스를 다루는 데 아주 강력한 언어지만,
현실에서는 SQL만으로 모든 작업을 처리할 수는 없다.

왜냐면 실제 서비스에서는 이런 작업도 필요하기 때문이다.

사용자 입력 받기
버튼 클릭 처리
보고서 출력
GUI 화면에 결과 보여주기
조건에 따라 SQL을 동적으로 바꾸기
외부 시스템과 통신하기

즉, SQL은 데이터를 다루는 언어이고
Java, Python, C 같은 범용 프로그래밍 언어는 프로그램 전체 흐름을 제어하는 언어다.

그래서 실제 개발에서는 보통

프로그램 언어가 전체 로직을 담당하고
SQL이 데이터 조회/수정 담당

이렇게 역할을 나눠서 쓴다.

2. SQL 접근 방식: 동적 SQL vs 내장 SQL

DB에 접근하는 방식은 크게 두 가지로 볼 수 있다.

1) 동적 SQL

프로그램 실행 중에 SQL문을 만들어서 DB에 보내는 방식이다.

예:

JDBC
ODBC

즉, 실행 시점에 SQL이 결정된다.

예를 들어 검색 조건이 사용자 입력에 따라 달라진다면 동적 SQL이 필요하다.

2) 내장 SQL (Embedded SQL)

프로그램 코드 안에 SQL을 직접 포함하는 방식이다.

예:

EXEC SQL SELECT ...;

이 방식은 전처리기가 SQL을 일반 함수 호출 코드로 변환한 뒤 컴파일한다.

즉,

SQL이 코드 안에 고정적으로 들어가고
컴파일 전에 변환 과정을 거친다

동적 SQL vs 내장 SQL 차이

구분 동적 SQL 내장 SQL

SQL 생성 시점	실행 시	컴파일 전/전처리 시
유연성	높음	낮음
대표 예시	JDBC, ODBC	Embedded SQL
장점	상황에 따라 쿼리 변경 가능	고정 쿼리 처리에 유리

요즘 실무에서는 보통 JDBC 같은 동적 SQL 방식이 훨씬 더 많이 쓰인다.

3. JDBC란?

JDBC(Java Database Connectivity) 는
Java에서 데이터베이스에 접속하고 SQL을 실행할 수 있게 해주는 API다.

쉽게 말하면
Java ↔ DB를 연결하는 표준 통로라고 생각하면 된다.

JDBC 기본 절차

JDBC 코드는 보통 이 흐름으로 간다.

DB 연결
SQL 실행 객체 생성
SQL 실행
결과 처리
자원 정리

예시:

Connection conn = DriverManager.getConnection(url, user, pass);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(
"SELECT dept_name, avg(salary) FROM instructor GROUP BY dept_name"
);

while (rs.next()) {
System.out.println(rs.getString("dept_name") + " " + rs.getFloat(2));
}

각 객체 역할 정리

Connection
DB와 연결된 통로
Statement
SQL문 실행용 객체
ResultSet
조회 결과를 담는 객체

연결> 실행> 결과 읽는 구조

NULL 처리 주의

JDBC에서 숫자 컬럼을 읽을 때는 NULL 처리가 헷갈릴 수 있다.

예:

int a = rs.getInt("a");
if (rs.wasNull()) {
// NULL 처리
}

왜냐면 getInt()는 NULL이어도 자바 기본형 int를 반환해야 해서
0처럼 보일 수 있기 때문이다.
그래서 wasNull()로 꼭 확인해야 한다.

4. PreparedStatement가 중요한 이유

SQL을 문자열 이어붙이기로 만들면 편해 보여도 위험하다.

예:

"SELECT * FROM instructor WHERE name='" + name + "'"

이런 방식은 사용자가 이상한 입력을 넣으면 SQL 자체가 변형될 수 있다.

SQL Injection이란?

예를 들어 사용자가 이름에 이렇게 입력했다고 해보자.

X' OR 'Y'='Y

그러면 원래 의도했던 쿼리가 아니라
항상 참이 되는 SQL이 만들어질 수 있다.

결과적으로:

로그인 우회
전체 데이터 노출
권한 없는 조회

같은 문제가 생길 수 있다.

이게 바로 SQL Injection Attack 이다.

해결책: PreparedStatement

PreparedStatement는 SQL 구조를 먼저 고정하고,
값만 나중에 바인딩하는 방식이다.

예:

PreparedStatement ps =
conn.prepareStatement("INSERT INTO instructor VALUES (?,?,?,?)");

ps.setString(1, "88877");
ps.setString(2, "Perry");
ps.setString(3, "Finance");
ps.setInt(4, 125000);
ps.executeUpdate();

장점

SQL Injection 방지
같은 형태의 SQL 반복 실행 시 성능상 이점
코드 가독성 향상

실무에서는 사용자 입력이 들어가는 SQL은 거의 무조건
PreparedStatement를 쓴다고 생각하면 된다.

보안은 PreparedStatement만으로 끝일까?

아니다. 이것도 같이 중요하다.

최소 권한 원칙
입력값 검증
화이트리스트 기반 필터링
DB 계정 권한 분리

즉, PreparedStatement는 핵심 방어 수단이지만
보안 전체의 전부는 아니다.

5. 메타데이터(Metadata)란?

메타데이터는 말 그대로 데이터에 대한 데이터다.

즉,

테이블 이름
컬럼 이름
타입
기본키 정보
스키마 구조

같은 정보를 의미한다.

이건 단순히 “보기용”이 아니라 실제로 꽤 유용하다.

활용 예

동적 쿼리 생성
자동 문서화
마이그레이션 스크립트 작성
DB 구조 점검
컬럼 기반 UI 자동 생성

ResultSetMetaData

조회 결과의 컬럼 정보를 확인할 수 있다.

예:

ResultSetMetaData rsmd = rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
 System.out.println(rsmd.getColumnName(i));
 System.out.println(rsmd.getColumnTypeName(i));
}

이건 “지금 조회된 결과가 어떤 컬럼들로 구성돼 있는지”를 보는 데 쓴다.

DatabaseMetaData

DB 전체 구조에 대한 정보를 가져온다.

예:

DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getTables("", "", "%", new String[]{"TABLE"});
while (rs.next()) {
 System.out.println(rs.getString("TABLE_NAME"));
}

즉, 스키마 수준의 정보 확인에 더 가깝다.

6. JDBC에서 트랜잭션 제어하기

PostgreSQL은 기본적으로 auto commit 상태다.
즉, SQL 하나 실행할 때마다 바로 반영된다.

그런데 여러 작업을 하나의 묶음으로 처리해야 할 때는
직접 트랜잭션을 제어해야 한다.

예:

conn.setAutoCommit(false);

// SQL 작업들 수행

conn.commit();
conn.rollback();

왜 필요할까?

예를 들어 계좌 이체는 보통 2단계다.

A 계좌에서 돈 차감
B 계좌에 돈 증가

이때 중간에 오류가 나면 둘 중 하나만 반영되면 안 된다.
그래서 둘 다 성공하거나, 둘 다 취소되어야 한다.

그걸 보장하는 게 트랜잭션이다.

7. 함수와 프로시저

데이터베이스에도 반복해서 쓰는 로직을 저장해둘 수 있다.
이게 함수(Function) 와 프로시저(Procedure) 다.

즉, 비즈니스 로직을 애플리케이션 코드에만 두지 않고
DB 내부에도 넣을 수 있다.

왜 DB 안에 로직을 넣을까?

여러 프로그램이 같은 로직 공유 가능
중복 코드 감소
로직 변경 시 애플리케이션 전체 수정 필요 감소
데이터 가까이에서 실행돼 효율적일 수 있음

함수 vs 프로시저 차이

구분 함수(Function) 프로시저(Procedure)

반환값	있음	보통 직접 반환 안 함
사용 위치	SQL 식 안에서 사용 가능	독립 실행
용도	계산 결과 반환	여러 단계 작업 수행
트랜잭션 제어	제한적	DBMS에 따라 가능

PostgreSQL 함수 예시

create function function_name(param_list)
returns return_type
language plpgsql
as $$
declare
begin
-- 로직
end;
$$;

테이블 함수

테이블 자체를 반환하는 함수도 있다.

create function function_name(param_list)
returns table (column_list)
language plpgsql
as $$
begin
end;
$$;

이건 결과를 하나의 릴레이션처럼 반환한다.

PostgreSQL 프로시저 예시

create procedure procedure_name(parameter_list)
language plpgsql
as $$
declare
begin
end;
$$;

PostgreSQL 11+부터 프로시저를 지원하고,
프로시저는 함수보다 트랜잭션 제어에 더 적합하다.

외부 언어 루틴

plpgsql 말고도

C
Java
C++
같은 외부 언어로 함수/프로시저를 만들 수 있다.

이건 강력하지만 보안상 위험할 수 있다.

특히 C는 메모리 접근이 자유로워서 더 조심해야 한다.

보안 대책

샌드박스 실행
메모리 안전 언어 사용
별도 프로세스에서 실행

대신 이런 방식은 성능 오버헤드가 생길 수 있다.

8. 트리거(Trigger)란?

트리거는 DB에서 특정 사건이 발생했을 때 자동으로 실행되는 작업이다.

즉,

INSERT
UPDATE
DELETE

같은 이벤트가 일어나면 자동으로 실행된다.

트리거는 보통 두 가지를 정의한다.

언제 실행할지
- BEFORE
- AFTER
무슨 동작을 할지
- 로그 저장
- 값 자동 수정
- 제약조건 보완
- 연쇄 갱신

트리거가 유용한 이유

트리거는 단순 자동화가 아니라
데이터 무결성 보조 장치 역할도 한다.

예를 들어 잘못된 입력을 자동으로 바꾸거나,
변경 이력을 남기거나,
관련 데이터 갱신을 자동화할 수 있다.

예시 1: 빈 문자열을 NULL로 바꾸는 트리거

create trigger setnull_trigger before update of takes
referencing new row as nrow
for each row
when (nrow.grade = '')
begin atomic
set nrow.grade = null;
end;

이건 성적이 빈 문자열로 갱신되면 자동으로 NULL로 바꾼다.

예시 2: 감사 로그 남기기

실습 예시처럼 last_name 변경 전 값을 로그 테이블에 저장하는 것도 대표적인 트리거 활용이다.

트리거 함수:

CREATE OR REPLACE FUNCTION log_last_name_changes()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$$
BEGIN
IF NEW.last_name <> OLD.last_name THEN
INSERT INTO employee_audits(employee_id, last_name, changed_on)
VALUES(OLD.id, OLD.last_name, now());
END IF;

RETURN NEW;
END;
$$;

이렇게 하면 UPDATE가 일어날 때마다 이전 성이 자동 저장된다.

OLD / NEW

트리거에서 자주 나오는 핵심 개념이다.

OLD: 변경 전 행
NEW: 변경 후 행

즉, UPDATE 트리거에서는 둘 다 중요하다.

트리거 사용 시 주의

트리거는 편리하지만 과하게 쓰면

로직 흐름이 숨겨져서 디버깅 어려움
성능 저하
예상 못한 부작용

이 생길 수 있다. 그래서 보통

감사 로그
무결성 보조
자동 파생값 갱신

같은 곳에 신중하게 쓴다.

9. 재귀 질의(Recursive Query)란?

재귀 질의는 계층 구조 데이터를 처리할 때 사용한다.

예:

조직도
댓글 트리
부품 조립 구조
선수과목 관계
상사-부하 관계

이런 구조는 “부모를 찾고, 그 부모의 부모를 또 찾고...” 같은 방식이 필요하다.
이때 재귀 질의를 쓴다.

기본 문법

WITH RECURSIVE cte_name AS (
 <base query>
 UNION ALL
 <recursive query>
)
SELECT * FROM cte_name;

구조는 두 부분으로 나뉜다.

base query

처음 시작점

recursive query

이전 결과를 이용해서 다음 단계 확장

더 이상 새 결과가 없으면 종료된다.

숫자 세기 예시

WITH RECURSIVE cnt(n) AS (
 SELECT 1
 UNION ALL
 SELECT n + 1
 FROM cnt
 WHERE n < 3
)
SELECT n FROM cnt;

결과는 1, 2, 3 이 나온다.

이건 재귀 질의 구조를 가장 단순하게 보여주는 예시다.

조직도 / 부하 직원 찾기 예시

WITH RECURSIVE subordinates AS (
SELECT employee_id, manager_id, full_name
FROM employees
WHERE employee_id = 2

UNION

SELECT e.employee_id, e.manager_id, e.full_name
FROM employees e
INNER JOIN subordinates s ON s.employee_id = e.manager_id
)
SELECT * FROM subordinates;

이 쿼리는 특정 직원 아래의 모든 하위 직원들을 단계적으로 찾는다.

즉,

base: 시작 직원
recursive: 그 직원의 부하
그다음은 부하의 부하
더 없으면 종료

재귀 질의에서 주의할 점

사이클이 있으면 무한 반복 문제가 생길 수 있다.
그래서 실제 실무에서는

중복 제거
방문 여부 추적
깊이 제한

같은 장치를 넣기도 한다.

10. 순위 함수와 윈도우 함수

이번 파트는 SQL에서 꽤 재밌는 부분이다.
왜냐면 집계처럼 계산하면서도 행을 유지할 수 있기 때문이다.

먼저, 기존 방식의 한계

예전 방식으로 순위를 구하려면 보통 서브쿼리를 써야 했다.

select ID, (1 + (select count(*)
 from student_grades B
 where B.GPA > A.GPA)) as s_rank
from student_grades A
order by s_rank;

이 방식도 맞긴 한데, 학생마다 서브쿼리가 한 번씩 실행되니까 비효율적이다.

rank() 윈도우 함수

select ID, rank() over (order by GPA desc) as s_rank
from student_grades
order by s_rank;

이제 DB가 자동으로 순위를 계산해준다.

OVER가 의미하는 것

윈도우 함수는 항상 OVER(...)와 함께 쓴다.

예:

rank() over (order by GPA desc)

여기서 OVER는 “어떤 기준으로 계산할지”를 정하는 창(window) 역할을 한다.

PARTITION BY

이건 그룹을 나눠서 각 그룹 안에서 따로 계산하게 한다.

select ID, dept_name,
 rank() over (partition by dept_name order by GPA desc) as dept_rank
from dept_grades
order by dept_name, dept_rank;

이 쿼리는 학과별 순위를 따로 구한다.

즉,

partition 안 하면 전체 대상 순위
partition 하면 그룹별 순위

주요 순위 함수

함수 의미

rank()	공동순위 허용, 다음 순위 건너뜀
row_number()	무조건 1,2,3...
percent_rank()	백분위 순위
cume_dist()	누적 분포
ntile(n)	n개 구간으로 나눔

rank() vs row_number()

이건 자주 비교된다.

예를 들어 점수가

이면

rank() 결과:

row_number() 결과:

즉,

공동순위 반영 → rank()
그냥 번호 매기기 → row_number()

ntile(n)

전체를 n개 구간으로 나눈다.

select ID, ntile(4) over (order by GPA desc) as quartile
from student_grades;

이건 GPA 기준으로 학생들을 4등분한다.

즉, 사분위처럼 나눌 때 유용하다.

11. 윈도우 함수의 진짜 핵심: 행을 안 합친다

이 부분이 중요하다.

GROUP BY는 여러 행을 하나로 합쳐버린다.
반면 윈도우 함수는 원래 행을 유지한 채 계산 결과를 덧붙인다.

예를 들어 이동합:

select date, sum(value) over
 (order by date rows between 1 preceding and 1 following)
from sales;

이건 현재 행 기준으로

이전 1행
현재 행
다음 1행

까지 포함해서 합계를 계산한다.

즉, 각 행마다 주변 데이터를 보고 계산하는 느낌이다.

ROWS vs RANGE

ROWS

행 개수를 기준으로 범위를 잡는다.

예:

rows unbounded preceding

처음 행부터 현재 행까지

RANGE

값 범위를 기준으로 잡는다.

예:

range interval '7 days' preceding

현재 날짜 기준 7일 전부터 현재까지

즉,

ROWS = 물리적 행 기준
RANGE = 값의 범위 기준

누적 잔액 예시

select account_number, date_time,
sum(value) over
(partition by account_number
order by date_time
rows unbounded preceding) as balance
from transaction
order by account_number, date_time;

이건 계좌별로 거래 내역을 시간순으로 보면서
처음 거래부터 현재까지의 누적 잔액을 계산한다.

실무적으로도 꽤 직관적인 예시다.

12. ODBC는 뭐고 JDBC랑 뭐가 다를까

ODBC는 Open Database Connectivity 의 약자다.
DB 접근용 API라는 점에서는 JDBC와 비슷하지만, 더 범용적이고 오래된 표준이다.

구분 ODBC JDBC

언어	주로 C/C++ 등	Java 전용
플랫폼	전통적으로 Windows 중심	Java 기반이라 OS 독립적
스타일	절차적	객체지향

지금은 Java에서는 거의 JDBC를 쓰는 편이다.

13. 핵심 요약

이번 고급 파트 핵심만 뽑으면 이렇다.

SQL만으로는 GUI 처리나 프로그램 제어까지 다 하긴 어렵다.
그래서 Java 같은 범용 언어와 DB를 연결해서 사용한다.
JDBC는 Java에서 DB에 접근하는 표준 API다.
PreparedStatement는 SQL Injection 방지와 성능 면에서 중요하다.
메타데이터를 이용하면 테이블 구조나 결과 컬럼 정보를 동적으로 다룰 수 있다.
트랜잭션 제어는 auto commit과 commit/rollback 흐름으로 이해하면 된다.
함수와 프로시저는 DB 내부에 비즈니스 로직을 저장하는 도구다.
트리거는 특정 이벤트 발생 시 자동 실행되는 로직이다.
재귀 질의는 계층 구조 데이터를 처리할 때 매우 유용하다.
윈도우 함수는 행을 유지하면서 계산한다는 점에서 GROUP BY와 다르다.
rank(), row_number(), ntile() 같은 함수는 순위와 분석에 자주 쓰인다.

마무리

5장은 DB를 실제 시스템 안에서 어떻게 활용하는지를 보여주는 장이라고 보면 된다.

개인적으로 시험에서 중요한 흐름은 이거다.

앱에서 DB 접근 → JDBC
안전하게 실행 → PreparedStatement
DB 내부 로직 → 함수 / 프로시저 / 트리거
복잡한 구조 처리 → 재귀 질의
분석형 계산 → 윈도우 함수

저작자표시 비영리 변경금지 (새창열림)

'CS STUDY > DB' 카테고리의 다른 글

[데이터베이스 시스템 7판 7장] 관계형 데이터베이스 정규화 정리 \| 함수 종속, BCNF, 3NF, 4NF까지 한 번에 이해하기 (0)	2026.03.12
[데이터베이스 시스템 7판 6장] E-R 모델 정리 \| ERD 기본 개념부터 관계형 스키마 변환까지 한 번에 이해하기 (0)	2026.03.12
[데이터베이스 시스템 7판 4장] SQL 조인, 뷰, 무결성 제약조건, 인덱스, 권한 (0)	2026.03.12
[데이터베이스 시스템 7판 3장] SQL 타입, 제약조건, 하위 질의(Subquery) 완벽 정리 \| IN, EXISTS, WITH까지 이해하기 (0)	2026.03.12
[데이터베이스 시스템 7판 2장] 관계형 데이터베이스 구조 정리 \| 릴레이션, 키, 관계대수, 무결성까지 한 번에 이해하기 (0)	2026.03.12

1. 왜 범용 프로그래밍 언어로 DB에 접근할까

2. SQL 접근 방식: 동적 SQL vs 내장 SQL

1) 동적 SQL

2) 내장 SQL (Embedded SQL)

동적 SQL vs 내장 SQL 차이

3. JDBC란?

JDBC 기본 절차

각 객체 역할 정리

NULL 처리 주의

4. PreparedStatement가 중요한 이유

SQL Injection이란?

해결책: PreparedStatement

장점

보안은 PreparedStatement만으로 끝일까?

5. 메타데이터(Metadata)란?

활용 예

ResultSetMetaData

DatabaseMetaData

6. JDBC에서 트랜잭션 제어하기

왜 필요할까?

7. 함수와 프로시저

왜 DB 안에 로직을 넣을까?

함수 vs 프로시저 차이

PostgreSQL 함수 예시

테이블 함수

PostgreSQL 프로시저 예시

외부 언어 루틴

보안 대책

8. 트리거(Trigger)란?

트리거가 유용한 이유

예시 1: 빈 문자열을 NULL로 바꾸는 트리거

예시 2: 감사 로그 남기기

OLD / NEW

트리거 사용 시 주의

9. 재귀 질의(Recursive Query)란?

기본 문법

base query

recursive query

숫자 세기 예시

조직도 / 부하 직원 찾기 예시

재귀 질의에서 주의할 점

10. 순위 함수와 윈도우 함수

먼저, 기존 방식의 한계

rank() 윈도우 함수

OVER가 의미하는 것

PARTITION BY

주요 순위 함수

rank() vs row_number()

ntile(n)

11. 윈도우 함수의 진짜 핵심: 행을 안 합친다

ROWS vs RANGE

ROWS

RANGE

누적 잔액 예시

12. ODBC는 뭐고 JDBC랑 뭐가 다를까

13. 핵심 요약

마무리

'CS STUDY > DB' 카테고리의 다른 글

티스토리툴바