[MariaDB] 마리아DB 스토어드 프로그램 - 커서 Cursor (MySQL)

[MariaDB] 마리아DB 스토어드 프로그램 - 커서 Cursor (MySQL)

 

스토어드 프로그램 종류는 4가지 입니다.

1. 스토어드 프로시저 Stored Procedure
2. 스토어드 함수 Stored Function
3. 커서 Cursor
4. 트리거 Trigger

 

이번 포스팅에서는 3번째인 커서(Cursor)에 대해 설명하겠습니다.

커서는 스토어드프로시저 내부에서 사용됩니다. 커서는 프로그래밍 언어의 파일처리 방법처럼 파일을 열고, 한 행씩 데이터를 처리합니다.

 

커서의 처리순서는 일반적으로 아래의 순서와 같이 처리됩니다.

1. 커서의 선언 : DECLARE CURSOR
2. 반복 조건 선언 : DECLARE CONTINUE HANDLER (더이상 읽을 행이 없을 경우에, 실행할 내용 설정)
3. 커서 열기 : OPEN
4. 커서에서 데이터 가져오기 : FETCH ( LOOP~END, LOOP문으로 반복 구간 지정 시작)
5. 데이터 처리 (LOOP문으로 반복구간 지정 끝)
   
6. 커서 닫기 : CLOSE

커서를 활용하여 프로시저에서 고객 평균 키 계산 예제

DROP PROCEDURE IF EXISTS cursorProc;	-- cursorProc 프로시저가 존재한다면 삭제
DELIMITER $$	-- 스토어드 프로그램 시작
CREATE PROCEDURE cursorProc()	- cursorProc 프로시저 생성
BEGIN	-- 프로시저 코드 시작
    DECLARE userHeight INT; -- 변수 userHeight INT형 선언. 고객의 키
    DECLARE cnt INT DEFAULT 0; -- 변수 cnt INT형 기본값 0으로 선언. 고객의 인원 수(=읽은 행의 수)
    DECLARE totalHeight INT DEFAULT 0; -- 변수 totlaHeight INT형 기본값 0으로 선언. 키의 합계

    DECLARE endOfRow BOOLEAN DEFAULT FALSE; -- 변수 endOfRow 선언. 행의 끝 여부(기본을 FALSE)

    DECLARE userCuror CURSOR FOR	-- 아래줄 셀렉트를 userCuror 커서로 선언
        SELECT height FROM userTBL;	-- userTBL 테이블에서 키만 선택해서 윗줄 userCuror에

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

    OPEN userCuror;  -- 커서 열기

    cursor_loop: LOOP	-- 루프할 명칭: LOOP 시작
        FETCH  userCuror INTO userHeight; -- 11번 줄에서 선언한 userCuror(키)값을 userHeight 변수에 입력. 고객 키 1개를 대입

        IF endOfRow THEN -- 더이상 읽을 행이 없으면 Loop를 종료
            LEAVE cursor_loop;	-- 루프를 떠남
        END IF;

        SET cnt = cnt + 1;	-- cnt를 1증가
        SET totalHeight = totalHeight + userHeight;        	= totalHeight 변수에 합산
    END LOOP cursor_loop;	-- 루프 끝나고 cursor_loop 시작점으로 다시 올라감
    
    -- 고객 키의 평균을 출력한다.
    SELECT CONCAT('고객 키의 평균 ==> ', (totalHeight/cnt));

    CLOSE userCuror;  -- 커서 닫기
END $$	-- 프로시저 코드 끝
DELIMITER ;	-- 스토어드 프로그램 끝

CALL cursorProc();	-- 프로시저 cursorProc를 호출

 

고객 테이블의 구매 총액에 따라 구매등급 추가 예제

USE sqlDB;	-- sqlDB 사용
ALTER TABLE userTBL ADD grade VARCHAR(5);  -- 고객 등급 열 grade 추가


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

    DECLARE userCuror CURSOR FOR-- 아래 셀렉트로 조회된 userid, sum값을 userCuror 커서로 선언
        SELECT U.userid, sum(price*amount)	-- userTBL의 userid, sum값을 윗줄 userCuror로
            FROM buyTBL B
                RIGHT OUTER JOIN userTBL U
                ON B.userid = U.userid
            GROUP BY U.userid, U.name ;

    DECLARE CONTINUE HANDLER 	-- 행의 끝까지 가서 더이상 찾을수 없을때.(NOT FOUND)
        FOR NOT FOUND SET endOfRow = TRUE;	-- 변수 endOfRow 값을 TRUE로 대입

    OPEN userCuror;  -- 커서 열기
    grade_loop: LOOP	-- 루프명 : LOOP 시작
        FETCH  userCuror INTO id, hap; -- 15행에서 선언한 userid, sum의 첫 행 값을 id, hap에 대입
        IF endOfRow THEN	-- 행이 끝나는 경우 endOfRow값이 트루가 됨
            LEAVE grade_loop;	-- 루프명 루프를 떠남
        END IF;

        CASE  	-- 케이스문
            WHEN (hap >= 1500) THEN SET userGrade = '최우수고객';	-- userCuror의 sum값에서 대입받은 hap값에 금액에 따라 userGrade 변수에 값을 대입
            WHEN (hap  >= 1000) THEN SET userGrade ='우수고객';
            WHEN (hap >= 1) THEN SET userGrade ='일반고객';
            ELSE SET userGrade ='유령고객';
         END CASE;

        UPDATE userTBL SET grade = userGrade WHERE userID = id;	-- userTBL의 테이블에서 id로 검색해서 위에서 선언한 userGrade값을 grade에 대입
    END LOOP grade_loop;	-- 루프 끝. 루프명(grade_loop) 시작점으로 이동

    CLOSE userCuror;  -- 커서 닫기
END $$
DELIMITER ;

CALL gradeProc();	-- gradeProc 프로시저 호출
SELECT * FROM userTBL;	-- userTBL 테이블 셀렉트하면 사용자의 등급이 추가되어 있음

 

 

[MariaDB] 전체보기

 

'MariaDB' 카테고리의 글 목록