[혼공SQL] chapter 7. 스토어드 프로시저(2)

여정이·2024년 8월 6일
mysql혼공단 12기혼자 공부하는 SQL
0

혼자 공부하는 SQL

목록 보기
23/28

📒 요약 : 스토어드 함수와 커서는 스토어드 프로시저와 함께 SQL 프로그래밍 기능으로 사용되는 데이터베이스 개체이다. 스토어드 함수와 커서를 잘 활용하면 SQL의 당순한 기능을 더욱 강력하게 확장할 수 있다.

스토어드 함수와 커서

스토어드 함수는 MYSQL에서 제공하는 내장 함수 외에 사용자가 직접 함수를 만드는 기능을 제공한다. 스토어드 함수는 스토어드 프로시저와 모양이 비슷하지만, RETURNS 예약어를 통해서 하나의 값을 반환해야 하는 특징을 가진다는 점에서 다르다. 그리고 무엇보다도 사용하는 용도에서 차이가 두드러진다.

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

1. 스토어드 함수

스토어드 함수는 앞에서 배운 스토어드 프로시저와 비슷하지만, 사용 방법이나 용도가 조금 다르다.


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

MySQL은 사용자가 이용할 수 있는 다양한 함수를 제공하지만, 사용자가 원하는 모든 기능을 수행할 수는 없다. 그래서 사용자가 필요한 함수를 직접 만들어서 사용할 수 있는데, 이렇게 직접 만들어서 사용하는 함수는 스토어드 함수라고 부른다. 스토어드 함수는 다음과 같은 형식으로 구성할 수 있다.

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

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

스토어드 프로시저의 형식과 상당히 비슷한데, 몇 가지 차이점이 있다.

  • 스토어드 함수는 RETURNS 문으로 반환할 값의 데이터 형식을 지정하고, 본문 안에서는 RETURN 문으로 하나의 값을 반환해야 한다.
  • 스토어드 함수의 매개변수는 모두 입력 매개변수이다. 따로 구분할 필요가 없기 때문에 IN을 붙이지 않는다.
  • 스토어드 프로시저는 CALL로 호출하지만 스토어드 함수는 SELECT문 안에서 호출한다.
  • 스토어드 프로시저 안에서는 SELECT문을 사용할 수 있지만, 스토어드 함수 안에서는 SELECT문을 사용할 수 없다.
  • 스토어드 프로시저는 여러 SQL문이나 숫자 계산 등의 다양한 용도로 사용하지만, 스토어드 함수는 어떤 계산을 통해서 하나의 값을 반환하는 데 주로 사용한다.

스토어드 함수의 사용

스토어드 함수를 사용하기 위해서는 먼저 아래의 SQL문으로 스토어드 함수 생성 권한을 허용해줘야 한다. MySQL에서 최초 1회 실행해주면 된다.

#스토어드 함수 생성 권한 허용하기
SET GLOBAL log_bin_trust_function_creators = 1;

먼저 간단한 스토어드 함수를 만들어서 사용해 보자. 아래의 SQL문은 매개변수를 통해 들어온 숫자 2개의 합을 계산하는 스토어드 함수이다.

USE market_db;
DROP FUNCTION IF EXISTS sumFunc;
DELIMITER $$
CREATE FUNCTION sumFunc(number1 INT, number2 INT)			#2개의 정수형 매개변수를 전달받음
    RETURNS INT												#이 함수가 반환하는 데이터 형식을 정수로 지정
BEGIN
    RETURN number1 + number2;								#RETURN문으로 정수형 결과 반환
END $$
DELIMITER ;

SELECT sumFunc(100, 200) AS '합계';							#SELECT문에서 함수를 호출하며 2개의 매개변수 전달

그림1. sumFunc 스토어드 함수 실행 결과

만약 필요하다면 함수의 리턴값을 SELECT~INTO로 저장했다가 사용할 수 있다.

SELECT sumFunc(130, 200) INTO @result1;
SELECT sumFunc(200, 450) INTO @result2;
SELECT @result1-@result2 AS 'result1과 result2의 차' ;

그림2. 함숫값을 변수에 대입하기

함수를 사용할 때에는 스토어드 프로시저를 삭제할 때와 마찬가지로 아래와 같이 DROP을 이용한다.

DROP FUNCTION sumFunc;





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

커서의 기본 개념

커서는 테이블에서 한 행씩 처리하기 위한 방식이다. 첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리한다. 커서는 대부분 스토어드 프로시저와 함께 사용되는데, 일반적으로 아래와 같은 과정을 통해 처리된다.

  1. 커서 선언하기
  2. 반복 조건 ㅅ선언하기
  3. 커서 열기
  4. 테이블에서 데이터 가져오기
  5. 데이터 처리하기
  6. 4번과 5번 과정을 반복하기
  7. 커서 닫기

커서의 단계별 실습

member 테이블에서 그룹의 평균 인원수를 구하는 스토어드 프로시저를 커서를 활용해서 작성해보자. 커서는 테이블의 한 행씩 접근해서 회원의 인원수를 누적시키는 방식으로 처리될 것이다. 먼저 전체 코드는 아래와 같다.

USE market_db;
DROP PROCEDURE IF EXISTS cursor_proc;
DELIMITER $$
CREATE PROCEDURE cursor_proc()
BEGIN
    DECLARE memNumber INT; -- 회원의 인원수
    DECLARE cnt INT DEFAULT 0; -- 읽은 행의 수
    DECLARE totNumber INT DEFAULT 0; -- 인원의 합계
    DECLARE endOfRow BOOLEAN DEFAULT FALSE; -- 행의 끝 여부(기본을 FALSE)

    DECLARE memberCuror CURSOR FOR-- 커서 선언
        SELECT mem_number FROM member;

    DECLARE CONTINUE HANDLER -- 행의 끝이면 endOfRow 변수에 TRUE를 대입 
        FOR NOT FOUND SET endOfRow = TRUE;

    OPEN memberCuror;  -- 커서 열기

    cursor_loop: LOOP
        FETCH  memberCuror 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 memberCuror; 
END $$
DELIMITER ;

CALL cursor_proc();

길고 복잡해 보이는데, 단계 별로 코드를 살펴보자.

1. 사용할 변수 준비하기

회원의 평균 인원수를 계산하기 위해서 각 회원의 인원수(memNumber), 전체 인원의 합계(totNumber), 읽은 행의 수(cnt) 변수를 준비한다. 전체 인원의 합계와 읽은 행의 수를 누적시켜야 하기 때문에 DEFAULT문을 사용하여 초기값을 0으로 설정한다.

 	DECLARE memNumber INT; -- 회원의 인원수
    DECLARE cnt INT DEFAULT 0; -- 읽은 행의 수
    DECLARE totNumber INT DEFAULT 0; -- 인원의 합계

추가로 행의 끝을 파악하기 위한 변수 endOfRow를 준비한다. 처음에는 당연히 행의 끝이 아닐 테니 FALSE로 초기화한다.

	DECLARE endOfRow BOOLEAN DEFAULT FALSE; -- 행의 끝 여부(기본을 FALSE)

2. 커서 선언하기

커서를 선언한다. 결국 SELECT문을 만든다고 생각하면 된다. member 테이블을 조회하는 SQL문을 커서로 만들어 놓으면 된다. 커서 이름은 memberCursor로 지정한다.

	 DECLARE memberCuror CURSOR FOR-- 커서 선언
        SELECT mem_number FROM member;

3. 반복 조건 선언하기

이제는 '행이 끝나면 어떻게 설정해야 더 이상 반복하지 않을까?'를 생각해 볼 차례이다. 커서가 행의 끝에 도착하면 앞에서 선언한 endOfRow 변수를 TRUE로 설정한다. 이렇게 설정한 내용은 반복하는 코드에서 활용된다.

DECLARE CONTINUE HANDLER는 반복 조건을 준비하는 예약어이다. 그리고 FOR NOT FOUND는 더 이상 행이 없을 때 이어진 문장을 수행한다. 즉, 행이 끝나면 endOfRow에 TRUE를 대입한다.

	DECLARE CONTINUE HANDLER -- 행의 끝이면 endOfRow 변수에 TRUE를 대입 
        FOR NOT FOUND SET endOfRow = TRUE;

4. 커서 열기

앞에서 준비한 커서를 OPEN으로 연다.

	OPEN memberCuror;  -- 커서 열기

5. 행 반복하기

커서의 끝까지 한 행씩 접근해서 반복할 차례이다. 코드의 형식은 아래와 같다.

cursor_loop: LOOP
	#반복되는 부분
END LOOP cursor_loop

cursor_loop는 반복할 부분의 이름을 지정한 것이다. 여기서는 커서를 이용한 실습이므로 알아보기 쉽도록 cursor_loop를 이름으로 지정했다. 그런데 이 코드는 무한으로 반복되는 코드이기 때문에 LEAVE를 이용해서 반복문을 빠져나가야 한다. 앞에서 행의 끝에 다다르면 endOfRow를 TRUE로 변경했다. 따라서 이를 이용하여 행의 끝에 다다르면 반복 조건을 선언한 3번에 의해서 endOfRow가 TRUE로 변경되고, 반복하는 부분을 빠져나간다.

	IF endOfRow THEN 
            LEAVE cursor_loop;
        END IF;

이제 반복할 부분을 표현해보자. FETCH는 한 행씩 읽어오는 것이다. 2번에서 커서를 선언할 때 인원수(mem_number) 행을 조회했으므로 memNumber 변수에는 각 회원의 인원수가 한 번에 하나씩 저장된다. SET 부분에서 읽은 행의 수(cnt)를 하나씩 증가시키고, 인원 수도 totNumber로 누적시켜 저장한다.

    cursor_loop: LOOP
        FETCH  memberCuror 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 '회원의 평균 인원 수';

6. 커서 닫기

모든 작업이 완료된 뒤에는 커서를 닫는다.

CLOSE memberCursor;
profile
여정이
0w0
이전 포스트

[혼공SQL] chapter 7. 스토어드 프로시저(1)

다음 포스트

[혼공SQL] chapter 7. 스토어드 프로시저(3)

0개의 댓글