[MySQL] 스토어드 프로시저(2)

1. 스토어드 함수

SUM(), CAST(), CONCAT(), CURRENT_DATE() 외에 필요한 기능일 직접 만들어서 사용하는 함수

DELIMITER $$
CREATE FUNCTION 스토어드_함수_이름(매개변수)
	RETURNS 반환형식
BEGIN
	프로그래밍
    RETURN 반환값;
END $$
DELIMITER ; 

SELECT 스토어드_함수_이름();

스토어드 함수는 RETURNS 문으로 반환할 값의 데이터 형식을 지정하고,

본문 안에서 RETURN 문으로 하나의 값을 반환해야한다.

 

스토어드 함수는 입력 매개변수만 존재하여 IN을 별도로 명시하지 않는다!

* 스토어드 프로시저는 입력/출력 매개변수가 존재하니 IN/OUT 명시

 

SELECT 문 안에서만 호출이 가능하다.

* 스토어드 프로시저는 CALL 로 호출

 

SET GLOBAL log_bin_trust_function_creators = 1;

MySQL 안에서 사용하는 변수인데 1번 설정해주어야함.

설정을 해주지 않으면, 

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)

이런 에러가 발생하며 정의한 FUNCTION을 사용할 수 없다.

 

MySQL 특정 버전대에서 Super 권한이 없는 user에게서 발생할 수 있는 문제로, 

mysql global variable 중  log_bin_trust_function_creators 옵션을 활성화해주지 않으면 발생할 수 있는 에러이다.

 

log_bin_trust_function_creators 는 MySQL이 function, trigger 생성에 대한 제약을 강제할 수 있는 기능으로,

해당 옵션의 default는 OFF이며, OFF상태의 경우 권한이 있더라도 trigger를 생성할 수 없고, function을 생성할 수 없다.

 

결론적으로 해당 옵션이 OFF 상태일 경우, root 권한이 없는 user가 생성한 function을 일반 user가 실행할 수 없게된다.

* 옵션 상태 확인 방법

SHOW GLOBAL VARIABLES LIKE 'log_bin_trust_function_creators';

 

* 실습1 ( 두개의 파라미터)

DELIMITER $$
CREATE FUNCTION sumfunc(number INT, number2 INT)
	RETURNS INT
BEGIN
	RETURN number+number2;
END $$
DELIMITER ;

SELECT sumfunc(2, 5) FROM DUAL;

 

* 실습 2 ( 복잡한 계산식을 해주는 FUNC, 내부에 변수 有)

DELIMITER $$
CREATE FUNCTION calfunc(dYear INT)
	RETURNS INT
BEGIN
	DECLARE runYear INT;
    SET runYear = YEAR(CURDATE()) - dYear;
    RETURN runYear;
END $$
DELIMITER ;


SELECT mem_id, mem_name, calfunc(YEAR(debut_date)) AS '활동 햇수'
FROM  member;

 

 

2. 커서로 한 행씩 처리하기

커서는 첫 번쨰 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리함.

 

 

3. 커서의 단계별 실습

1) 사용할 변수 준비

회원의 평균 인원 수를 계산하기 위해 memNumber, 전체 인원의 합계 totNumber, 읽은 행의 수 cnt 변수를 3개 선언한다.

행의 끝을 파악하지 위한 변수 endOfRow도 선언한다.

DECLARE memNumber INT;
DECLARE cnt INT DEFAULT 0;
DECLARE totNumber INT DEFAULT 0;

DECLARE endOfRow BOOLEAN DEFAUTL FALSE;

 

2) 커서 선언하기

DECLARE memberCursor CURSOR FOR
	SELECT mem_number FROM member;

 

3) 반복 조건도 준비

(더이상 처리할 행이 없을 때 행의 끝을 확인하는 변수 endOfRow 를 TRUE 로 바꿔주는 조건)

DECLARE CONTINUE HANDLER
	FOR NOT FOUND SET endOfRow = TRUE;

 

4) 커서 열기

OPEN memberCursor;

 

5) 행 반복하기

cursor_loop : LOOP
	[이부분을 반복]
END LOOP cursor_loop

cursor_loop 는 반복할 부분의 이름을 지정

LOOP 문 안에는 반복문을 빠져나갈 LEAVE 문을 꼭 포함해야한다

 

IF endOfRow THEN
	LEAVE cursor_loop;
END IF;

한 행씩 읽어오는 FETCH

cursor_loop : LOOP
	FETCH memberCursor INTO memNumer;
    
    IF endOfRow THEN
    	LEAVE cursor_loop;
    END IF;
    
    SET cnt = cnt + 1;
    SET totNumber = totNumber + memNumber;
END LOOP cursor_loop;

읽은 행의 수를 나타내는 cnt를 반복문을 수행할 때마다 1씩 증가시키고,

총 인원 수인 totNumber 에도 그룹의 인원수 값을 더해준다.

 

이 반복문을 빠져나오면 평균 인원수도 구할 수 있다.

SELECT (totNumber/cnt) AS '회원의 평균 인원 수';

 

 

6) 커서 닫기

CLOSE memberCursor;

 

7) 전체 코드 보기

DELIMITER $$
CREATE PROCEDURE cursor_proc()
BEGIN
	DECLARE memNumber INT;
    DECLARE totNumber INT DEFAULT 0;
    DECLARE cnt INT DEFAULT 0;
    DECLARE endOfRow BOOLEAN DEFAULT FALSE;              --마지막 행을 확인하는 변수
    
    DECLARE memberCursor CURSOR FOR 			 --커서 선언하기
    	SELECT mem_number FROM member;
       
    DECLARE CONTINUE HANDLER				 --반복 조건 설정
    	FOR NOT FOUND SET endOfRow = TRUE;
    
    OPEN memberCursor;					 --커서 열기
    
    cursor_loop : LOOP
    	FETCH memberCursor INTO memNumber;		 --커서 읽기
        
        IF endOfRow THEN
        	LEAVE cursor_loop;			 --반복문 나가는 조건
        END IF;
        
        SET cnt = cnt + 1;
        SET totNumber = totNumber + memNumber;
        
    END LOOP cursor_loop;
    
    SELECT (totNumber / cnt) AS '회원의 평균 인원 수';
    
    CLOSE memberCursor;			 		--커서 닫기
END $$
DELIMITER ;