[MySQL] Stored Procedure

 

Stored Procedure이란?

Stored Procedure는 MySQL의 `Stored Routine`의 한 종류로, SQL 명령문의 집합을 뜻합니다. 사용해 보신 분들은 쉽게 이해가 갈 설명인데요. 간단히 말하면 SQL문의 함수화라고 표현할 수 있습니다. 물론 비슷한 기능으로 `Stored Function` 도 존재하는데요, 이는 이전 글에서 다루고 있으니 확인해 주시면 좋겠습니다.

 

Stored Procedure 역시 SQL 명령문의 집합이기 때문에 다음과 같은 장점을 가지고 있습니다.

• 복수의 이용자들이 같은 DB를 이용하는 경우 동일한 SQL구문을 이용할 수 있음
• SQL의 내부 구현을 모르게 되어 보안적 측면의 강화
• 클라이언트에서 DB로 많은 요청을 보내지 않아도 되어 성능 향상 가능성. (단, DB의 자원을 더 많이 사용)

모듈화를 통해 얻을 수 있는 장점들을 지니고 있다고 생각하면 이해하시기 쉬울겁니다.

 

그러면 Stored procedure를 사용하는 방법, 특징 등을 살펴보면서 더 깊은 이해를 가져봅시다.

 

Stored Procedure의 사용법

Stored Procedure와 관련된 사용 방법은 크게 4가지입니다. 생성, 호출, 조회, 삭제 방법에 대해서 알아보면서 간단하게 사용법을 익혀봅시다.

생성

먼저 Stored Procedure에 대한 생성 방법입니다.

아래 코드는 MySQL의 공식 문서에 적힌 내용입니다. 밑에서 하나씩 설명을 드리겠습니다.

CREATE
    [DEFINER = user]
    PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body


proc_parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MySQL data type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body:
    SQL routine

 

먼저 첫 번째 코드 뭉치를 통해 `Stored Procedure`를 생성합니다. 간단하게 번역을 하자면 아래와 같습니다. []로 설정된 부분들은 생략이 가능하다는 것만 알고 계시면 될 것 같습니다.

 

CREATE
    [DEFINER = user] // 작성자 명
    PROCEDURE [IF NOT EXISTS] sp_name // Stored Procedure 이름
    ([proc_parameter[,...]]) // 사용할 파라미터
    [characteristic ...] routine_body // SQL 모음

 

 

그다음으로 알아볼 부분은 변수를 선언하는 부분입니다.

 

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

`()` 소괄호 내에 변수의 이름과 데이터 타입을 명시해 주면 됩니다.

 

이때 IN은 파라미터에 주입해 사용할 값, OUT은 프로시저의 결과로 리턴 받을 값, INOUT은 주입받아 사용하는 동시에, 리턴 받는 값을 의미합니다.

 

이때 IN으로 받는 경우는 프로시저 내부에서 사용만 가능, OUT은 리턴만 가능, INOUT은 둘 다 가능하다는 특징을 갖습니다.

 

아래 코드와 결과를 보시면 IN으로 받은 파라미터는 수정해도 결과가 적용되지 않으며, OUT은 내부에서 이용할 수 없고 NULL이 나오는 것을 볼 수 있습니다.

 

가능하시면 직접 한번 시도해 보셔도 좋을 것 같습니다.

DELIMITER &&
DROP PROCEDURE IF exists TEST_PROCEDURE2&& 
CREATE procedure TEST_PROCEDURE2 (in age int)
BEGIN
SELECT age ;
set age = 10;
END&&
DELIMITER ;

set @c = 3;
call TEST_PROCEDURE2(@c); ## 결과 3
select @c; ## 결과 3

DELIMITER &&
DROP PROCEDURE IF exists TEST_PROCEDURE3&& 
CREATE procedure TEST_PROCEDURE3 (out age int)
BEGIN
SELECT age ;
set age = 10;
END&&
DELIMITER ;


set @a = 1;
call TEST_PROCEDURE3(@a); ## 결과 null
select @a; ## 결과 10

DELIMITER &&
DROP PROCEDURE IF exists TEST_PROCEDURE4&& 
CREATE procedure TEST_PROCEDURE4 (inout age int)
BEGIN
SELECT age ;
set age = 20;
END&&
DELIMITER ;

set @b = 2;
call TEST_PROCEDURE4(@b); ## 결과 2
select @b; ## 결과 20

 

위의 코드에서 보시면 `BEGIN`이라는 문장이 있습니다.

이는 프로시저 내부의 SQL이 여러 개가 존재할 때, 이용하는 것으로 1개만 존재한다면 BEGIN을 적용하지 않아도 되고, BEGIN이 없다면 제일 상단의 SQL만이 적용이 됩니다. 아래 코드를 직접 따라 해 보시면서 실험해 보셔도 좋을 것 같네요.

 

DELIMITER &&
DROP PROCEDURE IF exists TEST_PROCEDURE&& 
CREATE procedure TEST_PROCEDURE (inout age int)
SELECT age ;
set age = 3;
END&&
DELIMITER ;


set @age = 0;
call TEST_PROCEDURE(@age); ## 0
select @age; ## 0

DELIMITER &&
DROP PROCEDURE IF exists TEST_PROCEDUREE&& 
CREATE procedure TEST_PROCEDUREE (inout age int)
set age = 3;
SELECT age ;
END&&
DELIMITER ;


set @age = 0;
call TEST_PROCEDUREE(@age); ## select가 나오지 않음. 
select @age; ## 3

 

 

호출

위의 생성 예제에서 지속적으로 봤겠지만, `call 프로시저명()`을 입력하는 것으로 프로시저를 호출할 수 있습니다.

또한 파라미터 주입은 원하는 값을 직접 작성하셔도 좋고, `set`이나 `declare`로 선언한 변수를 이용해 호출할 수 있습니다.

 

또한 프로시저 내부에서도 마찬가지로 프로시저를 호출해 이용할 수 있습니다.

 

아래 예제에서 `use_other_procedure` 프로시저는 위에서 사용했던 `TEST_PROCEDURE4`를 호출하고, 그 결과를 그대로 사용하는 것을 볼 수 있습니다.

 

delimiter &&
create procedure use_other_procedure(out age int)
	begin 
		declare test_age int;
        set test_age = 10;
		call TEST_PROCEDURE4(test_age);
        set age = test_age;
	end &&
delimiter ;


set @test_val = 5;
call use_other_procedure(@test_val); ## 결과 10
select @test_val; ## 결과 20

 

 

조회

지금까지 작성한 `Stored Procedure`들에 대한 정보를 조회하는 기능입니다.

총 3가지 방법으로 아래 예제에서 등장하는 첫 번째 조회 문에서 볼 수 있는 `ROUTINE_SCHEMA` 와 두 번째 조회문의 `Db`는 현재 사용 중인 스키마의 이름이니, 이를 필터링해서 보시면 될 것 같습니다.

 

마지막 줄에서는 `show create procedure 프로시저명` 을 입력하는 것으로 어떤 내용이 정의되었는지 등을 확인할 수 있습니다.

select * from information_schema.routines where ROUTINE_TYPE = 'PROCEDURE' and ROUTINE_SCHEMA = 'study'; ## 아래 두 것을 합친 내용 + a

show procedure status where Db = 'study'; ## DB이름, Procedure 이름, 타입 이름(프로시저인지 펑션인지), 작성자, 수정시간, 생성시간, 캐릭터셋 등등..

show create procedure use_other_procedure; ## create 과정에서 작성된 내용 확인 가능

 

삭제

삭제하는 방법은 아주 간단합니다.

`DROP PROCEDURE {프로시저 이름}` 을 이용해 작성할 수 있으며, `IF exists`와 같은 옵션을 줄 수 있습니다.

 

특징

Stored Procedure의 특징으로는 크게 3가지로 1. 파라미터의 다양성, 2. 내부에서 트랜잭션 동작 가능 3. 모듈화가 있습니다. 

 

파라미터의 다양성

먼저 파라미터가 다양합니다. 위에서 사용법에서 본 것처럼 IN, OUT, INOUT가 있다는 것을 알아봤습니다.

이는 Stored Procedure와 유사한 `Stored Function`과 차이가 나는 부분입니다.

 

다양한 파라미터를 사용할 수 있으며, 리턴 값의 유무와 개수를 원하는 대로 설정할 수 있습니다.

 

 

내부에서 트랜잭션 동작 가능

Stored Procedure는 내부에서 트랜잭션이 동작할 수 있습니다. 지금까지 예제에서는 Select 문만을 사용했지만, 내부에서 insert나 update, delete와 같은 데이터 조작과 관련된 동작을 수행할 수 있습니다.

 

 

DBMS를 이용해 SQL를 다룰 경우, 실행 불가능한 SQL(ex. 문법 오류)가 발생한 경우 해당 코드 자체가 동작하지 않기 때문에, rollback 등이 따로 동작하지 않습니다.

 

하지만 Stored Procedure에서는 내부에서 완전한 트랜잭션을 이용이 가능하고, 일반 웹 애플리케이션처럼, 쿼리 실행 도중에 오류가 발생한다면 rollback이 가능합니다.

 

아래 코드에서 `DECLARE EXIT HANDLER FOR SQLEXCEPTION` 부분처럼 동작 핸들러를 설정할 수 있기 때문입니다. 동작 핸들러에 대한 조금 더 깊은 내용은 공식 문서를 참고하시면 좋겠습니다.

 

drop procedure if exists rollback_sp;

delimiter &&
create procedure rollback_sp(name varchar(255))
BEGIN 
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		ROLLBACK;
		SELECT 'Error occurred, transaction rolled back' AS message;
	END;

	start transaction;
	select * from employee;
	insert into rollback_example(name) value ('tester1');
	insert into employee(name,grade) value('tester1', 'low');

	commit;

END&&
delimiter ;


call rollback_sp('tester1');

select count(*) from rollback_example; ## SP 이전과 동일한 카운트가 나온다.

 

 

모듈화

마지막 장점은 모듈화입니다. 이 글의 첫 부분에서 Stored Procedure를 이야기할 때, 말했던 것처럼 복잡한 쿼리들을 손쉽게 호출할 수 있게 해 줍니다.

 

그리고 이용자는 그 내부 구현에 대해서 알 필요가 없기 때문에, 보안적인 측면에서도 장점이 있다고 볼 수 있습니다.

 

아래 코드는 새로운 사원이 입사를 한 이후, DBMS의 계정을 생성해 주는 과정을 Stored Procedure로 제작한 예시입니다. 사원의 이름과 비밀번호, 등급을 기준으로 DB에 데이터를 넣어주고, DBMS 계정을 만들어주는 일련의 과정을 `register_employee()`를 호출하는 것으로 가능하게 하는 장점이 있습니다.

delimiter &&

create procedure register_employee(in name varchar(255), in password varchar(255), in grade enum('low' ,'middle', 'high'), out id int)
begin
	
    declare pwd varchar(255);
    set pwd = password;
    insert into employee(name, grade) value( name, grade);
    SELECT LAST_INSERT_ID() into id;
    
    set @create_sql = CONCAT('CREATE USER \'', name, '\'@\'localhost\' ',  'IDENTIFIED BY \'', password, '\';');
    select @create_sql;
    prepare create_statement from @create_sql;
    execute create_statement;
    deallocate prepare create_statement;
end &&
delimiter ;


set @last_id = 0;
call register_employee('tester1' ,'1q2w3e4r' , 'low', @last_id);
select @last_id;

 

마무리

지금까지 Stored Procedure에 대한 개념, 사용법, 특징에 대해서 알아보았습니다. 이전 글에서도 작성했듯이 저 개인적으로는 웹 애플리케이션에서 DB를 주로 다뤘기 때문에, 조금 낯선 감이 있었습니다. 

 

조사하는 과정에서 많은 레거시 프로젝트들은 DB에서 비즈니스 로직을 처리하는 경향들이 있었고, 그 과정에서 이런 기능들을 자주 사용했다고 합니다. 

Stored Procedure를 구현하는 방법이 표준 방법이 존재하지 않아 DBMS마다 다르다고 합니다. 그러다 보니 SP를 많이 이용하는 서비스에서는 DBMS에 대한 종속성이 좀 깊어지는 문제가 발생할 수 있다고 생각이 듭니다. 저는 JPA와 같은 ORM 기술들을 자주 이용해 프로젝트를 진행했는데, 문득 ORM이 주는 편의성만큼이나, DB에 대한 종속성을 줄여줄 수 있다는 점이 큰 장점으로 와닿는 순간이었습니다.

 

 

참고 자료

거미줄코딩 - [MySQL] 스토어드 프로시저(Stored Procedure) 기본

Heello World - [MySQL] 저장 프로시저 (Stored Procedure)

Patricia Arquette - 예외 발생 시 MySQL 트랜잭션을 자동으로 롤백하려면 어떻게 해야 합니까?

MySQL - 15.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements

MySQL - 15.6.7.2 DECLARE ... HANDLER Statement