[DB][데이터베이스] 5. Advanced SQL / 고급 sql

• Accessing SQL From a Programming Language
• Functions and Procedural Constructs
• Triggers
• Recursive Queries
• Advanced Aggregation Features

 by Prof. Chang Hwan Lee

---

Accessing SQL From a Programming Language

- Embedded SQL

SQL 표준은 다양한 프로그래밍 언어(C, C++, JAVA 등으)로 SQL imbedding을 정의한다

 

-- 전처리에 포함된 SQL 요청을 식별한다
EXEC SQL <embedded SQL statement > END-EXEC

EXEC SQL
  -- embedded SQL을 작성한다
  declare c cursor for 
  -- SQL
  select ID, name
  from student
  where tot_cred > :credit_amount
END_EXEC

-- 데이터베이스 시스템은 쿼리를 실행하고, 결과를 저장한다
EXEC SQL open c END_EXEC

-- 쿼리 결과의 한 튜플의 값을 host 언어 변수로 저장한다
EXEC SQL fetch c into :si, :sn END_EXEC

-- 종료
EXEC SQL close c;

-- Cursor로 Update 하기
declare c cursor for
  select *
  from instructor
  where dept_name = ‘Music’
for update

update instructor
set salary = salary + 100
where current of c

 

- Dynamic SQL

각 DBMS는 다른 embedded SQL 특징을 가진다

DB 프로그램끼리의 동작이 어려울 수 있기 때문에, 동적인 SQL이 필요하다

 

ODBC(Open Database Connectivity) / JDBC(Java Database Connectivity)

: 프로그램이 데이터베이스 서버와 상호작용하는 API으로, GUI-spreadsheets 등에서 사용할 수 있다

 

# python-mysql
import mysql.connector

cnx = mysql.connector.connect(user='root', password='',host='localhost')
cursor = cnx.cursor()
cursor.execute("USE market")

 

다음 파라미터로 연결한다

 

• connection handle
• connect하는 서버
• user identifier
• password

 

---

Functions and Procedural Constructs

- Functions

-- ex1
SET GLOBAL log_bin_trust_function_creators = 1;
drop function if exists self_quarantine_count;
-- The number of non-self-quarantined people who purchased products on "21-11-01".
delimiter &&
create function self_quarantine_count(target_date Date) 
    returns int
    begin
        declare cnt int default -1;
        select count(distinct U.user_id) into cnt
        from user as U, order_product as O
        where U.user_id = O.user_id and O.order_date = target_date and U.self_qaurantine = 0;
        return cnt;
    end &&
delimiter ;

-- ex2
create function instructors_of (dept_name char(20)
  returns table ( ID varchar(5),
                  name varchar(20),
                  dept_name varchar(20),
                  salary numeric(8,2) )

select *
from table (instructors_of (‘Music’))

 

- Procedures

-- ex1
drop procedure if exists topUser;
SET GLOBAL log_bin_trust_function_creators = 1;
drop function if exists self_quarantine_count;
delimiter &&
-- 함수 정의 시작
create procedure topUser()
begin
  --  The name of the user who put the highest price in the shopping cart
  select U.name, S.price
  from user as U, shopping_cart as S
  where U.user_id = S.user_id and S.price = (select max(S.price)
                                             from shopping_cart as S);
end &&
delimiter ;

call topUser

-- ex2
delimiter &
-- 절차 정의 시작
create procedure dept_count_proc (in dept_name varchar(20), 
									out d_count integer)
begin
  select count(*) into d_count
  from instructor
  where instructor.dept_name = dept_count_proc.dept_name
end &
delimiter ;

declare d_count integer;
call dept_count_proc( ‘Physics’, d_count);

 

- while / repeat / for loop

-- while
declare n integer default 0;
while n < 10 do
  set n = n + 1
end while

-- repeat
repeat
  set n = n – 1
until n = 0
end repeat

-- for loop
declare n integer default 0;
for r as
  select budget from department
  where dept_name = ‘Music’
do
  set n = n - r.budget
end for

 

- External Language Functions/Procedures

이는 보안상의 이유로 대부분 DB에서 직접적인 호출을 막는다

 

이를 해결하기 위해, sandbox; Java와 같은 안전한 언어를 사용하거나

데이터베이스 프로세스가 아닌 별도의 프로세스에서 외부 언어를 실행하는데

모두 오버헤드가 생긴다

 

---

Triggers

데이터베이스에 대한 수정의 부작용으로 시스템에 의해 자동으로 실행한다

insert / delete / update 이벤트를 발생시키고,

referencing old row as ~ / referencing new row as ~ 로 값을 참조시킨다

 

다음 예시 두 가지를 살펴보자

 

[Fig 1] trigger example 1

 

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)) /* time_slot_id not present in time_slot */
  begin
    rollback
  end;

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) 
          /* last tuple for time slot id deleted from time slot */
  		and orow.time_slot_id in (
          select time_slot_id
          from section)) /* and time_slot_id still referenced from section*/
  begin
  rollback
  end;

 

두 번 째 예시는 grade 값을 받았을 때, 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;

 

trigger는 summary 데이터를 유지하고, 특수관계(trasition/change or delta relation)에 대한 변경 사항을 기록한다

 

하지만 다음과 같은 경우에, risk가 존재한다

• 백업 복사본에서 데이터 로드
• 원격 사이트에서 업데이트 복제
• 오류 발생으로 트리거를 발생시키는 중요한 transaction 실패
• Cascading 실행

이러한 작업 수행 전에 트리거 실행을 비활성화 할 수 있다

 

---

?Recursive Queries

메모리 상에 가상의 테이블을 저장한다

재귀 쿼리를 이용해 실제로 테이블을 생성하거나 insert를 하지 않아도, 가상 테이블을 생성할 수 있다

 

with recursive <테이블명> as (
  select <초기값> as <컬럼별명1>
  union all
  select <컬럼별명1 계산식> from <테이블명> where <제어문>
)

 

다음 예시를 살펴보자

 

-- 직접/간접적으로 특정 과목의 필수과목을 찾는다
with recursive rec_prereq(course_id, prereq_id) as (
  select course_id, prereq_id
 	 from prereq
  union
  select rec_prereq.course_id, prereq.prereq_id, 
    from rec_rereq, prereq
    where rec_prereq.prereq_id = prereq.course_id
)

select ∗ from rec_prereq;

 

c_id , c_id의 선이수,

그것의 선이수, 또 선이수 와 같은 식으로 재귀적으로 select 한다

 

[Fig 2] recursive query example

 

---

Advanced Aggregation Features

- Ranking

rank는 특정 순서의 결합으로 이루어진다

 

select ID, rank() over (order by GPA desc) as s_rank
from student_grades
order by s_rank

-- rank()는 1224처럼 ranking 되는 것과 달리 dense_rank()는 1223처럼 ranking 된다
-- 이외에 percent_rank() / cume_dist() / row_number()

-- ntile(4) 는 4등분으로 나눠 정렬한다
select ID, ntile(4) over (order by GPA desc) as quartile
from student_grades;

 

rank는 partition으로 이루어진다

이는 group by가 이루어진 후 ranking 된다

 

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;

 

- Windowing

Group By는 집계된 결과만 보여주는 반면, 

window 함수는 기존 데이터에 집계된 값을 추가하여 나타낸다

 

윈도우를 특정하는 여러 방법에는 다음이 있다

• between rows 1 preceding and 1 following
• between rows unbounded preceding and current
• rows unbounded preceding
• range between 10 preceding and current row
• range interval 10 day preceding