개요
운영 서비스의 데이터베이스에서 프로시저를 사용하여 여러 테이블에 대한 작업을 한 번에 수행하고 있는데, 프로시저를 잘 알지 못하다 보니 동작 흐름 분석에 어려움이 있다.
때문에 이번 글에서는 프로시저에 대한 개념이나 아주 기본적인 문법을 정리해보려고 한다.
참고로 MySQL을 기반으로 문법을 정리한다.
Stored Procedure
프로시저는 여러 쿼리를 모아 하나의 함수처럼 실행하기 위한 쿼리 집합이다. 함수처럼 매개변수를 전달받아 반복적으로 상할 수 있는 블럭으로, 보통 구현이 복잡한 트랜잭션이나 연속 실행 등을 수행할 때 사용한다.
프로시저를 사용하면 하나의 요청으로 여러 쿼리를 실행하여 네트워크 부담을 줄일 수 있다. 하지만 처리 성능이 높지 않고, 재사용성이 좋지 않아 업무 사양 변경 시 응용 프로그램과 함께 프로시저 정의를 변경해야 할 수 있다.
정의
MySQL에서 프로시저는 CREATE PROCEDURE 문으로 생성할 수 있다. CREATE PROCEDURE 문의 가장 기본적인 구조는 다음과 같다.
CREATE PROCEDURE <데이터베이스>.<프로시저>(
변수유형[ IN | OUT | INOUT ] 변수이름 데이터타입,
...
)
BEGIN
실행 쿼리
END매개변수를 지정할 때는 매개변수가 입력(IN) 변수인지 출력(OUT) 변수인지 아니면 입출력 변수(INOUT)인지 기재해야 한다. 실행부는 BEGIN 키워드로 시작해서 END 키워드로 끝난다.
예시로 value라는 매개변수를 입력받아 그대로 SELECT하는 프로시저를 정의하면 아래와 같이 정의할 수 있다. 참고로 변수를 호출할 때에는 변수명만 사용해서 호출하면 된다.
CREATE PROCEDURE test_procedure(
IN value VARCHAR(20)
)
BEGIN
SELECT value;
END
프로시저 조회
프로시저의 목록이나 프로시저의 정의는 다음과 같은 쿼리를 사용하여 얻을 수 있다.
프로시저 목록 조회
SHOW PROCEDURE STATUS;
# 출력
Db |Name |Type |Definer |Modified |Created |Security_type|Comment
----+--------------+---------+------------------+---------------------+---------------------+-------------+---------
test|test_procedure|PROCEDURE|mgr@10.185.135.18%|2025-02-26 07:30:44.0|2025-02-26 07:30:44.0|DEFINER |
프로시저 정의 확인
SHOW CREATE PROCEDURE <데이터베이스>.<프로시저>;
# 예시
SHOW CREATE PROCEDURE test_procedure;
프로시저 호출
프로시저는 call 문으로 호출하여 사용한다.
CALL <프로시저>(<매개변수>, ...);
# 예시
call thinq_care_aic.test_procedure('tttt');
매개변수로 전달했던 tttt라는 문자열이 내부 구현대로 SELECT된 모습을 확인할 수 있다.
프로시저 삭제
테이블 삭제와 동일하게 프로시저도 DROP 문을 사용하여 삭제한다.
DROP PROCEDURE <데이터베이스>.<프로시저>;
# 예시
DROP PROCEDURE test_procedure;
참고 문서
https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html