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

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

* 스토어드 프로시저의 기본 구조

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름()
BEGIN
	이 부분에 SQL 프로그래밍 코딩
END $$
DELIMITER;
CALL 스토어드_프로시저_이름();

- 스토어드 프로시저는 DELIMITER $$ ~ END $$ 안에 작성하고 CALL로 호출

- 구분 문자로 $$를 많이 사용하지만, /, &, @ 등을 사용해도 상관없음. 다만, 다른 기호와 중복될 수 있으므로 기호 2개를 연속으로 사용하는 것이 좋음.

 

 

1. IF 문

 

1) IF문

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

* SQL문이 두 문장 이상 처리되어야 할 때는 BEGIN ~ END로 묶어줘야 하므로, 한 문장일 때도 써버릇 하자.

* 같다는 의미는 SQL에서는 =를 사용한다. 그리고 SELECT 뒤에 문자는 다른 언어의 print() 처럼 그냥 출력됨.

 

-- 예시
DROP PROCEDURE IF EXISTS ifProc1;
DELIMITER $$
CREATE PROCEDURE ifProc1()
BEGIN
	IF 100 = 100 THEN
		SELECT '100은 100과 같습니다.';
	END IF;
END $$
DELIMITER ;
CALL ifProc1();

 

2) IF ~ ELSE 문: 조건에 따라 다른 부분을 수행함

-- 예시
DROP PROCEDURE IF EXISTS ifProc2;
DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
	DECLARE myNum INT;
    SET myNum = 200;
	IF myNum = 100 THEN
		SELECT '100은 100과 같습니다.';
	ELSE
		SELECT '100SMS DKSLQSLEK.';
	END IF;
END $$
DELIMITER ;
CALL ifProc2();

 

3) IF 문의 활용

-- 예시
DROP PROCEDURE IF EXISTS ifProc3;
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
    	SELECT CONCAT('데뷔한 지', days, '일이나 지났습니다. 핑순이들 축하합니다!');
    ELSE
    	SELECT '데뷔한 지' + days + '일밖에 안되었네요. 핑순이들 화이팅~';
   	END IF;
END $$
DELIMITER ;
CALL ifProc3();

- INTO 변수가 붙으면 결과를 변수에 저장함

- CURRENT_DATE() 함수로 현재 날짜를 curDATE에 저장

- DATEDIFF() 함수로 데뷔 일자부터 현재 날짜까지 일수를 days에 저장

 

※ 날짜 관련 함수 ※

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

 

 

2. CASE 문

 

- CASE 문은 다중 분기로, 여러 조건에 따라 다른 SQL을 실행시킬 수 있음.

- CASE 문의 기본 형식

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

* GROUP BY를 이용해 회원별로 총 구매액을 구할 수 있음

 

★ ★ ★ ★ ★ 이거 코테에 많이 나오는 스타일 ★ ★ ★ ★ ★

-- 예시
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;

 

 

3. WHILE 문

 

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

- WHILE 문의 형식

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

 

- 특정 조건에서 종료하고 싶다면 아래의 문을 활용할 수 있음

* ITERATE[레이블] : 지정한 레이블로 가서 계속 진행 ( = CONTINUE)

* LEAVE[레이블] : 지정한 레이블을 빠져나감. WHILE 문이 종료 ( = BREAK)

DROP PROCEDURE IF EXISTS whileProc2;
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;
        END IF;
        SET i = i+1;
    END WHILE;

    SELECT '1부터 100까지의 합(4의 배수 제외), 1000 넘으면 종료 ==>', hap;
END $$
DELIMITER ;
CALL whileProc2();

 

 

4. 동적 SQL

 

PREPARE는 SQL 문을 실행하지는 않고 미리 준비만 해놓고, EXECUTE는 준비한 SQL 문을 실행함.

실행 후에는 DEALLOCATE PREPARE로 문장을 해제해주는 것이 바람직함

=> 이렇게 미리 SQL을 준비한 후 나중에 실행하는 것을 동적 SQL이라고 부른다 !!

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

 

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

- 일반 SQL에서 변수는 @변수명으로 지정하는데 별도의 선언은 따로 없어도 됨. 다만, 스토어드 프로시저에서는 변수는 DECLARE로 선언한 후에 사용해야 함.

DROP TABLE IF EXISTS gate_table;
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;