[데이터베이스] 고급 SQL
1. 프로그래밍 언어에서 SQL 접근
1.1 SQL과 프로그래밍 언어 통합 이유
- •
SQL은 선언형 질의어로 데이터 처리에 강력하지만
보고서 출력, 사용자 인터페이스와의 통신,
특정 질의 로직 구현은 범용 프로그래밍 언어가 필요함
SQL 접근 방식 두 가지:
- 1
동적 SQL (Dynamic SQL):
- •
프로그램 실행 중 SQL 문자열 생성 및 실행
- •
결과를 한 튜플씩 변수로 받아올 수 있음
- 2
내장 SQL (Embedded SQL):
- •
전처리기가 SQL 구문을 함수 호출로 변환
- •
컴파일 시점에 처리
1.2 JDBC
- •
Java에서 데이터베이스 접근을 위한 표준 API
1) 데이터베이스 접속:
- •
DriverManager.getConnection()사용
- •
매개변수:
- ◦
JDBC URL:
jdbc:oracle:thin:@db.yale.edu:2000:univdb
- ◦
사용자명, 비밀번호
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)