[DB] Stored Procedure

• 데이터베이스 관리 시스템(RDBMS)에 저장되어 특정 작업을 수행하는 로직을 정의한 코드 블록이다.
• 여러 SQL 작업을 절차적으로 수행할 수 있도록 지원하며, 복잡한 비즈니스 로직을 캡슐화하여 데이터베이스에서 직접 실행할 수 있다.
• 하나의 구체적인 작업을 수행하는 데 적합하다.
• 조건문을 사용하여 로직을 분기 처리할 수 있으며, 반복문을 통해 반복 작업을 수행할 수 있다.
• 에러 처리를 위한 로직을 포함할 수 있으며, 특정 상황에 따라 에러를 발생시킬 수도 있다.
• 입력(IN), 출력(OUT), 입력 및 출력(INOUT) 파라미터를 사용하여 데이터를 주고받을 수 있다.

 

예제 1: 두 정수의 곱셈 결과를 반환하는 프로시저

• 두 개의 정수를 입력받아 그 곱을 출력하는 프로시저.

DELIMITER $$
CREATE PROCEDURE product(IN a INT, IN b INT, OUT result INT)
BEGIN
    SET result = a * b;
END
$$
DELIMITER ;

CALL product(5, 7, @result);
SELECT @result;

 

예제 2: 두 정수를 맞바꾸는 프로시저

• 두 개의 정수를 입력받아 값의 위치를 바꾸는 프로시저.

DELIMITER $$
CREATE PROCEDURE swap(INOUT a INT, INOUT b INT)
BEGIN
    DECLARE temp INT;
    SET temp = a;
    SET a = b;
    SET b = temp;
END $$
DELIMITER ;

SET @a = 5, @b = 7;
CALL swap(@a, @b);
SELECT @a, @b;

 

예제 3: 부서별 평균 연봉을 가져오는 프로시저

• 모든 부서의 평균 연봉을 계산하여 출력하는 프로시저.

DELIMITER $$
CREATE PROCEDURE get_dept_avg_salary()
BEGIN
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employee
    GROUP BY dept_id;
END $$
DELIMITER ;

CALL get_dept_avg_salary();

 

예제 4: 닉네임 변경과 로그 기록 프로시저

• 사용자가 프로필 닉네임을 변경하면 이전 닉네임을 로그에 기록하고 새 닉네임으로 업데이트하는 프로시저.

DELIMITER $$
CREATE PROCEDURE change_nickname(IN user_id INT, IN new_nick VARCHAR(30))
BEGIN
    INSERT INTO nickname_logs (user_id, old_nickname, changed_at)
    SELECT id, nickname, NOW() FROM users WHERE id = user_id;

    UPDATE users SET nickname = new_nick WHERE id = user_id;
END $$
DELIMITER ;

CALL change_nickname(1, 'ZIDANE');

 

Stored Procedure vs Stored Function

	Stored Procedure	Stored Function
값 반환	여러 값을 반환할 수 있으며 OUT 파라미터를 사용하여 반환 값 반환 필수 아님	하나의 값을 반환 (RETURNS 절에서 정의된 타입) 값 반환 필수
사용 목적	복잡한 비즈니스 로직 여러 SQL 작업 수행	특정 계산이나 값 반환
호출 방법	CALL 문으로 호출	SQL 문 내에서 직접 호출 가능
파라미터	IN, OUT, INOUT 파라미터 사용 가능	오직 IN 파라미터 사용 가능
내부 트랜잭션 관리	프로시저 내에서 트랜잭션을 관리할 수 있음	함수 내에서 트랜잭션 관리 불가능
사용 가능 위치	주로 특정 작업을 수행하거나 트리거에서 사용	SQL 쿼리 내에서 직접 사용할 수 있음

 

Stored Procedure의 장단점

1. 네트워크 트래픽 감소 및 응답 속도 향상: 클라이언트와 서버 간의 데이터 전송이 줄어들어 네트워크 트래픽이 감소한다. 데이터베이스에서 직접 실행되므로 여러 SQL 명령문을 한 번의 호출로 처리할 수 있어 응답 속도가 향상된다.
2. 재사용 가능성: 한 번 작성된 프로시저는 여러 애플리케이션이나 서비스에서 재사용할 수 있다. 이를 통해 코드의 중복을 줄이고 유지보수를 간소화할 수 있다. 재사용 가능하다는 장점은, 다양한 곳에서 사용될 경우 의존성이 생기고 변경 관리가 어려워질 수 있다는 단점으로 작용할 수 있다. 한 곳에서 변경이 이루어지면 모든 연관된 시스템에 영향을 미칠 수 있다.
3. 보안 강화: 데이터베이스 내부에서 실행되기 때문에 외부에서 로직의 구현 방식을 알 수 없다. 이것은 보안 측면에서는 장점이 될 수 있지만, 다른 개발자나 관리자가 프로시저의 동작을 이해하기 어렵게 만들어 유지보수에 문제가 생길 수 있다.
4. 유연성 부족 및 복잡한 코드 작성 어려움: 프로시저 내에서 복잡한 로직이나 동적 쿼리 처리는 어렵다. 절차적 언어로 작성된 프로시저는 제한된 프로그래밍 기능을 제공하여 복잡한 비즈니스 로직을 구현하기 어렵다.
5. 디버깅의 어려움: 디버깅 도구의 제한적 지원으로 인해 프로시저 내에서 발생하는 오류를 찾고 수정하는 작업이 복잡하고 시간이 많이 걸릴 수 있다.
6. 플랫폼 종속성: 프로시저는 특정 DBMS에 종속적이므로, 데이터베이스를 변경할 경우 프로시저를 다시 작성하거나 수정해야 할 수도 있다.
7. 배포 및 버전 관리: 프로시저의 변경은 데이터베이스에 직접 반영되므로, 배포와 버전 관리에 신중해야 한다. 특히 다수의 개발자나 팀이 작업할 경우, 버전 충돌이나 호환성 문제가 발생할 수 있다.