SQL 고급 문법(4)

스토어드 프로시저

mysql에서 프로그래밍 기능에 필요할 때 사용하는 데이터베이스 개체

sql 프로그래밍은 기본적으로 스토어드 프로시저 안에서 만들어야 함

 

스토어드 프로시저의 구조

delimiter $$
create procedure 스토어드_프로시저_이름()
begin

end $$  -- 스토어드 프로시저 종료
delimiter ;  -- 종료 문자를 다시 세미콜론(;)으로 변경
call 스토어드_프로시저_이름()  -- 스토어드 프로시저 실행

 

if 문

조건문으로 가장 많이 사용되는 프로그래밍 문법 중 하나

 

if 문의 기본 형식

if <조건식> then
	sql문장들
end if;

'sql문장들'이 한 문장이라면 그 문장만 써도 되지만 두 문장 이상이 처리되어야 할 때는 begin~end로 묶어줘야 함

예시)

DROP PROCEDURE IF EXISTS ifProc;  -- 프로시저 이름 수정

DELIMITER $$

CREATE PROCEDURE ifProc()
BEGIN
    IF 100 = 100 THEN
        SELECT '100은 100과 같습니다.';
    END IF;
END $$

DELIMITER ;  -- 기본 구분자로 재설정

CALL ifProc();  -- 프로시저 이름 수정

 

if else 문

조건에 따라 다른 부분을 수행

예시)

DELIMITER $$

CREATE PROCEDURE ifProc2()
BEGIN
    DECLARE myNum INT;  -- 변수 선언
    SET myNum = 200;    -- 변수에 값 할당
    IF myNum = 100 THEN
        SELECT '100입니다.';
    ELSE
        SELECT '100이 아닙니다.';

 

if문의 활용

아이디가 APN(에이핑크)회원의 데뷔 일자가 5년이 넘었는지 확인하고 5년이 넘었으면 축하 메시지 출력 

DELIMITER $$

CREATE PROCEDURE ifProc3()
BEGIN
    DECLARE debutDate DATE;  -- 데뷔 일자
    DECLARE curDate DATE;    -- 오늘
    DECLARE days INT;        -- 활동한 일수

    SELECT debut_date INTO debutDate
    FROM market_db.member
    WHERE mem_id = 'APN';
        
    SET curDate = CURRENT_DATE(); -- 현재 날짜
    SET days = DATEDIFF(curDate, debutDate); -- 날짜의 차이, 일 단위
    
    IF (days / 365) >= 5 THEN -- 5년이 지났다면
        SELECT CONCAT('데뷔한 지 ', days, '일이나 지났습니다. 핑순이들 축하합니다.~');
    ELSE
        SELECT CONCAT('데뷔한 지 ', days, '일밖에 안되었네요. 핑순이들 화이팅~');
    END IF;
END $$

DELIMITER ;  -- 기본 구분자로 재설정

CALL ifProc3();

 

case 문

여러가지 조건 중에서 선택해야 하는 경우 case 문 사용해 조건 설정

case 
	when 조건1 then
		sql문장들1
	when 조건2 then
		sql문장들2
	when 조건3 then
		sql문장들3
	else
		sql문장들4
end case;

case와 end case 사이에는 여러 조건 삽입 가능

when 다음에 조건이 옴

조건이 여러개라면 when을 여러번 반복

모든 조건에 해당하지 않으면 마지막 else 부분 수행

에시)

drop procedure if exists caseProc;
delimiter $$
create procedure caseProc()
begin
	declare point int ;
    declare credit char(1);
    set point = 88 ;

	case
		when point >= 90 then
			set credit = 'A';
		when point >= 80 then
			set credit = 'B';
		when point >= 70 then
			set credit = 'C';
		when point >= 60 then
			set credit = 'D';
		else 
			set credit = 'F';
	end case;
    select concat('취득점수 ==>', point), concat('학점==>', credit);
end $$
delimiter ;
call caseProc();

 

 while 문

필요한 만큼 계속 같은 내용을 반복할 수 있음 

 

while문의 기본 형식

while <조건식> do
	sql 문장들
end while;

 

1에서 100까지 값을 모두 더하는 기능 while 문으로 구현

drop procedure if exists whileProc;
delimiter $$
create procedure whileProc()
begin 
	declare i int; -- 1에서 100까지 증가할 변수
    declare hap int; -- 더한 값을 누적할 변수
    set i =  1;
    set hap = 0;
    
    while (i <= 100) do
		set hap = hap + i;
        set i = i + 1;
	end while;
		select '1부터 100까지의 합 ==>', hap;
	end $$ 
    delimiter ;
    call whileProc();

 

while 문의 활용

iterate[레이블]: 지정한 레이블로 가서 계속 진행

leave[레이블]: 지정한 레이블을 빠져나감. 즉 while 문 종료

 

동적 sql

내용 변경이 필요할 때 동적 sql을 사용하면 변경되는 내용을 실시간으로 적용시켜 사용 가능

 

prepare 와 execute

prepare : sql문을 실행하지는 않고 미리 준비

execute : 준비한 sql문을 실행

deallocate prepare : 문장을 해제

예시)

use market_db;
prepare myQuery from 'select * from member where mem_id = "BLK"';
execute myQuery;
deallocate prepare myQuery;