프로시저

📌 프로시저(Stored Procedure)란?

 

프로시저(Stored Procedure) 는 SQL 문을 저장해 두고 필요할 때 호출하여 실행할 수 있는 데이터베이스 객체임.

 

✅ 프로시저의 주요 특징

• 재사용 가능 → 동일한 SQL 작업을 반복 실행할 때 유용

• 성능 최적화 → 서버에서 실행되므로 네트워크 트래픽 감소

• 비즈니스 로직 캡슐화 → SQL 로직을 하나의 프로시저로 묶어 관리 가능

• 입력(매개변수) & 출력(결과값) 가능 → IN, OUT, INOUT 파라미터 지원

 

📌 기본적인 프로시저 호출 흐름

클라이언트 → CALL 프로시저명() → MySQL에서 SQL 실행 → 결과 반환

 

 

---

1️⃣ 프로시저의 기본 문법

DELIMITER $$  -- 구분자 변경 (MySQL에서 여러 문장을 실행하기 위해 필요)
CREATE PROCEDURE 프로시저명()
BEGIN
    -- SQL 실행문
END$$
DELIMITER ;  -- 기본 구분자로 복귀

 

📌 프로시저 호출

CALL 프로시저명();

 

📌 프로시저 삭제

DROP PROCEDURE IF EXISTS 프로시저명;

 

 

---

2️⃣ 간단한 프로시저 예제

 

✅ 기본적인 프로시저 생성 및 호출

DELIMITER $$
CREATE PROCEDURE GetUsers()
BEGIN
    SELECT * FROM users;
END$$
DELIMITER ;

 

📌 실행

CALL GetUsers();

 

✅ 결과

+----+---------+------------+
| id | name    | email      |
+----+---------+------------+
| 1  | Alice   | alice@test.com |
| 2  | Bob     | bob@test.com   |
+----+---------+------------+

 

 

---

3️⃣ 매개변수(Parameter) 사용하기

 

프로시저에서 매개변수를 활용할 수 있음.

 

✅ 1. IN 매개변수 (입력)

 

📌 특정 사용자 조회

DELIMITER $$
CREATE PROCEDURE GetUserById(IN userId INT)
BEGIN
    SELECT * FROM users WHERE id = userId;
END$$
DELIMITER ;

 

📌 실행

CALL GetUserById(1);

 

 

---

✅ 2. OUT 매개변수 (출력)

 

📌 사용자 수를 반환하는 프로시저

DELIMITER $$
CREATE PROCEDURE GetUserCount(OUT totalUsers INT)
BEGIN
    SELECT COUNT(*) INTO totalUsers FROM users;
END$$
DELIMITER ;

 

📌 실행

CALL GetUserCount(@count);
SELECT @count;  -- @count 변수에 저장된 결과 확인

 

 

---

✅ 3. INOUT 매개변수 (입력 & 출력)

 

📌 입력된 값에 10을 더해 반환하는 프로시저

DELIMITER $$
CREATE PROCEDURE AddTen(INOUT num INT)
BEGIN
    SET num = num + 10;
END$$
DELIMITER ;

 

📌 실행

SET @value = 5;
CALL AddTen(@value);
SELECT @value;  -- 15 출력

 

 

---

4️⃣ 프로시저에서 제어문 사용하기

 

✅ 1. IF 문 사용

 

📌 사용자 존재 여부 확인

DELIMITER $$
CREATE PROCEDURE CheckUserExists(IN userEmail VARCHAR(255), OUT existsFlag BOOLEAN)
BEGIN
    DECLARE count INT;
    SELECT COUNT(*) INTO count FROM users WHERE email = userEmail;
    
    IF count > 0 THEN
        SET existsFlag = TRUE;
    ELSE
        SET existsFlag = FALSE;
    END IF;
END$$
DELIMITER ;

 

📌 실행

CALL CheckUserExists('alice@test.com', @result);
SELECT @result;  -- 1 (TRUE) 또는 0 (FALSE)

 

 

---

✅ 2. LOOP 문 사용

 

📌 1부터 N까지 숫자를 출력하는 프로시저

DELIMITER $$
CREATE PROCEDURE PrintNumbers(IN N INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    
    my_loop: LOOP
        IF i > N THEN
            LEAVE my_loop;
        END IF;
        
        SELECT i;
        SET i = i + 1;
    END LOOP;
END$$
DELIMITER ;

 

📌 실행

CALL PrintNumbers(5);

 

✅ 결과

+----+
| i  |
+----+
| 1  |
| 2  |
| 3  |
| 4  |
| 5  |
+----+

 

 

---

5️⃣ 트랜잭션을 활용한 프로시저

 

✅ 트랜잭션(COMMIT, ROLLBACK)을 사용하여 안전한 데이터 처리

DELIMITER $$
CREATE PROCEDURE TransferMoney(IN senderId INT, IN receiverId INT, IN amount DECIMAL(10,2))
BEGIN
    DECLARE senderBalance DECIMAL(10,2);
    
    -- 1. 현재 잔액 확인
    SELECT balance INTO senderBalance FROM accounts WHERE id = senderId;
    
    -- 2. 잔액 부족 여부 확인
    IF senderBalance < amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '잔액 부족';
    ELSE
        -- 트랜잭션 시작
        START TRANSACTION;
        
        -- 3. 송금자 잔액 차감
        UPDATE accounts SET balance = balance - amount WHERE id = senderId;
        
        -- 4. 수신자 잔액 증가
        UPDATE accounts SET balance = balance + amount WHERE id = receiverId;
        
        -- 5. 트랜잭션 완료
        COMMIT;
    END IF;
END$$
DELIMITER ;

 

📌 실행

CALL TransferMoney(1, 2, 500);

✅ 트랜잭션을 사용하면 오류 발생 시 데이터 변경이 롤백됨.

 

---

6️⃣ 프로시저의 장점과 단점

장점	단점
반복적인 SQL 작업을 줄이고 코드 재사용 가능	복잡한 로직을 프로시저에 넣으면 유지보수 어려움
실행 속도 빠름 (서버에서 직접 실행)	디버깅이 어렵고, 개발 환경에서 코드 관리가 불편함
보안 강화 (GRANT 권한 설정 가능)	많은 프로시저를 만들면 데이터베이스 복잡도가 증가
네트워크 트래픽 감소 (클라이언트에서 여러 SQL을 실행하는 대신 하나의 프로시저 호출)	버전 관리가 어렵고, 코드 변경 시 관리 부담 증가

 

✅ 정리하면:

• 복잡한 로직이 많을 경우 → 비즈니스 로직을 애플리케이션 코드에서 처리하는 것이 더 좋을 수도 있음.

• 단순한 반복 작업(SQL 실행이 많을 경우) → 프로시저를 활용하면 성능 최적화 가능.

 

---

📌 결론

 

✅ MySQL 프로시저란?

• 반복적으로 실행되는 SQL 문을 저장하여, 필요할 때 호출하는 기능.

• 매개변수 (IN, OUT, INOUT)를 지원하여 유연한 데이터 처리 가능.

• 트랜잭션(COMMIT, ROLLBACK)을 지원하여 안전한 데이터 변경 가능.

 

✅ 언제 사용할까?

• 동일한 SQL 연산이 반복적으로 실행될 때 (CRUD 작업 최적화)

• 데이터 무결성을 유지하면서 여러 테이블을 조작할 때 (트랜잭션 관리)

• 네트워크 트래픽을 줄이고 성능을 개선할 때 (서버에서 직접 실행)

 

 

 

 

출처 : ChatGPT