7. 스토어드 프로시저-2

0. 학습 목표

• 스토어드 프로시저의 작성 방법을 이해
• 스토어드 함수와 커서에 대해서 이해하고 활용방법을 학습
• 트리거의 적용 분야와 실제 사용방법을 학습

0. 요약

• 스토어드 프로시저는 MySQL에서 제공되는 프로그래밍 기능
• 스토어드 프로시저는 BEGIN ~ END 사이에 코드를 구현
• 스토어드 프로시저 작성 후 CALL 문을 통해 스토어드 프로시저 호출
• 입력 매개변수는 스토어드 프로시저에 값을 전달(IN)
• 출력 매개변수는 스토어드 프로시저에서 계산된 결과를 돌려 받음(OUT)
• 동적 SQL은 다이나믹하게 SQL을 생성한 후 실행, PREPARE, EXECUTE 문을 사용
• MySQL은 내장 함수로 제공되지 않는 기능을 스토어드 함수로 만들어 사용 가능
• 스토어드 함수는 RETURNS 예약어를 통해 반환된 데이터 형식을 미리 지정해야 함
• 스토어드 함수의 매개변수는 입력 매개변수, 출력 매개변수 없음
• 커서는 한 행씩 처리되도록 하는 기능, DECLARE로 선언, 내용은 SELECT
• 커서는 행이 끝날 때까지 반복, 행의 끝을 판단하기 위해 변수 endOfRow를 준비하고 TRUE인지를 체크

---

2. 스토어드 함수와 커서

※핵심 키워드:  스토어드 함수, RETURNS, 입력 매개변수, 커서, endOfRow

스토어드 프로시저와 함께 SQL 프로그래밍 기능으로 사용되는 데이터베이스 개체로는 스토어드 함수와 커서가 있다.

스토어드 함수는 MySQL에서 제공하는 내장 함수 외에 직업 함수를 만드는 기능을 제공한다. 스토어드 프로시저와 모양이 비슷하지만 세부적/용도가 다르며 RETURNS 예약어를 통해서 하나의 값을 반환해야 하는 특징이 있다.

커서는 스토어드 프로시저 안에서 한 행씩 처리할 때 사용하는 프로그래밍 방식이다.

---

2-1. 스토어드 함수

스토어드 함수의 개념과 형식

MySQL은 다양한 함수를 제공한다.(SUM, CAST, CONCAT 등) 하지만 사용자가 원하는 모든 함수를 제공하지 않으므로 필요하다면 사용자가 직접 함수를 만들어 사용할 수 있다. 이것을 스토어드 함수(Stored Function)이라고 한다.

DELIMITER $$

CREATE FUNCTION 스토어드_함수_이름(매개변수)
	RETURNS 반환형식
BEGIN
	## 코딩 ##
	RETURN 반환값;
END $$
DELIMITER ;

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

스토어드 함수의 형식을 보면 스토어드 프로시저와 상당히 유사하지만 차이점이 있다.

• 스토어드 함수는 RETRUNS문으로 반환할 값의 데이터 형식을 지정하고 본문 안에서는 RETURN문으로 하나의 값을 반환해야 한다.
• 스토어드 함수의 매개변수는 모두 입력 매개변수 이다. 그리고 IN을 붙이지 않는다.
• 스토어드 함수는 SELECT문 안에서 호출된다.(프로시저는 CALL을 사용)
• 스토어드 함수 안에서는 SELECT를 사용할 수 없다.(프로시저는 사용 가능)

스토어드 프로시저는 여러 SQL문이나 숫자 계산 등의 다양한 용도로 사용하지만, 스토어드 함수는 어떤 계산을 통해 하나의 값을 반환하는데 주로 사용된다.

---

스토어드 함수의 사용

우선 스토어드 함수를 사용하기 위해서는 아래 SQL로 스토어드 함수 생성 권한을 허용해 주어야 한다.

SET GLOBAL log_bin_trust_function_creators = 1;

이제 숫자 2개의 합계를 계산하는 스토어드 함수를 만들어 보자.

USE market_db;

DROP FUNCTION IF EXISTS sumFunc;

DELIMITER $$
CREATE FUNCTION sumFunc(number1 INT, number2 INT) -- 2개의 정수형 매개변수를 입력 받음
	RETURNS INT		     -- 데이터 형식은 정수
BEGIN
	RETURN number1 + number2;		-- 매개변수로 입력받은 값을 더해 반환
END $$
DELIMITER ;

SELECT sumFunc(100, 200) AS '합계';

# 합계
'300'

이번에는 데뷔 연도를 입력하면 활동 기간이 얼마나 되었는지 출력해 주는 함수를 만들어 보자.

USE market_db;

DROP FUNCTION IF EXISTS calcYearFunc;

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

SELECT calcYearFunc(2010) AS '활동 햇수';

# 활동 햇수
'12'

필요하다면 아래와 같이 함수의 반환 값을 SELECT ~ INTO ~로 저장했다가 사용할 수 있다. 함수의 반환값을 각 변수에 저장한 후 그 차이를 계산해 보자.

SELECT calcYearFunc(2007) INTO @debut2007;
SELECT calcYearFunc(2013) INTO @debut2013;
SELECT @debut2007 - @debut2013 AS '2007과 2013의 차이';

# 2007과 2013의 차이
'6'

자 이번에는 member 테이블에서 데뷔 년도(debut_date)의 년을 추출하여 회원 별 활동 햇수를 출력해 보자.

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

# mem_id, mem_name, 활동 햇수
'APN', '에이핑크', '11'
'BLK', '블랙핑크', '6'
'GRL', '소녀시대', '15'
'ITZ', '잇지', '3'
'MMU', '마마무', '8'
'OMY', '오마이걸', '7'
'RED', '레드벨벳', '8'
'SPC', '우주소녀', '6'
'TWC', '트와이스', '7'
'WMN', '여자친구', '7'

참고로 기존에 작성된 스토어드 함수의 내용을 확인하려면 아래와 같은 쿼리문을 사용하면 된다.

SHOW CREATE FUNCTION 함수_이름;

함수의 삭제는 DROP FUNCTION문을 사용한다.

DROP FUNCTION calcYearFunc;

---

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

커서의 기본 개념

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

커서는 일반적으로 다음의 작동 순서를 통해 처리된다.

---

커서의 단계별 실습

회원(가수 그룹)의 평균 인원수를 구하는 스토어드 프로시저를 작성해 보자. 커서를 활용해서 한 행씩 접근해서 회원의 인원수를 누적시키는 방식으로 처리해 보자.

• 사용할 변수 준비하기
  -memNumber: 평균 인원수를 계산하기 위한 각 회원의 인원 수
  -toNumber: 전체 인원의 합계
  -cnt: 읽은 행의 수
  ※전체 인원의 합계와 읽은 행의 수를 누적시켜야 하기 때문에 DEFAULT 문을 사용해서 초기값을 0으로 설정
  -endOfRow: 행의 끝을 파악하기 위한 변수, 처음은 행의 끝이 아닐테니 FALSE

DECLARE memNumber INT;
DECLARE toNumber INT DEFAULT 0;
DECLARE cnt INT DEFAULT 0;
DECLARE endOfRow BOOLEAN DEFAULT FALSE;

    DECLARE memberCursor CURSOR FOR
		SELECT mem_number FROM member;

• 반복 조건 선언하기
  -행의 끝에 다다르면 앞에서 선언한 endOfRow의 변수를 TRUE로 설정하면 된다
  -DECLARE CONTINUE HANDLER: 반복 조건을 준비하는 예약어
  -FOR NOT FOUND: 더 이상 행이 없을 때 이어진 문장을 수행

    DECLARE CONTINUE HANDLER
		FOR NOT FOUND SET endOfRow = TRUE;

• 커서 열기

OPEN memberCursor;

• 행 반복하기
  -cursor_loop라는 이름으로 Loop를 선언
  -endOfRow가 True이면 빠져 나가도록 선언
  -LEAVE cursor_loog를 설정하여 위 endOfRow가 True가 되면 반복문 탈출
  -FEATCH는 항 행씩 읽어옴
  -반복을 빠져나오면 toNumber/cnt로 나눠 회원의 평균 인원수를 계산

    cursor_loop: LOOP
		FETCH memberCursor INTO memNumber;
        
        IF endOfRow THEN
			LEAVE cursor_loop;
		END IF;
        
        SET cnt = cnt + 1;
        SET toNumber = toNumber + memNumber;
	END LOOP cursor_loop;
        
    SELECT (toNumber/cnt) AS '회원의 평균 인원 수';

• 커서 닫기

CLOSE memberCursor;

전체 통합 코드는 아래와 같다.

use market_db;

DROP PROCEDURE IF EXISTS curosr_proc;

DELIMITER $$
CREATE PROCEDURE cursor_proc()
BEGIN
	DECLARE memNumber INT;
    DECLARE toNumber 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 toNumber = toNumber + memNumber;
	END LOOP cursor_loop;
    
    SELECT (toNumber/cnt) AS '회원의 평균 인원 수';
    
    CLOSE memberCursor;
END $$
DELIMITER ;
    
CALL cursor_proc();

# 회원의 평균 인원 수
'6.6000'