[DATABASE] Stored Procedure

• 사용자가 정의한 프로시저
• RDBMS에 저장되고 사용되는 프로시저
• 구체적인 하나의 task를 수행

예시

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

• 위 쿼리는 곱셈을 수행하는 프로시저이다.
• input parameter인 a, b는 IN으로 명시해주고 결과 값 result는 OUT으로 명시해준다.

IMPORTANT
;가 끝이라고 판단할 수 있기 때문에 delimiter를 지정해주어야한다.

프로시저 실행 예시

call product(5, 7, @result)
select @result;

• @result는 사용자가 정의한 변수이다. 이 변수에 결과가 저장된다.

INOUT

delimiter $$
CREATE PROCEDURE swap(INOUT a int, INOUT b int)
BEGIN
    SET @temp = a;
    SET a = b;
    SET b = @temp;
END
$$
delimiter;

• INOUT이라고 지정할 경우 input parameter도 될 수 있고 결과도 될 수 있다.

SET @a = 5, @b = 7;
call swap(@a, @b);
select @a, @b;

• @a, @b 변수를 지정하여 값을 할당한 후 프로시저를 수행하면 @a와 @b가 swap(뒤바뀐) 결과를 얻을 수 있다.

NOTE
Parameter를 지정하지 않고 프로시저 내에서 쿼리를 수행할 경우 수행한 결과를 출력한다.

---

장점

• application에 transparent하다.
  • 여러 웹 애플리케이션에서 공통된 로직을 procedure로 생성하여 호출할 수 있다.
• network traffic을 줄여서 응답 속도를 향상시킬 수 있다.
  • 웹 애플리케이션과 DB는 서로 다른 서버를 사용하기 때문에 select, insert, update 등을 수행할 때마다 network traffic이 발생한다. 반면에 procedure를 생성하여 select, insert, update를 모두 수행하면 network traffic을 줄일 수 있다.
• 여러 서비스에서 재사용 가능하다.
  • spring, django, node 등 여러 서비스에서 공통된 로직을 procedure에 생성하여 재사용할 수 있다.
• 민감한 정보에 대한 접근을 제한할 수 있다.

단점

• 유지 관리 보수 비용이 커진다.
  • 웹 애플리케이션과 DB의 procedure를 모두 확인해야하며 둘 다 버전 관리를 해야한다.
• DB에 비지니스 로직을 수행하게되면 traffic이 커질 때 CPU 사용량이 늘어난다. 이로 인해 DB를 증설해야하지만 모든 데이터를 복제해야하기 때문에 간단하지 않다.
• procedure가 항상 transparent하지 않다.
  • procedure의 이름을 바꿔야한다면 바뀐 이름으로 procedure를 새로 생성한 후 애플리케이션에서 이름이 바뀐 procedure를 호출하도록 수정해야한다. 이처럼 상당히 번거로워질 수 있다.
• transparent하다고 무조건 좋은 것은 아니다.
  • procedure의 로직에 문제가 발생할경우 애플리케이션에 로직을 구현했을 때보다 영향이 커진다. 애플리케이션 서버 여러 대 중 한 대에만 로직을 수정하여 확인할 수 있기때문에 문제가 생길 경우 해당 서버에만 영향을 받지만 procedure에 문제가 발생할 경우 전체적으로 영향을 받게 된다.
• 로직을 애플리케이션(소스 코드)에 두고도 응답 속도를 향상 시킬 수 있다.
  • select, insert 등 쿼리를 동시에 수행할 경우에도 응답 속도를 향상 시킬 수 있다.
  • redis와 같은 cache를 사용하여 최초만 DB에 데이터를 요청하고 이후에는 cache에서 가져와 응답 속도를 향상 시킬 수 있다.
• procedure가 민감한 정보에 대한 접근을 완벽히 제한할 수는 없다.
  • procedure에서 민감한 정보를 return한다면 개발자들에게는 접근을 제한할 수 없다.
  • 접근 제한으로 인해 개발 및 CS 업무의 신속함이 떨어질 수 있다.
• 복잡하고 유연한 코드를 작성하기 어렵다.
• 가독성이 떨어진다.
• 디버깅이 어렵다.