내부함수와 프로시저

1. SQL에서 내부 함수와 프로시저

1) 내부 함수란? (Stored Function)

내부 함수는 입력값을 받아 내부적인 연산 처리를 거친 후, 반드시 하나의 결과값(리턴값)을 반환하는 데이터베이스 오브젝트입니다. 기본적으로 SQL 문(SELECT, WHERE, INSERT 등) 내부에서 일반적인 내장 함수(예: NOW(), CONCAT())처럼 직접 호출되어 녹아들 수 있는 구조를 가집니다.

• 예시 : 회원 등급 코드(VIP, GOLD, SILV)를 입력받아 사용자가 보기 편한 한글 명칭으로 변환하여 리턴하는 함수입니다.

DELIMITER //
 
CREATE FUNCTION fn_get_grade_name(p_grade_code VARCHAR(10))
RETURNS VARCHAR(20)
DETERMINISTIC -- 동일한 입력에 대해 항상 동일한 결과를 반환함을 명시 (성능 최적화)
BEGIN
    DECLARE v_grade_name VARCHAR(20);
 
    -- 비즈니스 규칙 분리 및 가공 로직
    IF p_grade_code = 'VIP' THEN
        SET v_grade_name = '최우수 회원';
    ELSEIF p_grade_code = 'GOLD' THEN
        SET v_grade_name = '우수 회원';
    ELSEIF p_grade_code = 'SILV' THEN
        SET v_grade_name = '일반 회원';
    ELSE
        SET v_grade_name = '미지정';
    END IF;
 
    -- 반드시 하나의 값을 리턴해야 함
    RETURN v_grade_name;
END //
 
DELIMITER ;

2) 프로시저란? (Stored Procedure)

프로시저는 특정한 비즈니스 로직이나 일련의 SQL 작업들을 하나의 실행 단위로 묶어둔 절차적 스크립트 블록입니다. 함수와 달리 리턴값이 필수적이지 않으며, 단독으로 CALL 프로시저명() 형태로 호출되어 실행됩니다. 여러 개의 행(ResultSet)을 반환하거나, 입력(IN) 파라미터뿐만 아니라 출력(OUT) 파라미터를 통해 여러 값을 동시에 넘겨줄 수도 있습니다.

• 예시: 사용자가 포인트를 사용할 때, '포인트 이력 추가'와 '회원 테이블의 잔여 포인트 차감'이라는 두 작업을 하나의 트랜잭션으로 묶어 처리하는 커스텀 비즈니스 로직입니다.

DELIMITER //
 
CREATE PROCEDURE sp_use_user_point(
    IN p_user_id INT,
    IN p_use_point INT,
    OUT p_result_code VARCHAR(10) -- 결과를 애플리케이션에 전달할 출력 파라미터
)
BEGIN
    -- 에러 발생 시 롤백을 수행하기 위한 핸들러 선언
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 예외 발생 시 전처리를 모두 취소하고 에러 코드 반환
        ROLLBACK;
        SET p_result_code = 'FAIL';
    END;
 
    -- 트랜잭션 시작 (개별 트랜잭션 관리 필요)
    START TRANSACTION;
 
    -- 1. 포인트 사용 이력 테이블에 데이터 삽입 (DML)
    INSERT INTO point_history (user_id, amount, type, created_at)
    VALUES (p_user_id, p_use_point, 'USE', NOW());
 
    -- 2. 회원 테이블의 현재 포인트 정보 차감 (DML)
    UPDATE users
    SET current_point = current_point - p_use_point
    WHERE user_id = p_user_id;
 
    -- 모든 작업이 정상적으로 수행되면 영구 반영
    COMMIT;
    SET p_result_code = 'SUCCESS';
 
END //
 
DELIMITER ;

2. 내부 함수(Stored Function) 사용의 장단점과 주의점

1) 장점: 비즈니스 로직 분리를 통한 개발 편의성

• 데이터의 포맷팅(예: 날짜 형식 변경, 특정 코드값의 명칭 치환)이나 반복적인 계산 로직을 DB 내부 함수로 캡슐화할 수 있습니다.
• 이 경우 백엔드 애플리케이션 코드가 복잡한 가공 로직을 직접 품지 않아도 되므로, 단순 CRUD 외의 세부적인 연산 처리를 고려하지 않아도 되어 SQL 작성이 간결해지고 개발 편의성이 올라갑니다.

2) 단점: 지나친 사용 시 데이터 정합성 훼손 및 속도 저하

• 속도 저하 (Row 단위 연산): SELECT 절이나 WHERE 절에 내부 함수를 사용하면, 조회되는 모든 행(Row)마다 함수가 매번 실행되는 Row-by-Row 부하가 발생합니다. 10만 건을 조회하면 함수가 10만 번 호출되므로 대량 데이터 처리 시 성능이 급격히 저하됩니다.
• 정합성 위험: 함수 내부에서 또 다른 테이블을 조회하거나 변경하는 로직이 얽힐 경우, 메인 쿼리의 실행 흐름과 예외 처리가 불투명해져 예측하지 못한 데이터 정합성 오류가 발생할 위험이 커집니다.

3) 단점: 실행 계획(Execution Plan)의 무력화

• 데이터베이스의 옵티마이저는 전체 쿼리를 분석하여 가장 최적의 경로(인덱스 활용 등)로 실행 계획을 수립합니다.
• 하지만 WHERE 절에 내부 함수를 씌워 컬럼을 가공해 버리면(WHERE my_function(column) = '값'), 옵티마이저가 해당 컬럼에 걸린 인덱스를 타지 못하고 풀 테이블 스캔(Full Table Scan)을 수행하게 됩니다. 즉, 엔진의 최적화 경로를 따르지 않아 심각한 병목을 유발합니다.

3. 프로시저(Stored Procedure)의 특징과 한계

1) 장점: 커스텀 비즈니스 로직 수행 및 네트워크 비용 절감

• 조건문(IF-ELSE), 반복문(WHILE), 예외 처리 등을 활용하여 DB 엔진 내부에서 복잡한 커스텀 비즈니스 로직을 완결성 있게 수행할 수 있습니다.
• 백엔드 서버와 DB 서버 간에 수많은 쿼리와 데이터를 주고받을 필요 없이, 서버는 프로시저 호출(CALL) 한 번만 보내고 DB 내부에서 모든 처리를 끝내기 때문에 네트워크 트래픽 비용이 극적으로 줄어듭니다.

2) 단점: 코드 파악 및 유지보수의 난해함 (추적의 한계)

• 형상 관리(Git 등)를 통해 애플리케이션 코드를 추적하는 현대적 개발 환경에서, 프로시저 내부에 숨겨진 로직은 '찾기가 힘듭니다.'
• 로직이 백엔드 코드와 DB 내부에 파편화되어 있으면 에러가 발생했을 때 디버깅이 매우 까다로우며, 시스템 전체의 데이터 흐름을 한눈에 파악하기 어렵게 만드는 주원인이 됩니다.

3) 단점: 개별 트랜잭션 관리의 필요성과 락(Lock) 리스크

• 프로시저는 내부에서 여러 DML(Insert, Update, Delete) 작업을 연속적으로 수행하므로, 내부적인 개별 트랜잭션 제어(COMMIT, ROLLBACK)를 정밀하게 관리해야 합니다.
• 로직이 길어지거나 예외 처리가 누락되면 트랜잭션이 종료되지 않고 길어져(Long Transaction), 테이블이나 행에 락(Lock)을 오래 쥐게 됩니다. 이는 실시간 트래픽을 처리하는 동시성 아키텍처에서 다른 요청들을 전부 마비시키는 데드락(Deadlock)이나 커넥션 풀 고갈을 초래할 수 있습니다.

4. 추가 개념: Routines, Trigger, Function, Schedule

DB 내부에서 자율적으로 혹은 절차적으로 돌아가는 이 요소들은 큰 틀에서 서로 유기적으로 연결된 DB 프로그래밍 객체들입니다.

• Routines (루틴) 데이터베이스 내에 저장되어 실행 가능한 절차적 프로그램들을 통칭하는 상위 개념입니다. 앞서 설명한 Stored Procedure와 Stored Function이 모두 Routines에 포함됩니다.
• Trigger (트리거) 특정 테이블에 INSERT, UPDATE, DELETE 같은 이벤트가 발생했을 때, 시스템에 의해 자동으로 유발(Trigger)되어 실행되는 특수한 루틴입니다. (예: 주문 테이블에 데이터가 들어오면, 로그 테이블에 이력을 자동으로 남기는 구조) 데이터 정합성을 강제할 수 있지만, 애플리케이션 모르게 뒤에서 동작하므로 남용 시 추적이 매우 어려워집니다.
• Function (함수) 앞서 언급한 Stored Function을 의미하며, 루틴의 일종으로서 값을 연산하고 리턴하여 쿼리 내에 녹아드는 역할을 담당합니다.
• Schedule / Event Scheduler (스케줄러) OS의 크론탭(Crontab)이나 리눅스의 스케줄러처럼, 정해진 특정 시간이나 주기마다 주기적으로 루틴을 자동 실행해 주는 기능입니다. (예: 매일 새벽 3시에 탈퇴한 회원의 데이터를 배치성으로 정리하는 작업) 외부 배치 서버(Spring Batch 등)를 두지 않고 DB 자체 자원만으로 자동화 작업을 수행할 때 활용됩니다.