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

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

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

 

스토어드 프로시저 구조

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름()
BEGIN
	-- 이 공간에 코딩
END $$ -- 스토어드 프로시저 종료
DELIMITER ; -- 종료 문자를 다시 세미콜론(;)으로 변경
CALL 스포어드_프로시저_이름(); -- 스토어드 프로시저 실행

-> 일반적으로 종료 문자(DELIMITER)를 $$를 많이 쓰고, 변경해도 상관없다. 다른 기호와 중복을 피하기 위해 기호 2개를 연속해서 사용한다.

 

IF 문

IF문 형식

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

'SQL 문장들'이 한 문장이면 그대로 써도 되지만, 여러 문장인 경우 BEGIN~END로 묶어줘야 한다.

 

예제

DROP PROCEDURE IF EXISTS ifProc1;
DELIMITER $$
CREATE PROCEDURE ifProc1()
BEGIN
	IF 100 = 100 THEN
		SELECT '100은 100과 같습니다.';
	END IF;
END $$ -- 스토어드 프로시저 종료
DELIMITER ; -- 종료 문자를 다시 세미콜론(;)으로 변경
CALL ifProc1(); -- 스토어드 프로시저 실행

• DELIMITER $$ : 세미콜론(;)으로는 SQL의 끝인지, 스토어드 프로시저의 끝인지 구별할 수 없어 $$ 사용함.

 

실행 결과

 

IF ~ ELSE 문

DROP PROCEDURE IF EXISTS ifProc2;
DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
    DECLARE myNum INT;
    SET myNum = 200;
    IF myNum = 100 THEN
        SELECT '100입니다.';
    ELSE
        SELECT '100이 아닙니다.';
    END IF;
END $$
DELIMITER ;
CALL ifProc2();

 

IF 활용

아이디가 'APN'인 회원의 데뷔 일자가 5년이 넘었는지 확인해보고 5년이 넘었으면 축하 메시지를 출력.

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 : 기존 SELECT와 달리 INTO가 붙으면 결과를 변수에 저장한다.

CURRENT_DATE() : 현재 날짜를 반환한다.

DATEDIFF() : 데뷔 일자부터 현재 날짜까지 일수를 days에 저장한다.

 

실행 결과

 

CASE 문

형식

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

 

90점 이상은 A, 80점 이상은 B, 70점 이상은 C, 60점 이상은 D, 60점 미만은 F로 나누어보자.

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

 

실행 결과

 

CASE문의 활용

회원들의 총 구매액을 계산해서 회원의 등급을 아래와 같이 나누고자 한다.

총 구매액	회원 등급
1500 이상	최우수 고객
1000 ~ 1499	우수 고객
1 ~ 999	일반 고객
0 이하 (구매한적 없음)	유령 고객

 

일단 GROUP BY를 이용해 구매 테이블(buy)에서 회원별로 총구매액을 구한다.

SELECT mem_id, SUM(price*amount) "총구매액" FROM buy GROUP BY mem_id;

 

추가로, 총구매액을 내림차순으로 정렬하자.

SELECT mem_id, SUM(price*amount) "총구매액" FROM buy GROUP BY mem_id ORDER BY SUM(price*amount) DESC;

 

이번에는 회원의 이름도 추가해보자. 회원의 이름은 회원 테이블(member)에 있으므로 구매 테이블(buy)와 조인해야 함.

SELECT B.mem_id, M.mem_name, SUM(price*amount) "총구매액" FROM buy B 
    INNER JOIN member M
    ON B.mem_id = M.mem_id
GROUP BY B.mem_id
ORDER BY SUM(price*amount) DESC;

 

추가로, 구매하지 않은 회원의 아이디와 이름도 출력해보자. 외부 조인을 사용해야 한다.

구매한 적이 없어도 회원 테이블에 있는 회원은 모두 출력해야하므로 INNER JOIN을 RIGHT OUTER JOIN으로 변경.

SELECT M.mem_id, M.mem_name, SUM(price*amount) "총구매액" 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;

 

최종적으로 총 구매액에 따라 회원 등급을 구분해보자.

아래의 CASE문을 이용해야한다.

CASE
    WHEN (총구매액 >= 1500) THEN '최우수고객'
    WHEN (총구매액 >=1000) THEN '우수고객'
    WHEN (총구매액 >= 1 ) THEN '일반고객'
    ELSE '유령고객'
END

 

위의 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 <조건식> 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문의 응용

1에서 100까지의 합계를 구할 때 4의 배수를 제외시키려면?

숫자를 더하는 중간에 1000이 넘으면 더하는 것을 그만두고 싶으면?

ITERATE 문과 LEAVE문을 이용해야 한다.

 

ITERATE [레이블] : 지정한 레이블로 가서 계속 진행한다. ( CONTINUE와 유사 )

LEAVE [레이블] : 지정한 레이블을 빠져나간다. 즉 WHILE문 종료 ( BREAK와 유사 )

DROP PROCEDURE IF EXISTS whileProc;
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
    DECLARE i INT;
    DECLARE hap INT;
    SET i = 1;
    SET hap = 0;
    
    myWhile: -- WHILE문을 myWhile이라는 레이블로 설정.
    WHILE (i <= 100) DO
        IF (i%4 = 0) THEN
            SET i = i + 1;
            ITERATE myWhile; -- myWhile 레이블로 돌아가서 진행
        END IF;
        SET hap = hap + i;
        IF (hap > 1000) THEN
            LEAVE myWhile; -- myWhile 레이블 탈출
        END IF;
        SET i = i + 1;
	END WHILE;
SELECT '1부터 100까지의 합(4의 배수 제외 + 1000 넘으면 종료)', hap;
END $$
DELIMITER ;
CALL whileProc();

 

모든 내용은 "혼자 공부하는 SQL" 교재를 통해 학습한 후, 정리한 글입니다.