[MySQL] PROCEDURE

참고

MySQL Documentation 13.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements

prerequisite

추가 설명

PROCEDURE 생성 구문

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

proc_parameter:
    [ IN | OUT | INOUT ] param_name 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

호출

DEFINER

  • deafult: CURRENT_USER. CREATE PROCEDURE 문을 실행하는 유저이다.
  • PROCEDURE 실행 시의 접근 권한을 체크 하는데 사용(작동시키는 유저가 아니다).
  • 'user_name'@'host_name' 과 같이 지정할 수 있다.

IF NOT EXISTS

  • 스키마에 동일한 이름의 이벤트가 존재시, CREATE PROCEDURE을 스킵하도록 한다(no action).
  • MySQL 8.0.29 버전 이후 사용가능

sp_name

proc_parameter

  • routine_body에서 사용할 파라미터
  • 대소문자를 구분 하지 않는다(not case-sensitive).
  • 파라미터가 없는 경우 빈 괄호로 남겨둔다.
  • 파라미터는 기본적으로 IN 파라미터 이다.
  • OUT 파라미터는 호출자에게 다시 리턴 된다. 프로시저 내의 초기값은 NULL이다.
  • INOUT 파라미터는 호출자에 의해 초기화(initialized) 되며, 프로시저 내에서 수정 될 수 있으며, 호출자에게 리턴 된다.
  • 프로시저 호출 시에 OUT, INOUT 파라미터에 사용자 정의 변수(user-defined variable)를 전달하여 프로시저가 해당 변수에 값을 return 하도록 할 수 있다.
  • 다른 stored routine 내에서 호출 시에 해당 stored routine의 파라미터(routine parameter) 또는 stored routine의 내부 변수(local routine variable)를 프로시저의 파라미터로 사용할 수 있다.
  • 트리거 내에서 프로시저를 호출하는 경우 NEW.col_name을 OUT 또는 INOUT 파라미터로 전달할 수있다.
  • 참고
  • 13.6.7.8 Condition Handling and OUT or INOUT Parameters
  • 25.8 Restrictions on Stored Programs

예시

mysql> delimiter //

mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
       BEGIN
         SELECT COUNT(*) INTO cities FROM world.city
         WHERE CountryCode = country;
       END//
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

mysql> CALL citycount('JPN', @cities); -- cities in Japan
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @cities;
+---------+
| @cities |
+---------+
|     248 |
+---------+
1 row in set (0.00 sec)

mysql> CALL citycount('FRA', @cities); -- cities in France
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @cities;
+---------+
| @cities |
+---------+
|      40 |
+---------+
1 row in set (0.00 sec)

delimiter

  • 프로시저가 정의되는 동안 mysql client delimiter 명령어를 사용하여 delimiter를 ; 에서 // 로 변경
  • procedure body 에서 ; delimiter가 mysql에 의해 해석되지 않고 서버로 전달 될 수 있도록 한다.
  • 참고 25.1 Defining Stored Programs

routine_body

  • 간단한 DML 문 또는 복수의 구문(compound-statement)가 올 수 있다.
  • CREATE 및 DROP과 같은 DDL 문을 포함하는 루틴을 허용
  • COMMIT, ROLLBACK과 같은 TRANSACTION 관련 statement 사용 가능
  • USE statement 사용 불가능 (routine이 호출되면 routine이 생성된 DB가 디폴트 DB가 됨)
  • stored routine에 사용 불가한 statement는 링크 참조 25.8 Restrictions on Stored Programs
  • MySQL은 stored routine이 생성되었을 때의 sql_mode 시스템 환경변수를 저장한다. stored routine이 작동할 때는 현재 서버의 sql_mode가 아닌 생성 당시의 셋팅으로 실행된다.

characteristic

COMMENT 'string'

  • 해당 이벤트에 대한 description
  • 최대 64자까지 입력이 가능하다.

LANGUAGE SQL

  • routine이 작성된 언어를 가리킨다.
  • 서버는 이 characteristic을 무시한다. 오직 SQL routine만 지원된다.

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  • routine의 데이터 사용에 대한 정보를 제공하는 용도
  • 이는 권고(advisory)일 뿐 서버가 이를 이용해 routine에서 사용되는 statements를 제약하지는 않는다.
  • CONTAINS SQL: routine이 data를 읽거나 쓰는 statements를 포함하지 않음을 명시. (ex. SET @x = 1)
  • NO SQL: routine이 SQL statements를 포함하지 않음을 명시
  • READS SQL DATA: routine이 data를 읽는 statements로 이루어져 있음을 명시. (ex. SELECT)
  • MODIFIES SQL DATA: routine이 data를 쓰는 statements로 이루어져 있음을 명시. (ex. INSERT or DELETE)

SQL SECURITY

  • routine을 호출시 해당 routine의 DEFINER의 권한을 사용하여 실행할지 호출한 사용자(INVOKER)의 권한으로 실행 할지를 지정
  • default는 DEFINER

실습

use md2;

DELIMITER //

CREATE PROCEDURE IF NOT EXISTS correctSeqTargetTree(
     IN p_user_name VARCHAR(100)
    ,IN p_tree_id INT UNSIGNED
    ,IN p_tree_type INT
)
COMMENT '특정 트리(폴더)의 트리(폴더, 파일)의 순서를 바로잡는 프로시저'
MODIFIES SQL DATA
BEGIN
    DECLARE v_done INT DEFAULT 0;
    DECLARE v_id INT UNSIGNED;
    DECLARE v_seq_num INT UNSIGNED DEFAULT 0;

    DECLARE target_trees CURSOR FOR
    SELECT t.id
    FROM tree t 
    WHERE t.user = p_user_name
    AND t.parent = p_tree_id
    AND t.type = p_tree_type
    AND t.delete_yn = 'N'
    ORDER BY seq
    ;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = true;

  OPEN target_trees;

    tree_loop: LOOP
      FETCH target_trees
      INTO v_id;

      SET v_seq_num = v_seq_num + 1;

      IF v_done THEN
        LEAVE tree_loop;
      END IF;

      UPDATE tree t2
      SET t2.seq = v_seq_num
      WHERE t2.id = v_id
      ;

    END LOOP;

  CLOSE target_trees;
END//

DELIMITER ;

links

social