스토어드 프로그램

스토어드 함수

스토어드 함수는 프로시저와 비슷하지만 반환타입과 반환값을 따로 지정하여 사용한다.

또한 예측 가능한 결과를 반환해야 한다는(결정성, 일관성 유지) 특징을 가지고 있다.

함수 목적

1. 단순한 계산 및 조회
2. 데이터 조작 없이 값 반환
3. 쿼리 내에서 사용
   프로시저는 CALL을 사용해 호출하지만, 함수는 SELECT를 사용해 호출하기 때문에 쿼리 내에서 함수 사용 가능
4. 결과의 예측 가능성

CREATE FUNCTION 함수 생성

DELIMITER //
CREATE FUNCTION 스토어드함수명(파라미터)
	RETURNS 반환형식
BEGIN
	코드블록...    
   	RETURN 반환값
END //
DELIMITER ;
SELECT 스토어드함수명();

위 구문으로 함수를 생성할 수 있지만, 따로 처리를 해주지 않으면 1418번 에러가 발생하게 된다.

Error Code: 1418. 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)

스토어드 함수 생성 오류 원인

위 메세지에서 확인할 수 있듯이 binary log를 기록하고 있는데 생성한 스토어드 함수가 DETERMINISTIC, NO SQL, READS SQL DATA 중 어느 것에도 속하지 않기 때문에 오류가 발생한다고 한다. 나열한 것들은 스토어드 함수의 옵션으로 이러한 옵션을 주게 되면 문제를 해결할 수 있다.

binary log

binary log는 DB 변경 사항이 발생할 때마다 기록을 하는 로그 파일이다. binary log를 기록하는 이유는 크게 두가지인데, 데이터 복구와 데이터 복제를 위함이다.

• 데이터 복구 : DB 손상 시 복구
• 데이터 복제 : 마스터-슬레이브 복제 설정에서 마스터 서버의 변경사항을 슬레이브 서버에 적용

binary log와 일관성

DETERMINISTIC, NO SQL, READS SQL DATA과 같은 옵션들은 데이터의 일관성을 보장하기 위해 사용한다.

binary log의 목적(데이터 복구, 데이터 복제)에 따라서 일관성 있는 데이터를 유지하는 것은 매우 중요하기 때문에, 스토어드 함수 생성 시 일관성을 보장하는 옵션을 주고 일관성을 깨뜨리지 않도록 함수 작성을 해야한다.

일관성을 깨뜨리는 경우

• 비결정적 함수
  같은 입력이 주어졌을 때 매 실행마다 다른 반환값을 가지는 함수를 비결정적 함수라고 한다.
  ex) 함수 내부에서 Rand(), Now(), UUID()를 실행하는 경우
• DB 상태를 변경하는 함수
  ex) 함수 내부에서 Insert, Update, Delete를 실행하는 경우

일관성을 깨뜨리는 함수들은 권장되지 않으며, 일관성을 깨뜨리게 되는 경우에는 함수가 아닌 프로시저로 작성한다.

스토어드 함수 생성 오류 해결

그렇기 때문에 다음 방법 중 하나를 사용해 오류를 해결해야 한다.

1. 스토어드 함수 생성 권한을 주기 (deprecated)
2. 스토어드 함수 생성 내부에서 결정성을 명시
3. 적절한 SQL 모드 설정으로 비결정적 요소를 제거
4. SUPER 권한 사용 (그러나 잠재적 보안 문제를 야기함)

이 방법들 중 1번 방식을 과거에는 많이 사용했었고, 최근에는 2번 방식을 사용하도록 권장된다.

1) 스토어드 함수 생성 권한을 주기

스토어드 생성 권한을 허용하여 스토어드 함수를 생성할 수 있다. 하지만 이 방식은 안전하지 못하며, deprecated 된 방식이다.

SET GLOBAL log_bin_trust_function_creators = 1;

해당 구문을 사용해 우회하는 경우에는 함수가 결정적 함수임을 명시하지 않아도 함수 생성이 가능하지만, 결정성 및 일관성을 저해할 수 있기 때문에 최근에는 2번 방식을 사용하는 것이 권장된다.

2) 스토어드 함수 생성 내부에서 결정성을 명시

MySQL 8.0.16 버전부터는 결정성을 명시하는 것이 좋다.
함수 생성 시 DETERMINISTIC, NO SQL, READS SQL DATA과 같은 옵션들을 주어 해당 함수가 결정성을 띄거나 일관성이 유지됨을 알린다.

어떤 함수가 결정적(DETERMINISTIC) 함수라는 것은 해당 함수에 같은 입력값이 주어질 경우 항상 같은 결과를 반환한다는 의미이다. 즉, 결정적 함수는 예측 가능한 결과를 반환한다.

DELIMITER //
CREATE FUNCTION 스토어드함수명(파라미터)
	RETURNS 반환형식
    DETERMINISTIC
BEGIN
	코드블록...    
   	RETURN 반환값
END //
DELIMITER ;
SELECT 스토어드함수명();

RETURNS 반환형식의 다음 줄에 DETERMINISTIC을 추가하여 이 함수가결정적 함수임을 명시한다.

이를 통해서 오류를 해결할 수 있다.

프로시저와 함수의 차이점

1. 스토어드 함수에서는 IN, OUT 파라미터를 사용할 수 없다. 스토어드 함수의 파라미터는 모두 입력 파라미터로 사용된다.
2. 스토어드 함수에서는 RETURNS로 반환 형식을 정하고 RETURN으로 하나의 값을 반드시 반환해야 한다. 프로시저는 반환 구문이 따로 없으며, OUT 파라미터로 값을 반환할 수 있다.
3. 스토어드 프로시저는 CALL로 호출하지만, 스토어드 함수는 SELECT문으로 호출한다.
4. 스토어드 프로시저 안에는 SELECT문을 사용할 수 있지만, 스토어드 함수 안에서는 집합 결과를 반환하는 SELECT문을 사용할 수 없다.
   (SELECT ~ INTO ~ 는 집합 결과를 반환하지 않으므로, 예외적으로 스토어드 함수 내에서 사용이 가능함)
5. 스토어드 함수는 동일 입력값에 대해 항상 같은 값을 반환해야 하는 결정성을 요구한다. 그러나 스토어드 프로시저는 비결정성을 허용한다.

정리

	프로시저	함수
목적	복잡한 비즈니스 로직 작성	단순 계산 및 조회. 쿼리 내에서 사용 가능
파라미터	IN(입력), OUT(출력) 두 종류	입력 파라미터만 존재함. IN 키워드 쓰지 않음
리턴값	존재하지 않음. 필요하다면 OUT 파라미터를 사용해 반환하도록 함	존재함. RETURNS와 RETURN으로 명시해야함
호출	CALL로 호출	SELECT로 호출
결정성	비결정성을 허용함	결정성이 요구됨
내부 SELECT문 사용	허용됨	집합 결과를 반환하는 SELECT문 사용 불가능(비결정적 요소)
INSERT, UPDATE, DELETE	허용됨	허용되지 않음(비결정적 요소)