Chapter 5: Advanced SQL
Outline
1. Accessing SQL From a Programming Language
2. Functions and Procedures
3. Triggers
4. Recursive Queries
5. Advanced Aggregation Features
Accessing SQL from a Programming Language
- A general purpose program은 함수( Java의 메서드) 등을 사용하여 database server와 연결합니다.
- Embedded SQL은 프로그램이 database server와 상호 작용할 수 있는 수단을 제공합니다.
JDBC
- SQL을 지원하는 Database Systems와 연동할 수 있는 JAVA API 입니다.
- 쿼리 날리기, 업데이트 등의 기능을 지원합니다.
- 데이터베이스에 존재하는 릴레이션 이름과 유형 등 메타데이터 검색 기능도 제공합니다.
SQL Injection
- SQL 인젝션(SQL 삽입, SQL 주입)은 코드 인젝션의 한 기법으로 클라이언트의 입력값을 조작하여 서버의 데이터베이스를 공격할 수 있는 공격방식을 말합니다.
- 사용자가 입력한 데이터를 제대로 필터링, 이스케이핑하지 못했을 때 발생합니다.
- Prepared statement 객체를 사용하여 사용자의 입력값을 파라미터로 넘겨주는 방식을 사용하는 것이 좋습니다.
PreparedStatement pStmt = conn.prepareStatement ( "insert into instructor(?,?,?,?)");
// 사용자가 입력한 값을 파라미터로 전달합니다
pStmt.setString (1, "88877");
pStmt.setString (2, "Perry");
pStmt.setString (3, "Finance");
pStmt.setInt (4, 125000);
Declaring SQL Functions
- 함수와 프로시저를 통해 "비즈니스 로직"을 데이터베이스에 저장하고 SQL 문에서 실행할 수 있습니다.
# FUNTION 정의 - input : 부서명 output : 해당 부서에 속한 instructor의 수
CREATE FUNCTION dept_count(dept_name VARCHAR(20))
RETURNS INTEGER
BEGIN # BEGIN은 '{'라고 생각하면 편합니다.
DECLARE d_count INTEGER;
SELECT COUNT(*) INTO d_count
FROM instructor
WHERE instructor.dept_name = dept_name
RETURN d_count;
END # '}'
# FUNCTION 사용
# instructor > 12인 모든 department의 name, budget을 찾는 쿼리
select dept_name , budget
from department
where dept_ count (dept_name ) > 12;
Table Functions
- TABLE을 반환하는 Function을 table functions라고 합니다. ( SQL 표준 )
# Input : dept_name , output : instructor 테이블
create function instructor_of (dept_name char (20))
returns table ( # 반환할 테이블의 속성 정의
ID varchar (5),
name varchar (20),
dept_name varchar (20),
salary numeric (8,2))
return table # 아래 쿼리 결과를 반환합니다.
(select ID, name, dept_name, salary
from instructor
where instructor.dept_name = instructor_of.dept_name);
# Usage
SELECT *
FROM TABLE (instructor_of('Music'));
Language Constructs
# LOOP 문
LOOP
처리문;
EXIT 탈출 조건;
END LOOP;
# WHILE 문
WHILE 조건
LOOP
처리문;
END LOOP;
# FOR 문
FOR 증감변수 IN 초깃값..최종값
LOOP
처리문;
END LOOP;
External Language Routines
- SQL에서는 JAVA, C#, C, C++ 등으로 정의된 함수를 가져와서 쓸 수 있습니다.
create procedure
dept_count_proc (in dept_name varchar (20),
out count integer)
language C
external name '/usr/avi/bin/dept_count_proc'
create function dept_count(dept_name varchar (20))
returns integer
language C
external name '/usr/avi/bin/dept_count'
Security with External Language Routines
- Use SandBox techniques : 데이터베이스 코드의 다른 부분에 액세스하거나 손상시키는 데 사용할 수 없는 Java와 같은 안전한 언어를 사용합니다.
- 데이터베이스 프로세스 메모리에 액세스하지 않고 별도의 프로세스에서 외부 함수/프로시저를 실행합니다.
Triggers
- 트리거(Trigger)는 데이터베이스 테이블에 발생하는 데이터 변경 이벤트에 대해 자동으로 반응하여, 특정 작업을 수행하는 데이터베이스 객체입니다.
- 트리거 메커니즘을 설계하기 위해 다음을 수행해야 합니다.
1. 트리거를 수행할 조건을 지정합니다.
2. 트리거가 실행될 때 수행할 작업을 지정합니다.
Trigger to Maintain credits_earned value
# Trigger example
CREATE TRIGGER credits_earned # credits_earned라는 트리거를 생성
AFTER UPDATE OF takes ON (grade) # takes 테이블의 grade 열이 업데이트될 때마다 실행됩니다.
REFERENCING NEW ROW AS nrow # 새로운 행(nrow)과 이전 행(orow)을 참조
REFERENCING OLD ROW AS orow
FOR EACH ROW
WHEN nrow.grade <> 'F' AND nrow.grade IS NOT NULL # 새로운 값이 F나 NULL이 아니면서
AND (orow.grade = 'F' OR orow.grade IS NULL) # 예전 값이 F나 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; # 트리거 본문이 끝나는 지점
Statement Level Triggers
- 일반적으로, 트리거는 각 행이 수정될 때마다 작동합니다. 따라서 대량의 행이 업데이트될 때는 성능 저하가 발생할 수 있습니다. 하지만, for each statement 구문을 사용하면 한 번에 여러 행을 처리할 수 있으므로, 대량의 행이 업데이트될 때 더 효율적인 처리가 가능합니다.
- referencing old table 또는 referencing new table 구문을 사용하여, 이전 및 새로운 버전의 테이블을 참조할 수 있습니다.
- 이전 및 새로운 테이블은 트랜잭션 내에서 변경된 행의 모든 버전을 포함하는 일시적인 테이블입니다. 이러한 일시적인 테이블을 전이 테이블(transition table)이라고 합니다.
When Not To Use Triggers
- 데이터베이스에서 요약 데이터를 유지 관리하는 materialized view 기능을 제공합니다.
- 데이터베이스에서 복제게 대한 기본 지원을 제공합니다.
- Encapsulation (캡슐화)를 사용하면 트리거를 사용하지 않고도 필요한 작업을 효과적으로 수행할 수 있습니다.
- 필드를 업데이트하는 메서드를 정의합니다.
- 업데이트 메서드로 작업을 수행할 수 있습니다.
- Cascading execution : 트리거 호출로 의도치 않은 트리거가 연쇄적으로 실행될 수 있고, 이로 인해 잘못된 데이터를 로딩하는 등의 문제가 발생할 수 있습니다.
Recursive Queries
- 이행폐포(transitive closure) : 이행폐포는 관계형 데이터베이스에서 두 레코드 간의 관계를 탐색하는 연산 중 하나입니다. 이를 이용하면, 한 레코드와 직접적으로 연결된 다른 레코드를 찾을 뿐만 아니라, 그들 사이의 간접적인 관계를 찾을 수 있습니다.
이헹폐포의 예시 ) 방향 그래프에서 한 노드에서 다른 노드로 이동할 수 있는 모든 경로를 찾는 것
- SQL에서는 다음과 같이 재귀를 통하여 이행폐포를 구할 수 있습니다.
# 특정 과목의 모든 선수 과목을 직접적으로 또는 간접적으로 찾는 방법, 특정 과목의 이행폐포 찾기
# 생성되는 rec_prereq가 prereq 테이블의 이행 폐포입니다.
with recursive rec_prereq (course_id , prereq_id) as # 쿼리에서 임시로 사용가능한 재귀적 테이블 생성 req_prereq 속성은 (course_id , prereq_id)입니다.
select course_id , prereq_id # Non-Recursive 이 코드로 course_id와 prereq_id를 선택하여 새로운 rec_prereq 테이블을 만듭니다.
from prereq
union # Recursive
select rec_prereq course_id , prereq prereq_id ,
from rec_rereq , prereq
where rec_prereq prereq_id = prereq course_id
)
select *
from rec_prereq;
Advanced Aggregation Features
Ranking
- rank()를 사용하여 투플들에게 순위를 부여할 수 있습니다.
rank() = 1,1,3,4 ( 중복 값에 대해 같은 순위, 그 다음순위는 중복순위 + 중복값 개수)
- dense_rank()를 사용하면 순위를 빼곡하게 채워줍니다.
dense_rank() = 1,1,2,3 ( 중복 값에 대해 같은 순위, 그 다음순위에는 중복순위 + 1)
- row_number()는 rank()와 달리 중복 값을 허용하지 않습니다.
row_number() = 1,2,3,4 ( 중복 값에도 다른 순위)
# RANK(), GPA 내림차순으로 순위를 정합니다.
SELECT ID , RANK () OVER (ORDER BY GPA DESC ) AS s_rank
FROM student_grades;
# RANK()로 순위를 정했고, 그것을 정렬해서 보고싶으면 ORDER BY 구문을 추가합니다.
SELECT ID , RANK () OVER (ORDER BY GPA DESC ) AS s_rank
FROM student_grades
ORDER BY s_rank;
Ranking ( Cont.)
- OVER()내부에 PARTITION BY를 사용하여 그룹을 짓고 그룹마다 순위를 정할 수 있습니다.
- ntile() : ntile() 함수는 정렬된 행(row)을 n개의 구간(bucket)으로 분할하고, 차례대로 행 번호를 부여합니다.
- 만약 전체에 12개의 투플이 존재하고, ntile(5)를 하는 경우에 5개의 그룹이 생성됩니다. 그리고 나머지가 0이 될 때까지 첫번째 그룹부터 한개씩 행을 추가합니다. 이 경우에는 1, 2번 그룹에서 각각 행을 1개씩 더 가지게 됩니다.
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;
# ntile()
select ID , ntile (4) over ( order by GPA desc ) as quartile
from student_grades
Windowing
# date 오름차순 순서로 해당일, 전날, 다음날의 value의 합을 계산합니다.
SELECT (date, sum value ) OVER
(ORDER BY date BETWEEN ROWS 1 PRECEDING AND 1 FOLLOWING)// BETWEEN (1개 앞의 행 ~ 1개 뒤의 행)
FROM sales;
between rows unbounded preceding and current : 이전 모든 행(첫번째 행) ~ 현재 행
rows unbounded preceding : 현재 행 앞의 모든 행
range between 10 preceding and current row : 현재 행의 10번째 앞 행 ~ 현재 행
range interval 10 day preceding : 현재 행의 1일전 , 2일전 ,..., 10일 전의 행