20180601 MySQL : Function Example

MySQL 쿼리를 짜다보면, 동일한 액션에 대해서 함수를 구현해야 하는 경우가 있다. 그동안 제대로 써본 적이 없어서 함수나 프로시저에 대해서 자신이 없었는데, LeetCode 덕분에 공부할 수 있게되어서 다행이라고 생각한다.

함수(Function)란?

보통 값을 계산하고 결과 값을 반환하기 위해서 함수를 많이 사용한다. 대부분 구성이 프로시저와 유사하지만 IN 파라미터만 사용할 수 있다. 반드시 반환될 값의 데이터 타입을 RETURN 문에 선언해야 한다. 또한 PL/SQL 블록 내에서 RETURN 문을 통해서 반드시 값을 반환해야 한다.(PL/SQL 은 Pro-cedural Language 의 준말에 해당하는 표현으로서 SQL 문을 사용하여 프로그램을 작성할 수 있도록 확장해놓은 표현이다.)

환경

• window 10

• mysql Ver 14.14 Distrib 5.7.20, for Win64 (x86_64)

Create Function

Hello World 를 출력하는 함수를 아래와 같이 만들어보고 실행해본다.

DELIMITER $$
 
CREATE FUNCTION Hello_World() RETURNS TEXT
BEGIN
    RETURN 'Hello World';
END;
 
$$
DELIMITER ;

SELECT Hello_World();
 
+---------------+
| Hello_World() |
+---------------+
| Hello World   |
+---------------+

CREATE Function with a parameter

파라미터가 존재하는 함수를 만들겠다. 우선 만들어진 Hello_World() 함수를 삭제하고 새롭게 생성하는 방식을 취한다. 아래의 내용으로 해당 Function 을 drop 시키고 새롭게 만든다. 

DELIMITER $$
 
CREATE FUNCTION Hello_World(addressee TEXT) RETURNS TEXT
BEGIN
    RETURN CONCAT('Hello ', addressee);
END;
 
$$
DELIMITER ;

SELECT Hello_World('Space');
+----------------------+
| Hello_World('Space') |
+----------------------+
| Hello Space          |
+----------------------+
 
 
SELECT Hello_World('Earth');
+----------------------+
| Hello_World('Earth') |
+----------------------+
| Hello Earth          |
+----------------------+

CREATE Function with a local variable

함수 내부의 지역변수가 존재하는 함수를 아래와 같이 만들어보자

DROP FUNCTION IF EXISTS Hello_World;
 
DELIMITER $$
 
CREATE FUNCTION Hello_World(addressee TEXT) RETURNS TEXT
BEGIN
  DECLARE strlen INT;
  SET strlen = LENGTH(addressee);
  RETURN CONCAT('Hello ', addressee, ' - your parameter has ', strlen, ' characters');
END;
 
$$
DELIMITER ;

SELECT Hello_World('SPACE');
+-----------------------------------------------+
| Hello_World('SPACE')                          |
+-----------------------------------------------+
| Hello SPACE - your parameter has 5 characters |
+-----------------------------------------------+

이렇게 함수를 몇가지 공부해보고나서, 의아한것이 있다. 바로 DELIMITER Keyword이다. 사실 MySQL 은 ';' (세미콜론) 으로 해당 문장이 끝나는데 함수를 이용하게 되면 세미콜론 때문에 함수가 생성이 되지 않는다. 그러한 문제를 해결하기 위해 DELIMITER 를 사용하는 것이다.

DELIMITER 는 MySQL 의 클라이언트 내장명령으로 mysql 에 접속하고 help 를 치게되면 나타난다. BEGIN ~ END 블록이 있는 저장루틴을 만드는 경우 블록내부의 SQL 문은 세미콜론(;) 때문에 종료하게 되며 이를 해결하는 것이 DELIMITER 이다. 결국 DELIMITER 는 함수의 시작과 끝에 위치하여 한번에 실행될 수 있게 해주는 역할을 수행한다.

+) Function DDL 명령어

(1) Function 조회

SHOW FUNCTION STATUS WHERE DB = 'LearnSQL'\G
 
*************************** 1. row ***************************
                  Db: learnsql
                Name: hello_world
                Type: FUNCTION
             Definer: doubler@%
            Modified: 2018-06-02 07:50:26
             Created: 2018-06-02 07:50:26
       Security_type: DEFINER
             Comment:
character_set_client: euckr
collation_connection: euckr_korean_ci
  Database Collation: utf8_general_ci

(2) 특정 Function 생성문 확인

SHOW CREATE FUNCTION hello_world\G
 
*************************** 1. row ***************************
            Function: Hello_World
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`doubler`@`%` FUNCTION `Hello_World`(addressee TEXT) RETURNS text CHARSET utf8
BEGIN
  DECLARE strlen INT;
  SET strlen = LENGTH(addressee);
  RETURN CONCAT('Hello ', addressee, ' - your parameter has ', strlen, ' characters');
END
character_set_client: euckr
collation_connection: euckr_korean_ci
  Database Collation: utf8_general_ci

(3) 특정 Function 삭제

DROP FUNCTION Hello_World;

[ 참고링크 ]

[ 함수(Function) ]

[ CREATE FUNCTION examples ]