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

* SQL 프로그래밍은 스토어드 프로시저 안에 만들어야함

 

1. 스토어드 프로시저 구조

2022.09.18 - [개발자 :-)/SQL] - [MySQL] 데이터베이스 개체(인덱스, 뷰, 스토어드 프로시저)

[MySQL] 데이터베이스 개체(인덱스, 뷰, 스토어드 프로시저)

DELIMITER $$       --구분자
CREATE PROCEDURE 스토어드_프로시저_이름()
BEGIN
	프로그래밍
END $$             --스토어드 프로시저 종료
DELIMITER;         --종료문자를 다시 세미콜론으로 변경

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

* Delimiter : 구분자

 MySQL에서는 SQL과 세미콜론(;)을 포함한 여러 문장들로 구성된 복합문인 Each stored Program을 

서버로 보내기위해 delimiter를 일시적으로 재정의 해주어야한다!

(Each stored Program : 트리거, Stored Procedure)

보통 $$를 많이 사용하고, \, @의 문자를 사용해도 되지만 다른 기호와 중복될 수 있으니 두개를 연속해서 사용해야한다.

 

 

2. IF 문

조건식이 참이라면 SQL문장들을 실행하고, 거짓이면 넘어감

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

 

3. IF ~ ELSE 문

DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
	DECLARE myNum INT;
    SET myNum = 200;
    IF myNum = 100 THEN
    	SELECT '100';
    ELSE
    	SELECT '100 아님';
    END IF;
END $$
DELIMITER;

* 예제

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 '5년 넘음~';
    ELSE
    	SELECT '5년도 안됨';
	END IF;
END $$
DELIMITER;

 

4. CASE 

2가지 이상의 다중분기처리가 가능

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

CASE 문의 활용

Q. 쇼핑몰에서 총 구매액에 따라 회원의 등급을 나누고 싶다면?

SELECT	B.mem_id
	, M.mem_name
        , SUM(price*amount) as "총 구매액"
        , CASE WHEN SUM(price*amount) > 100000 THEN "GOLD"
               WHEN SUM(price*amount) > 80000 THEN "SILVER"
               ELSE "NORMAL"
        END "회원등급"
FROM	buy B
	RIGHT OUTER JOIN member M
    ON	B.mem_id = M.mem_id
GROUP BY B.mem_id
ORDER BY SUM(price*amount) DESC;

 

5. WHILE 문

조건식이 참인 경우 반복한다.

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

DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
DECLARE i INT;
    DECLARE sum INT;
    SET i = 1;
    SET sum = 0;
    
    WHILE (i<=100) DO
    	SET sum = sum + i;
        SET i = i + 1;
    END WHILE;
    
    SELECT "1부터 100까지 합 : ", sum FROM DUAL;
END $$
DELIMITER ;

 

WHILE 문의 응용

ITERATE문과 LEAVE 문을 활용할 수 있다.

Q_1. 1부터 100까지 합에서 4의 배수는 더하기 싫다면?

Q_2. 합이 1000이 넘으면 반복문을 멈추고 싶다면?

DELIMITER $$
CREATE PROCEDURE whilePro2()
BEGIN	
	DECLARE i INT;
    DECLARE sum INT;
    SET i = 1;
    SET sum = 0;
    
    myWhile:
    WHILE( i<=100) DO
    	IF(i%4 = 0) THEN
        	SET i = i + 1;
            ITERATE myWhile;
        END IF;
        SET sum = sum + i;
        IF (sum > 1000) THEN
        	LEAVE myWhile;
        END IF;
        SET i = i + 1;
	END WHILE;
    
    SELECT '1부터 100까지 합(4의 배수 제외, 1000 넘으면 종료) : ', sum FROM DUAL;
END $$
DELIMITER;

소스코드에서 myWhile은 지정한 WHILE문의 label로 다시 반복문을 실행할 수 있도록

ITERATE myWhile; 하여 호출할 수 있다.

 

 

6. 동적 SQL

PREPARE와 EXECUTE

PREPARE는 SQL문을 실행하지 않고 미리준비해놓음.

EXECUTE는 준비한 SQL문을 실행함.

그리고 실행 후에는 DEALLOCATE PREPARE를 해주어야 한다!

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

쿼리를 실시간으로 변형시켜가며 사용할 수 있다~!

 

활용

출입문에 들어갈때 태그를 한다.

태그할 때 마다 날짜를 저장할 수 있게 한다면?

--테이블 구조
CREATE TABLE gate_table (id INT AUTO_INCREMENT PRIMARY KEY, entry_time DATETIME);

-- 동적SQL 활용
SET @curDate = CURRENT_TIMESTAMP()    --현재 날짜와 시간

PROPARE myQuery FROM 'INSERT INTO gate_table VALUES(NULL, ?)';
EXECUTE myQuery USING @curDate;
DEALLOCATE PREPARE myQuery;

SELECT * FROM gate_table;