[MaraiaDB] 저장 프로시저 (Stored procedure) 만들기

다음은 MariaDB 공식 문서에서 가져온 저장 프로시저 (Stored procedure) 문법입니다.

CREATE
    [OR REPLACE]
    [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MariaDB data type

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

routine_body:
    Valid SQL procedure statement

위 기본 문법을 참고하여 일반적으로 MariaDB에서 다음과 같이 간단한 저장 프로시저를 만들 수 있습니다.

-- CREATE PROCEDURE example
DELIMITER //

CREATE OR REPLACE PROCEDURE simple_proc (OUT param1 INT)
BEGIN
    SELECT COUNT(*) INTO param1 FROM PRODUCT;
END;
//

DELIMITER ;

위와 같이 "simple_proc" 프로시저를 정의하였다면 다음과 같이 호출 할 수 있습니다.

CALL simple_proc(@param1);

SELECT @param1;

프로시저의 호출 결과로 "PRODUCT" 테이블의 전체 레코드 개수를 반환받습니다.

저장 프로시저 내에서 임시 테이블을 생성할 때는 다음과 같이 할 수 있습니다.

CREATE OR REPLACE TEMPORARY TABLE T_PRODUCT_LIST
(
    product_no    INT NOT NULL
);

위와 같이 생성된 임시 테이블은 현재 세션에서만 유효합니다. 데이터베이스 연결이 끊어지는 등의 세션이 종료될 때, 함께 삭제됩니다.

저장 프로시저 내에서 트랜잭션의 처리는 다음과 같이 할 수 있습니다.

CREATE OR REPLACE TEMPORARY TABLE T_PRODUCT_LIST
(
    product_no    INT NOT NULL
);

트랜잭션 처리를 위해서는 START TRANSACTION ~ COMMIT / ROLLBACK 형식으로 호출하여야 합니다.
트랜잭션의 시작을 위하여 "START TRANSACTION" 구문을 입력하고, 일괄 작업 내역을 정리한 다음, 모든 과정이 문제없이 끝나면 최종적으로 "COMMIT"를 호출하여 적업된 내용이 실제 데이터베이스에 반영되도록 합니다.

작업을 처리하는 과정중에 오류가 발생하면 "COMMIT" 구문이 호출되지 않기 때문에, 작업 했던 내용은 모두 사라집니다.
정상적인 트랜잭션 처리를 위하여 작업 중 오류가 발생했을 때, "ROLLBACK" 시키기 위하여 다음과 같이 오류 핸들러를 사용하면 됩니다.

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    DROP TABLE IF EXISTS T_PRODUCT_LIST;
END;

위와 같이 오류 핸들러를 정의하여 두면, 작업 처리 중에 오류가 발생하면 자동으로 "ROLLBACK"하게 됩니다.

FOR 문을 이용하여 여러 반복 작업을 수행할 수도 있습니다.

CREATE TABLE t1 (a INT);

DELIMITER //

FOR i IN 1..3 DO
  INSERT INTO t1 VALUES (i);
END FOR;
//

DELIMITER ;

SELECT * FROM t1;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+

정수 역순으로 반복문을 수행할 경우에는 다음과 같이 "REVERSE"로 지정합니다.

CREATE TABLE t1 (a INT);

DELIMITER //

FOR i IN REVERSE 1..3 DO
  INSERT INTO t1 VALUES (i);
END FOR;
//

DELIMITER ;

SELECT * FROM t1;
+------+
| a    |
+------+
|    3 |
|    2 |
|    1 |
+------+

참고 자료

• "MariaDB - CREATE PROCEDURE":https://mariadb.com/kb/en/create-procedure/
• "MariaDB - START TRANSACTION":https://mariadb.com/kb/en/start-transaction/
• "MySQL START TRANSACTION , COMMIT":https://blog.naver.com/luvtoma/221164710293
• "MariaDB - DECLARE HANDLER":https://mariadb.com/kb/en/declare-handler/
• "MariaDB - FOR":https://mariadb.com/kb/en/for/