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는 꼭 한 칸 띄어서 ; 새미콜론을 붙여주여야 한다.