[MySQL] 8. SQL 프로그래밍(IF문, CASE문, WHILE문, 동적SQL)

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

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

 

스토어드 프로시저는 다음과 같은 형식을 갖는다.

DELIMITER $$
CREATE PROCEDURE <스토어드 프로시저 이름>
BEGIN
    <SQL 프로그래밍 코드 부분>
END $$
DELIMITER ;

CALL <스토어드 프로시저 이름>();

---

IF 문은 조건식이 참이라면 'SQL 문장들'을 실행하고, 그렇지 않으면 그냥 넘어간다.

 

기본 IF 문의 형식은 다음과 같다.

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

 

여기서 'SQL 문장들'이 한 문장이라면 그 문장만 써도 되지만, 두 문장 이상이 처리되어야 할 때에는 다음과 같이 BEGIN ~ END로 묶어주어야 한다.

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

 

IF 문 예제

USE market_db;

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

CALL ifProc1();

 

Result

 

 

IF ~ ELSE 문 예제

스토어드 프로시저 안에서 변수를 선언할 경우 '@'를 사용하지 않고 DECLARE 명령어를 사용한다.

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

CALL ifProc2();

 

Result

 

 

DELIMITER $$
CREATE PROCEDURE ifProc3()
BEGIN
    DECLARE debutDate DATE; -- 데뷔일
    DECLARE curDate DATE; -- 오늘
    DECLARE days INT; -- 활동한 일수
    
    SELECT debut_date INTO debutDate -- debut_date 결과를 변수에 대입
        FROM member
        WHERE mem_id = 'APN';
        
    SET curDate = CURRENT_DATE(); -- 현재 날짜
    SET days = DATEDIFF(curDate, debutDate); -- 날짜의 차이, 일 단위
    
    IF (days / 365) >= 5 THEN
        SELECT CONCAT('데뷔한지 ', days, '일이나 지났습니다.');
    ELSE
        SELECT '데뷔한지 ' + days + '일 밖에 안되었습니다';
	END IF;
END $$
DELIMITER ;

CALL ifProc3();

 

Result

---

CASE 문은 분기가 2가지 이상의 여러 가지일 경우 사용하는 명령어이다.

IF 문은 참 또는 거짓 두 가지만 있기 때문에 2중 분기라는 용어를 사용하지만 CASE 문은 다중 분기라고 부른다.

 

CASE 문의 형식은 다음과 같다.

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

 

CASE 문의 예

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();

 

Result

 

 

SELECT M.mem_id, M.mem_name, SUM(B.price * B.amount) '총구매액',
        CASE
            WHEN SUM(price * amount) >= 1500 THEN '최우수고객'
            WHEN SUM(price * amount) >= 1500 THEN '우수고객'
            WHEN SUM(price * amount) >= 1500 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(B.price * B.amount) DESC ;

 

Result

---

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

 

WHILE 문의 형식은 다음과 같다.

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

 

WHILE 문 활용 예

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();

 

Result

 

 

WHILE 문에서 ITERATE와 LEAVE 문의 활용

 

ITERATE : 지정한 레이블로 이동하여 계속 진행

LEAVE : 지정한 레이블을 빠져나감. 즉, WHILE 문을 종료시킴

 

DELIMITER $$
CREATE PROCEDURE whileProc2()
BEGIN
    DECLARE i INT; -- 1에서 100까지 증가할 변수
    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; -- 지정한 레이블 문으로 가서 계속 진행
        END IF;
        SET hap = hap + i;
        IF (hap > 1000) THEN
            LEAVE myWhile; -- 지정한 레이블 문을 떠남. 즉, WHILE 문 종료
        END IF;
        SET i = i + 1;        
    END WHILE;
    
    SELECT '1부터 100까지의 합(4의 배수 제외), 1000 넘으면 종료 ==> ', hap;
END $$
DELIMITER ;

CALL whileProc2();

 

Result

---

SQL 문은 내용이 고정되어 있는 경우가 대부분이다. 하지만 상황에 따라 내용 변경이 필요할 때 동적 SQL을 사용하면 변경되는 내용을 실시간으로 적용시켜 사용할 수 있다.

 

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

 

아래 구문은 동적 SQL의 간단한 예제이다.

use market_db;

PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK"';
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery;

 

Result

 

 

동적 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, ?)';
EXECUTE myQuery USING @curDate;
DEALLOCATE PREPARE myQuery;

SELECT * FROM gate_table;

 

Result