[3월 1주차-3/4]SQL 프로그래밍 : 스토어드 프로시저, 함수, 인덱스, 뷰, 트리거 활용법

1️⃣스토어드 프로시저 (Stored Procedure)

데이터베이스에서 스토어드 프로시저(Stored Procedure)는 여러 SQL 문을 하나의 프로시저로 저장하여 필요할 때 호출할 수 있는 기능입니다. 이는 코드의 재사용성을 높이고, 네트워크 트래픽을 줄이며, 데이터베이스의 보안을 강화하는 데 도움을 줍니다.

장점

• 절차적 기능 구현 가능: 복잡한 비즈니스 로직을 데이터베이스 내에서 처리할 수 있습니다.
• 유지 보수 용이: 동일한 로직을 여러 곳에서 사용할 경우, 수정이 필요할 때 한 번만 변경하면 됩니다.
• 트래픽 감소: 애플리케이션에서 다수의 SQL 쿼리를 실행하는 대신, 하나의 스토어드 프로시저 호출로 대체할 수 있어 네트워크 트래픽이 감소합니다.
• 보안 강화: 직접적인 테이블 접근을 제한하고, 필요한 작업만 수행하도록 구성할 수 있습니다.

단점

• 실행 속도가 느려질 수 있음: 복잡한 로직을 포함할 경우 성능이 저하될 가능성이 있습니다.
• 데이터베이스 시스템 간 호환성이 낮음: 특정 DBMS에서만 작동할 수 있어 이식성이 낮아질 수 있습니다.

스토어드 프로시저 예제

delimiter &&
create procedure do_proc()
begin
    -- 변수 선언
    declare customer_cnt int;
    declare add_number int;
    
    -- 변수 초기값 설정
    set customer_cnt = 0;
    set add_number = 100;
    set customer_cnt = (select count(*) from customer);
    
    select customer_cnt + add_number;
end &&
delimiter ;

-- 스토어드 프로시저 호출
call do_proc();

-- 스토어드 프로시저 내용 확인
show create procedure do_proc;

-- 스토어드 프로시저 삭제
drop procedure do_proc;

2️⃣SQL 프로그래밍

SQL은 다양한 제어문을 지원하여 조건문(IF), 반복문(WHILE), 그리고 선택문(CASE) 등을 활용하여 프로그래밍할 수 있습니다.

IF문 사용

select store_id, if(store_id = 1, '일', '이') as one_two
from customer
group by store_id;

delimiter &&
create procedure do_if(customer_id_input int)
begin
    declare store_id_i int;
    declare s_id_one int;
    declare s_id_two int;
    
    set store_id_i = (select store_id from customer 
                      where customer_id = customer_id_input);
    
    if store_id_i = 1 then set s_id_one = 1;
    else set s_id_two = 2;
    end if;
    
    select store_id_i, s_id_one, s_id_two;
end &&
delimiter ;

call do_if(1);

CASE문 사용

CASE 문은 여러 조건을 평가하고 그에 따라 결과를 반환하는 데 유용합니다.

select customer_id, sum(amount) as amount,
    case
        when sum(amount) >= 150 then 'VVIP'
        when sum(amount) >= 120 then 'VIP'
        when sum(amount) >= 100 then 'GOLD'
        when sum(amount) >= 80 then 'SILVER'
        else 'BRONZE'
    end as customer_level
from payment
group by customer_id;

while ~ leave 문 사용

-- while문을 실행하기 위한 스토어드 프로시저 생성
drop procedure if exists do_while;
delimiter &&
create procedure do_while(param_1 int, param_2 int)
begin
	declare i int;
    declare while_sum int;
    
    set i = 1;
    set while_sum = 0;
    
    mywhile:
	while (i<= param_1) do
		set while_sum = while_sum + param_2;
        set i = i+1;
        
        if (while_sum >100) then leave mywhile;
        end if;
	end while;
	select while_sum;
end &&
delimiter ;
call do_while(1000,3);

동적 SQL 사용

drop procedure if exists do_dynamic;
delimiter &&
create procedure do_dynamic(t_name varchar(50), c_name varchar(50), customer_id int)
begin
	set @t_name = t_name;
    set @c_name = c_name;
    set @customer_id= customer_id;
    set @sql = concat('select ', @c_name, ' from ', @t_name, ' where customer_id =', @customer_id);
    
    select @sql;
    prepare dynamic_query from @sql;
    execute dynamic_query;
    deallocate prepare dynamic_query;
end &&
delimiter ;

call do_dynamic('payment', '*', 1);

3️⃣인덱스(Index)

데이터베이스 인덱스는 테이블에서 원하는 데이터를 빠르게 찾을 수 있도록 도와주는 구조입니다.

장점

• 데이터 검색 속도 향상: 인덱스를 사용하면 검색 성능이 대폭 개선됩니다.
• I/O 성능 증가: 디스크 I/O를 줄여 전체적인 응답 속도를 높일 수 있습니다.

단점

• 추가적인 저장 공간 필요: 인덱스를 유지하기 위한 추가적인 공간이 필요합니다.
• 데이터 수정 시 성능 저하 가능: 데이터가 변경될 때마다 인덱스도 함께 업데이트되어야 하기 때문에 성능이 저하될 수 있습니다.
• 너무 많은 인덱스 사용 시 성능 저하 가능: 불필요한 인덱스는 오히려 성능을 떨어뜨릴 수 있습니다.

클러스터형 인덱스

클러스터형 인덱스는 기본 키를 기준으로 테이블의 데이터가 정렬되는 방식입니다.

비클러스터형 인덱스

비클러스터형 인덱스는 테이블의 물리적 순서를 변경하지 않고 별도의 인덱스 페이지를 생성하여 데이터를 관리합니다.

-- 인덱스
use test;
create table do_clusterindex(
col_1 int,
col_2 varchar(50),
col_3 varchar(50)
);
insert into do_clusterindex values (2, '사자', 'lion');
insert into do_clusterindex values (5, '호랑이', 'tiger');
insert into do_clusterindex values (3, '얼룩말', 'zbera');
insert into do_clusterindex values (4, '코뿔소', 'rhinoceros');
insert into do_clusterindex values (1, '거북이', 'turtle');
select * from do_clusterindex;

-- 클러스터형 인덱스
alter table do_clusterindex
	add constraint primary key (col_1);
select * from do_clusterindex;
# -> col_1 순서로 테이블 정렬됨

insert into do_clusterindex values (0, '물고기', 'fish');
select * from do_clusterindex;
# -> 새로 입력된 데이터 또한 기본 키 기준으로 정렬됨

alter table do_clusterindex
	drop primary key,
    add constraint primary key do_clusterindex (col_2);
select * from do_clusterindex;
# -> 데이터가 가나다 순으로 정렬

-- 복합키 인덱스
alter table do_clusterindex
	drop primary key,
    add constraint primary key(col_1, col_3);
show index from do_clusterindex;

-- index 삭제
alter table do_clusterindex drop primary key;

-- 비클러스터형 인덱스: 정렬 안됨
create table do_nonclusterindex(
col_1 int,
col_2 varchar(50),
col_3 varchar(50)
);
insert into do_nonclusterindex values (2, '사자', 'lion');
insert into do_nonclusterindex values (5, '호랑이', 'tiger');
insert into do_nonclusterindex values (3, '얼룩말', 'zbera');
insert into do_nonclusterindex values (4, '코뿔소', 'rhinoceros');
insert into do_nonclusterindex values (1, '거북이', 'turtle');
select * from do_nonclusterindex;

create index ix_do_non_1 on do_nonclusterindex (col_1);
select * from do_nonclusterindex;
# -> 정렬 안됨

insert into do_nonclusterindex values (0, '물고기', 'fish');
select * from do_nonclusterindex;
# -> 정렬 안됨. 입력된 순서대로 저장됨

create index ix_do_non_2 on do_nonclusterindex (col_2);
create index ix_do_non_3 on do_nonclusterindex (col_3);
# -> 정렬 안됨

create index ix_do_non_1_2 on do_nonclusterindex (col_1, col_2);
create index ix_do_non_1_3 on do_nonclusterindex (col_1, col_3);

-- index 삭제
drop index ix_do_non_1_2 on do_nonclusterindex;
drop index ix_do_non_1_3 on do_nonclusterindex;

4️⃣뷰(View)

뷰(View)는 하나 이상의 테이블에서 가져온 데이터를 기반으로 생성된 가상 테이블입니다. 실제 데이터를 저장하지 않고, 기존 테이블의 데이터에 대한 특정한 관점을 제공합니다.

장점

• 유지 보수 용이: 복잡한 쿼리를 미리 정의하여 재사용할 수 있습니다.
• 보안성 우수: 특정 컬럼이나 데이터를 제한적으로 제공할 수 있어 보안이 향상됩니다.

단점

• 정의된 뷰 변경 불가: 뷰 자체의 구조를 변경할 수 없습니다.
• 삽입, 삭제, 갱신 제한 사항 존재: 복합적인 뷰에서는 데이터 조작이 제한될 수 있습니다.
• 인덱스 생성 불가: 일반적인 테이블과 달리, 뷰에는 인덱스를 생성할 수 없습니다.

뷰 생성 / 수정 / 삭제

-- 뷰
use sakila;
CREATE VIEW v_customer
AS
	SELECT first_name, last_name, email FROM customer;
SELECT* FROM v_customer;

-- 2개의 테이블 조인해 뷰 생성
create view v_payuser
as
	select first_name, last_name, email, amount, address_id
    from customer as a
		inner join (select customer_id, sum(amount) as amount from payment
        group by customer_id) as b on a.customer_id = b.customer_id;

select * from v_payuser;

-- 뷰 테이블과 일반 테이블 조인
select a.*, b.*
from v_payuser as a
	inner join address as b on a.address_id = b.address_id;
    
-- 뷰 수정
alter view v_customer
as 
	select customer_id,first_name, last_name, email, address_id
    from customer;
select * from v_customer;

-- 뷰 생성 및 교체
create or replace view v_customer
as
	select '뷰가 있으면 수정, 없으면 생성';

select * from v_customer;

-- 뷰 정보 확인
describe v_payuser;
show create view v_payuser;

-- 뷰 삭제
drop view v_customer;
drop view v_payuser;

-- 뷰 사용해 데이터 조작하기
use test;
create table tbl_a(
col_1 int not null,
col_2 varchar(50) not null
);

create table tbl_b (
col_1 int not null,
col_2 varchar(50) not null
);

insert into tbl_a values(1, 'tbl_a_1');
insert into tbl_a values(2, 'tbl_a_2');
insert into tbl_b values(1, 'tbl_b_1');
insert into tbl_b values(2, 'tbl_a_2');

create view v_tbl_a
as select col_1, col_2 from tbl_a;
select * from v_tbl_a;

-- 단일뷰 데이터 수정, 추가, 삭제
# set sql_safe_updates = 0;
update v_tbl_a set col_2 = 'tbl_a 열 수정'
where col_1 = 1;

insert v_tbl_a values (3, 'tbl_a_3');

delete from v_tbl_a where col_1 = 3;

-- 복합 뷰 생성
create view v_tbl_a_b
as
	select
		a.col_1 as a_col_1,
        a.col_2 as a_col_2,
        b.col_2 as b_col_2
	from tbl_a as a
		inner join tbl_b as b on a.col_1 = b.col_1;

-- 복합 뷰 데이터 수정, 입력 불가

5️⃣스토어드 함수(Stored Function)와 커서(Cursor)

스토어드 함수 (Stored Function)

스토어드 함수는 특정 입력값을 받아 처리한 후, 하나의 값을 반환하는 SQL 객체입니다.

 

✅ 스토어드 프로시저와 함수의 차이점

• 프로시저(Procedure): 여러 개의 SQL 문을 실행하고, SELECT 문을 사용하여 여러 행의 데이터를 반환할 수 있습니다.
• 함수(Function): 단일 값을 반환하며, RETURN 문을 사용하여 하나의 데이터만 반환합니다.

-- 함수 생성 권환 부여
set global log_bin_trust_function_creators = 1;

use test;
drop function if exists user_sum;

-- 스토어드 함수 생성
delimiter &&
create function user_sum ( num_1 int, num_2 int)
	returns int
begin
	return num_1 + num_2;
end &&
delimiter ;

-- 함수 호출
select user_sum(1,5);
-- 함수 삭제
drop function user_sum;

커서 (Cursor)

커서는 데이터를 한 행씩 처리합니다. 여러 행을 처리할 때 유용하게 사용됩니다. 

drop procedure if exists do_cursor;

-- 커서 생성(커서는 프로시저 안에서만 사용 가능)
delimiter &&
create procedure do_cursor()
begin
	declare endOfRow boolean default false;
	-- 커서에 사용할 변수
	declare user_payment_id int; -- payment_id 를 저장할 변수
	declare user_amount decimal(10,2) default 0; -- amount 를 저장할 변수
	declare idCursor cursor for -- 커서 선언
	select payment_id from payment where staff_id = 1;
    
	-- 반복 조건 선언
	declare continue handler -- 행의 끝이면 endOfRow 변수에 TRUE 대입
	for not found set endOfRow = true;
    
    -- 커서 열기
	open idCursor;
    
	-- 반복 구문
	sum_loop: loop
		fetch idCursor into user_payment_id; -- 첫 번째 데이터 가져오기
        if endOfRow then
			leave sum_loop; -- 마지막 행이면 종료
		end if;

	-- 데이터 처리
    set user_amdo_cursorount = user_amount + (select amount from payment 
										where payment_id = user_payment_id);
    end loop sum_loop;
    
	-- 데이터 결과 반환
    select user_amount;
	-- 커서 닫기
	close idCursor;
end &&
delimiter ;

call do_cursor;

6️⃣ 트리거 (Trigger)

트리거는 특정 이벤트(INSERT, UPDATE, DELETE)가 발생할 때 자동으로 실행되는 SQL 프로시저입니다. 트리거를 사용하면 데이터 무결성을 유지하고, 자동으로 특정 작업을 수행할 수 있습니다.

트리거 유형

• 행 트리거(Row Trigger): 특정 행이 변경될 때마다 실행됩니다.
• 문장 트리거(Statement Trigger): 트리거가 설정된 테이블에서 트랜잭션이 발생할 때마다 한 번 실행됩니다.
• BEFORE 트리거: 트리거가 발생한 이벤트(INSERT, UPDATE, DELETE)가 실행되기 전에 동작합니다.
• AFTER 트리거: 트리거가 발생한 이벤트가 실행된 후 동작합니다.

use test;
create table tbl_trigger_1 (
col_1 int,
col_2 varchar(50)
);
create table tbl_trigger_2 (
col_1 int,
col_2 varchar(50)
);

insert into tbl_trigger_1 values(1, 'data 1');

-- update 발생 시 트리거 생성
delimiter &&
create trigger do_update_trigger
after update
on tbl_trigger_1
for each row

begin
	insert into tbl_trigger_2 values (old.col_1, old.col_2);
end &&
delimiter ;
# -> tbl_trigger_1 테이블에 변경이 발생하면 tbl_trigger_2 테이블에 변경 내역이 기록됨

set sql_safe_updates=0;
update tbl_trigger_1 set col_1=2, col_2 = 'change 1 to 2';

select * from tbl_trigger_2;
/*
	col_1	col_2
	  1    data 1
*/


select * from tbl_trigger_1;
/*
	col_1	col_2
	  2	 change 1 to 2
*/

---

이 글에서는 스토어드 프로시저, SQL 프로그래밍(IF, WHILE, CASE문), 인덱스, 뷰(View), 스토어드 함수와 커서 그리고 트리거에 대해 상세히 정리하였습니다.