SQL 프로그래밍

 변수 지정이나 흐름제어 함수 등에서 예상할 수 있었듯이 SQL에서도 C언어나 JAVA 같은 프로그래밍 언어처럼 코드를 짜서 프로그램을 만들 수 있다. SQL 프로그래밍은 앞으로 다루게 될 스토어드 프로시저, 스토어드 함수, 커서, 트리거 등과 같은 기술의 밑바탕이 되니 정리를 하고 넘어가고자 한다.

 

 가장 먼저 기본적으로 스토어드 프로시저를 만드는 방법을 알고있어야 한다. DELIMITER $$ ... END $$로 코딩하는 부분을 묶어주는 것이다. 여기서 세미콜론(;)이 아닌 달러 표시($$)로 시작과 끝을 구분하는 이유는 코딩하는 부분에서도 세미콜론을 사용하기에 시작과 종료 지점을 명확하게 구분하기 위함이다 ($$ 대신 //, @@, &&과 같은 구분자를 사용해도 된다). 기초 틀이니 코드를 통해 익혀보자.

 

스토어드 프로시저

DELIMITER $$
CREATE PROCEDURE 스토어드 프로시저 이름()
BEGIN
/*

		SQL 프로그래밍 코딩
        
*/
END $$
DELIMITER ;

CALL 스토어드 프로시저 이름() ; -- 위에서 코딩한 스토어드 프로시저를 실행

  간단하게 DELIMITER 두 번으로 스토어드 프로시저를 만드는 줄을 구분해주고 CREATE PROCEDURE...로 프로시저 이름 설정, BEGIN...END$$로 코딩의 시작과 끝을 알리는 것이다. 이렇게 설정한 프로시저는 CALL 프로시저이름()을 통해 호출할 수 있는 것이다. 이제 실제 프로그래밍 실습을 통해 사용법을 익히자.

 

IF...ELSE

-- IF...ELSE (이중 분기)
/* 기본 틀
IF <Boolean Expression: 부울 표현식> THEN
		SQL문 1
ELSE
		SQL문 2
END IF ;
*/

USE sqldb ;
DROP PROCEDURE IF EXISTS ifProc ; -- 기존에 만든 적이 있다면 삭제
DELIMITER $$
CREATE PROCEDURE ifProc()
BEGIN
	DECLARE var1 INT ; -- var1 변수 선언
    SET var1 = 100 ; -- 변수에 값 대입
    
    IF var1 = 100 THEN
		SELECT '100입니다.' ;
	ELSE
		SELECT '100이 아닙니다.' ;
	END IF ;
END $$
DELIMITER ;

CALL ifProc() ;

-- emloyees db에서 직원번호 10001번의 직원이 입사일이 5년이 넘었는지 확인하자.

USE employees ;
DROP PROCEDURE IF EXISTS ifProc2 ;

DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
	DECLARE hireDATE DATE ; -- 입사일 변수
    DECLARE curDATE DATE ; 	-- 오늘 날짜 변수
    DECLARE days INT ;		-- 근무일수 변수
    
    SELECT hire_date INTO hireDATE -- hire_date 열의 결과를 hireDATE에 대입
		FROM employees.employees
		WHERE emp_no = 10001 ;
	
    SET curDATE = CURRENT_DATE() ; -- 현재 날짜를 구하는 함수를 curDATE에 대입
    SET days = DATEDIFF(curDATE, hireDATE) ; -- 날짜의 차이, 일 단위로
    
    IF (days/365) >= 5 THEN
			SELECT CONCAT('입사한지 ', days, '일이나 지났습니다. 축하합니다!') ;
	ELSE
			SELECT '입사한지' + days + '일밖에 안 되었네요. 열심히 일하세요.' ;
	END IF ;
END $$
DELIMITER ;

CALL ifProc2() ;

 위 실습은 부울 표현식(Boolean Expression)의 조건에서 참거짓을 판단하여 두 가지 영역 중에 해당하는 결과값을 출력하는 구조이기에 '이중 분기'라고 칭한다. 

 

CASE

-- CASE (다중 분기)

-- IF..ELSE로 학점을 점수대 별로 구분하는 프로그램을 만들자.

DROP PROCEDURE IF EXISTS ifProc3 ;
DELIMITER $$
CREATE PROCEDURE ifProc3()
BEGIN
	DECLARE point INT ;
    DECLARE credit CHAR(1) ;
    SET point = 77 ;
    
    IF point >= 90 THEN
			SET credit = 'A' ;
	ELSEIF point >= 80 THEN
			SET credit = 'B' ;
	ELSEIF point >= 70 THEN
			SET credit = 'C' ;
	ELSEIF point >= 60 THEN
			SET credit = 'D' ;
	ELSE
			SET credit = 'F' ;
	END IF ; -- 이처럼 3개 이상의 구역이 존재하는 것을 다중 분기라고 한다.
    SELECT CONCAT('취득점수==>', point), CONCAT('학점==>', credit) ;
END $$
DELIMITER ;

CALL ifProc3() ; 

-- 다중 분기에서 IF...ELSEIF...ELSE문은 CASE문으로 대체가 가능하다.

DROP PROCEDURE IF EXISTS caseProc ;
DELIMITER $$
CREATE PROCEDURE caseProc()
BEGIN
	DECLARE point INT ;
    DECLARE credit CHAR(1) ;
    SET point = 77 ;

	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' ; -- 조건에 맞는 WHEN 문장이 여러개더라도 먼저 통과한 조건문으로 처리됨
	END CASE ;
    SELECT CONCAT('취득점수==>', point), CONCAT('학점==>', credit) ;
END $$
DELIMITER ;

CALL caseProc() ;

/* 초기화한 sqldb에서 구매액이 1500원 이상인 고객은 '최우수 고객',
	1000원 이상인 고객은 '우수 고객', 1원 이상인 고객은 '일반 고객',
    아무런 기록이 없으면 '유령 고객'이라는 결과를 받는 CASE문을 만들어보자 */
    
USE sqldb ;
SELECT userID, SUM(price*amount) AS '총 구매액'
	FROM buytbl
    GROUP BY userID
    ORDER BY SUM(price*amount) DESC ; -- 총 구매액이 높은 순서대로 정렬해서 시각화한다.

-- 사용자 이름도 보여주기 위해 조인을 이용하자.

SELECT B.userID, U.name, SUM(price*amount) AS '총 구매액'
	FROM buytbl B
		INNER JOIN usertbl U
			ON B.userID = U.userID
    GROUP BY B.userID, U.name
    ORDER BY SUM(price*amount) DESC ;

-- 구매하지 않은 이용자까지도 나타내보자.

SELECT U.userID, U.name, SUM(price*amount) AS '총 구매액'
	FROM buytbl B
		RIGHT OUTER JOIN usertbl U
			ON B.userID = U.userID
    GROUP BY U.userID, U.name
    ORDER BY SUM(price*amount) DESC ;

-- 이제 처음에 목표했던 결과를 CASE문을 사용해 나타내보자.

SELECT U.userID, U.name, SUM(price*amount) AS '총 구매액',
		CASE
			WHEN (SUM(price*amount) >= 1500) THEN '최우수 고객'
			WHEN (SUM(price*amount) >= 1000) THEN '우수 고객'
			WHEN (SUM(price*amount) >= 1) THEN '일반 고객'
			ELSE '유령고객'
		END AS '고객 등급'
    FROM buytbl B
		RIGHT OUTER JOIN usertbl U
			ON B.userID = U.userID
    GROUP BY U.userID, U.name
    ORDER BY SUM(price*amount) DESC ;

 주석에서도 설명했듯이 '다중 분기'의 활용법도 알아봤다. 이 CASE문은 SELECT문에서 더 많이 사용된다. 쉽게 생각하자면 엑셀에는 범위들을 설정하고 그 범위에 따라 특정 동작을 수행하도록 하는 함수기능이 있는데 이와 비슷하다고 보면 된다. 출력값을 얻기위해 최종 SQL문을 바로 처음부터 작성하는 것보다는 마지막 실습처럼 차근차근 단계별로 쌓아올리는 방법을 이용하면 더 쉽고 간편하게 코드를 완성할 수 있다.

 

WHILE, ITERATE/LEAVE

/* 기본 형식
WHILE <부울 식> DO
	SQL 명령문들 ...
END WHILE ;
*/

-- 1부터 100까지 값을 모두 더하는 기능을 만들어보자.

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 hap ;
END $$
DELIMITER ;

CALL whileProc() ;

-- 1부터 100까지의 합계에서 7의 배수를 제외하고 합계가 1000이 넘으면 합산을 멈추는 프로그램으로 수정해보자.

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 -- WHILE문에 label을 지정한다.
			IF (i%7 = 0) THEN
				SET i = i + 1 ;
                ITERATE myWhile ; -- 지정한 label문으로 돌아가서 계속하라는 의미.
			END IF ;
            
				SET hap = hap + i ;
			
            IF (hap > 1000) THEN
				LEAVE myWhile ; -- 지정한 label문을 탈출한다. (종료 명령어)
			END IF ;
            
            SET i = i + 1 ;
		END WHILE ;
        
        SELECT hap ;
END $$
DELIMITER ;

CALL whileProc2() ;

-- 1부터 1000까지의 숫자 중에서 3의 배수와 8의 배수만 더하는 스토어드 프로시저를 만들어보자.

DROP PROCEDURE IF EXISTS whileProc3 ;
DELIMITER $$
CREATE PROCEDURE whileProc3()
BEGIN
	DECLARE i INT ;		-- 1부터 1000까지 증가하는 변수
    DECLARE stk INT ; 	-- 누적 합계
    SET i = 1 ;
    SET stk = 0 ;
    
		WHILE (i <= 1000) DO
			IF (i%3 = 0) OR (i%8 = 0) THEN
				SET stk = stk + i ;
			END IF ;
        
				SET i = i + 1 ;
        
        END WHILE ;
        
        SELECT stk ;
END $$
DELIMITER ;
CALL whileProc3() ;