Chapter 5. Advanced SQL

핵심 요약: SQL만으로는 표현할 수 없는 계산, 비선언적 동작(UI·리포팅), 제어 흐름이 필요하므로 프로그래밍 언어에서 SQL에 접근하는 표준(JDBC, ODBC, Python DB-API)이 제공된다. PreparedStatement는 SQL Injection을 원천적으로 차단하는 표준 방어책이다. Trigger는 DB 수정(insert/delete/update)의 부작용으로 자동 실행되는 문장으로, 참조 무결성 유지·파생값 갱신·제약 강화에 쓰인다.

1. 프로그래밍 언어에서 SQL 접근하기

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

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

접근 방식의 종류

JDBC — Java API
ODBC — C 언어용 표준 API
Python Database API — Python 표준
Embedded SQL — 언어 컴파일러가 SQL을 전처리

2. JDBC (Java Database Connectivity)

JDBC는 SQL을 지원하는 데이터베이스와 통신하기 위한 Java API(application program interface)다.

JDBC의 역할

데이터 조회(querying) 및 수정(updating)
쿼리 결과 조회(retrieval)
메타데이터 조회 — DB 내 릴레이션, 속성 이름·타입 등

JDBC 통신 모델 (4단계)

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

아키텍처

  JAVA Application (JDBC function calls)
         │
  ┌──────┼───────┬────────┬───────┐
  │      │       │        │       │
Oracle  MySQL  SQLite  Access   ← Driver
  │      │       │        │
Oracle  MySQL  SQLite  Access   ← DB

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

2.1 JDBC 기본 코드

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);
    }
}

try-with-resources 문법(Java 7 / JDBC 4 이상)을 사용하면 Connection/Statement가 자동으로 close 된다.

2.2 데이터 수정과 결과 조회

// Update
try {
    stmt.executeUpdate(
        "insert into instructor values('77987', 'Kim', 'Physics', 98000)");
} catch (SQLException sqle) {
    System.out.println("Could not insert tuple. " + sqle);
}
 
// Query + Fetch
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));
}

executeUpdate — INSERT/UPDATE/DELETE 등 영향 행 수 반환
executeQuery — SELECT 결과 ResultSet 반환
ResultSet은 커서(cursor) 기반으로, next()를 호출하여 행을 앞으로 이동한다.

2.3 결과 필드 접근과 Null 처리

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");

2.4 Prepared Statement

PreparedStatement는 ? 플레이스홀더를 가진 SQL을 미리 컴파일해 두고, 파라미터만 바인딩하여 실행한다.

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();

파라미터 번호는 1부터 시작한다.
경고: 사용자 입력을 쿼리에 포함할 때는 반드시 PreparedStatement를 사용하고, 문자열 연결로 쿼리를 만들지 말 것.
문자열 연결 방식은 name = "O'Henry"처럼 따옴표가 포함된 값에서 깨지며, 더 심각하게는 SQL Injection에 취약하다.

2.5 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는 내부적으로 사용자 입력을 이스케이프(escape)하여 '를 \'로 치환하므로 쿼리 구조가 바뀌지 않는다. → 항상 PreparedStatement를 사용하고, 사용자 입력은 파라미터로 전달한다.

2.6 Metadata 기능

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"));
}

3. ODBC (Open Database Connectivity)

ODBC는 C 언어 애플리케이션이 DB 서버와 통신하는 표준이다. GUI, 스프레드시트 등도 ODBC를 통해 DB에 접근할 수 있다.

ODBC 흐름

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

ODBC 코드 예시

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);

lenOut에 음수가 반환되면 null 값을 의미한다.
가변 길이 타입은 최대 길이와 실제 길이 저장 위치를 함께 지정한다.
실제 코드에서는 모든 호출의 반환값을 검사해야 한다(예제는 간결성을 위해 생략).

4. Python Database API

Python은 DB-API 2.0 표준(PEP 249)을 따르며, Jupyter 환경에서는 ipython-sql 매직 함수로 편리하게 쓸 수 있다:

pip install ipython-sql

magic 함수 없이도 sqlite3, psycopg2 등 DB-API 호환 모듈로 동일한 connect→cursor→execute→fetch 흐름을 따른다.

5. Triggers

Trigger는 데이터베이스 수정의 부작용(side effect)으로 시스템이 자동 실행하는 문장이다.

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

조건(Condition) — 트리거가 실행될 조건
동작(Action) — 트리거가 실행될 때 수행할 작업

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

5.1 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)으로 활용 가능. 예: 빈 문자열 성적을 null로 변환.

예시 — 빈 성적을 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;

5.2 파생값 유지 트리거 — `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이었다 → 이번에 처음 통과한 경우만 학점 가산.
course로부터 credits을 조회하여 student의 tot_cred에 더한다.

5.3 참조 무결성 유지 트리거

Foreign key 외의 복잡한 참조 제약은 트리거로 구현할 수 있다. 예: section이 참조하는 time_slot_id가 반드시 time_slot 테이블에 존재해야 하고, time_slot에서 삭제 시 section이 여전히 참조 중이면 롤백.

-- 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;

6. 정리 — 각 접근 방식의 비교

항목	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, DB별
Injection 방어	PreparedStatement	파라미터 바인딩	파라미터 쿼리

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

Juhyeon's Blog

탐색기

Ch5_Advanced_SQL

Chapter 5. Advanced SQL

1. 프로그래밍 언어에서 SQL 접근하기

접근 방식의 종류

2. JDBC (Java Database Connectivity)

JDBC의 역할

JDBC 통신 모델 (4단계)

아키텍처

2.1 JDBC 기본 코드

2.2 데이터 수정과 결과 조회

2.3 결과 필드 접근과 Null 처리

2.4 Prepared Statement

2.5 SQL Injection

2.6 Metadata 기능

3. ODBC (Open Database Connectivity)

ODBC 흐름

ODBC 코드 예시

4. Python Database API

5. Triggers

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

5.1 Triggering Events and Actions

5.2 파생값 유지 트리거 — `credits_earned`

5.3 참조 무결성 유지 트리거

6. 정리 — 각 접근 방식의 비교

그래프 뷰

목차

Properties

백링크

Ch5_Advanced_SQL

Chapter 5. Advanced SQL

1. 프로그래밍 언어에서 SQL 접근하기

접근 방식의 종류

2. JDBC (Java Database Connectivity)

JDBC의 역할

JDBC 통신 모델 (4단계)

아키텍처

2.1 JDBC 기본 코드

2.2 데이터 수정과 결과 조회

2.3 결과 필드 접근과 Null 처리

2.4 Prepared Statement

2.5 SQL Injection

2.6 Metadata 기능

3. ODBC (Open Database Connectivity)

ODBC 흐름

ODBC 코드 예시

4. Python Database API

5. Triggers

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

5.1 Triggering Events and Actions

5.2 파생값 유지 트리거 — credits_earned

5.3 참조 무결성 유지 트리거

6. 정리 — 각 접근 방식의 비교

그래프 뷰

목차

Properties

백링크

5.2 파생값 유지 트리거 — `credits_earned`