MySQL 프로시저 작성 문제

Introduction

MySQL 저장 프로시저(Stored Procedure)는 복잡한 비즈니스 로직을 데이터베이스 내에서 실행할 수 있게 해주는 강력한 도구입니다. 하지만 프로시저를 작성하면서 다양한 구문 에러, 런타임 에러, 그리고 성능 문제에 직면할 수 있습니다. 이 글에서는 프로시저 관련 흔한 문제들과 해결 방법을 다루겠습니다.

Environment

# MySQL 버전 확인
mysql --version

# 프로시저 관련 설정 확인
mysql -u root -p -e "SHOW VARIABLES LIKE '%procedure%';"

-- 프로시저 테스트 환경
DELIMITER //

CREATE PROCEDURE test_procedure()
BEGIN
    SELECT 'Hello World';
END //

DELIMITER ;

-- 프로시저 실행
CALL test_procedure();

Problem

프로시저 작성 시 발생하는 에러들:

-- 에러 1: DELIMITER 문제
CREATE PROCEDURE get_user_count()
BEGIN
    SELECT COUNT(*) FROM users;
END;
-- ERROR 1064: You have an error in your SQL syntax

-- 에러 2: 변수 선언 위치 오류
DELIMITER //
CREATE PROCEDURE process_data()
BEGIN
    DECLARE v_count INT;
    SET v_count = 0;
    DECLARE v_name VARCHAR(100);  -- BEGIN 다음에 선언해야 함
END //
DELIMITER ;
-- ERROR 1064: DECLARE statement not allowed here

-- 에러 3: 커서 사용법 오류
DELIMITER //
CREATE PROCEDURE iterate_users()
BEGIN
    DECLARE v_done BOOLEAN DEFAULT FALSE;
    DECLARE v_user_id INT;
    
    DECLARE cur CURSOR FOR SELECT id FROM users;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    
    OPEN cur;
    FETCH cur INTO v_user_id;  -- 반복문 없이 단일 FETCH
    CLOSE cur;
END //
DELIMITER ;

# 프로시저 에러 로그 확인
tail -100 /var/log/mysql/error.log | grep -i procedure
# [ERROR] Procedure mydb.process_data:15: DECLARE statement not allowed here

Analysis

프로시저 에러의 원인을 분석했습니다:

-- 프로시저 목록 확인
SHOW PROCEDURE STATUS WHERE Db = 'mydb';

-- 프로시저 상세 정보 확인
SHOW CREATE PROCEDURE get_user_count;

-- 프로시저 에러 디버깅
DELIMITER //
CREATE PROCEDURE debug_procedure()
BEGIN
    DECLARE v_test INT DEFAULT 0;
    
    -- 잘못된 참조
    SET v_test = non_existent_variable;
    
    -- 에러 발생
    SELECT * FROM non_existent_table;
END //
DELIMITER ;

CALL debug_procedure();
-- ERROR 1054: Unknown column 'non_existent_variable' in 'field list'

Solution

1단계: 올바른 프로시저 구문

-- 정확한 DELIMITER 사용
DELIMITER //

CREATE PROCEDURE get_user_count()
BEGIN
    DECLARE v_count INT;
    
    SELECT COUNT(*) INTO v_count
    FROM users;
    
    SELECT v_count AS user_count;
END //

DELIMITER ;

-- 프로시저 실행
CALL get_user_count();

2단계: 변수 선언 및 처리

DELIMITER //

CREATE PROCEDURE process_order(
    IN p_order_id INT,
    OUT p_result VARCHAR(200)
)
BEGIN
    DECLARE v_status VARCHAR(50);
    DECLARE v_amount DECIMAL(10,2);
    DECLARE v_user_id INT;
    
    -- 주문 정보 조회
    SELECT status, amount, user_id
    INTO v_status, v_amount, v_user_id
    FROM orders
    WHERE id = p_order_id;
    
    -- 상태 처리
    CASE v_status
        WHEN 'pending' THEN
            SET p_result = CONCAT('Order ', p_order_id, ' is pending');
        WHEN 'completed' THEN
            SET p_result = CONCAT('Order ', p_order_id, ' completed with amount: ', v_amount);
        WHEN 'cancelled' THEN
            SET p_result = CONCAT('Order ', p_order_id, ' was cancelled');
        ELSE
            SET p_result = CONCAT('Order ', p_order_id, ' has unknown status: ', v_status);
    END CASE;
    
    -- 결과 반환
    SELECT p_result AS result;
END //

DELIMITER ;

3단계: 커서 사용법

DELIMITER //

CREATE PROCEDURE iterate_users()
BEGIN
    DECLARE v_done BOOLEAN DEFAULT FALSE;
    DECLARE v_user_id INT;
    DECLARE v_user_name VARCHAR(100);
    
    -- 커서 선언
    DECLARE cur CURSOR FOR 
        SELECT id, name FROM users WHERE status = 'active';
    
    -- 핸들러 선언 (반드시 커서 선언 다음에)
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    
    -- 결과 테이블 생성
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_results (
        user_id INT,
        user_name VARCHAR(100)
    );
    
    -- 커서 열기
    OPEN cur;
    
    -- 반복 루프
    read_loop: LOOP
        FETCH cur INTO v_user_id, v_user_name;
        
        IF v_done THEN
            LEAVE read_loop;
        END IF;
        
        -- 사용자 처리 로직
        INSERT INTO temp_results (user_id, user_name)
        VALUES (v_user_id, v_user_name);
        
    END LOOP;
    
    -- 커서 닫기
    CLOSE cur;
    
    -- 결과 반환
    SELECT * FROM temp_results;
    
    -- 임시 테이블 삭제
    DROP TEMPORARY TABLE IF EXISTS temp_results;
END //

DELIMITER ;

4단계: 에러 처리 및 트랜잭션

DELIMITER //

CREATE PROCEDURE transfer_funds(
    IN p_from_account INT,
    IN p_to_account INT,
    IN p_amount DECIMAL(10,2),
    OUT p_success BOOLEAN,
    OUT p_message VARCHAR(200)
)
BEGIN
    DECLARE v_from_balance DECIMAL(10,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_success = FALSE;
        SET p_message = 'Transaction failed due to error';
    END;
    
    -- 트랜잭션 시작
    START TRANSACTION;
    
    -- 잔액 확인
    SELECT balance INTO v_from_balance
    FROM accounts
    WHERE id = p_from_account
    FOR UPDATE;
    
    -- 잔액 부족 확인
    IF v_from_balance < p_amount THEN
        ROLLBACK;
        SET p_success = FALSE;
        SET p_message = 'Insufficient funds';
    ELSE
        -- 송금 처리
        UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_account;
        UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_account;
        
        COMMIT;
        SET p_success = TRUE;
        SET p_message = 'Transfer successful';
    END IF;
    
    SELECT p_success AS success, p_message AS message;
END //

DELIMITER ;

Lessons Learned

1. DELIMITER 필수: 프로시저 생성 시 세미콜론을 구분자로 사용하지 않도록 DELIMITER를 변경해야 합니다
2. 변수 선언 순서: DECLARE는 BEGIN과 첫 번째 실행문 사이에 선언해야 합니다
3. 핸들러 위치: DECLARE HANDLER는 커서 선언 다음에 위치해야 합니다
4. 트랜잭션 관리: 프로시저에서 트랜잭션을 사용할 때는 반드시 예외 처리를 포함해야 합니다
5. 매개변수 활용: IN, OUT, INOUT 매개변수를 적절히 활용하여 재사용 가능한 프로시저를 작성해야 합니다

이 블로그는 외부 스폰서십, 제휴 마케팅 또는 광고 수익을 받지 않습니다.