Stored Procedure

쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용

개요

C 또는 Java와 같은 언어를 이용해서 개발하다 보면, 동일한 기능을 하는 코드를 함수로 분리해서 개발한다.
비슷하게 Database에서도 Procedure를 이용해서 함수처럼 만들 수 있고, 보안과 성능 등의 장점이 있다.

정의

https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html
Mysql의 경우 아래와 같은 형식으로 정의할 수 있다.

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

CREATE
    [DEFINER = user]
    FUNCTION [IF NOT EXISTS] sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    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:
    Valid SQL routine statement

조금 더 간단하게 살펴보자

DELIMITER $$
CREATE PROCEDURE 스토어드 프로시져 이름(In 또는 Out 파라미터)
BEGIN 
	원하는 동작 작성
END$$ 
DELIMITER ;

CALL 스토어드 프로시저이름();

아래 Procedure는 개인 프로젝트를 사용하면서 작성했던 데이터 중 일부이다.

-- 만약 SP_ONION_GROW라는 이름을 가진 Procedure가 있다면 삭제한다.
DROP PROCEDURE IF EXISTS SP_ONION_GROW;
-- MySQL의 종료문자는 기본적으로 ';' 이지만,
-- CREATE PROCEDURE 내부에서도 ;가 종료문자이므로, 식별이 어렵다. 잠시 $$으로 변경해둔다.
DELIMITER $$
CREATE PROCEDURE SP_ONION_GROW() -- 특정 이름을 가진 프로시저를 만든다.
BEGIN -- BEGIN -> END 사이에 원하는 동작을 작성한다.
    INSERT INTO notificate.alarm(sender_id, receiver_id, content, created_at, is_sended, is_read, type)
    SELECT a.user_id as sender_id,
           a.user_id as "receiver_id",
           'content' as content,
           now()     as created_at,
           false     as is_sended,
           false     as is_read,
           3         as type
    FROM onion.onion a
    WHERE 1 = 1
      AND a.send_date is null
      AND a.is_disabled = false
      AND a.grow_due_date <= now();
END$$ -- 프로시저의 작성이 종료되었다.
DELIMITER ; -- 종료문자를 $$ 에서 다시 ;로 변경해주자.

수정과 삭제

수정의 경우 ALTER PROCEDURE,
삭제의 경우 DROP PROCEDURE를 활용한다.

CREATE PROCEDURE SP_SAMPLE
AS
BEGIN
    SELECT * FROM TableName;
END;

-- 수정하는 경우
ALTER PROCEDURE SP_SAMPLE
AS
BEGIN
    SELECT Column1, Column2 FROM TableName;
END;

-- 삭제하는 경우
DROP PROCEDURE SP_SAMPLE

예외처리

DECLARE 액션 HANDLER FOR 오류조건 처리할문장을 활용한다.

• 액션의 종류
  액션의 종류에는 CONTINUE와 EXIT가 있다.
  CONTINUE의 경우에는 처리할 문장이 처리된다.
  EXIT의 경우에는 종료된다.

• 오류조건
  MySQL의 경우 숫자로된 오류 코드 또는 SQLSTATE 등이 온다.

• 처리할 문장
  처리할 문장이 하나라면 한 문장으로 작성, 여러 개일 경우에는 BEGIN - END로 묶어줄 수 있다.

DROP PROCEDURE IF EXISTS SP_SAMPLE;
DELIMITER $$
CREATE PROCEDURE SP_SAMPLE()
BEGIN
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT '테이블 없음' as '메시지';
    SELECT * FROM city;
END $$
DELIMITER ;

CALL SP_SAMPLE();

테이블이 존재하는 경우 해당 테이블의 데이터가 모두 출력되고 테이블이 없는 경우, 테이블없음 이라는 결과가 나온다.

스토어드 프로시저의 특징

1. 성능향상, 네트워크 비용 감소
   긴 코드로 구현된 쿼리 실행 시, 클라이언트->서버로 쿼리의 모든 텍스트가 전송되어야 한다. 하지만 프로시저화 되어있다면, 프로시저 이름과 매개 변수 등 몇 글자만 전송하면 되므로 네트워크 부하의 감소로 이어질 수 있다.
   또한 DBMS에 따라 1회만 컴파일 후 메모리에 로딩되어, 이후 실행 시 컴파일을 하지 않고 저장된 내용을 호출하여 성능향상이 발생함. ( MySQL은 해당이 없다..!)

2. 일관성
   한 번 작성하면 여러 번 사용가능하다. 동일한 로직이 반복된다면 재사용 및 관리가 편한다.

3. 보안
   사용자 별로 테이블에 접근 권한을 주지 않고 프로시저에만 접근 권한을 줄 수 있다. 즉 원하지 않는 데이터에 대한 공개를 막을 수 있다.

위와 같은 장점이 있지만 물론 단점도 있다.
유지보수 와 디버깅의 어려움
스토어드 프로시저의 로직이 복잡해지면 이해하는 과정이 어렵다. 또한 디버깅 과정이 매우 어려워진다.
이식성
각 DBMS마다 조금씩의 문법의 차이가 존재하여, 타 DBMS로 이전이 어려울 수 있음.