[MySQL] 스토어드 함수와 커서 정리

데브마우스 2024. 1. 5. 12:15

스토어드 함수는 무엇인가요?

스토어드 함수(Stored Function)은 MySQL 제공하는 내장 함수가 아닌, 직접 함수를 만드는 기능을 제공합니다. 스토어드 함수는 프로시저와 비슷해보이지만 세부적으로 다릅니다. 용도가 다르며, RETURNS 예약어를 통해 하나의 값을 반환한다는 특징이 있습니다.

스토어드 함수의 형식과 특징

스토어드 함수는 다음과 같은 형식으로 작성됩니다.

DELIMITER $$
CREATE FUNCTION 스토어드_함수_이름(파라미터 데이터타입) -- SQL문은 프로그래밍 언어와 다르게 데이터 타입이 뒤에옵니다.
	RETURNS 반환 데이터 타입 -- RETURN 뒤에 S가 있습니다.
BEGIN
	SQL문 코딩
    RETURN 반환값; -- RETURN 뒤에 S가 없습니다.
END $$
DELIMITER ;

SELECT 스토어드_함수_이름(); -- 스토어드 함수 실행 코드입니다.

스토어드 함수의 특징은 아래와 같습니다.

RETURNS 뒤에 반환할 데이터 타입을 작성합니다.
RETURN 뒤에는 하나의 값을 반드시 반환해야하니다.
스토어드 함수의 파라미터는 모두 입력 파라미터이며 스토어드 프로시저와 달리 IN이 붙지 않습니다.
스토어드 함수는 SELECT로 호출합니다. 스토어드 프로시저는 CALL로 호출합니다.
★
스토어드 함수에서는 SELECT를 사용할 수 없습니다. 하지만 스토어드 프로시저 안에서는 SELECT문을 사용할 수 있습니다.
스토어드 함수는 어떠한 계산으로 하나의 값을 반환하는 용도로 사용합니다. 스토어드 프로시저는 여러 SQL문이나 숫자 계산 등의 다양한 용도로 사용됩니다.

스토어드 함수를 사용하려면 아래와 같은 함수 생성 권한을 허용해줘야합니다.

SET GLOBAL log_bin_trust_function_creators = 1; -- 스토어드 함수 생성 권한 허용 SQL문

★ SELECT ~ INTO ~ 를 활용하여 변수에 값을 저장하는 방법입니다.

SELECT YEAR(curdate()) INTO @A; -- @A에 올 해 년도(글 작성 시점 기준 2024)를 입력합니다.

아래 코드로 기존에 작성된 스토어드 함수의 내용을 확인할 수 있습니다.

SHOW CREATE FUNCTION 함수_이름;

커서로 행을 처리하는 방법 무엇인가요?

커서는 SQL(Structured Query Language)에서 커서(cursor)는 데이터베이스에서 데이터를 조작하고 조회할 때 사용되는 개체입니다. 커서는 일종의 포인터로서, 특정 결과 집합(ResultSet) 내에서 현재 위치를 나타냅니다. 커서는 한 행씩 처리합니다.

커서의 사용 순서는 아래와 같습니다.

커서 선언하기
1. 변수 선언: DECLARE 변수이름 데이터타입 [DEFAULT 값];
2. 행의 끝을 파악하기 위한 변수 선언: DECLARE endOfRow BOOLEAN DEFAULT FALSE;
3. 커서 선언: DECLARE 커서_이름 CURSOR FOR SELECT 열_이름 FROM 테이블_이름;
반복 조건 선언하기
1. DECLARE CONTINUE HANDLER FOR NOT FOUND SET endOfRow = TRUE;
  1. 반복 조건 선언 코드는 위와 같은 방식을 사용하며 거의 고정된 코드입니다.
커서 열기
1. 준비한 커서를 OPEN으로 엽니다: OPEN memberCursor;
데이터 가져오기 / 데이터 처리하기 : 이 부분을 반복합니다.
1. 커서_이름: Loop
  반복할 SQL문
  END LOOP 커서_이름
2. LEAVE로 반복할 이름을 빠져나갑니다.
  IF endOfRow THEN
  LEAVE 커서_이름;
  END IF;
3. FETCH는 한 행씩 읽어오는 SQL문입니다.
커서 닫기
1. CLOSE로 커서 닫기: CLOSE 커서_이름;

CALL 함수_이름();으로 스토어드 함수를 실행할 수 있습니다.