[데이터베이스] 고급 SQL

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

1.1 SQL과 프로그래밍 언어 통합 이유

• SQL은 선언형 질의어로 데이터 처리에 강력하지만

SQL 접근 방식 두 가지:

1. 동적 SQL (Dynamic SQL):
2. 내장 SQL (Embedded SQL):

1.2 JDBC

• Java에서 데이터베이스 접근을 위한 표준 API

1) 데이터베이스 접속:

• DriverManager.getConnection() 사용
• 매개변수:

2) SQL 구문 전달:

• Statement 객체로 SQL 실행
• executeQuery(): 질의 (select) 실행
• executeUpdate(): 삽입, 수정, 삭제 실행

3) 예외 및 자원 관리:

• try-catch로 예외 처리
• SQL 예외: SQLException
• 자원 반환: close() 메소드로 접속 종료
• try-with-resources 구문으로 자동 자원 반환 가능

4) 질의 결과 검색:

• ResultSet 객체에 질의 결과 저장
• next() 메소드로 다음 튜플 이동
• getString(), getFloat() 등으로 속성 값 추출

5) 준비된 구문 (Prepared Statement):

• ?로 값 대체 후 실행
• setString(), setInt()로 값 지정
• 재사용 시 효율성 증가, SQL 인젝션 방지

1.3 ODBC

• 데이터베이스 독립적인 API
• C 언어 등에서 사용 가능
• 자동 커밋 기능 해제:

SQLSetConnectOption(conn, SQL_AUTOCOMMIT, 0);

• 명시적 커밋/롤백:

SQLTransact(conn, SQL_COMMIT);
SQLTransact(conn, SQL_ROLLBACK);

1.4 내장 SQL

• C, C++, Java 등에서 SQL 구문 삽입 가능
• 전처리기가 SQL 구문을 함수 호출로 변환
• 구문:

EXEC SQL <embedded SQL statement>;

• 호스트 변수 사용 시 :변수명으로 표시
• 커서(cursor)를 통해 질의 결과 반복 처리 가능

2. 함수와 프로시저

2.1 사용자 정의 함수

• 복잡한 데이터 타입 (예: 이미지, 지리정보) 처리 가능
• 학과 교수 수 반환 함수 예시:

create function dept_count(dept_name varchar(20)) returns integer ...

• 테이블 함수: 릴레이션 반환 가능

2.2 프로시저

• 입출력 매개변수 (in, out) 사용
• 예:

create procedure dept_count_proc(in dept_name varchar(20), out count integer) ...

• call 구문으로 호출

2.3 프로시저 언어 구문

• 변수 선언: declare
• 값 할당: set
• 복합문: begin ... end
• 반복문: while, repeat, for
• 조건문: if ... then ... elseif ... else ... end if
• 예외 처리:

declare out_of_classroom_seats condition;
declare exit handler for out_of_classroom_seats ...

2.4 외부 언어 루틴

• C, Java, C# 등으로 함수/프로시저 작성 가능
• 예:

create function dept_count(dept_name varchar(20))
returns integer
language C
external name '/usr/avi/bin/dept_count';

• 샌드박스(sandbox) 환경에서 보안 실행 가능

3. 트리거

3.1 트리거 개념

• 특정 사건(삽입, 삭제, 갱신)에 대해 자동 실행되는 SQL 구문
• 무결성 제약조건을 보완하거나 자동화 작업에 사용

3.2 트리거 예시

1) 참조 무결성 보장:

• section.time_slot_id가 time_slot에 존재하는지 검사:

create trigger timeslot_check1 after insert on section
referencing new row as nrow
when (nrow.time_slot_id not in (select time_slot_id from time_slot))
begin rollback; end;

2) 재고 관리:

• 재고량이 최소량 이하로 내려가면 orders 릴레이션에 주문 추가:

create trigger reorder after update of level on inventory
referencing old row as orow, new row as nrow
when nrow.level <= (select level from minlevel where item = orow.item)
and orow.level > (select level from minlevel where item = orow.item)
begin atomic
insert into orders (select item, amount from reorder where item = orow.item);
end;

3.3 트리거의 한계와 부적합한 경우

• on delete cascade 기능 대신 트리거 사용 시 복잡성 증가
• 실체화 뷰 유지는 트리거 대신 DBMS 지원 기능 활용 가능
• 데이터베이스 복제 시 트리거 동작 방지 필요 (not for replication)
• 트리거 무한 루프 방지: 연결고리 길이 제한 (예: 16)