MariaDB에서의 Stored Procedure

Spring Boot 3.1.1
MariaDB 11.0.2

상황

Spring Boot에서는 연결된 DB를 초기화하는 SQL문을 작성하여 파일로 저장하고, application.properties 또는 application.yml 파일에서 설정값을 세팅하면 Spring Boot Application을 실행할 때마다 해당 SQL문을 자동 실행한다.

만약 SQL을 자동 실행하게 설정해놓았다면, 프로젝트의 resources/ 에 존재하는 data.sql 또는 schema.sql이 존재하는지 확인하고 존재한다면 어플리케이션 실행시 해당 SQL파일들을 실행한다.

다음과 같이 설정되어 있다면, Spring Boot Application 실행시마다 some_routine 테이블에 8개의 데이터가 삽입된다.

application.properties

spring:
  sql:
    init:
      mode: always

data.sql

insert into some_routine(routine) values 
('BED_AFTER'),
('BED_BEFORE'),
('BREAKFAST_AFTER'),
('BREAKFAST_BEFORE'),
('DINNER_AFTER'),
('DINNER_BEFORE'),
('LUNCH_AFTER'),
('LUNCH_BEFORE');

여기서 FK로 관계를 맺는 테이블간에 row를 Spring Boot Application 실행시 마다 삽입하고 싶은 상황이다. 그러나 이 경우 단순한 DML, DDL문으로는 삽입할 수 없을 것이다.

그 이유는 다음과 같다. 아래 다이어그램을 보면 demo_a와 demo_b는 관계를 맺고 있다. 이 상황에서 demo_a에 하나의 row를 삽입하고, 이 row의 PK를 FK로 가지는 demo_b row를 여러 개 삽입하고 싶다.

그렇게 되면 아래와 같은 쿼리문을 작성하게 될 것이다. 먼저 삽입된 demo_a에 먼저 삽입된 row의 PK를 저장해놓고 ? 자리에 명시해야하는데, 가능한가? 불가능할 것 같다.

insert into demo_a(...) values (...);
insert into demo_b(survey_id) values (?);
insert into demo_b(survey_id) values (?);
insert into demo_b(survey_id) values (?);

대안

여러 대안이 있을 것이다. data를 load하는 api를 만들어 놓을 수 도 있다. 그러나 이번에는 MariaDB의 Stored Procedure를 사용하여 해결할 것이다. (data 삽입을 한 파일에서 해결하고 싶었다.)

Stored Procedure는 Procedure를 DB자체에서 저장하고, 이를 호출(Call)하여 사용할 수 있는 기능이다. Procedure는 함수처럼 매개변수를 받고, 여러줄의 SQL문을 포함할 수 있다. 변수를 선언할 수 있으며, 제공되는 기본 function들도 사용할 수 있다.

https://mariadb.com/kb/en/stored-procedure-overview/


Stored Procedure의 장점은 첫째로 보안성이라고 한다. 데이터를 DB자체에서 처리하므로 네트워크로 민감한 정보가 나가는 것을 방지할 수 있다.

둘째는 네트워크 트래픽을 감소시킬 수 있다. SQL 한 문장을 실행하고 결과를 받는 기존의 방법과 달리, Procedure는 한 번의 원격 Call만으로도 Procedure에 정의된 여러 줄의 SQL문과 조건문, 반복문을 실행하여 결과를 얻을 수 있으므로 네트워크 트래픽을 감소시킬 수 있다.


다만, 무거운 연산의 경우엔 Application Server에서 수행하는 것이 바람직하겠다.


Spring Boot에서는 Stored Procedure를 바로 data.sql에 작성할 수 없다. separator 때문. 따라서 다음과 같이 설정해주면 사용할 수있다.

application.yml

spring:
  sql:
    init:
      mode: always
      separator: ^;

data.sql

create or replace procedure load_survey_contents()
begin
    declare survey_id bigint;
    declare cur datetime;
    set cur = now();
    insert into survey(date, created_at, updated_at) values (CURDATE(), cur, cur);
    set survey_id = last_insert_id();
    insert into survey_content(survey_id, survey_content_type) values(survey_id, "CHOICE_STATUS");
    insert into survey_content(survey_id, survey_content_type) values(survey_id, "CHOICE_EMOTION");
    insert into survey_content(survey_id, survey_content_type) values(survey_id, "TEXT");
end ^;
call load_survey_contents ^;