[SQL] 스토어드 프로그램, 트리거(Trigger)

🍪 스토어드 프로그램(stored program)

- 데이터베이스에서 실행되는 프로그램 또는 함수로 일련의 SQL문을 포함하고 있는 데이터 베이스 객체

- 스토어드 프로그램은 주기억장치(메모리)에 저장된 명령어들의 연속으로 이루어진 프로그램을 의미

- 실행 가능한 형태의 프로그램

- 특정 작업이 자주 수행되어야 하거나 여러 곳에서 사용되어야 할 때 호출

 

1️⃣ 스토어드 프로시저(stored procedure)

- 데이터베이스 객체 중 하나로 데이터베이스에서 수행할 수 있는 일련의 SQL문과 제어문을 저장한 SQL 스토어드 프로그램을 의미

- 데이터 검색, 조작, 업데이트, 삭제 등 다양한 작업 수행

 

- 조건문 작성법

delimiter $$
create procedure proc_if()
begin
declare x int;
        declare y int default 5;
        set x = 10;
        
        if x > y then
select 'x는 y보다 큽니다.' as 결과;
else 
select 'x는 y보다 작거나 같습니다.' as 결과;
end if;
end $$
delimiter ;

/* 조건문 결과 확인 */
call proc_if();

 

2️⃣ 시스템 스토어드 프로시저

데이터베이스 시스템에 내장되어 있는 특수한 유형의 저장 프로그램

 

3️⃣ 사용자 정의 프로시저

- 자주 사용하는 SQL문을 프로시저로 생성하여 저장한 후 필요 시에 호출해서 사용

 

 

▶︎ 사용자 정의 프로시저에서 매개변수의 선언방식을 지정하는 방법

1. IN : 프로시저에 값을 전달하기 위한 입력 매개변수
2. OUT : 프로시저에 값을 반환하기 위한 출력 매개변수
3. INOUT : 입출력 모두를 위한 매개변수, 조회 결과를 호출자에게 반환

 

- 사용자 정의 프로시저 생성, 호출 (1)

delimiter $$
create procedure proc_고객정보()
begin
select *
    from 고객;
    
    select count(*) as 고객수
    from 고객;
end $$
delimiter ;

call proc_고객정보();

- 사용자 정의 프로시저 생성, 호출 (2)  : 매개변수 사용

delimiter $$
create procedure proc_도시고객정보
(
    in city varchar(50)
    )
begin
select *
    from 고객
    where 도시 = city;
    
    select count(*) as 고객수
    from 고객
    where 도시 = city;
end $$
delimiter ;

call proc_도시고객정보('부산광역시');

- 사용자 정의 프로시저 생성, 호출 (3) 

delimiter $$
create procedure proc_고객회사명_마일리지추가
(
in company varchar(50)
    ,in add_mileage int
    )
begin
select 고객번호, 고객회사명, 마일리지 as 변경전마일리지
    from 고객
    where 고객회사명 = company;
    
    update 고객
    set 마일리지 = 마일리지 + add_mileage
    where 고객회사명 = company;
    
    select 고객번호, 고객회사명, 마일리지 as 변경후마일리지
    from 고객
    where 고객회사명 = company;
end $$
delimiter ;

call proc_고객회사명_마일리지추가('굿모닝서울',500);

 

- (error) 문자셋이 맞지 않아 발생하는 에러
Error Code: 1267. Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '=' 0.0021 sec 

➡️ 해결 방법

alter table 고객 convert to character set utf8mb4 collate utf8mb4_general_ci;

- 사용자 정의 프로시저 생성, 호출 (4) + 조건문 

delimiter $$
create procedure proc_고객회사명_평균마일리지로변경
(
    in company varchar(50)
    )
begin
declare 평균마일리지 int;
    declare 보유마일리지 int;
    
    select 고객회사명, 마일리지 as 변경전마일리지
    from 고객
    where 고객회사명 = company;
    
    set 평균마일리지 = (select avg(마일리지) from 고객);
    set 보유마일리지 = (select 마일리지 from 고객 where 고객회사명 = company);
    
if(보유마일리지 > 평균마일리지) then
update 고객
        set 마일리지 = 마일리지 + 100
        where 고객회사명 = company;
else 
update 고객
set 마일리지 = 평균마일리지
        where 고객회사명 = company;
end if;
    
    select 고객회사명, 마일리지 as 변경후마일리지
    from 고객
    where 고객회사명 = company;
end $$
delimiter ;

call proc_고객회사명_평균마일리지로변경('굿모닝서울');

 

 

- 사용자 정의 프로시저 생성, 호출 (5) + 조건문 

delimiter $$
create procedure proc_고객등급
(
    in company varchar(50)
    ,out grade varchar(20)
    )
begin
declare 보유마일리지 int;
    
    select 마일리지
    into 보유마일리지
    from 고객
    where 고객회사명 = company;
    
    if 보유마일리지 > 100000 then
set grade = '최우수고객회사';
elseif 보유마일리지 > 50000 then
set grade = '우수고객회사';
else
set grade = '관심고객회사';
end if;
end $$
delimiter ;

 

call proc_고객등급('그린로더스', @그린로더스등급);
call proc_고객등급('오뚜락', @오뚜락등급);

select @그린로더스등급, @오뚜락등급;

 

- 사용자 정의 프로시저 생성, 호출 (6) 인상금액 계산 

delimiter $$
create procedure proc_인상금액
	(
    in increase_rate int
    ,inout price int
    )
begin
	set price = price * (1 + increase_rate / 100);
end $$
delimiter ;

set @금액 = 10000;
call proc_인상금액(10, @금액);
select @금액;
/* 한 번 더 인상 */
call proc_인상금액(10, @금액);
select @금액;

 

 

 

4️⃣  사용자 정의 함수

- 사용자 정의 함수 생성 

delimiter $$
create function func_금액(quantity int, price int)
returns int
begin
declare amount int;
    
    set amount = quantity * price;
    return amount;
end $$
delimiter ; 
select func_금액(100,4500);

select *, func_금액(주문수량, 단가) as 주문금액
from 주문세부;

- (error) function 생성 제약처리 에러 발생

Error Code: 1418. This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) 0.0016 sec

➡️ 해결 방법

show global variables like 'log_bin_trust_function_creators';
SET GLOBAL log_bin_trust_function_creators = 1;

 

출처 : http://www.mysqlkorea.com/gnuboard4/bbs/board.php?bo_table=community_03&wr_id=1965

---

트리거(Trigger) ⭐️

- 데이터베이스에서 데이터 삽입, 변경, 삭제와 같은 특정 이벤트 발생할 때마다 자동으로 실행되는 작업

 

💡 트리거를 사용해서 로그 남기는 이유

• 변경 이력 추적: 데이터베이스에서 발생하는 데이터 변경 작업(삽입, 수정, 삭제) 추적  ➡️ 데이터 변경의 원인과 시간을 추적하여 데이터의 변경 과정을 분석할 수 있다.
• 오류 분석 및 복구: 데이터 변경 작업 중에 발생한 오류를 파악하고, 문제가 발생한 시점의 데이터 상태를 복구
• 보안 강화: 데이터베이스의 보안을 강화하기 위해  민감한 데이터의 접근 및 변경 여부를 모니터링하고, 비정상적인 활동에 대한 조치를 취할  수 있다.

 

- 트리거 생성(1) : 로그 테이블에 정보 남기도록 함 (insert 됐을 때)

create table 제품로그
(
로그번호 int auto_increment primary key
,처리 varchar(10)
,내용 varchar(100)
,처리일 timestamp default current_timestamp
);

delimiter $$
create trigger trigger_제품추가로그
after insert on 제품
for each row
begin
insert into 제품로그(처리, 내용)
    values('insert', concat('제품번호 :', new.제품번호, '제품명 : ', new.제품명));
end $$
delimiter ;

- 제품 추가 후 트리거 확인

insert into 제품(제품번호, 제품명, 단가, 재고)
values(99, '레몬캔디', 2000, 10);

select * from 제품 where 제품번호 = 99;
select * from 제품로그;

- 트리거 생성(2): 로그 테이블에 정보 남기도록 함 (update 됐을 때)

delimiter $$
create trigger trigger_제품변경로그
after update on 제품
for each row
begin
if(new.단가 <> old.단가) then
insert into 제품로그(처리, 내용)
    values('update', concat('제품번호 :', old.제품번호, ' 단가 : ', old.단가, '->', new.단가 ));
    end if;
    
    if(new.재고 <> old.재고) then
    insert into 제품로그(처리, 내용)
    values('update', concat('제품번호 : ', old.제품번호, ' 단가 : ', old.재고, '->', new.재고));
    end if;
end $$
delimiter ;

- 제품 단가 변경 후 트리거 확인

update 제품
set 단가 = 2500
where 제품번호 = 99;

select * from 제품로그;

 

- 트리거 생성(3): 로그 테이블에 정보 남기도록 함 (delete 됐을 때)

delimiter $$
create trigger trigger_제품삭제로그
after delete on 제품
for each row
begin
insert into 제품로그(처리, 내용)
    values('delete', concat('제품번호 : ', old.제품번호, ' 제품명 : ', old.제품명));
end $$
delimiter ;

 

-  제품 삭제 후 트리거 확인

delete from 제품
where 제품번호 = 99;

select * from 제품로그;