[SQL] MYSQL 스토어드 프로시저 - 커서

✍️ Study Note

 

 

 

---

 

스토어드 프로시저 커서 생성

커서를 생성하여 반복 구간을 설정함으로서, 특정 작업을 행이 끝날 때까지 진행하도록 함.

 

 

쿼리 순서

1. 변수 선언
2. 커서 선언 (작업 진행할 열 선택)
3. 반복조건 설정 (행이 끝날때까지 진행하기 위해)
4. 커서 오픈 후, 반복구간 및 반복구간 종료시점 설정 
5. 진행할 작업 세팅 
6. 커서 닫기

 

 

예시 #1

DELIMITER $$
CREATE PROCEDURE CURSORPROC()
BEGIN
    DECLARE USERHEIGHT INT; -- 고객 키 (데이터)
    DECLARE CNT INT DEFAULT 0; -- 고객 수 (읽은 행의 수)
    DECLARE TOTALHEIGHT INT DEFAULT 0; -- 키의 합계
    
    DECLARE ENDOFROW BOOLEAN DEFAULT FALSE;
    
    DECLARE USERCUROR CURSOR FOR 		-- (1) 커서 선언
    	SELECT HEIGHT FROM USERTBL;
	
    DECLARE CONTINUE HANDLER FOR NOT FOUND 
    SET ENDOFROW = TRUE;			-- (2) 반복조건 선언, 행의 끝이 오면 TRUE로 변경됨
    
    OPEN USERCUROR;				-- (3) 커서 열기
    
    CURSOR_LOOP : LOOP				-- (4) 반복구간 지정
   	FETCH USERCUROR INTO USERHEIGHT;
        
   	IF ENDOFROW THEN LEAVE CURSOR_LOOP;	-- 읽을 행이 없으면 LOOP 종료
   	END IF;
        
    	SET CNT = CNT +1;			-- 읽을 행이 있다면, 다음 행 이동을 위해 +1
        SET TOTALHEIGHT = TOTALHEIGHT + USERHEIGHT; -- TOTAL 확인을 위해 조회한 키 더하기
	END LOOP CURSOR_LOOP;
    
    SELECT CONCAT('고객 키의 평균 ==>', (TOTALHEIGHT/CNT));
    CLOSE USERCUROR;				-- (5) 커서 닫기
END $$
DELIMITER ;

CALL CURSORPROC();

 

예시 #2

DELIMITER $$
CREATE PROCEDURE GRADEPROC()
BEGIN
    DECLARE ID VARCHAR(10);			-- 변수 선언
    DECLARE HAP BIGINT;
    DECLARE USERGRADE CHAR(5);
    
    DECLARE ENDOFROW BOOLEAN DEFAULT FLASE;
    
    DECLARE USERCUROR CURSOR FOR		-- (1) 커서 선언
	SELECT U.USERID, SUM(PRICE*AMOUNT)
        FROM BUYTBL B RIGHT JOIN USERTBL U
        ON B.USERID = U.USERID
        GROUP BY U.USERID, U.NAME;
        
    DECLARE CONTINUE HANDLER FOR NOT FOUND 	-- (2) 반복조건 선언, 행의 끝이 오면 TRUE로 변경되고 종료.
    SET ENDOFROW = TRUE;
    
    OPEN USERCUROR;				-- (3) 커서 열기
    GRADE_LOOP : LOOP				-- (4) 반복구간 지정
	FETCH USERCUROR INTO ID, HAP;		-- 위에서 선언한 커서 사용
	IF ENDOFROW THEN LEAVE GRADE_LOOP;	-- 읽을 행이 없으면 LOOP 종료
	END IF;
        
        CASE 					-- 읽을 행이 있다면, 조건 설정
	    WHEN (HAP >=1500) THEN SET 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; -- 확인한 고객등급을 테이블에 업데이트
	END LOOP GRADE_LOOP;

	CLOSE USERCUROR; -- (5) 커서 닫기
END $$
DELIMITER ;

CALL GRADEPROC();
SELECT * FROM USERTBL;