MySQL - 스토어드 프로시저

1. 스토어드 프로시저란?

스토어드 프로시저는 SQL 코드 블록을 하나의 이름으로 저장해두고, 필요할 때마다 CALL 명령어로 실행할 수 있게 만든 저장 프로시저 객체다.

특징:

• 복잡한 로직을 미리 저장해두고 반복 실행 가능
• SQL 내부에서 조건문, 반복문 사용 가능
• 매개변수 IN/OUT을 통해 유연한 데이터 전달
• 쿼리 재사용성, 유지보수성 향상

---

2. 프로시저를 만들기 위한 준비: DELIMITER 설정

MySQL은 기본적으로 ;로 SQL 문장의 끝을 인식한다.
하지만 스토어드 프로시저 안에는 ;가 많이 들어가기 때문에, 이걸 구분하기 위해 DELIMITER를 설정해줘야 한다.

DELIMITER $$
... 스토어드 프로시저 생성 ...
END $$
DELIMITER ;

즉, $$가 끝나는 지점까지를 하나의 프로시저로 보겠다는 의미다.

 

---

3. 스토어드 프로시저 기본 구조

DELIMITER $$

CREATE PROCEDURE 프로시저명()
BEGIN
	-- SQL문 또는 제어 흐름 문장
END $$

DELIMITER ;

---

4. 조건문 (IF문) 사용하기

단순 IF

DELIMITER $$

CREATE PROCEDURE if1()
BEGIN
	IF 100 = 100 THEN
		SELECT '100은 100과 같습니다.';
	END IF;
END $$

DELIMITER ;

CALL if1();

IF- ELSE 문

DELIMITER $$

DROP PROCEDURE IF EXISTS if2;

CREATE PROCEDURE if2()
BEGIN
	DECLARE myNum INT;
	SET myNum = 200;

	IF myNum = 100 THEN
		SELECT '100입니다.';
	ELSE
		SELECT '100이 아닙니다.';
	END IF;
END $$

DELIMITER ;

CALL if2();

---

5. 다중 조건 처리: CASE 문

IF가 조건 1~2개 정도 처리할 때 쓰였다면, 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는 복수 조건을 명확하게 구분할 수 있어 성적, 등급, 등 다양한 상황에서 유용하다.

---

6. 반복문: WHILE

MySQL은 FOR문을 지원하지 않기 때문에 반복은 WHILE로 처리한다.

ex) 1부터 100까지 합 구하기

DROP PROCEDURE IF EXISTS whileProc;

DELIMITER $$

CREATE PROCEDURE whileProc()
BEGIN
	DECLARE i INT;
	DECLARE result INT;

	SET i = 1;
	SET result = 0;

	WHILE i <= 100 DO
		SET result = result + i;
		SET i = i + 1;
	END WHILE;

	SELECT '1부터 100까지의 합', result;
END $$

DELIMITER ;

CALL whileProc();

---

7. 실전 예제 1: 회원 총 구매액 기반 등급 부여

요구사항

• 총 구매액이 1500 이상이면 ‘최우수 고객’
• 1000 ~ 1499: ‘우수 고객’
• 1 ~ 999: ‘일반 고객’
• 구매 내역이 없으면 ‘일반 회원’

USE market_db;

SELECT 
	M.mem_id, M.mem_name, SUM(B.price * B.amount) AS "총 구매액",
	CASE
		WHEN SUM(B.price * B.amount) >= 1500 THEN '최우수 고객'
		WHEN SUM(B.price * B.amount) >= 1000 THEN '우수 고객'
		WHEN SUM(B.price * B.amount) >= 1 THEN '일반 고객'
		ELSE '일반 회원'
	END AS '회원 등급'
FROM 
	member M
		LEFT OUTER JOIN buy B ON B.mem_id = M.mem_id
GROUP BY M.mem_id
ORDER BY SUM(B.price * B.amount) DESC;

LEFT OUTER JOIN을 통해 구매 내역이 없는 회원도 조회 가능. SUM()에 따라 등급이 결정됨.

 

---

8. 실전 예제 2: 특정 선수 포지션 확인 프로시저

요구사항

• 매개변수로 선수 ID를 받아서,
• 그 선수가 ‘타자’인지 아닌지를 판별

USE baseball_league;

DROP PROCEDURE IF EXISTS CheckPosition;

DELIMITER $$

CREATE PROCEDURE CheckPosition(IN p_player_id INT)
BEGIN
	DECLARE v_player_position VARCHAR(100);

	SELECT position INTO v_player_position
	FROM players
	WHERE player_id = p_player_id;

	IF v_player_position = '타자' THEN
		SELECT '해당 선수는 타자입니다.';
	ELSE
		SELECT '해당 선수는 타자가 아닙니다.';
	END IF;
END $$

DELIMITER ;

CALL CheckPosition(101);
CALL CheckPosition(105);
CALL CheckPosition(103);

IN 파라미터를 통해 외부에서 선수 ID를 전달받고, DECLARE로 선언한 지역 변수에 값을 담아 조건문(IF)으로 분기 처리한다.

---

9. 변수 사용 요약

• 전역 변수: SET @변수명 = 값; (세션 내 유지됨)
• 지역 변수: DECLARE 변수명 데이터타입; (프로시저 내에서만 사용)
• 대입: SET 변수 = 값; 또는 SELECT 컬럼 INTO 변수;

---

10. 마무리 요약: 스토어드 프로시저 핵심 정리

1. CREATE PROCEDURE로 작성, CALL로 실행
2. 조건문(IF, CASE), 반복문(WHILE) 지원
3. 매개변수 IN/OUT/INOUT을 통해 데이터 전달 가능
4. DECLARE, SET, SELECT INTO를 활용한 변수 선언 및 활용
5. 실전에서 데이터 분석, 검증, 반복 처리를 위해 자주 사용됨