[MySQL] 커서(Cursor) 예제

#. 커서(Cursor)

 

- 프로시저 내부에서 복수 행을 처리할 때 사용하는 구성 요소

- 파일 포인터가 열린 파일에서의 현재 위치를 표시하는 것처럼, 커서는 결과 집합에서 현재 위치를 표시

- 쿼리의 행 집합에서 한 행씩 옮겨가며 명령을 처리함.

- OPEN, FETCH, CLOSE 문을 사용하여 커서를 제어

- 커서의 작동 순서

① 커서 선언(DECLARE)

② 반복 조건 선언(DECLARE, HANDLER)

③ 커서 열기(OPEN)

④ 커서에서 데이터 가져오기(FETCH)

⑤ 데이터 처리

⑥ 커서 닫기(CLOSE)

 

 

#. 실습

 

1. 테이블 확인

 

mysql> SELECT * FROM EMP;

 

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-03-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1982-12-09 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1983-01-12 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1200.00 |    NULL |     10 |
|  9001 | BROWN  | CLERK     | 7902 | 2020-01-30 | 1000.00 |    NULL |     20 |
|  9002 | NIKO   | ANALYST   | 7782 | 2020-11-05 | 4500.00 |    NULL |     20 |
+-------+--------+-----------+------+------------+---------+---------+--------+
16 rows in set (0.00 sec)

 

728x90

 

 

2. 프로시저 만들기 (커서 이용)

 

DELIMITER $$
CREATE PROCEDURE info_hiredate(IN p_year CHAR(5))
BEGIN
    DECLARE cnt INT DEFAULT 0;
    DECLARE empName VARCHAR(10);
    DECLARE endOfRow BOOLEAN DEFAULT FALSE;   /* 행의 끝 여부 */
    DECLARE userCursor CURSOR FOR
        SELECT ename
        FROM emp 
        WHERE YEAR(HIREDATE) = p_year;   /* 커서 선언*/
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET endOfRow = TRUE;  /* 행의 끝일 때 Handler 정의 */
    OPEN userCursor;   /* 커서 열기 */
    cloop: WHILE TRUE DO
        FETCH userCursor INTO empName;   /* 읽은 행 empName에 대입 */
        IF endOfRow THEN   /* 더 이상 읽을 행이 없으면 반복문 종료 */
            LEAVE cloop;
        END IF;
        SET cnt = cnt + 1;
        SELECT CONCAT(cnt, '번', empNAME);
    END WHILE;
    CLOSE userCursor; /* 커서 닫기 */
END $$
DELIMITER ;

 

 

3. 프로시저 실행

 

mysql> CALL info_hiredate(1982);

 

+----------------------------+
| CONCAT(cnt, '번', empNAME) |
+----------------------------+
| 1번SCOTT                   |
+----------------------------+
1 row in set (0.00 sec)
 
+----------------------------+
| CONCAT(cnt, '번', empNAME) |
+----------------------------+
| 2번MILLER                  |
+----------------------------+
1 row in set (0.01 sec)
 
Query OK, 0 rows affected (0.01 sec)

 

 

4. (참고) 프로시저 삭제

 

mysql> DROP PROCEDURE info_hiredate;