[혼자공부하는SQL] 11강

04-3. SQL 프로그래밍

0. 시작하기 전에

1) 스토어드 프로시저는

: MySQL에서 프로그래밍 기능이 필요할 때 사용하는 데이터베이스 개체이다.

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

2) 구조

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

CALL 스토어드_프로시저_이름(); -- 스토어드 프로시저 실행

(1) 일반적으로 구분문자(DELIMITER)는 $$를 많이 사용하지만, 원한다면 /, &, Q 등을 사용해도 상관없다. 다른 기호와 중복될 수 있으므로, 기호 2개를 연속해서 사용하는 것이 좋다.

(2) 즉, 스토어드 프로시저는 DEIMITER $$ ~ END $$ 안에 작성하고 CALL로 호출한다.

1. IF문

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

1) IF 문의 기본 형식

: IF문은 조건식이 참이라면 ‘SQL문장들’을 실행하고, 그렇지 않으면 그냥 넘어간다.

(1) 형식

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

: ‘SQL문장들’이 한 문장이라면, 그 문장만 써도 되지만, 두 문장 이상이 처리되어야 할 때는 BEGIN ~ END로 묶어줘야 한다.

(2) 예시

USE market_db;

DROP PROCEDURE IF EXISTS ifProc1; -- 기존에 ifProc1()을 만든 적이 있다면 삭제한다.

DELIMITER $$ -- 세미콜론(;)으로는 SQL 끝인지, 스토어드 프로시저의 끝인지 구별할 수 없어 $를 사용한다.
CREATE PROCEDURE ifProc1() -- 스토어드 프로시저의 이름을 ifProc1()로 지정했다.
BEGIN
	IF 100 = 100 THEN -- 조건식으로 100과 100이 같은지 비교했다.
		SELECT '100은 100과 같습니다.';
	END IF;
END $$
DELIMITER ;

CALL ifProc1(); -- CALL로 호출하면 ifProc1()이 실행된다.

: 다른 프로그래밍언어에서는 같다는 의미로 ==를 사용하지만, SQL은 =를 사용한다.

: 그리고 SELECT 뒤에 문자가 나오면, 그냥 화면에 출력해준다. (print()와 같은 기능)

2) IF ~ ELSE문

: 조건에 따라 다른 부분을 수행한다. 조건식이 참이라면 ‘SQL문장1’을 실행하고, 그렇지 않으면 ‘SQL문장2’를 실행한다.

(1) 예시

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

3) 응용

DROP PROCEDURE IF EXISTS ifProc3;
DELIMITER $$
CREATE PROCEDURE ifProc3()
BEGIN
    DECLARE debutDate DATE; -- 데뷰일
    DECLARE curDate DATE; -- 오늘
    DECLARE days INT; -- 활동한 일수

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

(1) 변수 선언

DECLARE 변수명 변수타입;

(2) 검색 결과를 변수에 저장

SELECT 열_이름 INTO 저장할 변수
	FROM 테이블
	WHERE 조건

(3) 현재 날짜 구하는 함수 : CURRENT_DATE();

-- 예시
SET curDATE = CURRENT_DATE()

(4) 날짜의 차이 (일 단위)를 구하는 함수 : DATEDIFF(최근 날짜, 데뷔 날짜)

-- 특징 : 결과가 양수면 날짜1이 더 미래, 음수면 날짜2가 더 미래이다.
SELECT DATEDIFF('2025-12-31', '2025-01-01') AS result; -- 결과: 364 (날짜 차이)  
SELECT DATEDIFF('2025-01-01', '2025-12-31') AS result; -- 결과: -364  
SELECT DATEDIFF(NOW(), '2024-01-01') AS result; -- 현재 날짜와 비교 가능  

-- 예시 : 오늘 날짜와 데뷔 날짜의 차이
SET dyas = DATEDIFF(curDATE, debutDate);

2. CASE문

: 조건을 설정하여 여러 가지 조건 중 선택할 수 있다.

1) CASE문의 기본 형식

: IF문은 참 아니면 거짓 두 가지만 있기 때문에, 2중 분기라는 용어를 사용한다.

: 반면, CASE문은 2가지 이상의 여러 가지 경우일 때 처리가 가능하므로 다중 분기라고 부른다.

: SWITCH ~ CASE문과 비슷한 기능을 한다.

(1) 형식

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

: 조건이 여러개라면 WHEN을 여러 번 반복하고, 모든 조건에 해당하지 않으면 마지막 ESLE 부분을 수행한다.

(2) 예시

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

2) CASE 문의 활용

(1) 예시의 상황

: 회원의 등급을 4단계로 나누려고 한다.

 

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

(2) 코드

USE market_db;

-- 그룹 별 총 구매액
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;

-- 그룹 별 총 구매액 (내림차순) - 아이디, 이름, 구매액
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(B.price * B.amount) DESC;

-- 그룹 별 총 구매액 (내림차순) - 유령 고객까지 반영 (mem_id를 신경 쓰기)
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(B.price * B.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(B.price * B.amount) DESC;

(3) 궁금증

Q. 예시 1에서는 CASE 뒤에 END 하고 CASE 라고 적었는데, 예시 2에서는 END 하고 "회원등급"이라고 했어. 무슨 차이고 왜 그런거야?

A. CASE 문은 프로시저 내부에서 변수에 값을 할당할 때와 SELECT 문에서 컬럼 값을 결정할 때 사용하는 방식이 다르다.

구분사용 위치END 뒤 표현설명

예시 1	프로시저 내부 (SET 사용)	END CASE;	CASE 문이 단독으로 사용되며, SET을 통해 변수에 값을 할당하기 때문에 END CASE;로 종료해야 한다.
예시 2	SELECT 문 내부 (컬럼 값 결정)	"회원등급"	CASE 문이 SELECT 문의 컬럼 값으로 사용되므로, END 뒤에 컬럼명을 지정하여 결과 테이블에 해당 값이 포함되도록 해야 한다.

즉, 예시 1에서는 프로시저 내부에서 값 할당을 위해 END CASE;로 종료하고, 예시 2에서는 SELECT 문에서 컬럼 값을 결정하므로 END 뒤에 컬럼명을 명시한 것이다.

3. WHILE문

1) WHILE문의 기본 형식

: WHILE문은 조건식이 참인 동안에 SQL문장들을 계속 반복한다.

(1) 형식

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

(2) 예시

: 1부터 100까지 더하는 함수 whileProc

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

2) WHILE문의 응용

(1) 종류

• ITERATE [레이블] : 지정한 레이블로 가서 계속 진행한다.
• LEAVE [레이블] : 지정한 레이블을 빠져나간다. 즉 WHILE문이 종료된다.

(2) 예시

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문에 label을 지정
    WHILE (i <= 100) DO
       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();

4. 동적 SQL

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

1) PREPARE와 EXECUTE

(1) 의미

• PREPRAE : SQL 문을 실행하지는 않고, 미리 준비만 해놓는다.
• ESECUTE : 준비한 SQL문을 실행한다.
• DEALLOCATE PREPARE : 실행한 후에는 문장을 해제해주는 것이 바람직하다.

(2) 예시

: mem_id가 ‘BLK”인 멤버의 정보를 보여준다.

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

2) 동적 SQL의 활용

(1) 물음표(?)의 사용

: PREPARE 문에서는 ?로 향후에 입력될 값을 비워놓고, EXECUTE에서 USING으로 ?에 값을 전달할 수 있다.

: 이를 통해 실시간으로 필요한 값을 전ㄷ라해서 동적으로 SQL이 실행된다.

(2) 예시

DROP TABLE IF EXISTS gate_table;

-- 테이블 생성
CREATE TABLE gate_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entry_time DATETIME
);

-- 현재 날짜와 시간 대입
SET @curDate = CURRENT_TIMESTAMP();

-- SQL문 준비 및 실행
PREPARE myQuery FROM 'INSERT INTO gate_table VALUES (NULL, ?)';
EXECUTE myQuery USING @curDate;

-- SQL문 해제
DEALLOCATE PREPARE myQuery;

-- 테이블 검색
SELECT * FROM gate_table;