stored procedure란? 그리고 장.단점

stored procedure란?

 

• RDBMS에 저장되고 사용되는 사용자가 정의한 프로시저입니다.

 

• 구체적인 하나의 테스크(task)를 수행합니다.

 

• 에러를 핸들링하거나 에러를 일으키는 등의 다양한 로직을 정의할 수 있습니다.

stored procedure 사용법

 

두 정수의 곱셈 결과를 가져오는 프로시저

 

delimiter $$
create procedure product(in a int, in b int, out result int)
begin
    set result = a * b;
end
$$
delimiter ;

 

• in은 인자로 받는 파라미터를 의미하며 out은 아웃 파라미터를 의미합니다.( in은 생략 가능합니다. )

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

 

delimiter $$
create procedure swap(inout a int, inout b int)
begin
    set @temp = a;
    set a = b;
    set b = @temp;
end
$$
delimiter ;

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

 

• inout은 마라미터를 받음과 동시에 아웃 파라미터로 사용하기 위해 사용하는 키워드입니다.

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

 

delimiter $$
create procedure get_dept_avg_salary()
begin
    select dept_id, avg(salary)
    from employee
    group by dept_id
end
$$
delimiter ;

call get_dept_avg_salary();

 

• select문을 위와같이 사용하면 return 키워드나 out 파라미터를 사용하지 않아도 결과를 return합니다.

사용자가 프로필 닉네임을 변경하면 이전 닉네임은 로그에 저장하고 새 닉네임으로 업데이트

 

delimiter $$
create procedure change_nickname(user_id int, new_nick varchar(3))
begin
    insert into nickname_logs(
        select id, nickname, now() from users where id = user_id
    );
    update users set nickname = new_nick where id = user_id;
end
$$
delimiter ;

stored procedure vs stored function

 

유튜버 "쉬운 코드"님의 자료를 참조하였습니다.

 

stored procedure 장.단점

 

장점

 

• 컴파일한 stored procedure를 재사용함으로써 컴파일된 쿼리에 대한 캐시를 적극적으로 활용하여 성능에 대한 이점을 얻을 수 있습니다. ( RDBMS 버전에 따라서 쿼리문은 똑같지만 대.소문자 사용여부에 따라서 다른 쿼리문으로 판단하여 새로 컴파일될 수 있습니다. )

 

• stored procedure의 로직 부분만 변경했다면 서버를 다시 빌드하여 새로 배포할 필요가 없습니다.

 

• 서버는 stored procedure 호출을 DB서버로 요청하면 되기 때문에 한 개 또는 여러 개의 SQL문을 사용함으로써 발생되는 네트워크 트래픽량을 줄일 수 있습니다.

 

• 같은 DB를 사용하는 여러 서비스에서 stored procedure를 사용하여 로직을 재사용할 수 있습니다.

 

• DB에 대한 직접적인 접근을 막고 비즈니스 로직 구현에 필요한 stored procedure를 제공함으로써 프로젝트 참여자로부터 민감한 데이터에 대한 직접적인 접근을 막을 수 있습니다.

단점

 

• 서버 코드와 DB stored procedure 코드 둘 다 신경써가면서 코딩하고 버전관리하고 코딩해야하기 때문에 유지 관리 보수 비용이 커집니다.

 

• DB 서버의 CPU 사용률이 높아져 DB 서버의 부하가 커질 수 있습니다. 그렇기 때문에 스케일 아웃이 비교적 쉬운 웹 서버 환경에서는 stored procedure을 사용하지 않고 stored procedure 로직을 웹 서버로 끌고와서 처리하는 것이 좋을 떄도 있습니다.

 

• stored procedure의 이름을 변경하면 모든 서버의 stored procedure 호출 코드를 변경해주어야 하는 번거로움이 있습니다.

 

• 데이터에 대한 직접적인 접근을 막고 stored procedure에 대한 접근만 허용했다면 보안적인 측면에서 이점이 있겠지만 개발 및 CS업무의 신속함이 떨어집니다.

 

• stored procedure는 프로그래밍 언어에 비해 가독성이 떨어지고 디버깅이 어렵습니다.