[MySQL] FUNCTION

참고

MySQL Documentation 13.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements

prerequisite

추가 설명

FUNCTION 생성 구문

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

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

DEFINER

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

IF NOT EXISTS

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

sp_name

func_parameter

  • routine_body에서 사용할 파라미터
  • 대소문자를 구분 하지 않는다(not case-sensitive).
  • 파라미터가 없는 경우 빈 괄호로 남겨둔다.

type

  • Function에서는 FUNCTION의 RETURN value의 타입을 지정
  • function body에서는 반드시 RETURN value를 지정해 줘야함
  • RETURN 문이 다른 타입의 값을 반환하는 경우 올바른 타입으로 강제 재지정됩니다.
  • 만약 RETURN type으로 enum 또는 set이 지정되었는데 function body에서는 integer를 리턴하는 경우 함수에서 반환되는 값은 SET 멤버 집합의 해당 ENUM 멤버에 대한 문자열

예시

mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
       RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

routine_body

  • 간단한 DML 문 또는 복수의 구문(compound-statement)가 올 수 있다.
  • CREATE 및 DROP과 같은 DDL 문을 포함하는 루틴을 허용
  • 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만 지원된다.

[NOT] DETERMINISTIC

  • 같은 파라미터에 대해서는 같은 값을 반환한다면 해당 routine은 DETERMINISTIC 하다고 여겨진다
  • 따로 지정하지 않으면 기본값은 NOT DETERMINISTIC 이다.
  • 해당 routine이 DETERMINISTIC 하다면 명시하는 것이 성능에 좋다.
  • 만약 A라는 파라미터에 항상 10ㅌ00이라는 결과를 내놓은 routine이 있고 이를 WHERE 절에서 조건으로 사용하였다면 DETERMINISTIC한 routine이라면 1번만 호출하면 되지만 NOT DETERMINISTIC라면 각 row의 수만큼 호출 되어야 한다.

{ 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

links

social