참고
MySQL Documentation 13.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements
prerequisite
- CREATE ROUTINE 권한을 가지고 있어야 한다.
추가 설명
- 기본적으로 MySQL은 routine을 만든 생성자에게 ALTER ROUTINE과 EXECUTE 권한을 부여
- 이는 automatic_sp_privileges system variable을 disable 함으로서 변경 가능하다.
- 참고 25.2.2 Stored Routines and MySQL Privileges
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
호출
- CALL 구문 사용 13.2.1 CALL 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
- stored function은 loadable function과 namespace를 공유한다.
- 서로 다른 종류의 function에 대한 참조를 해석하는 방법은 다음 문서 참고
- 9.2.5 Function Name Parsing and Resolution.
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 ;