Stored Procedure

사용법

예시는 MySQL 기준이다.

 

두 정수의 곱셈 결과를 나타내는 프로시저

mysql> DELIMITER $$ -- 기존에 쿼리에서 사용하던 ';' 기호 대신 "$$"가 쿼리의 끝을 나타내도록 변경
mysql> CREATE PROCEDURE product(IN a int, IN b int, OUT result int) -- a, b는 input이고 result라는 변수를 output으로 사용함. 아무것도 적지 않을 경우 default로서 IN이 적용됨.
BEGIN
	SET result = a * b;
END
$$
mysql> DELIMITER ; -- 다시 ';'가 쿼리의 끝을 나타내도록 변경

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

결과로는 35가 테이블에 출력된다.

 

두 정수를 맞바꾸는 프로시저

mysql> DELIMITER $$
mysql> CREATE PROCEDURE swap(INOUT a int, INOUT b int) -- a, b는 input과 output의 역할을 동시에 함
BEGIN
	SET @temp = a;
    SET a = b;
    SET b = @temp;
END
$$
mysql> DELIMITER ;

mysql> set @a = 5, @b = 7;
mysql> call swap(@a, @b);
mysql> select @a, @b;

@a와 @b에 각각 7과 5가 출력된다

 

각 부서 별 평균 연봉을 가져오는 프로시저

mysql> DELIMITER $$
mysql> CREATE PROCEDURE get_dept_avg_salary()
BEGIN
	select dept_id, avg(salary)
    from employee
    group by dept_id;
END
$$
mysql> DELIMITER ;

mysql> call get_dept_avg_salary();

결과는 다음과 같다.

 

Stored Procedure와 Stored Function의 차이

	stored procedure	stored function
create 문법	create procedure...	create function...
return 키워드로 값 반환	불가능 (SQL Server에서 *상태코드 반환용으로는 가능)	가능 (MySQL, SQL Server는 값 반환 시 필수)
파라미터로 값 반환	가능 (값 반환 시 필수)	일부 가능 (Oracle은 가능하지만 권장되지 않음, PostgreSQL 가능)
값 반환이 필수인가?	필수 아님	필수
SQL statement에서 호출	불가능	가능
트랜잭션 사용	가능	대부분 불가능 (Orcale은 가능)
주된 사용 목적	비즈니스 로직	단순 연산

*상태코드 반환이란 프로시저 성공 여부 자체를 0 또는 1의 값으로 반환하는 것을 의미한다.

 

Stored Procedure 장/단점

장점

1. DB와 WAS 간 통신 횟수를 줄임으로써 응답 속도를 개선할 수 있다.

비즈니스 로직이 logic tier에 있을 때 해당 로직이 여러 개의 SQL 문으로 이루어져 있으면 각 SQL마다 네트워크 트래픽이 발생한다. 이때 해당 SQL 문들을 하나의 프로시저로 대체할 수 있다고 하면 그만큼 트래픽이 감소해 응답 속도를 개선할 수 있는 경우도 있을 것이다.

 

2. 여러 서비스에서 재사용이 가능하다.

예를 들어 logic tier의 서비스들이 3개 존재하고 각각 Java(Spring), Python(django), JavaScript(Node.js)로 이루어져 있을 때 이들이 공유하는 하나의 DB를 이용한 비즈니스 로직 A가 존재한다고 가정하자. A는 각 WAS 별로 따로 구현되어야 할 것이다. 이런 경우에는 이들이 공유하는 DB에 프로시저를 설정해 둔다면 서비스 별로 따로 구현하는 수고를 줄일 수 있어 유지보수가 용이해질 수 있다.

 

3. 민감한 정보에 대한 접근을 제한할 수 있다.

개발자에게 노출시키면 안 되는 개인정보가 있다고 가정하자. 이때 개발자가 DB에 담겨있는 데이터에 직접적으로 접근하는 것은 막되 이를 조회하는 프로시저에 대한 권한만 줌으로써 이를 해결할 수 있다.

 

단점

1. 비즈니스 로직이 logic과 data 2개의 tier에 동시에 존재함으로써 유지보수 비용이 증가한다.

 

2. DB 서버를 추가하는 작업이 WAS를 늘리는 작업에 비해 어렵다.

프로시저에 많은 비즈니스 로직을 의존하고 있는 서비스의 경우 특정 시기에 갑자기 트래픽이 급증해 DB에 대한 부하가 늘어났다고 가정하자. DB 서버 추가를 통해 문제를 해결하고 싶어도 기존 DB의 데이터를 새로운 DB에도 추가해 주어야 제 역할을 할 수 있는데 이미 부하가 한계에 달한 상황에서는 쉽지 않을 것이다.

반면 비즈니스 로직을 logic tier에 두고 WAS를 증설하는 방법을 채택하면 AWS 등에서 제공하는 Auto Scailing을 이용해 이러한 상황을 다소 간단하게 해결할 수 있다. 

 

3. 특정 아키텍처 구조에서는 재사용 가능하다는 장점이 의미가 없을 수도 있다.

장점 2번에서의 그림과 다르게 위와 같이 DB의 접근을 담당하는 서비스가 따로 존재하는 구조에서는 프로시저의 재사용 가능하다는 장점이 유명무실해진다.

 

4. 비즈니스 로직을 logic tier에 놓더라도 응답속도를 향상시킬 수 있는 방법들이 존재한다.

위와 같이 캐시를 활용하거나, 동시에 진행 가능한 SQL들의 경우엔 비동기로 처리하는 등의 방법이 있다.

 

5. 프로시저가 민감 정보에 대한 접근을 완벽하게 차단할 수는 없다.

개발자가 악의를 품고 민감 정보에 대한 조회를 수행하는 프로시저를 개발할 수도 있고, 애초에 민감 정보에 대한 접근을 무턱대고 막는다면 개발 및 CS 업무의 신속성이 떨어지므로 DB 혹은 테이블에 대한 담당자를 지정하거나 민감한 정보를 암호화하는 등의 대안을 사용하는 것이 좋다.

 

6. 가독성이 떨어지고 프로그래밍 언어에 포함된 여러 유용한 기능들을 사용할 수 없다.