[DataBase] 스토어드 프로시저 (Stored Procedure)

이번 포스팅에서는 스토어드 프로시저(Stored Procedure)에 관해 알아보도록 하겠습니다. 

 

 

Stored Procedure ?

• 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합
• 프로그래밍 언어의 함수처럼 인자를 넘기는 것이 가능 (IN, OUT, INOUT)
• 리턴되는 값은 레코드들의 집합
• 간단한 분기문(if, case)과 루프(loop)를 통한 프로그래밍이 가능
• 디버깅이 힘들고 서버단의 부하를 증가시킨다는 단점이 존재

 

 

[ 정의 문법 ]

DELIMITER //
CREATE PROCEDURE procedure_name(parameter1, ...)
BEGIN
    statements
END //
DELIMITER

 

 

[ 호출 문법 ]

CALL stored_procedure_name(arguments1, ...)

 

 

[ 삭제 문법 ]

DROP PROCEDURE [프로시저명];

 

 

그럼 이제, 예시를 통해 어떻게 사용하는지 알아볼까요 ?

 

 

예제

평소 작업 시 자주 사용하는 SELECT 문이 있다고 해볼게요.

SELECT * FROM employees WHERE first_name = 'zerozae';
SELECT * FROM departments WHERE dept_name = 'development';

이처럼 자주 호출되는 쿼리문을 매번 작성하기 보단, 한번에 처리하고 싶다? 라는 생각이 들 때 바로 프로시저를 사용하면 됩니다. 당장은 두 줄짜리 SELECT 문이지만, 언제나 양이 많아질 상황을 함께 고려하는 것이 좋아요 ! 

 

 

[ 프로시저 생성 ]

DELIMITER //
CREATE PROCEDURE test_proc()
BEGIN
    SELECT * FROM employees WHERE first_name = 'zerozae';
    SELECT * FROM departments WHERE dept_name = 'development';
END //

 

 

[ 프로시저 호출 ]

CALL test_proc();

 

 

이런식으로 프로시저를 사용해요 ! 그런데 여기서 의문점이 하나 들 수 있는데요. 바로 View와 비슷한거 같은데?.. 라는 생각이 들 수 있습니다. View와 비슷하게 직접적인 접근에서 SQL문을 보호하는 역할도 해줌과 동시에 많은 작업을 간편하게 해주는 역할도 하는 것이죠. 

 

추가적으로 View와 무엇이 다른가라고 생각해보면, 이런 프로시저는 프로그래밍 언어와 같은 기능을 담당할 수 있어요. IF문을 사용하거나 인자를 넘겨 반환받을 수 있는 것처럼 말이죠.

 

 

➡️ 예제 더 살펴보기 

DELIMITER //
CREATE PROCEDURE return_age(IN member_age int(3))
BEGIN
    SELECT *
    FROM member
    WHERE age = member_age;
END //
DELIMITER;

-- 호출
CALL return_age(20);

 

위 예제는 인자로 넘겨준 20살의 나이와 일치하는 나이를 가진 회원의 정보 레코드를 반환 받을 수 있습니다. 이것이 뷰와의 차이점이라고 할 수 있어요. 뷰는 지정된 셀렉트 문을 뷰로서 만들어 호출하지만 프로시저는 인자를 넘겨 리턴되는 결과를 얻을 수 있죠.

 

 

DELIMITER //
CREATE PROCEDURE return_age(IN member_age int, INOUT totalMember int)
BEGIN
    SELECT COUNT(1) INTO totalMember
    FROM member
    WHERE age = member_age;
END //
DELIMITER;

-- 호출
SET @twenty_count = 0
CALL return_age(20, @twenty_count);
SELECT @twenty_count;

위 예제는 주어진 나이를 만족하는 레코드 수를 totalMember라는 파라미터에 넣어 리턴해주는 프로시저로 프로시저 호출 후 @twenty_count 를 SELECT로 출력해보면 20살의 나이를 만족하는 레코드 수를 확인할 수 있습니다.

 

 

 

장점 및 단점 

[ 장점 ]

- 데이터베이스의 보안 향상
- 기능의 추상화
- 네트워크 소요 시간 절감
- 절차적 기능 구현

 

[ 단점 ]

- 낮은 처리 성능
- 애플리케이션 코드의 조각화 (유지보수 어려움)

 

 

 

 

< 참고 자료 >

스토어드 프로시저(Stored Procedure)란 무엇인가? employees 예제를 통해 기초 알아보기