mysql - CREATE FUNCTION

파이썬에서 사용자 정의 함수를 만드는 def처럼 mysql에도 CREATE FUNCTION을 이용하여 함수를 만들 수 있다. 기본적인 쿼리 문법은 다음과 같다.

1. 퀴리문법

CREATE FUNCTION [함수 이름] ( [입력값 이름] [데이터 타입], ...)

RETURNS [결과값 데이터 타입]

BEGIN

DECLARE [임시값 이름] [데이터 타입];
SET [임시값 이름] = [입력값 이름];
쿼리;
RETURN 결과값

END

이렇게만 보면 사실 잘 이해가 안되기 때문에 예시를 들어 보도록 하자 아래 예시는 포켓몬의 공격력과 방어력의 합을 가져오는 예시다.

포켓몬 DB는 아래 사이트에 잘 정리 해두었다. -> pokemon DB

CREATE FUNCTION getAbility(attack INT, defense INT)
	RETURNS INT
BEGIN
    DECLARE a INT;
    DECLARE b INT;
    DECLARE ability INT;
    SET a = attack;
    SET b = defense;
    SELECT a + b INTO ability;
    RETURN ability;
END

위 예제는 함수의 인자로 숫자형 데이터 두개를 받고 그 두 개의 데이터는 각각 attack과 defense로 명시하며 함수의 결과 또한 INT라고 설정한 것이다.

이때 중요한 것은 RETURNS로 꼭 S를 붙여주어야 한다는 것이다.

BEGIN과 END사이에는 쿼리가 들어가며 DECLARE는 쿼리 안에서 사용할 변수 명을 임시로 지정해 준다는 의미이다.

SET은 그 임시 값에 우리가 아까 인자로 넣었던 값을 할당해 줄 때 사용한다. 

그 후에는 원하는 쿼리를 넣어주면 된다.

2. 생성한 함수 사용 예시

SELECT name,  getAbility(attack, defense) AS absum,
CASE 
	WHEN getAbility(attack, defense) >= 120 THEN 'strong'
	WHEN getAbility(attack, defense) >= 70 THEN 'normal'
	WHEN getAbility(attack, defense) <= 69 THEN 'weak'
END AS abtype

FROM pokemon.mypokemon;

3. DBeaver에서 함수 생성 시 유의할 점

나는 DBeaver을 사용한다. 별 이유는 없고 내가 공부를 DBeaver로 시작을 해서 그런지 익숙해서 사용한다. 만약 DBeaver을 사용한다면 위 대로는 함수가 만들어지지 않고 다음과 같은 오류가 뜬다.

SQL Error [1418] [HY000]: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration 
and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

이 오류는 mysql의 바이너리 로깅 요구사항을 만족시키지 못해서 그렇다. 따라서 NOSQL 키워드를 추가하여 사용자 정의 함수의 속성을 지정해주어야 한다.

CREATE FUNCTION getAbility(attack INT, defense INT)
	RETURNS INT
	NO SQL
BEGIN
    DECLARE a INT;
    DECLARE b INT;
    DECLARE ability INT;
    SET a = attack;
    SET b = defense;
    SELECT a + b INTO ability;
    RETURN ability;
END;

NO SQL 말고도 DETERMINISTIC과 READS SQL DATA 총 3가지의 키워드가 존재하는데 그 셋의 차이와 쓰임새는 다음과 같다.
1. DETERMINISTIC:
   - 역할: DETERMINISTIC 키워드는 함수가 항상 동일한 입력에 대해 동일한 출력을 생성하며 부작용(side effect)이 없음을 나타냅니다. 즉, 함수의 결과가 항상 예측 가능하고 변하지 않는 것을 의미합니다.
   - 예: 수학적 함수나 날짜 및 시간 변환 함수와 같이 입력값에 따라 결과가 항상 같은 함수.

2. NO SQL:
   - 역할: NO SQL 키워드는 함수가 SQL 문을 실행하지 않음을 나타냅니다. 함수가 데이터베이스를 수정하지 않고 SQL 쿼리를 실행하지 않는 경우에 사용됩니다.
   - 예: 단순한 수치 연산 함수 또는 문자열 조작 함수와 같이 데이터베이스에 영향을 주지 않는 함수.

3. READS SQL DATA:
   - 역할: READS SQL DATA 키워드는 함수가 데이터베이스에서 데이터를 읽지만 수정하지 않음을 나타냅니다. 즉, 함수는 데이터를 쿼리할 수 있지만 수정 작업은 수행하지 않습니다.
   - 예: SELECT 문을 사용하여 데이터를 검색하는 함수.

무슨 말인지 감이 잘 안잡히면 '예'를 보고 조금이라도 감을 잡아야겠다.

 

위 키워드들은 데이터베이스의 무결성과 안전성을 보장하기 때문에 꼭 필요하다고 한다.

특히 함수를 정의 한다는 것은 내가 새로운 법칙을 정의하는 것이기 때문에 이러한 속성을 올바르게 지정하여 데이터베이스 시스템의 안전성을 보장하는 것이다.

 

4. MYSQL Workbench에서 함수 생성 시 주의할 점

SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER //
	CREATE FUNCTION getAbility(attack INT, defense INT)
	RETURNS INT
BEGIN
    DECLARE a INT;
    DECLARE b INT;
    DECLARE ability INT;
    SET a = attack;
    SET b = defense;
    SELECT a + b INTO ability;
    RETURN ability;
END;

//
DELIMITER ;

work bench에서는 SET을 통해 사용자 계정에 function create 권한을 생성해 주고 함수의 시작 지점과 끝을 DELIMITER로 지정해 줘야 한다.

주의할 점으로는 마지막 DELIMITER는 꼭 한 칸 띄어서 ; 새미콜론을 붙여주여야 한다.