[MySQL] 스토어드 프로시저

 개발을 하다 보면 쿼리문 작성에 시간을 많이 쏟게 되고, ORM 이던 Raw query 문이던 쿼리 작성을 위한 코드를 프로젝트에 많이 넣게 되는데, 이런 쿼리 레벨에서의 로직을 DB 에 직접 넣을 수 가 있다. MySQL 에서 '스토어드 프로시저' 라는 기능으로 제공되고 있다.

 

 간단히 말하면 데이터베이스 내에 하나의 함수를 작성한다고 보면 된다.

 

 스토어드 프로시저를 쓰면 아래와 같은 장점이 있다.

1. 성능 향상: 스토어드 프로시저는 데이터베이스 서버에서 실행되므로 네트워크 지연을 줄일 수 있고, 데이터 처리를 최적화할 수 있다.
2. 재사용성: 스토어드 프로시저는 여러 번 호출할 수 있으며, 코드 중복을 방지하고 관리가 쉽다.
3. 보안 강화: 애플리케이션에서 직접 SQL 쿼리를 작성하는 대신 스토어드 프로시저를 호출하면 SQL 인젝션 공격을 예방할 수 있다.
4. 로직 분리: 비즈니스 로직을 데이터베이스에 유지함으로써 애플리케이션과 데이터베이스 간의 분리를 강화할 수 있다.

스토어드 프로시저의 생성 방법은 아래와 같다.

DELIMITER //
-- 스토어드 프로시저 생성 
CREATE PROCEDURE GetEmployee(IN employeeID INT) 
-- 스토어드 프로시저 로직시작
BEGIN 
SELECT * 
FROM employees 
WHERE id = employeeID; 
END // 
DELIMITER ; 


-- 스토어드 프로시저 호출
CALL GetEmployee(1);

 

위의 DELIMITER 는 스토어드 프로시저 등을 선언할 때 자주 쓰는 문법인데, 쿼리의 종료 기호를 ; 가 아닌 // 으로 바꾸겠다는 의미이다. 스토어드 프로시저 등을 작성하다 보면 내부에서 ; 등을 여러번 쓰는 경우가 있다고 하는데, 이때 쿼리문이 종료되지 않도록 하기 위해서 사용한다고 한다.  

 위의 쿼리에서는 // 으로 쿼리의 종료 기호를 바꿨다가, 나중에 ; 으로 원상복귀 시켜주는 것이다.

 

입력받는 파라미터는 아래와 같이 IN [파라미터이름] [타입] 으로 선언할 수 있다.

CREATE PROCEDURE InsertProduct(IN productName VARCHAR(255), IN categoryID INT, IN price DECIMAL(10, 2))
BEGIN
    -- productName, categoryID, price 파라미터를 사용하여 제품 추가 로직을 작성
    INSERT INTO products (name, category_id, price) VALUES (productName, categoryID, price);
END;

 

VARCHAR 은 문자열, INT 는 정수, DECIMAL 은 실수 타입이다.

 

리턴할 값은 OUT [파라미터명] [타입] 으로 선언하고, 내부에서 사용할 변수 선언은 아래와 같이 DECLARE [변수명] [타입] 을 이용해서 선언하는데, 타입은 위에서 파라미터의 변수 타입을 선언한것과 같이 타입을 선언하면 된다.

CREATE PROCEDURE CalculateTotalPrice(IN quantity INT, IN unitPrice DECIMAL(10, 2), OUT totalPrice DECIMAL(10, 2))
BEGIN
    DECLARE taxRate DECIMAL(5, 2);
    SET taxRate = 0.1; -- 변수 초기화

    -- totalPrice 계산
    SET totalPrice = (quantity * unitPrice) * (1 + taxRate);
END;

 

쿼리의 로직을 코드가 아닌 데이터베이스 내부로 옮길 수 있다는 장점이 있긴 한데, 코드 관점에서 관리가 안될 것 같다는 생각이 들어 정말로 좋은 방법일까? 하고 생각이 들기도 한다.

DBA 가 따로 있어서 스토어드 프로시저를 요청하거나 할 수 있다면 관리를 알아서 잘 해줄테니.. 좋을 거 같은데 Feature 기능 개발자가 관리를 한다면 잘 될까 ?하는 의문이긴 한데, 실제 서비스에서 사용하면서 개발하지 않고는 잘 모르겠다.