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

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

drop procedure if exists topUser; 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

1 1 between rows unbounded preceding and current

rows unbounded preceding

range between 10 preceding and current row

10 range interval 10 day preceding

반응형

from http://snupi.tistory.com/180 by ccl(A) rewrite - 2021-11-26 23:01:49