[SQL] 스토어드 프로시저, 스토어드 함수, 커서

스토어드 프로시저

스토어드 프로시저(저장 프로시저)란 MySQL에서 제공하는 프로그래밍 기능이다.

스토어드 프로시저는 쿼리 문의 집합으로도 볼 수있으며, 어떠한 동작을 일괄 처리하기 위한 용도로도 사용한다.

자주 사용하는 쿼리를 반복하기보다는 스토어드 프로시저로 묶어 놓고, 필요할 때마다 MySQL을 운영할 수있다.

가장 많이 사용하는 필수적인 형식

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름 (IN 또는 OUT 매개변수)
BEGIN

    -- 이 부분에 SQL 프로그래밍할 코드 작성
    
END $$
DELIMITER ;

$$ 는 $ 하나로 바꿔줘도 되지만 명확하게 표기하기 위해서 2개를 사용한다. (##, %%, // 등으로 교체가능)

스토어드 프로시저를 호출하는 형식

CALL 스토어드_프로시저_이름();

필요하다면 괄호 안에 매개변수를 넣어서 사용할 수 있다.

예시

DELIMITER $$
CREATE PROCEDURE user_proc()
BEGIN 
    SELECT * FROM member;
END $$
DELIMITER ;

CALL user_proc();

스토어드 프로시저의 삭제

주의할 점은 CREATE PROCEDURE에서는 스토어드 프로시저 이름 뒤에 괄호를 붙이지만,
DROP PROCEDURE 에서는 괄호를 붙이지 않아야 한다.

DROP PROCEDURE 스토어드_프로시저_이름;

예시

DROP PROCEDURE user_proc;

매개변수의 사용

스토어드 프로시저에서는 실행 시 입력 매개변수를 지정할 수 있다.

입력 매개변수를 지정하는 형식

IN 입력_매개변수_이름 데이터_형식

입력 매개변수가 있는 스토어드 프로시저를 실행하기 위한 형식

CALL 스토어드_프로시저(전달_값);

출력 매개변수의 형식

OUT 출력_매개변수_이름 데이터_형식

출력 매개변수의 값을 대입하기 위해서는 주로 SELECT ~ INTO 문을 사용한다.

출력 매개변수가 있는 스토어드 프로시저를 실행하는 형식

CALL 프로시저_이름(@변수명);
SELECT @변수명;

입력 매개변수의 예시

DELIMITER $$
CREATE PROCEDURE user_proc1(IN userName VARCHAR(10))
BEGIN
    SELECT * FROM member WHERE mem_name = userName;
END $$
DELIMITER ;

CALL user_proc1('에이핑크');

DELIMITER $$
CREATE PROCEDURE user_proc2(
    IN userNumber INT,
    IN userHeight INT
)
BEGIN
    SELECT * FROM member
        WHERE mem_number > userNumber AND height > userHeight;
END $$
DELIMITER ;

CALL user_proc2(6, 165);

출력 매개변수의 예시

noTable

CREATE TABLE IF NOT EXISTS noTable(
    id INT AUTO_INCREMENT PRIMARY KEY,
    txt CHAR(10)
    );

DELIMITER $$
CREATE PROCEDURE user_proc3(
    IN txtValue CHAR(10),
    OUT outValue INT
    )
BEGIN
    INSERT INTO noTable VALUES (NULL, txtValue);
    SELECT MAX(id) INTO outValue FROM noTable; -- 방금 입력한 행의 순차 번호
END $$
DELIMITER ;

출력 매개변수인 outValue를 지정했다.
INTO outValue 구문으로 outValue 에 id 열의 최대값을 저장했다.
스토어드 프로시저를 만드는 시점에는 아직 존재하지 않는 테이블을 사용해도 된다.
CALL로 실행하는 시점에는 사용한 테이블이 있어야 한다.

CALL user_proc3('테스트1', @myValue);
SELECT CONCAT('입력된 ID 값 ==>', @myValue);

IF ~ ELSE 문 활용

DELIMITER $$
CREATE PROCEDURE ifelse_proc(
    IN memName VARCHAR(10)
)
BEGIN 
    DECLARE debutYear INT; -- 변수 선언
    SELECT YEAR(debut_date) INTO debutYear FROM member
        WHERE mem_name = memName;
	IF (debutYear >= 2015) THEN
        SELECT '신인 가수네요. 화이팅하세요.' AS '메시지';
	ELSE 
        SELECT '고참 가수네요. 그동안 수고하셨어요.' AS '메시지';
	END IF;
END $$
DELIMITER ;

CALL ifelse_proc('오마이걸');

WHILE 문 활용

DELIMITER $$
CREATE PROCEDURE while_proc()
BEGIN
    DECLARE hap INT; -- 합계
    DECLARE num INT; -- 1 부터 100까지 증가
    SET hap = 0;
    SET num = 1;
    
    WHILE (num <= 100) DO -- 100까지 반복
        SET hap = hap + num;
        SET num = num + 1; -- 숫자 증가
    END WHILE;
    SELECT hap AS '1~100 합계';
END $$
DELIMITER ;

CALL while_proc();

동적 SQL 활용

DELIMITER $$
CREATE PROCEDURE dynamic_proc(
    IN tableName VARCHAR(20)
)
BEGIN
    SET @sqlQuery = CONCAT('SELECT * FROM ' , tableName);
    PREPARE myQuery FROM @sqlQuery;
    EXECUTE myQuery;
    DEALLOCATE PREPARE myQuery; -- myQuery 해제
END $$
DELIMITER ;

CALL dynamic_proc('member');

스토어드 함수

스토어드 함수

MySQL은 SUM( ), CONCAT( ), CURRENT_DATE( ) 등 다양한 함수를 제공한다.
하지만 MySQL이 사용자가 원하는 모든 함수를 제공하는 것은 아니다.
필요하다면 사용자가 직접 함수를 만들어서 사용할 수 있다.
이렇게 직접 만들어서 사용하는 함수를 스토어드 함수라고 한다.

스토어드 함수 형식

DELIMITER $$
CREATE FUNCTION 스토어드_함수_이름(매개변수)
    RETURNS 반환형식
BEGIN 

    이 부분에 프로그래밍 코딩
    RETURN 반환 값;
    
END $$
DELIMITER ;
SELECT 스토어드_함수_이름();

스토어드 프로시저와의 차이점

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

스토어드 함수의 사용

스토어드 함수를 사용하기 위해서는 먼저 다음 SQL 로 스토어드 함수 생성 권한을 허용해줘야 한다.
MySQL 에서 한 번만 설정해주면 이후에는 신경쓰지 않아도 된다.

SET_GLOBAL log_bin_trust_function_creators = 1;

예시

DELIMITER $$
CREATE FUNCTION sumFunc(number1 INT, number2 INT)
    RETURNS INT
BEGIN
    RETURN number1 + number2;
END $$
DELIMITER ;

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

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 '활동 햇수';
SELECT mem_id, mem_name, calcYearFunc(YEAR(debut_date)) AS '활동 햇수' FROM member;

스토어드 함수의 내용 확인

SHOW CREATE FUNCTION 함수_이름;

함수의 삭제

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

커서로 한 행씩 처리

커서

커서(cursor)는 테이블에서 한 행씩 처리하기 위한 방식이다.
커서는 첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리한다.

커서의 일반적인 작동 순서

커서 단계별 구현

1. 사용할 변수 준비하기

각 회원의 인원수: memNumber
전체 인원의 합계: totNumber
읽은 행의 수: cnt
DEFAULT 문을 사용해서 초기값 0으로 설정

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

행의 끝을 파악 하기 위한 변수: endOfRow

DECLARE endOfRow BOOLEAN DEFAULT FALSE;

2. 커서 선언하기

커서라는 것은 결국 SELECT 문
커서 이름은 memberCursor

DECLARE memberCursor CURSOR FOR
    SELECT mem_number FROM member;

3. 반복 조건 선언

행의 끝에 다다르면 앞에서 선언한 endOfRow 변수를 TURE 로 지정
DECLARE CONTINUE HANDLER는 반복 조건을 준비하는 예약어 이다.
FOR NOT FOUND는 더 이상 행이 없을 때 이어진 문장을 수행한다.
즉, 행이 끝나면 endOfRow에 TRUE를 대입한다.

DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET endOfRow = TRUE;

4. 커서 열기

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

OPEN memberCursor;

5. 행 반복하기

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

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

cursor_loop는 반복할 부분의 이름을 지정한 것
LEAVE는 반복할 이름을 빠져나간다.
행의 끝에 다다르면 반복 조건을 선언한 3번에 의해서 endOfRow가 TRUE로 변경되고 반복하는 부분을 빠져나감.

IF endOfRow THEN
    LEAVE cursor_loop;
END IF;

FETCH는 한 행씩 읽어오는 것이다.

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 '회원의 평균 인원 수';

6. 커서 닫기

CLOSE memberCursor;

커서의 통합 코드

DELIMITER $$
CREATE PROCEDURE cursor_proc()
BEGIN
    DECLARE memNumber INT;
    DECLARE cnt INT DEFAULT 0;
    DECLARE totNumber 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 ;

CALL cursor_proc();

해당 포스팅의 글과 내용은 우재남 저자님의 혼자 공부하는 SQL 책을 재구성하여 만들었습니다.

좀 더 자세한 내용이 필요하시다면 '혼자 공부하는 SQL 책'을 구매해주세요

우재남 저자님 카페: https://cafe.naver.com/thisisMySQL

이것이MySQL이다 : 네이버 카페

한빛미디어 [이것이 MySQL이다] 카페입니다.

cafe.naver.com

'DB' 카테고리의 다른 글

[DB] 데이터베이스 설계 (0)	2024.11.17
[SQL] 트리거 (0)	2024.06.08
[SQL] 인덱스 (0)	2024.06.05
[SQL] 뷰 (1)	2024.06.03
[SQL] 테이블 제약조건 (0)	2024.06.02

쌈뽕코딩

[SQL] 스토어드 프로시저, 스토어드 함수, 커서

목차

스토어드 프로시저

매개변수의 사용

스토어드 함수

커서로 한 행씩 처리

'DB' 카테고리의 다른 글

티스토리툴바

[SQL] 스토어드 프로시저, 스토어드 함수, 커서

목차

스토어드 프로시저

매개변수의 사용

스토어드 함수

커서로 한 행씩 처리

'DB' 카테고리의 다른 글

'DB' Related Articles

티스토리툴바