Ch5_Advanced_SQL

Chapter 5. Advanced SQL

한 줄 핵심: SQL만으로는 모든 계산·비선언적 동작(UI·리포팅)을 할 수 없으므로 범용 언어에서 SQL을 호출하는 표준(JDBC/ODBC/Python DB-API)이 필요하고, DB 수정에 자동 반응하는 Trigger로 무결성·파생값을 유지한다.

이 챕터가 답하는 핵심 질문

• Q1. 왜 프로그래밍 언어에서 SQL을 호출해야 하나? 방식엔 뭐가 있나?
• Q2. JDBC로 DB와 어떻게 통신하나? (4단계 모델)
• Q3. PreparedStatement는 왜 쓰고, SQL Injection을 어떻게 막나?
• Q4. 쿼리 결과의 메타데이터는 어떻게 조회하나?
• Q5. ODBC(C 언어)의 통신 흐름은?
• Q6. Trigger란 무엇이고 어떻게 정의하나?
• Q7. Trigger의 실전 용도는? (파생값 유지·참조 무결성)
• Q8. (Outline 보충) Functions/Procedures와 Recursive Query는?

---

Q1. 왜 프로그래밍 언어에서 SQL을 호출해야 하며, 접근 방식엔 무엇이 있나?

A. SQL은 범용 언어가 아니라서 표현력이 부족하고, 비선언적 동작을 할 수 없기 때문이다.

데이터베이스 프로그래머가 범용 언어에서 SQL을 호출해야 하는 두 가지 이유:

• SQL의 표현력 한계: SQL은 범용 언어(Turing-complete)의 표현력이 없어 모든 쿼리를 SQL로 표현할 수는 없다.
• 비선언적 동작(Non-declarative actions): 리포트 출력, 사용자 상호작용, GUI로 결과 전달 같은 작업은 SQL 자체로 불가능하다.

접근 방식

방식	언어
JDBC	Java API
ODBC	C 언어용 표준 API
Python Database API	Python 표준(PEP 249)
Embedded SQL	컴파일러가 SQL을 전처리

강의 Outline에는 Functions and Procedures, Recursive Queries, Advanced Aggregation Features도 포함되지만, 본 슬라이드는 위 접근 방식과 Trigger에 집중한다.

---

Q2. JDBC로 데이터베이스와 어떻게 통신하나?

A. Connection → Statement → 쿼리 실행 → Exception 처리의 4단계 모델을 따른다.

JDBC는 SQL을 지원하는 DB와 통신하기 위한 Java API다. 데이터 조회·수정, 결과 조회, 그리고 메타데이터 조회(릴레이션·속성 이름·타입)를 지원한다.

통신 모델 4단계

1. Connection을 연다.
2. Statement 객체를 생성한다.
3. Statement로 쿼리를 전송하고 결과를 가져온다.
4. Exception 메커니즘으로 에러를 처리한다.

각 DBMS 벤더(Oracle, MySQL, SQLite, Access…)가 자신의 JDBC 드라이버를 제공하며, 애플리케이션은 동일한 JDBC API로 이기종 DB에 접근한다.

기본 코드 (try-with-resources, Java 7 / JDBC 4 이상 — Connection/Statement 자동 close)

public static void JDBCexample(String dbid, String userid, String passwd) {
    try (Connection conn = DriverManager.getConnection(
            "jdbc:oracle:thin:@db.yale.edu:2000:univdb", userid, passwd);
         Statement stmt = conn.createStatement();
    ) {
        … Do Actual Work ….
    }
    catch (SQLException sqle) {
        System.out.println("SQLException : " + sqle);
    }
}

데이터 수정과 결과 조회

// Update — INSERT/UPDATE/DELETE, 영향 행 수 반환
stmt.executeUpdate(
    "insert into instructor values('77987', 'Kim', 'Physics', 98000)");
 
// Query — SELECT 결과 ResultSet 반환
ResultSet rset = stmt.executeQuery(
    "select dept_name, avg(salary) from instructor group by dept_name");
while (rset.next()) {
    System.out.println(rset.getString("dept_name") + " " + rset.getFloat(2));
}

• ResultSet은 커서(cursor) 기반이라 next()로 행을 앞으로 이동한다.
• rset.getString("dept_name")와 rset.getString(1)은 동일(컬럼 이름 또는 1부터 시작하는 인덱스).
• Null 처리: 기본형(getInt 등)을 읽은 직후 wasNull()로 방금 읽은 값이 null이었는지 확인한다.

int a = rset.getInt("a");
if (rset.wasNull()) System.out.println("Got null value");

---

Q3. PreparedStatement는 왜 쓰며, SQL Injection을 어떻게 막나?

A. 미리 컴파일된 ? 파라미터 바인딩으로 사용자 입력이 쿼리 구조를 바꾸지 못하게 막기 때문이다.

PreparedStatement는 ? 플레이스홀더를 가진 SQL을 미리 컴파일해 두고 파라미터만 바인딩한다. 파라미터 번호는 1부터 시작한다.

PreparedStatement pStmt = conn.prepareStatement(
    "insert into instructor values(?,?,?,?)");
pStmt.setString(1, "88877");
pStmt.setString(2, "Perry");
pStmt.setString(3, "Finance");
pStmt.setInt(4, 125000);
pStmt.executeUpdate();

절대 규칙

사용자 입력을 쿼리에 넣을 때는 반드시 PreparedStatement를 쓰고, 문자열 연결로 쿼리를 만들지 말 것. 문자열 연결은 name = "O'Henry"처럼 따옴표가 든 값에서 깨지고, 무엇보다 SQL Injection에 취약하다.

SQL Injection — 사용자 입력을 문자열 연결할 때 악의적 입력이 쿼리 구조를 바꾸는 공격.

취약한 코드:

"select * from instructor where name = '" + name + "'"

사용자가 X' or 'Y' = 'Y 입력 →

select * from instructor where name = 'X' or 'Y' = 'Y'

조건이 항상 참이 되어 모든 튜플 노출. 더 심각하게 X'; update instructor set salary = salary + 10000; -- 은 추가 문장을 실행시킨다.

방어: PreparedStatement는 내부적으로 입력을 이스케이프(' → \')하여 쿼리 구조가 바뀌지 않게 한다.

---

Q4. 쿼리 결과나 DB의 메타데이터는 어떻게 조회하나?

A. 결과 컬럼 정보는 ResultSetMetaData, DB 스키마 정보는 DatabaseMetaData로 조회한다.

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();
// getColumns 인자: Catalog, Schema-pattern, Table-pattern, Column-pattern
// null → 모든 catalog/schema,  "" → 현재 catalog/schema,  "%" → SQL like
ResultSet rs = dbmd.getColumns(null, "univdb", "department", "%");
while (rs.next()) {
    System.out.println(rs.getString("COLUMN_NAME"),
                       rs.getString("TYPE_NAME"));
}

---

Q5. ODBC(C 언어)의 통신 흐름은 어떻게 되나?

A. 환경/연결 핸들 할당 → SQLConnect → SQLExecDirect → SQLBindCol → SQLFetch → 해제 순서다.

ODBC는 C 언어 애플리케이션이 DB 서버와 통신하는 표준이다. GUI, 스프레드시트 등도 ODBC로 DB에 접근한다. 각 DB는 클라이언트에 링크할 “driver” 라이브러리를 제공한다.

흐름

1. SQL 환경(HENV)과 연결 핸들(HDBC)을 할당한다.
2. SQLConnect(conn, server, SQL_NTS, user, SQL_NTS, passwd, SQL_NTS)로 연결. SQL_NTS = 앞 인자가 null-terminated string임을 표시.
3. SQLExecDirect로 쿼리 전송.
4. SQLBindCol로 C 변수를 결과 속성에 바인딩 — 이후 SQLFetch 시 자동으로 값이 채워진다.
5. SQLFetch()로 튜플을 가져온다.
6. 종료: SQLDisconnect → SQLFreeConnect → SQLFreeEnv.

int ODBCexample() {
    RETCODE error;
    HENV env;      /* environment */
    HDBC conn;     /* database connection */
    SQLAllocEnv(&env);
    SQLAllocConnect(env, &conn);
    SQLConnect(conn, "db.yale.edu", SQL_NTS, "avi", SQL_NTS,
               "avipasswd", SQL_NTS);
    { … Do actual work … }
    SQLDisconnect(conn);
    SQLFreeConnect(conn);
    SQLFreeEnv(env);
}

char deptname[80]; float salary; int lenOut1, lenOut2; HSTMT stmt;
char *sqlquery = "select dept_name, sum(salary) from instructor group by dept_name";
SQLAllocStmt(conn, &stmt);
error = SQLExecDirect(stmt, sqlquery, SQL_NTS);
if (error == SQL_SUCCESS) {
    SQLBindCol(stmt, 1, SQL_C_CHAR, deptname, 80, &lenOut1);
    SQLBindCol(stmt, 2, SQL_C_FLOAT, &salary, 0, &lenOut2);
    while (SQLFetch(stmt) == SQL_SUCCESS) {
        printf(" %s %g\n", deptname, salary);
    }
}
SQLFreeStmt(stmt, SQL_DROP);

• SQLBindCol 인자: stmt 변수, 결과 내 속성 위치, SQL→C 타입 변환, 변수 주소. 가변 길이 타입은 최대 길이와 실제 길이 저장 위치를 함께 지정.
• lenOut에 음수가 반환되면 null 값을 의미한다.
• 실제 코드는 모든 호출의 반환값을 검사해야 한다(예제는 간결성을 위해 생략).

Python Database API: DB-API 2.0(PEP 249) 표준을 따르며 connect → cursor → execute → fetch 흐름. Jupyter에서는 pip install ipython-sql 매직 함수로 편리하게 쓸 수 있다.

---

Q6. Trigger란 무엇이고 어떻게 정의하나?

A. Trigger는 DB 수정(insert/delete/update)의 부작용으로 시스템이 자동 실행하는 문장으로, 실행 조건과 동작을 지정해 정의한다.

트리거 설계 시 지정할 두 요소:

1. 조건(Condition) — 트리거가 실행될 조건
2. 동작(Action) — 실행 시 수행할 작업

직관 — 트리거는 "DB에 설치한 자동 센서"

트리거는 **“~하면 자동으로 ~해라”**라는 IFTTT(if this then that) 규칙을 DB 안에 박아 두는 것이다. 집의 동작 감지 센서처럼 동작한다.

• 언제 깨어나나(event): 누군가 테이블을 insert/delete/update 할 때. (센서가 움직임을 감지하는 순간)
• 정말 작동할까(condition, when): 추가 조건을 만족할 때만. (밤에만 켜지는 센서처럼)
• 무엇을 하나(action): 다른 테이블을 갱신하거나, rollback으로 작업을 취소.
  핵심은 사람이 직접 호출하지 않아도 DB가 알아서 실행한다는 점 — 그래서 “수정의 부작용으로 시스템이 자동 실행”이라고 한다. 주 용도는 ① 파생값 자동 갱신 ② FK로 표현 못 하는 복잡한 무결성 규칙 강제.

Trigger는 SQL:1999에서 표준화되었지만 그 이전부터 대부분 DBMS가 비표준 문법으로 지원했다. → 교재 문법이 특정 DBMS에서 그대로 동작하지 않을 수 있으니 매뉴얼 확인 필요.

Triggering Events and Actions

• Triggering event: insert, delete, update
• update 트리거는 특정 속성으로 제한 가능: after update of takes on grade
• 수정 전/후 값 참조:
  • referencing old row as — delete, update (수정 전 값)
  • referencing new row as — insert, update (수정 후 값)
• 실행 시점: before / after. before는 추가 제약(extra constraint)으로 활용 가능.

직관 — old/new는 "수정의 before & after 사진"

한 행이 바뀔 때 트리거는 그 행의 **변경 전 사진(old row)**과 **변경 후 사진(new row)**을 둘 다 볼 수 있다.

• insert: 새로 생기니 after 사진만(new) 존재.
• delete: 사라지니 before 사진만(old) 존재.
• update: 둘 다 존재 → old와 new를 비교해 “무엇이 어떻게 바뀌었는지”를 판단할 수 있다(예: 성적이 F였다가 A로 바뀜).

before vs after: before는 수정이 DB에 반영되기 직전에 끼어들어 값을 고치거나(빈 성적→null) 막을 수 있다. after는 수정이 끝난 뒤 그 결과를 보고 다른 테이블에 반영(파생값 갱신)할 때 쓴다.

old/new를 언제 볼 수 있나

takes(ID='00128', course_id='CS-101', grade='F')가 grade='A'로 수정된다고 하자.

• old row는 수정 전 사진: grade='F'
• new row는 수정 후 사진: grade='A'

트리거는 두 사진을 비교해서 “이번 수정으로 처음 통과했는가?” 같은 질문에 답할 수 있다. 이 비교가 없으면 단순히 A가 보였다는 이유만으로 이미 계산된 학점을 또 더하는 실수를 할 수 있다.

예시 — 빈 성적을 null로 변환 (before 트리거):

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;

---

Q7. Trigger의 실전 용도는? (파생값 유지·참조 무결성)

A. 학점 누적 같은 파생값 자동 갱신과, FK로 표현 못 하는 복잡한 참조 무결성을 트리거로 구현한다.

파생값 유지 — credits_earned: 학생이 과목을 처음 통과하면 student.tot_cred를 자동 증가.

create trigger credits_earned after update of takes on (grade)
referencing new row as nrow
referencing old row as orow
for each row
when nrow.grade <> 'F' and nrow.grade is not null
    and (orow.grade = 'F' or orow.grade is null)
begin atomic
    update student
    set tot_cred = tot_cred +
        (select credits from course
         where course.course_id = nrow.course_id)
    where student.id = nrow.id;
end;

• 조건 해석: 새 성적이 F도 null도 아니고, 이전 성적은 F이거나 null이었다 → 이번에 처음 통과한 경우만 학점 가산.

참조 무결성 유지: section이 참조하는 time_slot_id는 반드시 time_slot에 존재해야 하고, time_slot에서 삭제 시 section이 여전히 참조 중이면 rollback.

-- section에 insert 시 time_slot_id 존재 확인
create trigger timeslot_check1 after insert on section
referencing new row as nrow
for each row
when (nrow.time_slot_id not in (select time_slot_id from time_slot))
begin
    rollback
end;
 
-- time_slot에서 delete 시 section이 여전히 참조 중인지 확인
create trigger timeslot_check2 after delete on timeslot
referencing old row as orow
for each row
when (orow.time_slot_id not in (select time_slot_id from time_slot)
      and orow.time_slot_id in (select time_slot_id from section))
begin
    rollback
end;

---

Q8. (Outline 보충) Functions / Procedures와 Recursive Query는?

A. SQL 안에서 재사용 가능한 로직을 함수·프로시저로 정의하고, 계층/그래프 탐색은 with recursive로 표현한다. (강의 outline 항목 — 슬라이드 본문에는 코드가 없어 표준 SQL 기준으로 직관만 보충)

Functions and Procedures — 자주 쓰는 계산·로직을 DB 안에 이름 붙여 저장해 두고 재사용하는 것(stored routine).

• Function: 값을 반환한다. SELECT 안에서 호출 가능.
• Procedure: 작업을 수행한다(여러 문장 실행, in/out 파라미터). call로 호출.

직관 — 함수/프로시저는 "DB에 저장한 단축키"

매번 똑같이 길게 쓰는 쿼리·계산을 이름 하나로 묶어 DB 서버에 저장해 두는 것. 엑셀 함수처럼 인자만 넣어 호출한다. 장점: ① 중복 제거 ② 네트워크 왕복 감소(로직이 서버에서 실행) ③ 권한·보안 일원화.

-- Function: 특정 학과의 교수 수를 반환
create function dept_count(dept varchar(20))
    returns integer
begin
    declare d_count integer;
    select count(*) into d_count
    from instructor
    where dept_name = dept;
    return d_count;
end;
 
-- 호출: 쿼리 안에서 값처럼 사용
select dept_name, budget
from department
where dept_count(dept_name) > 12;

-- Procedure: 같은 기능을 out 파라미터로
create procedure dept_count_proc(in dept varchar(20), out d_count integer)
begin
    select count(*) into d_count
    from instructor
    where dept_name = dept;
end;
 
-- 호출
declare d_count integer;
call dept_count_proc('Physics', d_count);

Recursive Query — 자기 자신을 반복 참조하며 계층/그래프를 끝까지 따라가는 쿼리. with recursive로 작성.

직관 — recursive query는 "한 단계씩 번져 나가는 물결"

선수과목(prereq)처럼 “A의 선수과목, 그 선수과목의 선수과목, …”을 끝까지 추적해야 할 때 일반 join으로는 깊이를 모른다(몇 단계인지 미리 알 수 없음). recursive query는 ① **출발점(base case)**에서 시작해 ② **한 칸씩 더 따라간 결과를 계속 누적(recursive step)**하다가 ③ 더 추가될 게 없으면 멈춘다(fixed point). 잔잔한 호수에 돌을 던졌을 때 물결이 한 겹씩 번지다 멈추는 것과 같다.

-- course_id의 모든 (직접+간접) 선수과목 찾기
with recursive rec_prereq(course_id, prereq_id) as (
    -- base: 직접 선수과목
    select course_id, prereq_id
    from prereq
  union
    -- recursive step: 한 단계 더 거슬러 올라가기
    select rec_prereq.course_id, prereq.prereq_id
    from rec_prereq, prereq
    where rec_prereq.prereq_id = prereq.course_id
)
select * from rec_prereq;

• base + recursive step을 union으로 연결하고, recursive step은 정의 중인 뷰(rec_prereq)를 다시 참조한다.
• 새 튜플이 더 안 생기면(고정점 도달) 자동 종료. union(중복 제거)이라 무한 루프를 막는다.

---

부록. 접근 방식 비교

항목	JDBC	ODBC	Python DB-API
언어	Java	C	Python
모델	Connection → Statement → ResultSet	Env → Conn → Stmt → Bind → Fetch	connect → cursor → execute → fetchall
파라미터 바인딩	? + setXxx	SQLBindParameter	%s / ? + 튜플
메타데이터	ResultSetMetaData, DatabaseMetaData	SQLDescribe, SQLColumns	cursor.description
Injection 방어	PreparedStatement	파라미터 바인딩	파라미터 쿼리

---

출처: Database System Concepts, 7th Edition (Silberschatz, Korth, Sudarshan)