반응형
[MariaDB] 마리아DB 스토어드 프로그램 - 커서 Cursor (MySQL)
스토어드 프로그램 종류는 4가지 입니다.
- 스토어드 프로시저 Stored Procedure
- 스토어드 함수 Stored Function
- 커서 Cursor
- 트리거 Trigger
이번 포스팅에서는 3번째인 커서(Cursor)에 대해 설명하겠습니다.
커서는 스토어드프로시저 내부에서 사용됩니다. 커서는 프로그래밍 언어의 파일처리 방법처럼 파일을 열고, 한 행씩 데이터를 처리합니다.
커서의 처리순서는 일반적으로 아래의 순서와 같이 처리됩니다.
- 커서의 선언 : DECLARE CURSOR
- 반복 조건 선언 : DECLARE CONTINUE HANDLER (더이상 읽을 행이 없을 경우에, 실행할 내용 설정)
- 커서 열기 : OPEN
- 커서에서 데이터 가져오기 : FETCH ( LOOP~END, LOOP문으로 반복 구간 지정 시작)
- 데이터 처리 (LOOP문으로 반복구간 지정 끝)
- 커서 닫기 : 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' 카테고리의 글 목록
전산 관련 경험을 기록 하는 곳
reddb.tistory.com
반응형
'MariaDB' 카테고리의 다른 글
| [MariaDB] 마리아DB 스토어드 프로그램 - 트리거 Trigger (MySQL) (0) | 2020.10.18 |
|---|---|
| [MariaDB] 마리아DB 스토어드 프로그램 - 스토어드 함수 Stored Function (MySQL) (0) | 2020.10.13 |
| [MariaDB] 마리아DB 스토어드 프로그램 - 스토어드 프로시저 Stored Procedure (MySQL) (0) | 2020.10.13 |