스토어드 프로그램

SQL 스토어드 프로그램: 데이터베이스에서 실행된는 일련의 SQL문을 포함하고 있는데이터베이스 객체

프로시저 호출
call 프로시저명;

프로시적 삭제
drop procedure 프로시저명;

delimiter $$
create procedure 프로시저명
    (
     [in/out/inout 매개변수]
    )
begin
    실행코드
end $$
delimiter ;

in: 프로시저에 외부 입력값을 전달하는 데 사용
out: 프로시저가 실행을 완료한 후 외부로 결과값을 전달하는 데 사용
inout: 외부에서 값을 전달하고 프로시저가 실행을 완료한 후 수정된 결과값을 외부로 반환

조건문

IF

if 조건1 then
    조건1이 참일 때 실행할 코드
[elseif 조건2 then
    조건2가 참일 때 실행할 코드][else      모든 조건이 참이 아닐 때 실행할 코드]
end if;

10과 5의 크기를 비교하는 프로시저

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 ;

CASE

case
   when 조건1 then
       조건1이 참일 때 실행할 코드
   [when 조건2 then
        조건2가 참일 때 실행할 코드]
   [else
       모든 조건이 참이 아닐 때 실행 할 코드]
end case;

delimiter $$
create procedure proc_case()
	begin
		declare x int default 10;
        declare y int;
        set y = x mod 2;
        
		case 
			when y=0 then
				select 'x는 짝수입니다' as 결과;
			else
				select 'x는 홀수입니다' as 결과;
		end case;
	end $$
delimiter ;

반복문

WHILE

조건이 참인 동안 반복적으로 코드 실행

while 조건 do
    반복적으로 실행할 코드
end while;

delimiter $$
create procedure proc_while()
	begin
		declare x int default 0;
        declare y int default 0;
        
        while x<10 do
			set x=x+1;
            set y=y+x;
		end while;
        select x,y;
	end $$
delimiter ;

LOOP

레이블명: loop
    반복적으로 실행할 코드
    if 조건 then
        leave 레이블명;
    end if;
end loop;

delimiter $$
create procedure proc_loop()
	begin
		declare x int default 0;
        declare y int default 0;
        
        loop_sum:loop
			set x=x+1;
            set y=y+x;
            if x>10 then
				leave loop_sum;
			end if;
            select x,y;
		end loop;
	end $$
delimiter ;

REPEAT

조건이 참이 될 때까지 코드를 실행

repeat
    반복적으로 실행할 코드
until 조건;

delimiter $$
create procedure proc_repeat()
begin
	declare x int default 0;
    declare y int default 0;
    
    repeat 
		set x=x+1;
        set y=y+x;
	until x>=10 end repeat;
    select x,y;
end $$
delimiter ;

SQL 스토어드 프로시저: 데이터베이스에서 수행할 수 있는 일련의 SQL문과 제어문을 저장한 SQL 스토어드 프로그램

고객회사명을 입력하면 고객의 보유 마일리지에 따라 등급을 보이는 프로시저

delimiter $$
create procedure proc_고객등급
	(
    in company varchar(50),
    out grade varchar(50)
    )
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_고객등급('그린로더스', @그린로더스등급);
select @그린로더스등급;

사용자 정의 함수

delimiter $$
create function 함수명(매개변수)
returns 반환형식
deterministic/not deterministic
begin
    실행코드
    return 반환값;
end $$
delimiter ;

deterministic: 함수가 같은 입력에 대해 항상 동일한 결과 반환 ex) 연산
not deterministic: 함수가 같은 입력에 대해 매번 다른 결과를 반환할 수 있다 ex) now()

수량과 단가를 입력해 두 수를 곱한 값을 반환하는 함수

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

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

트리거

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

delimiter $$
create trigger 트리거명
before/after insert/update/delete on 테이블명
for each row
begin
    실행코드
end $$
delimiter ;

트리거 본문 내에서 old, new 사용하면 트리거의 영향을 받는 행의 컬럼에 접근할 수 있다
insert 트리거에서는 new.컬럼명만 사용가능
delete 트리거에서는 old.컬럼명만 사용가능
update 트리거에서는 old.컬럼명을 사용하면 변경되기 전 컬럼 값을 참조할 수 있고, new.컬럼명을 사용하면 변경 후의 값을 참조 가능

제품 테이블에서 단가나 재고가 변경되면 변경된 사항을 제품로그 테이블에 저장하는 트리거

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 ;