[혼공SQL] 4-3 SQL 프로그래밍

본 포스팅은 '한빛출판네트워크'의 '혼공SQL' 책을 기반으로 작성한 포스팅입니다.

 

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

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

 

1. IF문

1.1 IF 문의 기본 형식

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

 

📌 기본 IF문의 형식

• 두 문장 이상이 처리되어야 할 때는 BEGIN ~ END로 묶어줘야 한다.

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

 

📌 예시

DELIMITER $$  -- 세미콜론으로는 SQL 끝인지, 스토어드 프로시저의 끝인지 구별할 수 없어서 $$ 사용
CREATE PROCEDURE ifProc1() -- 스토어드 프로시저의 이름 지정
BEGIN
   IF 100 = 100 THEN      -- 조건식
      SELECT '100은 100과 같습니다.';   -- 위의 조건식이 참이면 실행 (print()와 유사한 기능)
   END IF;
END $$
DELIMITER ;
CALL ifProc1();    --스토어드 프로시저 실행

 

1.2 IF ~ ELSE문

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

 

1.3 IF 문의 활용

DELIMITER $$
CREATE PROCEDURE ifProc3()
BEGIN
    DECLARE debutDate DATE; -- 데뷔일자  (변수 설정)
    DECLARE curDate DATE; -- 오늘  (변수 설정)
    DECLARE days INT; -- 활동한 일수  (변수 설정)

    SELECT debut_date INTO debutDate -- 데뷔일자를 추출하여 debutDate에 저장
       FROM market_db.member
       WHERE mem_id = 'APN';

    SET curDATE = CURRENT_DATE(); -- 현재 날짜를 curDATE에 저장

		-- 데뷔일자부터 현재 날짜까지 일수를 days에 저장
    SET days = DATEDIFF(curDATE, debutDate); -- 날짜의 차이, 일 단위

    IF (days/365) >= 5 THEN -- 5년이 지났다면
          SELECT CONCAT('데뷔한지 ', days, '일이나 지났습니다. 핑순이들 축하합니다!');
    ELSE
          SELECT '데뷔한지 ' + days + '일밖에 안되었네요. 핑순이들 화이팅~' ;
    END IF;
END $$
DELIMITER ;
CALL ifProc3();

 

📌 날짜 관련 함수

• CURRENT_DATE() : 오늘 날짜를 알려줌
• CURRENT_TIMESTAMP() : 오늘 날짜 및 시간을 함께 알려줌
• DATEDIFF(날짜1, 날짜2) : 날짜2부터 날짜1까지 일수로 몇 일인지 알려줌

 

2. CASE 문

• 여러가지 조건 중에서 선택하는 경우 사용

2.1 CASE 문의 기본 형식

• 2가지 이상의 여러 가지 경우일 때 처리가 가능하므로 ‘다중 분기’라고 부름
• CASE와 END CASE 사이에는 여러 조건들을 넣을 수 있다.

DELIMITER $$
CREATE PROCEDURE caseProc()
BEGIN
    DECLARE point INT ; -- 변수 생성
    DECLARE credit CHAR(1);  -- 학점을 저장한 credit 변수 준비
    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';  -- 모든 조건에 해당하지 않으면 F학점으로 처리
    END CASE;

    SELECT CONCAT('취득점수==>', point), CONCAT('학점==>', credit);
END $$
DELIMITER ;
CALL caseProc();

 

2.2 CASE 문의 활용

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 ; --총 구매액이 많은 순서로 정렬

 

📌 내부조인(INNER JOIN)

SELECT B.mem_id, M.mem_name, SUM(price*amount) "총구매액"
   FROM buy B
					-- 회원의 이름은 회원 테이블(member)에 있으므로 구매 테이블(buy)과 조인해야 함
         INNER JOIN member M   -- 내부 조인
         ON B.mem_id = M.mem_id  
   GROUP BY B.mem_id
   ORDER BY SUM(price*amount) DESC ;

 

📌 외부조인(OUTER JOIN)

-- 구매하지 않은 회원의 아이디와 이름 출력
-- 구매하지 않은 회원에 대한 정보가 없으므로 M.mem_name

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 문을 새로운 열로 추가

• 새로운 ‘회원등급’ 열이 추가되고 총 구매액에 따라서 회원이 분류됨

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 ;

 

3. WHILE 문

• 필요한 만큼 계속 같은 내용을 반복할 수 있음

3.1 WHILE 문의 기본 형식

DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
    DECLARE i INT; -- 1에서 100까지 증가할 변수
    DECLARE hap INT; -- 더한 값을 누적할 변수
    SET i = 1;
    SET hap = 0;

    WHILE (i <= 100) DO  -- irl 100이하인 동안에 계속 반복
        SET hap = hap + i;  -- hap의 원래의 값에 i를 더해서 다시 hap에 넣으라는 의미
        SET i = i + 1;      -- i의 원래의 값에 1을 더해서 다시 i에 넣으라는 의미
    END WHILE;

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

 

3.2 WHILE 문의 응용

• **ITERATE[레이블]** : 지정한 레이블로 가서 계속 진행 (CONTINUE와 비슷한 역할)
• **LEAVE[레이블]** : 지정한 레이블을 빠져나감 (WHILE 문이 종료 / BREAK와 비슷한 역할)

DELIMITER $$
CREATE PROCEDURE whileProc2()
BEGIN
    DECLARE i INT; -- 1에서 100까지 증가할 변수
    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;     -- 4의 배수이면 i를 1 증가
         ITERATE myWhile; -- 지정한 label문으로 가서 계속 진행
       END IF;
       SET hap = hap + i;  -- 4의 배수가 아니면 hap에 누적
       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();

 

4. 동적 SQL

• 상황에 따라 내용 변경이 필요할 때 동적 SQL을 사용하면 변경되는 내용을 실시간으로 적용시켜 사용할 수 있음

4.1 PREPARE와 EXECUTE

• PREPARE : SQL 문을 실행하지는 않고 미리 준비
• EXECUTE : 준비한 SQL 문을 실행
• 실행 후 DEALLLOCATE PREPARE로 문장을 해제

use market_db;
PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK"';  -- 준비만
EXECUTE myQuery;  -- 실행
DEALLOCATE PREPARE myQuery;  -- 문장을 해제

 

4.2 동적 SQL의 활용

-- 출입용 테이블 생성
-- id : 자동 증가, entry_time : DATETIME형으로 준비
CREATE TABLE gate_table (id INT AUTO_INCREMENT PRIMARY KEY, entry_time DATETIME);

SET @curDate = CURRENT_TIMESTAMP(); -- 현재 날짜와 시간을 @curDate 변수에 넣음

-- ?를 사용해서 entry_time에 입력할 값을 비워 놓음
PREPARE myQuery FROM 'INSERT INTO gate_table VALUES(NULL, ?)';
-- USING 문으로 @curDate 변수를 넣은 후 실행
EXECUTE myQuery USING @curDate;
DEALLOCATE PREPARE myQuery;

SELECT * FROM gate_table;

 

• @curDate
  • 일반 SQL에서 변수는 @변수명으로 지정하는데 별도의 선언은 없어도 됨
  • 스토어드 프로시저에서 변수는 DECLARE로 선언한 후 사용해야 함