파트 8-2. 스토어드 프로그램 - 스토어드 함수, 커서

1. 스토어드 함수


mysql이 사용자가 원하는 모든 함수를 제공하진 않으므로 사용자가 직접 함수를 만들어서 사용할 필요가 있다. 이렇게 사용자가 직접 만들어서 사용하는 함수를 스토어드 함수라고 한다.

  • 스토어드 프로시저의 파라미터와 달리 in,out 등을 사용할 수 없다. 스토어드 함수의 파라미터는 모두 입력 파라미터로 사용된다.
  • 스토어드 함수는 returns문으로 반환할 값의 데이터 형식을 지정하고, 본문 안에서는 return 문으로 하나의 값을 반한해야 한다. 스토어드 프로시저는 별도의 반환하는 구문이 없으며, 꼭 필요하다면 여러 개의 out 파라미터를 사용해서 값을 반환한다.
  • 스토어드 프로시저는 call로 호출하지만, 스토어드 함수는 select 문장 안에서 호출된다.
  • 스토어드 프로시저 안에는 select문을 사용할 수 있지만, 스토어드 함수 안에서는 집합 결과를 반환하는 select를 사용할 수 없다.
  • 스토어드 프로시저는 여러 sql문이나 숫자 계산 등의 다양한 용도로 사용되지만, 스토어드 함수는 어떤 계산을 통해서 하나의 값을 반환하는데 주로 사용된다.
  • 스토어드 함수를 사용하기 위해서는 먼저 쿼리문으로 스토어드 함수 생성 권한을 허용해줘야 한다.
-- 형식
set global log_bin_trust_function_creators =1; -- 권한 허용

delimiter $$
create function 스토어드함수이름(파라미터)
    returns 반환형식
begin
    코딩
    return 반환값;
end $$
delimiter ;
select 스토어드함수이름();


-- 예시
drop function if exists getagefunc;
delimiter $$
create function userfunc(byear int)
    returns int
begin 
    declare age int;
    set age = year(curdate()) - byear; -- 현재날짜에서 연도만 받아서 - 매개 인자
    return age;
end $$
delimiter ;

select getagefunc(1979);
select getagefunc(1979) into @age1979; -- 반환값을 변수에 저장
select userid, name, getagefunc(birthyear) as '나이' from usertbl; -- 함수의 반환값을 열로 사용

drop function getagefunc; -- 삭제


2. 커서


스토어드 프로시저 내부에서 커서를 사용할 수 있다. 커서는 테이블에서 여러 개의 행을 쿼리한 후에, 쿼리의 결과인 행 집합을 한 행씩 처리하기 위한 방식이다.

그림1

그림과 같은 방식이 표준이 되는 커서 방식이다. 커서의 대부분의 순서는 다음과 같다. 커서끝을 확인하기 위한 변수 선언, 커서 선언, 반복조건선언, 커서 오픈, 루프문, 커서 닫기순이다. 예시를 한 번 보자.

-- 예시 1
DROP PROCEDURE IF EXISTS cursorProc;
DELIMITER $$
-- 프로시저 선언
CREATE PROCEDURE cursorProc()
BEGIN
    DECLARE userHeight INT; -- 고객의 키
    DECLARE cnt INT DEFAULT 0; -- 고객의 인원 수(=읽은 행의 수)
    DECLARE totalHeight INT DEFAULT 0; -- 키의 합계
    
    -- 행의 끝의 여부를 확인하기 위한 변수
    DECLARE endOfRow BOOLEAN DEFAULT FALSE; 

    -- 이 부분이 커서 선언인데 파이썬의 for문과 비슷하다.
    -- usertbl에서 뽑아온 모든 height행 usercuror에 들어있다고 보면 된다.
    -- 나중에 fetch로 행 하나씩 뽑아쓴다.
    -- declare 커서이름 커서타입 for 어디서가져올지
    DECLARE userCuror CURSOR FOR-- 커서 선언
        SELECT height FROM userTbl;

    -- 반복 조건
    -- 커서에 이제 들어갈게 없어서 끝에 도달하면 true로 바꿔주는 구문    
    DECLARE CONTINUE HANDLER -- 행의 끝이면 endOfRow 변수에 TRUE를 대입 
        FOR NOT FOUND SET endOfRow = TRUE;
    
    -- 여기 위에까지가 커서를 사용하기 위한 선언, 조건들을 작성한 것


    OPEN userCuror;  -- 커서 열기

    cursor_loop: LOOP
        -- 여기서도 파이썬의 for문과 비슷하다 약간 위에 비슷하다고 한 것과 같이보면
        -- 파이썬의 for문을 나눠논 것 같은 느낌이다.
        -- usercuror에 있는 height하나를 뽑아내서 userheight에 대입
        -- 만약 더이상 뽑아내지 못하면 위에서 적은대로 endofrow 를 true로 수정한다.
        FETCH  userCuror INTO userHeight; -- 고객 키 1개를 대입
        
        IF endOfRow THEN -- 더이상 읽을 행이 없으면 Loop를 종료
            LEAVE cursor_loop;
        END IF;

        SET cnt = cnt + 1;
        SET totalHeight = totalHeight + userHeight;        
    END LOOP cursor_loop;
    
    -- 고객 키의 평균을 출력한다.
    SELECT CONCAT('고객 키의 평균 ==> ', (totalHeight/cnt));
    
    CLOSE userCuror;  -- 커서 닫기
END $$
DELIMITER ;

CALL cursorProc();


-- 예시 2 커서를 이용해 열로 고객의 등급을 추가
ALTER TABLE userTbl ADD grade VARCHAR(5);  -- '고객등급' 열 만들기

DROP PROCEDURE IF EXISTS gradeProc;
DELIMITER $$
-- 프로시저 선언
CREATE PROCEDURE gradeProc()
BEGIN
    DECLARE id VARCHAR(10); -- 사용자 아이디를 저장할 변수
    DECLARE hap BIGINT; -- 총 구매액을 저장할 변수
    DECLARE userGrade CHAR(5); -- 고객 등급 변수
    
    -- 끝을 표시할 변수 선언
    DECLARE endOfRow BOOLEAN DEFAULT FALSE; 

    DECLARE userCuror CURSOR FOR-- 커서 선언
        SELECT U.userid, sum(price*amount)
            FROM buyTbl B
                RIGHT OUTER JOIN userTbl U
                ON B.userid = U.userid
            GROUP BY U.userid, U.name ;

    -- 더이상 뽑아낼 행이 없다면 endofrow = ture
    DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET endOfRow = TRUE;
    
    OPEN userCuror;  -- 커서 열기
    grade_loop: LOOP
        -- usercuror에서 행 하나 뽑아서 id,hap에 대입
        FETCH  userCuror INTO id, hap; 

        IF endOfRow THEN
            LEAVE grade_loop;
        END IF;

        -- 케이스에 따라 usergrade 대입
        CASE  
            WHEN (hap >= 1500) THEN SET userGrade = '최우수고객';
            WHEN (hap  >= 1000) THEN SET userGrade ='우수고객';
            WHEN (hap >= 1) THEN SET userGrade ='일반고객';
            ELSE SET userGrade ='유령고객';
         END CASE;
        -- usergrade 업데이트
        UPDATE userTbl SET grade = userGrade WHERE userID = id;
    END LOOP grade_loop;
    
    CLOSE userCuror;  -- 커서 닫기
END $$
DELIMITER ;

CALL gradeProc(); -- 프로시저 호출
SELECT * FROM userTBL; -- 잘 업데이트 됬는지 확인



본 포스팅은 ‘이것이 MySQL이다’를 읽고 공부한 내용을 바탕으로 작성하였습니다.


© 2021. By Backtony