[SQL] 스토어드 함수와 커서

스토어드 함수

스토어드는 함수는 스토어드 프로시저와 비슷하게 사용자가 직접 함수를 만들어 사용하는 방법이다.

 

기본 문법

• DELMITER, BEGIN과 END는 스토어드 프로시저와 동일한 형태
• 매개변수는 입력 매개변수만 존재하며 (변수명 변수_형식)으로 작성한다.(ex. (value1 INT, value2 INT))
• 출력은 RETURNS가 담당한다.
• 호출 시 CALL이 아닌 SELECT를 사용한다.
• 삭제 시 DROP문을 사용한다.

//기본문법
DELIMITER $$
CREATE FUNCTION 스토어드_함수_이름(매개변수1 매개변수1_형식, 매개변수2 매개변수2_형식)
	RETURNS 반환형식
BEGIN

	함수 내용
    RETURN 반환값;
    
END $$
DELIMITER;


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



//함수 삭제
DROP FUNCTION 스토어드_함수_이름;

 

 

 

커서(CURSOR)

테이블에서 한 행씩 작업을 처리하기 위한 방법이다.

첫 번째 행부터 마지막 행까지 정해진 작업을 순차적으로 처리한다.

 

커서 사용 과정

1. 커서 선언하기
2. 반복 조건 선언하기
3. 커서 열기
4. 데이터 가져오기
5. 데이터 처리하기
6. 커서 닫기

특정 컬럼의 데이터의 평균을 커서로 구하는 과정으로 커서 사용 과정을 살펴보자.

 

0. 사용할 변수 준비

모든 팀의 평균 인원 수를 구하기 위해 팀 인원수(memNumber), 전체 인원수(totNumber), 팀의 수(teamNumber) 변수를 선언한다.

전체 인원 수와 팀의 수는 초기값을 0으로 지정한다.

또한 마지막 행 여부를 확인하기 위한 endOfRow를 선언한다.

DECLARE memNumber INT;
DECLARE totNumber INT DEFAULT 0;
DECLARE teamNumber INT DEFAULT 0;
DECLARE endOfRow BOOLEAN DEFAULT FALSE;

 

1. 커서 선언하기

커서는 결국 데이터를 조회해야하기 때문에 SELECT문을 사용한다.

여기선 member 테이블의 mem_number 컬럼의 데이터를 사용한다.

DECLARE memberCursor FOR
	SELECT mem_number FROM member;

 

2. 반복 조건 선언하기

마지막 행까지 작업을 수행해 더이상 행이 없을 경우 endOfRow를 TRUE로 변환한다.

DELCARE CONTINUE HANDLER
	FOR NOT FOUNT SET endOfRow = TRUE;

 

 

3. 커서 열기

커서는 간단하게 OPEN 키워드로 열 수 있다.

OPEN memberCursor;

 

 

4. 행 반복하기

반복할 작업, 즉 데이터를 가져오고 처리하는 과정을 수행한다.

LOOP 안에는 반복문을 빠져나갈 조건문이 있어야 무한 반복이 발생하지 않는다.

해당 조건문은 LEAVE 키워드를 사용해 지정한다.

//cursor_loop는 반복할 작업의 이름을 지정한 것이다
cursor_loop: LOOP
	반복할 작업
    
    IF endOfRow THEN
    	LEAVE cursor_loop;
    END IF;
    
END LOOP cursor_loop

 

 

5. 커서 닫기

CLOSE 키워드를 사용해 커서를 닫는다.

CLOSE memberCursor;