커서

MySQL에서 커서(cursor)는 SELECT 문에서 검색된 결과 집합을 처리하는 데 사용됩니다. 커서는 데이터 집합 내의 개별 행을 가리키는 작은 포인터 역할을 합니다.

커서는 다음과 같은 순서로 사용됩니다.

1. 커서를 선언합니다. (DECLARE CURSOR)
2. 반복 조건 선언 (DECLARE CONTINUE HANDLER)
3. 커서를 열고, 데이터를 검색합니다. (OPEN)
4. 커서를 이용해 데이터를 처리합니다.(LOOP ~ END LOOP 반복)
5. 커서를 닫습니다.(CLOSE)

왜 커서를 사용하나요?

MySQL에서 커서는 결과 집합을 한 번에 가져오지 않고 한 행씩 처리할 때 사용됩니다.

일반적으로 MySQL에서 데이터를 처리할 때는 SELECT 문을 사용하여 데이터를 가져와서 PHP, Python, Java 또는 다른 프로그래밍 언어로 처리합니다. 이때 가져온 데이터는 MySQL 서버에서 한 번에 모두 처리되어야 하므로 메모리에 많은 양의 데이터가 필요합니다.

하지만 데이터 양이 많을 때는 이러한 방식으로 처리할 경우 메모리 부족으로 인한 성능 저하 및 서버 다운 등의 문제가 발생할 수 있습니다.

커서는 이러한 문제를 해결하기 위해 MySQL 서버에서 한 번에 한 행씩 데이터를 가져와서 프로그램에서 처리하도록 합니다. 이렇게 하면 가져온 데이터의 양이 많아도 메모리 사용량이 적어지므로 성능 저하나 서버 다운 등의 문제를 방지할 수 있습니다.

또한 커서는 데이터를 순회하면서 필요한 데이터만 가져오는 것이 가능해지므로 성능 최적화에도 도움이 됩니다. 이를 통해 불필요한 데이터를 가져오지 않고 필요한 데이터만 처리할 수 있습니다.

따라서, MySQL에서 커서를 사용하면 메모리 사용량을 줄이고 성능을 최적화할 수 있으며, 대용량 데이터 처리에도 유용합니다.

---

실습

✔️고객의 평균키를 구하는 스토어드 프로시저 작성

use sqlDB;
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; -- 행의 끝 여부 기본 false
    DECLARE userCuror CURSOR FOR-- userTbl에서 height컬럼을 선택해 커서 선언
		SELECT height FROM userTbl; -- 집합결과가 userCuror에 들어감
	
    DECLARE CONTINUE HANDLER -- 행이 끝이면 변수에 true 할당
		FOR NOT FOUND SET endOfRow = TRUE;
        
	OPEN userCuror;-- 커서 열기..
    
    cursor_loop: LOOP
        FETCH  userCuror INTO userHeight; -- 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();

---

✔️아래 테이블에 고객등급 컬럼을 추가하고 커서를 통해 등급을 나누는 작업을 하는 실습

select * from usertbl;

✔️고객 등급 열 추가

-- 고객등급 열 추가
ALTER TABLE userTbl ADD grade VARCHAR(5);
select * from usertbl;

✔️먼저 고객별 구매한 가격을 확인하는 쿼리를 작성

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;

이 값을 매개변수로 받아 커서로 1행씩 반복 작업을 수행할 예정

✔️ 스토어드 프로시저 작성

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; -- 기본값 false변수 선언
    
    DECLARE userCuror CURSOR FOR -- 고객별 구매급액으로 묶은 값을 userCuror에 할당
		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;
            
	DECLARE CONTINUE HANDLER FOR -- 값이 없으면 모두 순회한 것이니까 endOfRow = true
		NOT FOUND SET endOfRow = true;
        
	OPEN userCuror; -- 커서 열고
    grade_loop:loop
		fetch userCuror INTO id, hap; -- id,hap을 한 줄씩 입력받는다.
       
		IF endOfRow THEN -- 마지막 줄이면 루프 떠남
			LEAVE grade_loop;
		END IF ;
        
        CASE -- 유저의 등급을 정하고 userGrade 변수에 할당한다.
			WHEN (hap >= 1500) THEN SET userGrade = '최우수고객';
            WHEN (hap >= 1000) THEN SET userGrade = '우수고객';
            WHEN (hap >= 1) THEN SET userGrade = '일반고객';
            ELSE SET userGrade = '유령고객';
		END CASE;
        
        -- 할당한 변수를 같은 아이디를 가진 데이터의 grade에 할당시킨다.
        UPDATE	userTbl SET grade = userGrade WHERE userID = id;
	END LOOP grade_loop;
    close userCuror;
END $$
DELIMITER ; 
CALL gradeProc();
select * from userTBL;