프로시저(Stored Procedure)란?
― MES/ERP 같은 엔터프라이즈 시스템에서 아직도 프로시저를 쓰는 이유
“요즘 JPA + QueryDSL이면 다 되는데, 굳이 DB에 로직을 넣어야 해?”
👉 MES·ERP 같은 데이터 집중형 서비스에서는 Yes
⚡ 미리 스포
| 상황 | 사용 빈도 |
|---|---|
| 일반 웹 서비스 (게시판·커머스) | ❌ 거의 안 씀 (JPA + QueryDSL로 충분) |
| ERP·MES·금융·공공 등 대규모 엔터프라이즈 | ✅ 자주 씀 (초고속 트랜잭션 & 보안) |
1️⃣ 프로시저(Stored Procedure)란?
- 미리 정의된 SQL 쿼리들의 집합
→ 서버 측 함수처럼 DB에 저장해두고 CALL 한 번으로 실행 - DB 안에 로직·트랜잭션을 패키징해서 네트워크 왕복을 최소화
- 하나의 프로시저 = 특정 업무 시나리오를 처리하는 쿼리 블록
예) “SELECT → 결과에 따라 INSERT/UPDATE”를 Java if 문 없이
DB 내부에서 한 방에 처리
2️⃣ 프로시저의 특징
- 메서드처럼 호출 → 외부에서는 내부 SQL을 몰라도 됨
- 한 요청으로 다중 SQL 실행 → 네트워크 부하 ↓
- 반환값은 선택사항
- 전통적 정의: 결과를 반환하지 않는 루틴
- 현대 DBMS:
OUT파라미터·SELECT로 결과 반환 가능
- 함수와 차이
- 함수: 단일 값 반환, 식(expression) 안에서 호출 가능
- 프로시저: 여러 OUT / 트랜잭션 처리, 식 안에서는 호출 불가
3️⃣ 장점 vs 단점
| ✅ 장점 | ❌ 단점 |
|---|---|
| 네트워크 부하↓ : DB 서버 내에서 실행되므로 클라-서버간 트래픽 감소 ➡️ 네트워크 소요시간 줄여줌 | 복잡해지면 가독성↓ · 추적 난이도 ↑ So 유지보수 어렵다(로직이 DB에 숨어있어서) |
| 성능 최적화 : 대용량 배치·실시간 연산 | 텍스트·숫자 조작은 Java 코드가 더 빠를 수 있음 |
| 보안 강화 : SQL 인젝션 차단, 권한 제어 | Git 형상관리 × → 버전 관리 어려움 |
| 트랜잭션 일괄 관리 | IDE 테스트·Mocking 어려움 |
| 여러 시스템에서 재사용 (API 같이 호출) | DB 이식성↓ (DBMS 종속) |
4️⃣ 2025년 현재 프로시저 사용 현황
| 구분 | 설명 |
|---|---|
| 기업 시스템 (제조, 금융, 공공) | ✅ 적극 사용 - 빠른 데이터 처리 - 대량 데이터 조작 최적화 - 기존 레거시 시스템 호환 필요 - 운영 중 안정성과 성능이 중요 |
| 스타트업, 신규 서비스 개발 | ❌ 거의 사용 안 함 - 비즈니스 로직은 서버(Spring, Django 등)에서 처리 - 유지보수성, 테스트, 확장성 중시 |
| 대규모 트래픽 서비스 (카카오, 네이버 등) | 🔵 제한적 사용 - 서버에서 로직 처리 - 성능 최적화나 배치 작업만 일부 프로시저 활용 |
| MES, ERP, WMS (제조/물류 시스템) | ✅ 필수적으로 사용 - 데이터 일관성과 속도가 중요한 환경 - 프로시저 기반 데이터 흐름이 표준처럼 자리잡음 |
5️⃣ 예제 코드
*********^*********^*********^*********^*********^*********^*********^*********^*********^*********^*********^********/
-- PAGE NAME P_PRO_PPZ200_Q Procedure
-- COPYRIGHT JihooSoft
-- REMARK 공정정보(조회) 프로시저
/*********^*********^*********^*********^*********^*********^*********^*********^*********^*********^*********^********/
-- Date By Modification
-----------------------------------------------------------------------------------------------------------------------
-- 2019-11-20 jade 신규 자동 생성
/*********^*********^*********^*********^*********^*********^*********^*********^*********^*********^*********^********/
CREATE PROCEDURE [dbo].[P_PRO_PPZ200_Q] (
@p_work_type VARCHAR(10) = 'Q', -- 작업타입 (Q:쿼리, N:등록, U:수정, D:삭제, META:정보수집 등등)
@p_bs_cd VARCHAR(10),
@p_fac_cd VARCHAR(10),
@p_prc_cd varchar(10),
@p_prc_nm nvarchar(50),
@p_delete_yn varchar(1),
@p_error_code VARCHAR(100) = '' OUTPUT, -- 사용자 에러코드 리턴
@p_row_count INT = 0 OUTPUT, -- 실행/리턴하는 레코드행수
@p_error_note NVARCHAR(100) = '' OUTPUT, -- 사용자 지정 문자열
@p_return_str NVARCHAR(100) = '' OUTPUT, -- 사용자 지정 반환값
@p_error_str NVARCHAR(1000) = '' OUTPUT, -- 오류메세지
@ErrorState VARCHAR(500) = '' OUTPUT, -- 오류번호/심각도/오류상태번호오류발생루틴내의 줄번호
@ErrorProcedure NVARCHAR(200) = '' OUTPUT -- 오류발생 프로시저/트리거
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--DECLARE /* 프로시저 내에서 사용할 변수 정의 : 사용시 앞부분 주석 해제 */
--@v_변수명 VARCHAR(500), -- 변수설명
BEGIN
-- 변수에 대한 초기화 처리
--SET @v_변수명 = '';
BEGIN TRY
-----------------------------------------------------------------------------------------------------------------------
-- 첫번째 리턴 레코드셋 처리
IF @p_work_type IN ( 'Q' )
BEGIN
-----------------------------------------------------------------------------------------------------------------------
-- 쿼리문을 실행한다
SELECT a.prc_cd,
a.prc_nm,
a.use_yn,
a.sq,
a.fr_dt,
a.to_dt,
a.prt_prc,
a.rmks,
a.rack_id, --생산자재투입내역출고로케이션
a.in_rack_id,--생산품입고로케이션
o.rack_location,
in_rack_location = i.rack_location,
a.cid,
a.cdt,
a.mid,
a.mdt,
b.pre_fix,
b.date_type,
b.seq_len,
b.last_no,
a.out_prc,
a.auto_result,
delete_yn = isnull(a.delete_yn,'0')
FROM TPRO_PPZ200 a
LEFT OUTER JOIN TPRO_PPZ210 b ON a.bs_cd = b.bs_cd AND a.prc_cd = b.prc_cd -- 사용하지 않음
LEFT OUTER JOIN TMAS_BCW200 o ON o.fac_cd = @p_fac_cd and o.rack_id = a.rack_id
LEFT OUTER JOIN TMAS_BCW200 i ON i.fac_cd = @p_fac_cd and i.rack_id = a.in_rack_id
WHERE 1=1
AND a.bs_cd = @p_bs_cd
AND (@p_prc_cd = '' or CHARINDEX(@p_prc_cd, a.prc_cd) > 0 )
AND (@p_prc_nm = '' or CHARINDEX(@p_prc_nm, a.prc_nm) > 0 )
and (@p_delete_yn = '0' and a.delete_yn <> '1' or @p_delete_yn = '1')
-- 실행된레코드행수 받기
SELECT @p_row_count = @@ROWCOUNT
IF @p_row_count > 0
SET @p_error_code = 'MSG0001' -- 정상적으로 조회가 되었습니다
ELSE
SET @p_error_code = 'MSG0006' -- 조회된 자료가 없습니다.
END -- 첫번째 리턴 끝
-----------------------------------------------------------------------------------------------------------------------
END TRY
BEGIN CATCH
SELECT @p_error_code = CASE SUBSTRING(@p_work_type,1,1)
WHEN 'Q' THEN 'ERR0006' -- 조회시 에러가 발생하였습니다.
WHEN 'N' THEN 'ERR0008' -- 등록시 오류가 발생하였습니다.
WHEN 'U' THEN 'ERR0009' -- 수정시 오류가 발생하였습니다.
WHEN 'D' THEN 'ERR0010' -- 삭제시 오류가 발생하였습니다.
ELSE 'ERR0000'
END
SELECT @ErrorState = CONVERT(VARCHAR(100), ERROR_NUMBER()) + '|' + CONVERT(VARCHAR(100), ERROR_SEVERITY()) + '|' + CONVERT(VARCHAR(100), ERROR_STATE()) + '|' + CONVERT(VARCHAR(100), ERROR_LINE()),
@ErrorProcedure = 'PRC=' + ERROR_PROCEDURE(), @p_error_str = ERROR_MESSAGE();
END CATCH;
END
------------------------------------------------------------------------------------------/* **********************************************
* 프로시저 : sp_jooniljjang_coffee_order
* 목적 : 커피 주문 요청 시
* 1) 재고 확인 → 부족하면 메시지 반환
* 2) 주문 이력 저장
* 3) 재고 차감
* 파라미터
* IN p_coffee_id : 주문할 커피 ID
* IN p_qty : 수량
* OUT o_result : 처리 결과 메시지
********************************************** */
DELIMITER //
CREATE PROCEDURE sp_jooniljjang_coffee_order(
IN p_coffee_id INT,
IN p_qty INT,
OUT o_result VARCHAR(50)
)
BEGIN
DECLARE v_stock INT; -- 현재 재고 수량
DECLARE v_price DECIMAL(10,2); -- 커피 단가
DECLARE v_total DECIMAL(10,2); -- 총 결제 금액
-- ① 트랜잭션 시작
START TRANSACTION;
/* ② 현재 재고 확인 (행 잠금 - FOR UPDATE) */
SELECT stock_qty
INTO v_stock
FROM coffee_inventory
WHERE coffee_id = p_coffee_id
FOR UPDATE;
/* ③ 재고·메뉴 유효성 체크 */
IF v_stock IS NULL THEN
SET o_result = '존재하지 않는 커피입니다.';
ROLLBACK;
ELSEIF v_stock < p_qty THEN
SET o_result = CONCAT('재고 부족 (현재 ', v_stock, '잔 가능)');
ROLLBACK;
ELSE
/* ④ 단가 조회 */
SELECT price
INTO v_price
FROM coffee_menu
WHERE id = p_coffee_id;
SET v_total = v_price * p_qty;
/* ⑤ 주문 이력 저장 */
INSERT INTO coffee_order (coffee_id, qty, total_price, order_time)
VALUES (p_coffee_id, p_qty, v_total, NOW());
/* ⑥ 재고 차감 */
UPDATE coffee_inventory
SET stock_qty = stock_qty - p_qty
WHERE coffee_id = p_coffee_id;
/* ⑦ 커밋 & 결과 세팅 */
COMMIT;
SET o_result = CONCAT('✅ 주문 완료! 결제 금액 ', v_total, '원');
END IF;
END //
DELIMITER ;
호출
CALL sp_jooniljjang_coffee_order(2, 3, @msg); -- 아메리카노(id=2) 3잔 주문
SELECT @msg; -- 결과 메시지 확인
Spring에서 호출
@Procedure(name = "Product.countByCategory")
Integer countByCategory(String category);JPA @NamedStoredProcedureQuery 또는 MyBatis의 CALL sp_jooniljjang_coffee_order 방식으로도 호출 가능
6️⃣ 실제 MES 시나리오
“제품 생산 처리” 프로시저
- 생산 요청 저장 (
INSERT) - 원자재 재고 차감 (
UPDATE) - 생산 이력 기록 (
INSERT) - 로그·모니터링 (
INSERT)
→ 프론트/백엔드에서는 CALL sp_produce_goods() 한 줄로 끝
7️⃣ 왜 Spring Service 대신 DB 프로시저를 쓰나?
| 항목 | Spring Service | DB Procedure |
|---|---|---|
| 개발 유연성 | OOP·DI·테스트 용이 | SQL 기반, 유연성 낮음 |
| 성능(대량 데이터) | 중간 | 탁월 (네트워크 1 Hop) |
| 트랜잭션 | @Transactional | DB 내 원자성 |
| 다중 플랫폼 재사용 | Java 전용 | .NET·Python 등 공통 사용 |
| 레거시 호환 | 신규 시스템 유리 | 기존 Oracle/MSSQL 환경에 최적 |
MES 특성
- 공장 장비 → DB → Dashboard 까지 ms 단위 응답 필요
- 수십만 건 실시간 집계 → DB 내 연산이 유리
- 타 시스템(POP, PDA, 외부 SAP)과 공통 로직 공유 필수
8️⃣ 언제 프로시저를 적극 쓰면 좋을까?
- 대용량 배치/집계
- 복잡 트랜잭션
- 레거시 연동
- 보안·권한 통제
9️⃣ 결론 & 개발 팁
- 도메인 특성(MES·ERP = 데이터 집약 + 실시간성)에 따라 DB 프로시저가 표준인 경우가 많음
- 역할 분리
- Spring Service: 사용자 인증·외부 API 호출·결과 가공
- DB Procedure: 대량 데이터 처리·핵심 트랜잭션
- DDL 스크립트를 Git에 관리해 버전 관리 문제 해결
- 신규 서비스라면 JPA·Batch·View로 대체 가능성 먼저 검토
💬 한 줄 요약
웹 서비스는 코드가 왕, MES·ERP는 DB가 왕 — 프로시저는 여전히 현역이다.
하나씩 실천하는 개발자