[MariaDB] Create Procedure(프로시저)

루틴에는 FUNCTION 과 PROCEDURE 가 있다.

이 글은 PROCEDURE의 기본에 대해 알아보고자 한다.

프로시저(Procedure) 란?

특정 작업을 실행하기 위한 루틴이다.

CALL 문을 이용해서 실행할 수 있다.

SHOW PROCEDURE 프로시저 조회

https://mariadb.com/kb/en/show-procedure-status/

SHOW PROCEDURE STATUS
    [LIKE 'pattern' | WHERE expr]

프로시저 내용 조회

SELECT
       ROUTINE_SCHEMA
     , ROUTINE_NAME
     , ROUTINE_DEFINITION
     , ROUTINE_TYPE
 FROM
       INFORMATION_SCHEMA.ROUTINES
WHERE 1=1
--        ROUTINE_DEFINITION LIKE '%찾고자하는 문자열%'
  AND  ROUTINE_TYPE='PROCEDURE';
--  AND ROUTINE_NAME = '찾고자하는 루틴 이름'

CREATE Syntax

https://mariadb.com/kb/en/create-procedure/

syntax

/* DELIMITER : 프로시저의 시작과 끝 */
DELIMITER $$

CREATE PROCEDURE routine_name /* Routine name */
  ([in, out, inout] parameter_name parameter_type ...)   /* Parameter list */
  [CONTAINS SQL, NO SQL, READS SQL DATA, MODIFIES SQL DATA]   /* Data access clause */
  BEGIN                        /* Routine body */
    UPDATE ...;
  END;
DELIMITER ;

CREATE
    [OR REPLACE]
    [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
    PROCEDURE [IF NOT EXISTS] 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

routine_name : string

parameter List
> proc_parameter: in, out, inout (default in)

> parameter_name : string

> parameter_type : int, varchar etc..

> 여러 파라매터 받기/보내기 가능

Data access clause

> 함수가 수행하는 작업을 서버에 알려주는 정보 절입니다. MariaDB는 지정된 절이 올바른지 어떤 식으로든 확인하지 않습니다. 이러한 절을 지정하지 않으면 CONTAINS SQL기본적으로 가 사용됩니다.

> CONTAINS SQL, NO SQL, READS SQL DATA, MODIFIES SQL DATA (default CONTAINS SQL)

Rountine body

> BEGIN으로 시작하여 END;로 끝남.

> BEGIN과 END 사이에 실행하고자 하는 명령어를 입력.

sample

/* DELIMITER : 프로시저의 시작과 끝 */
DELIMITER $$

CREATE PROCEDURE Withdraw /* Routine name */
  (parameter_amount DECIMAL(6,2),     /* Parameter list */
  parameter_teller_id INTEGER,
  parameter_customer_id INTEGER)
  MODIFIES SQL DATA                   /* Data access clause */
  BEGIN                        /* Routine body */
    UPDATE Customers
        SET balance = balance - parameter_amount
        WHERE customer_id = parameter_customer_id;
    UPDATE Tellers
        SET cash_on_hand = cash_on_hand + parameter_amount
        WHERE teller_id = parameter_teller_id;
    INSERT INTO Transactions VALUES (
        parameter_customer_id,
        parameter_teller_id,
        parameter_amount);
  END;
DELIMITER ;

DELIMITER //

CREATE PROCEDURE simpleproc (OUT param1 INT)
 BEGIN
  SELECT COUNT(*) INTO param1 FROM t;
 END;
//

DELIMITER ;


DELIMITER //

CREATE PROCEDURE simpleproc2 (
  OUT param1 CHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin'
)
 BEGIN
  SELECT CONCAT('a'),f1 INTO param1 FROM t;
 END;
//

DELIMITER ;

 

USED PROCEDURE 프로시저 사용

CALL sp_name
CALL sp_name([parameter[,...]])
CALL sp_name[()]

 

참조

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

Stored Procedure Overview

https://mariadb.com/kb/en/create-procedure/

CREATE PROCEDURE

https://sowon-dev.github.io/2022/11/28/221129MySQL-function-Procedure/

[MySQL/MariaDB] 함수(function) vs 프로시저(Procedure) 차이

https://mariadb.com/kb/en/create-procedure/

CREATE PROCEDURE