Procedure

Posted: 2026-01-29 Tags: cs database

contents

1. 스토어드 프로시저란 무엇인가?

스토어드 프로시저(저장 프로시저) 는 관계형 데이터베이스 시스템에 접근하는 애플리케이션이 사용할 수 있는 일종의 서브루틴입니다.

애플리케이션 코드(Java, Python 등)가 아닌, 데이터베이스 서버 내부에 저장되고 컴파일된 SQL 함수나 스크립트라고 생각하면 됩니다.

비유:
- 일반 SQL: 주방에 들어가서 셰프에게 하나하나 지시합니다. "양파 썰고, 팬 달구고, 기름 붓고, 볶아주세요..."
- 스토어드 프로시저: 셰프에게 "5번 메뉴 하나요!"라고 외칩니다. 셰프는 이미 레시피(프로시저)를 외우고 저장해 두었습니다. 말하는 에너지(네트워크 트래픽)를 아끼고, 셰프는 더 빨리 요리합니다.

2. 프로시저의 구조

선언적인 일반 SQL(SELECT)과 달리, 프로시저는 절차적 로직(Procedural Logic) 을 허용합니다.

입력 매개변수 (Input): 외부에서 넣어주는 데이터 (예: user_id).
출력 매개변수 (Output): 프로시저가 돌려주는 데이터.
변수 (Variables): 로직 내부에서 값을 임시 저장.
제어 흐름: IF/ELSE 문, WHILE 루프, CASE 문.
트랜잭션 관리: COMMIT, ROLLBACK.

3. 왜 사용하는가? (장점)

A. 네트워크 트래픽 감소

미사용 시: 앱이 50줄짜리 긴 SQL 텍스트를 네트워크로 보냅니다.
사용 시: 앱은 CALL ProcessOrder(123);만 보냅니다.
결과: 특히 클라우드 환경에서 지연 시간(Latency)이 줄어듭니다.

B. 성능 (실행 계획 캐싱)

프로시저를 생성하면 DB는 이를 파싱하고 문법을 체크한 뒤 실행 계획(Execution Plan) 을 만듭니다. 이 계획은 캐시(저장)됩니다.

이후에 호출할 때는 파싱/계획 단계를 건너뛰고 즉시 실행되므로 빠릅니다.

C. 보안 (권한의 세분화)

매우 중요한 보안 기능입니다.

애플리케이션에 Users 테이블에 대한 직접적인 SELECT/INSERT 권한을 뺏습니다.
대신 sp_CreateUser 프로시저에 대한 EXECUTE 권한만 줍니다.
결과: 해커가 앱을 장악해도 DELETE FROM Users를 실행할 수 없습니다. 오직 허용된 프로시저만 실행 가능합니다.

D. 유지보수성 (DRY 원칙)

웹, 모바일, 관리자 패널 등 5개의 앱이 같은 DB를 쓸 때, "회원가입" 로직이 바뀌면 DB에 있는 프로시저 하나만 수정하면 됩니다. 5개 앱을 전부 다시 배포할 필요가 없습니다.

4. 왜 피하는가? (단점)

A. "비즈니스 로직"의 위치

현대적 아키텍처(마이크로서비스)는 비즈니스 로직을 애플리케이션 계층(Java/Node 등)에 두는 것을 선호합니다.

이유: DB는 확장이 어렵습니다(수직 확장). 반면 앱 서버는 확장이 쉽습니다(수평 확장). CPU를 많이 쓰는 로직을 DB에 넣으면 병목현상의 원인이 됩니다.

B. 벤더 종속성 (Vendor Lock-in)

SQL Server는 T-SQL을 씁니다.
Oracle/PostgreSQL은 PL/SQL을 씁니다.
문법이 완전히 다릅니다. 프로시저를 많이 쓰면 나중에 DB를 다른 회사 제품으로 바꾸기가 매우 어렵습니다.

C. 버전 관리 및 디버깅

Git으로 코드를 관리하기가 앱 코드보다 어렵습니다.
운영 중인 서버에서 프로시저를 디버깅하는 것은 Python 스크립트를 디버깅하는 것보다 훨씬 까다롭습니다.

5. 프로시저 vs. 함수 (Functions)

면접 단골 질문입니다.

특징	스토어드 프로시저 (SP)	사용자 정의 함수 (UDF)
반환 값	값 반환이 없을 수도, 여러 개일 수도 있음 (OUT 변수 사용).	반드시 단 하나의 값(또는 테이블)을 반환해야 함.
사용법	독립적으로 실행함 (`CALL my_proc()`).	SQL 문장 안에서 사용함 (`SELECT my_func() ...`).
트랜잭션	`COMMIT` / `ROLLBACK` 사용 가능.	트랜잭션 제어 불가능.
출력	여러 개의 결과 셋(Table)을 반환 가능.	스칼라 값이나 테이블 변수만 반환.

6. 문법 예시 (돈 이체 시나리오)

트랜잭션이 포함된 프로시저의 예시입니다.

-- (MySQL/PostgreSQL 일반화 문법)
CREATE PROCEDURE TransferMoney(
    IN sender_id INT,
    IN receiver_id INT,
    IN amount DECIMAL(10,2),
    OUT status_message VARCHAR(100)
)
BEGIN
    -- 1. 트랜잭션 시작
    START TRANSACTION;

    -- 2. 보내는 사람 잔액 확인
    DECLARE current_balance DECIMAL(10,2);
    SELECT balance INTO current_balance FROM Accounts WHERE id = sender_id;

    IF current_balance >= amount THEN
        -- 로직: 보내는 사람 차감
        UPDATE Accounts SET balance = balance - amount WHERE id = sender_id;
        
        -- 로직: 받는 사람 입금
        UPDATE Accounts SET balance = balance + amount WHERE id = receiver_id;
        
        -- 3. 변경사항 저장
        COMMIT;
        SET status_message = '성공: 이체 완료';
    ELSE
        -- 3. 되돌리기 (안전장치)
        ROLLBACK;
        SET status_message = '오류: 잔액 부족';
    END IF;
END;

7. 고급 이슈: 파라미터 스니핑 (Parameter Sniffing)

프로시저 사용 시(특히 SQL Server) 발생하는 성능 문제입니다.

GetOrders(@date)라는 프로시저를 만듭니다.
처음 실행할 때, 결과가 1개뿐인 날짜를 넣었습니다.
DB는 작은 결과에 최적화된 실행 계획을 만듭니다. 그리고 이 계획을 캐싱(저장) 합니다.
두 번째 실행할 때, 결과가 100만 개인 날짜를 넣었습니다.
DB는 (1개짜리에 최적화된) 캐싱된 계획을 그대로 사용합니다. 100만 개 처리에는 최악의 계획일 수 있습니다.
결과: 쿼리가 타임아웃 되거나 엄청나게 느려집니다.
해결: WITH RECOMPILE 옵션 등을 써서 "처음 들어온 값을 믿지 말고 매번 계획을 다시 짜라"고 지시해야 합니다.

references