[혼공S] CH4-(3). SQL 프로그래밍

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

스토어드 프로시져의 기본 구조는 아래와 같다.

DELIMITER $$
CREATE PROCEDURE
BEGIN
   -- SQL 프로그래밍 코딩
END $$
DELIMITER ; 
CALL 프로시져 이름 ;

즉, DELIMITER $$ ~ END $$ 안에 작성하고 CALL로 호출하는 것이다.

 

IF 문

IF문은 조건문으로 가장 많이 사용되는 프로그래밍 문법 중 하나이다.

 

IF 문의 기본 형식

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

'SQL 문장들'이 한 문장이면 그 문장만 쓰면 되지만, 두 문장 이상이면 BEGIN~END로 묶어줘야 한다.

-- produdure를 만들기 전에 만약 존재하면 없애주기. 
DROP procedure IF EXISTS ifProc1 ; 

-- stored procedure 만들기.
DELIMITER $$  
CREATE PROCEDURE ifProc1() -- ifProc 이라는 프로시져 만들기
BEGIN  -- 
   IF 100 = 100 THEN 
      SELECT '100은 100과 같습니다.' ; 
   END IF ; 
END $$
DELIMITER ;

CALL ifProc1() ;

 

IF ~ ELSE 문

IF ~ ELSE 문은 조건에 따라 다른 부분을 수행한다. 조건식이 참이면 'SQL문장들1'을 수행하고, 그렇지 않으면 'SQL문장들2'를 수행한다.

# IF ELSE 문
-- ifProc이 존재하면 제거하기  
DROP PROCEDURE IF EXISTS ifProc2 ; 

-- if else문을 이용한 stored procedure 만들기
DELIMITER $$
CREATE PROCEDURE ifProc2() 
BEGIN 
   DECLARE myNum INT ; -- DECLARE를 이용하여 myNum 변수를 선언.
   SET myNum = 200 ; -- SET을 이용하여 변수에 값을 할당. 
   IF myNum = 100 THEN SELECT '100입니다.' ;
   ELSE SELECT '100이 아닙니다.' ; 
   END IF ; 
END $$
DELIMITER ; 

CALL ifProc2() ;

IF 문의 활용

기존 테이블인 member테이블과 IF문을 활용하여, 

"아이디가 APN(에이핑크)인 회원의 데뷔일지가 5년이 넘었는지를 확인해보고, 넘었으면 축하메세지를 보내"는 쿼리문을 작성해보자.

# 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' ;  -- debutDate 변수에 값 할당 (member 테이블에서 찾아서)
      
   SET curDate = CURRENT_DATE() ; -- curDate에 현재날짜 값 할당
   SET days = DATEDIFF(curDate, debutDate)  ; -- days에 날짜 차이값 할당 	
   
   IF (days / 365) >= 5 THEN 
      SELECT CONCAT('데뷔한 지', days, '일이나 지났습니다. 핑순이들 축하합니다!') ;  
   ELSE 
      SELECT '데뷔한 지 ' + days + '일밖에 안되었네요. 핑순이들 파이팅~!' ; 
   END IF ; 
END $$ 
DELIMITER ; 

CALL ifProc3() ;

 

※ 날짜 관련 함수

# 날짜 관련 함수
SELECT CURRENT_DATE() ; -- 오늘 날짜 출력
SELECT CURRENT_TIMESTAMP() ; -- 오늘 날짜 및 시간 출력
SELECT DATEDIFF(날짜1, 날짜2) -- 두 날짜 간 차이 일수를 출력

 

CASE 문

여러 가지 조건 중에서 선택해야 하는 경우에는 CASE문을 쓸 수 있다.

 

CASE 문의 기본형식 

IF 문은 T/F의 두 가지 경우만을 고려하기 때문에, 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로 나눈다고 생각해보자.

# CASE 문
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  -- 여기서 BETWEEN으로 안해도 되나? 
         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 문의 활용

인터넷 마켓 데이터베이스의 회원들은 물건을 구매하는데, 회원들의 총 구매액을 계산해서 회원의 등급을 4단계로 나누려 한다. 이를 위해서 먼저 구매 테이블에서 회원별로 구매액을 계산해야 한다. 

-- 회원별로 구매액 계산하기 
SELECT mem_id, SUM(price * amount) "총구매액" 
   FROM buy 
   GROUP BY mem_id ;

해당 테이블을 member 테이블에서의 mem_id 와 buy 테이블에서의 mem_id를 INNER JOIN 시킨 후, 해당 테이블을 구매액이 많은 순서대로 정렬해보자.

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 총구매액 DESC ;

이번에는 구매하지 않은 회원의 아이디와 이름도 출력해보도록하자. 구매 테이블서는 구매한 적이 없어도 회원 테이블에 있는 회원은 모두 출력해야 하므로, INNER JOIN 대신에 RIGHT OUTER JOIN을 사용하면 된다. (기준이 member 테이블이 됨.)

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  -- member 테이블의 회원 ID를 기준으로 해서
   ORDER BY 총구매액 DESC ;

여기서, CASE문을 이용하여 총구매액을 기준으로 회원 등급을 나눠보고자 한다. 

SELECT M.mem_id, M.mem_name, SUM(price * amount) "총구매액",  
       CASE    -- 열 의 마지막에 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  -- member 테이블의 회원 ID를 기준으로 해서
   ORDER BY 총구매액 DESC ;

WHILE 문

WHILE 문은 필요한 만큼 계속 같은 내용을 반복할 수 있다. WHILE 문의 기본 형식은 아래와 같다.

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

예를 들어, 1에서 100까지의 값을 모두 더하는 기능을 WHILE 문으로 구현해보고자 한다.

# WHILE 문
DROP PROCEDURE IF EXISTS whileProc ;
   
DELIMITER $$
CREATE PROCEDURE whileProc() 
BEGIN 
   DECLARE i INT ; 
   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 문의 응용

아래의 두 개를 이용하여 WHILE 문을 응용할 수 있다. 

• ITERATE [레이블] : 지정한 레이블로 가서 계속 진행함.
• LEAVE [레이블] : 지정한 레이블을 빠져나감. 

예를 들어, 1부터 100까지 더하는데, 4의 배수는 제외하고 더한 값이 1000을 넘었을 때 바로 그 값을 출력하고 WHILE문이 종료하도록 해보자.

# WHILE 문의 응용
DROP PROCEDURE IF EXISTS whileProc2 ; 

DELIMITER $$ 
CREATE PROCEDURE whileProc2() 
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  -- 4의 배수인 경우
        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 whileProc2() ;

동적 SQL 

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

 

PREPARE과 EXECUTE

PREPARE을 이용해서 나중에 사용할 쿼리문을 저장시켜 놓았다가, 필요할 때 EXECUTE를 이용하여 실행할 수 있다.

# 동적 SQL 
USE market_db ; 
PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK"' ; 
EXECUTE myQuery ; 
DEALLOCATE PREPARE myQuery ;

PREPARE 문을 쓸 때 '?'를 이용하여 실시간으로 변하는 값을 입력할 수도 있다.

# 동적 SQL의 응용
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 ;

 

Reference

혼자 공부하는 SQL, 우재남 지음