[SQL] 스토어드 프로시저

목차

• IF 문
• IF ELSE 문
• CASE 문
• WHILE 문 
• 동적 SQL

 

스토어드 프로시저

스토어드 프로시저는 MySQL 에서 프로그래밍 기능이 필요할 때 사용하는 데이터베이스 개체이다.

SQL 프로그래밍은 기본적으로 스토어드 프로시저 안에 들어가야 한다.

 

스토어드 프로시저의 구조

일반적으로 구분 문자(DELIMITER)는 $$을 많이 사용하지만, /, &, @ 등을 사용해도 상관없다.

 

 

 

IF 문

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

 

IF 문의 기본 형식

IF <조건식> THEN
    SQL 문장들
END IF;

 

'SQL 문장들' 이 한 문장이라면 그 문장만 써도 되지만, 두 문장 이상 처리할 때는 BEGIN ~ END로 묶어줘야 한다.

 

예시

DELIMITER $$ 
CREATE PROCEDURE ifProc1()
BEGIN 
    IF 100 = 100 THEN
        SELECT '100은 100과 같습니다.';
	END IF;
END $$ -- 세미콜론으로는 SQL의 끝인지 프로시저의 끝인지 구별할 수 없어 구분자 사용
DELIMITER ;
CALL ifProc1(); -- CALL로 호출되면 ifProc1 이 실행

 

IF ~ ELSE 문

조건식이 참이라면 'SQL 문장들 1', 그렇지 않다면 'SQL 문장들 2'를 실행

 

예시

DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
    DECLARE myNum INT; --DECLARE 예약어를 사용해 myNum 변수 선언	
    SET myNum = 200; -- SET 예약어로 변수에 200 대입
    IF myNum = 100 THEN 
        SELECT '100 입니다.';
	ELSE 
        SELECT '100이 아닙니다.';
	END IF;
END $$
DELIMITER ;
CALL ifProc2;

 

 

IF 문의 활용

DELIMITER $$
CREATE PROCEDURE ifProc3()
BEGIN 
    DECLARE debutDate Date; -- 데뷔일자
    DECLARE curDate Date; -- 오늘일자
    DECLARE days INT; -- 활동한 일수
    
    SELECT debut_date INTO debutDate -- SELECT 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 '데뷔한 지' + days + '일 밖에 안되었네요. 핑순이들 화이팅~';
	END IF;
END $$
DELIMITER ;
CALL ifProc3;

 

MySQL에서 제공하는 날짜와 관련된 함수

• CURRENT_DATE( ): 오늘 날짜를 알려준다.
• CURRENT_TIMESTAMP( ): 오늘 날짜및 시간을 함께 알려준다.
• DATEDIFF(날짜1, 날짜2): 날짜 2부터 날짜1 까지 일수로 몇 일인지 알려준다.

 

CASE 문

여러 가지 조건 중에서 선택해야 하는 경우도 있다.

 

CASE 문의 기본 형식

CASE
    WHEN 조건 1 THEN
        SQL 문장들 1
    WHEN 조건 2 THEN
        SQL 문장들 2
    WHEN 조건 3 THEN
        SQL 문장들 3
    ELSE 
        SQL 문장들 4
END CASE;

 

예시

DELIMITER $$
CREATE PROCEDURE ifProc4()
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 ifProc4;

 

CASE 문의 활용

SELECT M.mem_id, M.mem_name, SUM(price * amount) "총 구매액",
    CASE 
        WHEN (SUM(price * amount) >= 1500) THEN '최우수 고객'
        WHEN (SUM(price * amount) >= 1000) THEN '우수 고객'
		WHEN (SUM(price * amount) >= 1) THEN '일반 고객'
        ELSE '유령 고객'
	END "회원 등급"
    FROM buy B
    RIGHT OUTER JOIN member M
    ON B.mem_id = M.mem_id
    GROUP BY M.mem_id
ORDER BY SUM(price * amount) DESC;

 

 

WHILE 문

WHILE 문은 조건식이 참인 동안에 'SQL 문장들'을 반복한다.

 

WHILE 문의 기본 형식

WHILE <조건식> DO
    SQL 문장들
END WHILE;

 

예시

DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN 
    DECLARE i INT;
    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문 종료

DELIMITER $$
CREATE PROCEDURE whileProc2()
BEGIN 
    DECLARE i INT;
    DECLARE hap INT;
    SET i = 1;
    SET hap = 0;
    
    myWhile:
    WHILE (i <= 100) DO
        IF(i % 4 = 0) THEN
            SET i = i + 1;
		    ITERATE myWhile; -- 지정한 lable 문으로 가서 계속 진행
		END IF;
        SET hap = hap + i;
        IF(hap > 1000) THEN
            LEAVE myWhile; -- 지정한 lable 문을 떠남. 즉, While 문 종료
		END IF;
        SET i = i + 1;
END WHILE;
SELECT '1부터 100까지의 합(4의 배수 제외)', '1000넘으면 종료 ==>', hap;
END $$
DELIMITER ;
CALL whileProc2();

 

 

 

동적 SQL

SQL 문은 내용이 고정되어 있는 경우가 대부분이다.

하지만 상황에 따라 내용 변경이 필요할 때 동적 SQL 을 사용하면 변경되는 내용을 실시간으로 적용시킬 수 있다.

 

PREPARE 와 EXECUTE 

• PREPARE는 SQL 문을 실행하지 않고, 미리 준비만 해놓는다.
• EXECUTE는 준비한 SQL 문을 실행한다. 
• 그리고 실행 후에는 DEALLOCATE PREPARE로 문장을 해제해주는 것이 바람직하다.

PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK"'; -- 실행 준비
EXECUTE myQuery; -- 실행 시점
DEALLOCATE PREPARE myQuery;

 

 

동적 SQL의 활용

PREPARE 문에서는 ? 로 향후에 입력될 값을 비워놓고, EXECUTE 에서 USING 으로 ?에 값을 전달할 수 있다.

그러면 실시간으로 필요한 값들을 전달해서 동적으로 SQL이 실행된다.

 

CREATE TABLE gate_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entry_time DATETIME
);

SET @curDate = CURRENT_TIMESTAMP(); -- 현재 날짜와 시간을 변수에

PREPARE myQuery FROM 'INSERT INTO gate_table VALUES (NULL, ?)'; -- ?을 사용해서 entry_time 값 대기
EXECUTE myQuery USING @curDate; -- 값을 변수에 넣고 실행
DEALLOCATE PREPARE myQuery;

SELECT * FROM gate_table;

 

 

 

해당 포스팅의 글과 내용은 우재남 저자님의 혼자 공부하는 SQL 책을 재구성하여 만들었습니다.

좀 더 자세한 내용이 필요하시다면 '혼자 공부하는 SQL 책'을 구매해주세요

우재남 저자님 카페: https://cafe.naver.com/thisisMySQL

이것이MySQL이다 : 네이버 카페