제로베이스 데이터사이언스 스쿨 - Part.02 데이터 분석을 위한 SQL-10

 10. 저장 프로시져

 

SQL 저장 프로시저 (Stored Procedure)

1. 저장 프로시저란?

• 정의: 여러 SQL 명령문을 하나로 묶은 재사용 가능한 SQL 블록
• 목적: 자주 반복되는 쿼리나 복잡한 데이터 처리 로직을 효율적으로 관리
• 특징:
  • DELIMITER를 사용해 여러 명령문을 하나로 정의
  • CALL 명령어로 실행
  • 필요 시 DROP PROCEDURE로 삭제 가능

💡 기본 예시

DELIMITER //
CREATE PROCEDURE CUSTOMER_GENDER_PROCEDURE(IN INPUT_A VARCHAR(20))
BEGIN
    SELECT * FROM CUSTOMER WHERE GENDER = INPUT_A;
END //
DELIMITER ;

CALL CUSTOMER_GENDER_PROCEDURE('M');

→ 성별(GENDER)에 따라 고객 데이터를 조회

 

---

 

2. 매개변수(Parameter) 종류

 

유형	설명	사용 예시
IN	외부에서 값을 입력받음 (읽기 전용)	입력값을 조건으로 조회
OUT	프로시저 실행 후 결과를 반환	조회 결과를 외부 변수에 저장
INOUT	입력받은 값을 내부에서 수정 후 반환	누적 계산, 변환 로직 등

💡 IN + OUT 예시

DELIMITER //
CREATE PROCEDURE CUSTOMER_GENDER_PROCEDURE_OUT(IN INPUT_A VARCHAR(20), OUT OUTPUT_A INT)
BEGIN
  SELECT COUNT(MEM_NO) AS OUTPUT_A
  FROM CUSTOMER
  WHERE GENDER = INPUT_A;
END //
DELIMITER ;

SET @CNT_MEM = 0;
CALL CUSTOMER_GENDER_PROCEDURE_OUT('M', @CNT_MEM);
SELECT @CNT_MEM;

→ 남성 회원 수를 OUT 매개변수로 반환

💡 INOUT 예시

DELIMITER //
CREATE PROCEDURE IN_OUT_PARAMETER(INOUT COUNT INT)
BEGIN
    SET COUNT = COUNT + 10;
END //
DELIMITER ;

SET @counter = 1;
CALL IN_OUT_PARAMETER(@counter);
SELECT @counter;

→ 입력값 1에 10을 더해 반환

 

---

 

3.  저장 프로시저 활용

✅ 실습 ① 호텔 이름을 조건으로 평균·최대 가격 조회

DELIMITER //
CREATE PROCEDURE SERVICE_PRICE_PROCEDURE(IN INPUT_A VARCHAR(20))
BEGIN
    SELECT SERVICE_NAME, AVG(PRICE) AS AVG_PRICE, MAX(PRICE) AS MAX_PRICE
    FROM SERVICE
    WHERE HOTEL_CODE IN (
        SELECT HOTEL_CODE FROM HOTEL WHERE HOTEL LIKE CONCAT('%', INPUT_A, '%')
    )
    GROUP BY SERVICE_NAME;
END //
DELIMITER ;

CALL SERVICE_PRICE_PROCEDURE('seoul');

→ HOTEL 이름에 특정 키워드가 포함된 서비스의 평균 및 최대 가격 조회

 

✅ 실습 ② 특정 성별의 주문 수 조회 (OUT 활용)

DELIMITER //
CREATE PROCEDURE CUSTOMER_GENDER_ORDER_PROCEDURE_OUT(IN INPUT_A VARCHAR(20), OUT OUTPUT_A INT)
BEGIN
    SELECT COUNT(DISTINCT B.ORDER_NO) AS OUTPUT_A
    FROM CUSTOMER AS A
    LEFT JOIN SALE AS B ON A.MEM_NO = B.MEM_NO
    WHERE GENDER = INPUT_A;
END //
DELIMITER ;

SET @CNT_ORDER = 0;
CALL CUSTOMER_GENDER_ORDER_PROCEDURE_OUT('W', @CNT_ORDER);
SELECT @CNT_ORDER;

→ LEFT JOIN과 OUT 변수를 활용한 회원별 주문 수 계산

 

✅ 실습 ③ INOUT 예제: 입력값 × 1.1 반환

DELIMITER //
CREATE PROCEDURE IN_OUT_1_1_PARAMETER(INOUT COUNT INT)
BEGIN
    SET COUNT = COUNT * 1.1;
END //
DELIMITER ;

SET @counter = 10;
CALL IN_OUT_1_1_PARAMETER(@counter);
SELECT @counter;

→ 입력값에 1.1을 곱해 반환 (세금, 수수료 계산 등 활용 가능)

---

4. 코딩 테스트 실습

(1) 특정 연도 주문 데이터 조회

CREATE PROCEDURE CUSTOMER_SALE_YEAR_PROCEDURE(IN INPUT_A VARCHAR(20))
BEGIN
    SELECT *
    FROM CUSTOMER AS A
    LEFT JOIN SALE AS B ON A.MEM_NO = B.MEM_NO
    WHERE YEAR(ORDER_DATE) = INPUT_A;
END;
CALL CUSTOMER_SALE_YEAR_PROCEDURE('2024');

→ 주문 연도를 매개변수로 입력하여 해당 연도 데이터만 조회

 

(2) 특정 생월 회원 수 반환 (OUT)

CREATE PROCEDURE CUSTOMER_SALE_BIRTH_PROCEDURE(IN INPUT_A INT, OUT OUTPUT_A INT)
BEGIN
    SELECT COUNT(MEM_NO) AS OUTPUT_A
    FROM CUSTOMER
    WHERE MONTH(BIRTH_DATE) = INPUT_A;
END;

SET @CNT_ORDER = 0;
CALL CUSTOMER_SALE_BIRTH_PROCEDURE(8, @CNT_ORDER);
SELECT @CNT_ORDER;

→ 입력된 월의 생일을 가진 회원 수 계산

 

(3) 회원별 최근 구매일과 기준일 차이 계산

CREATE PROCEDURE SALE_DAY_DIFF_PROCEDURE(IN INPUT_A INT, IN INPUT_B DATE)
BEGIN
WITH BASE AS (
    SELECT MEM_NO, MAX(ORDER_DATE) AS ORDER_DATE_MAX
    FROM SALE
    WHERE MEM_NO <> 9999999 AND YEAR(ORDER_DATE) = INPUT_A
    GROUP BY MEM_NO
)
SELECT *, TIMESTAMPDIFF(DAY, ORDER_DATE_MAX, INPUT_B) AS DAY_DIFF
FROM BASE;
END;

CALL SALE_DAY_DIFF_PROCEDURE(2024, '2025-01-01');

→ 특정 연도 및 기준일 기준으로 회원별 최근 구매일자 차이 계산

 

 

* 이 글은 제로베이스 데이터사이언스 파트타임 스쿨의 강의 자료 일부를 발췌하여 작성되었습니다.

---

 

💡 생각 정리

이번 강의에서는 단순 쿼리 실행을 넘어, SQL을 “프로그램처럼 구조화하는 방법”을 배웠다.
특히 저장 프로시저는 반복되는 로직을 캡슐화해 효율적으로 관리할 수 있는 점에서,

데이터베이스 내에서의 함수형 사고를 가능하게 했다.

IN, OUT, INOUT 매개변수의 차이를 실습으로 익히면서,

단순 조회뿐 아니라 결과 전달·수정까지 제어할 수 있다는 점이 인상 깊었다.

또한 프로시저를 통해 복잡한 비즈니스 로직(예: 구매주기, 생일 기준 회원 수, 날짜 차이 계산 등)을 SQL 내부에서 직접 자동화할 수 있음을 확인했다. 이 덕분에 분석 효율성과 실행 속도를 동시에 확보할 수 있었다.

---

🚀 적용점

• 자주 사용하는 로직을 저장 프로시저로 등록해 자동화 쿼리 라이브러리 구축
• 매개변수 유형(IN/OUT/INOUT)을 상황별로 적절히 선택하는 연습
• DELIMITER와 CALL 문법을 익혀 실제 실무 환경에서 프로시저 호출 자동화
• 특정 연도, 월, 기준일 등 입력 조건 기반 동적 쿼리 설계 연습
• 서브쿼리·WITH문과 프로시저를 결합하여 분석용 함수형 SQL 설계 실습

👉 이번 강의는 SQL을 “데이터 조회 도구”에서 “비즈니스 로직 엔진”으로 확장시키는 단계였다.
프로시저 개념을 숙지하면, 데이터 엔지니어링·자동화·ETL 파이프라인 구축에도 강력히 응용할 수 있다.