4.3 SQL 프로그래밍

스토어드 프로시저는 MySQL에서 프로그래밍 기능이 필요할 때 사용하는 데이터베이스 개체
SQL 프로그래밍은 기본적으로 스토어드 프로시저 안에 만들어야 함

• 구조

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름()
BEGIN
	이 부분에 SQL프로그래밍 코딩
END $$   # 스토어드 프로시저 종료
DELIMITER ;   # 종료 문자를 다시 세미콜론으로 변경
CALL 스토어드_프로시저_이름()   # 스토어드 프로시저 실행

* DELIMITER부터 END까지가 스토어드 프로시저 코딩 부분

IF ~ ELSE 문

USE market_db;
DROP PROCEDURE IF EXISTS ifProc2;  -- 기존에 만든적이 있다면 삭제

DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
   DECLARE myNum INT;  -- myNum 변수선언
   SET myNum = 200;  -- 변수에 값 대입
   IF myNum = 100 THEN  
      SELECT '100입니다.';
   ELSE
      SELECT '100이 아닙니다.';
   END IF;
END $$
DELIMITER ;
CALL ifProc2();

DROP PROCEDURE IF EXISTS ifProc3; 

DELIMITER $$
CREATE PROCEDURE ifProc3()
BEGIN
	-- 변수 선언
    DECLARE debutDate DATE; -- 데뷔일
    DECLARE curDate DATE; -- 오늘
    DECLARE days INT; -- 활동한 일수
	
    -- 데뷔일을 SELECT해서 변수에 대입
    SELECT debut_date INTO debutDate -- debut_date 결과를 hireDATE에 대입
       FROM market_db.member
       WHERE mem_id = 'APN';
       
	-- 현재 날짜를 변수에 대입하고, 위의 데뷔일과 차이 계산하여 변수에 대입
    SET curDATE = CURRENT_DATE(); -- 현재 날짜
    SET days =  DATEDIFF(curDATE, debutDate); -- 날짜의 차이, 일 단위
	
    -- IF ~ ELSE문 실행
    IF (days/365) >= 5 THEN -- 5년이 지났다면
          SELECT CONCAT('데뷔한지 ', days, '일이나 지났습니다. 핑순이들 축하합니다!');
    ELSE
          SELECT '데뷔한지 ' + days + '일밖에 안되었네요. 핑순이들 화이팅~' ;
    END IF;
END $$
DELIMITER ;
CALL ifProc3();

CASE 문

IF문은 참과 거짓이 있기에 2중 분기이다.
CASE문은 2가지 이상의 경우일 때 처리가 가능하므로 다중분기이다.

• 구조

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

ex)
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문의 활용
  고객을 총 구매액에 따라 등급을 부여하려고 한다. 이를 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   # 구매하지 않은 아이돌을 유령고객으로 지정하기 위해 OUTER JOIN을 이용
         ON B.mem_id = M.mem_id
   GROUP BY M.mem_id
   ORDER BY SUM(price*amount) DESC ;

WHILE 문 (반복문)

• 구조

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

ex)
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;  -- hap의 원래의 값에 i를 더해서 다시 hap에 넣으라는 의미
        SET i = i + 1;      -- i의 원래의 값에 1을 더해서 다시 i에 넣으라는 의미
    END WHILE;

    SELECT '1부터 100까지의 합 ==>', hap;   
END $$
DELIMITER ;
CALL whileProc();

• WHILE 문의 응용 : ITERATE문과 LEAVE문을 활용
  ITERATE : WHILE문으로 올라감
  LEAVE : WHILE문을 빠져나감
  (ITERATE / LEAVE : 혼공SQL 교재)

ex)
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문의 이름
    WHILE (i <= 100) DO  -- While문에 label을 지정
       IF (i%4 = 0) THEN
         SET i = i + 1;     
         ITERATE myWhile; -- 지정한 label문으로 가서 계속 진행
       END IF;
       SET hap = hap + i; 
       IF (hap > 1000) THEN 
         LEAVE myWhile; -- 지정한 label문을 떠남. 즉, While 종료.
       END IF;
       SET i = i + 1;
    END WHILE;

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

동적 SQL

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

PREPARE과 EXECUTE

PREPARE을 통해 SQL문을 실행하지 않고 미리 준비
EXECUTE를 통해 준비한 SQL문을 실행
그리고 실행 후에는 DEALLOCATE PREPARE로 문장을 해제해주는 것이 바람직

예시
USE market_db;

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

이를 통해 쿼리문을 필요에 따라 변경할 수 있다.

• 예시를 통해 활용을 해보자!
  PREPARE문에서 '?'를 통해 향후 입력될 값을 비워 놓고, EXECUTE에세ㅓ USING을 통해 ?에 값을 전달
  그러면 실시간으로 필요한 값들을 전달해서 동적으로 SQL에 실행됨
  ex) 실무에서 종종 발생하는 예시 : 보안이 중요한 출입문에서 출입한 내역을 테이블에 기록해 놓는다. 이 때 출입증을 태그하는 순간의 날짜와 시간이 INSERT문으로 만들어져서 입력되도록 해야 한다.
  how?

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;