[DBP] CH3-3. PL/SQL - Cursor

🌳 Cursor

SQL 처리 결과가 저장된 작업 영역에 이름을 지정하고, 저장된 정보에 접근할 수 있게 함
SQL 명령을 실행시키면 서버는 명령을 parse하고 실행하기 위한 메모리 영역을 open하는데, 이 영역을 cursor라고 부름

커서의 종류
- 암시적 커서 (Implicit Cursor)
  - 모든 DML과 PL/SQL SELECT 문에 암시적으로 PL/SQL이 선언
- 명시적 커서 (Explicit Cursor)
  - 프로그래머가 선언하고 명령하며 블록의 실행 가능한 부분에서 특정 명령을 통해 조작

🌳 암시적 커서

SQL 문장이 처리되는 곳에 대한 익명의 주소
오라클 DB에서 실행되는 모든 SQL 문장은 암시적인 커서
SQL문이 실행되는 순간 자동으로 열림과 닫힘 실행

암시적 커서 속성

속성	설명
SQL%ROWCOUNT	해당 SQL문에 영향을 받는 행의 수
SQL%FOUND	해당 SQL문의 영향을 받는 행의 수가 1개 이상일 경우 TRUE
SQL%NOTFOUND	해당 SQL문에 영향을 받는 행의 수가 없을 경우 TRUE
SQL%ISOPEN	암시적 커서가 열려있는지 여부 검색

예시

DECLARE
    v_sal emp.sal%TYPE;
    v_update_row NUMBER;
    v_empno emp.empno%TYPE;
BEGIN
    v_empno := 7900;

    SELECT sal
    INTO v_sal
    FROM emp
    WHERE empno = v_empno;

    IF SQL%FOUND THEN         -- SELECT문에 영향을 받는 행이 있으면 True 
        DBMS_OUTPUT.PUT_LINE('데이터 존재: '||v_sal);
    END IF;

    UPDATE emp
    SET sal = sal*1.1
    WHERE empno = v_empno;

    v_update_row := SQL%ROWCOUNT;      -- UPDATE문에 영향을 받는 행의 수

    DBMS_OUTPUT.PUT_LINE('급여인상 사원수: '||v_update_row);
END;

🌳 명시적 커서

IMG_B1689B7E3972-1

DECLARE: 이름이 있는 SQL 영역 생성
OPEN: 커서 활성화
FETCH: 커서의 현재 데이터 행을 해당 변수에 넘김
EMPTY: 현재 데이터 행의 존재 여부 검사, 레코드가 없으면 FETCH 하지 않음
CLOSE: 커서가 사용한 자원 해제

명시적 커서 선언과 처리

커서 선언

DECLARE
  CURSOR cursor_name IS
      SELECT문;

커서 연결
```
OPEN cursor_namel
```
- 커서 안의 검색이 실행
- 검색시 아무런 데이터 행을 추출하지 못할 경우에는 예외 발생

커서로부터의 데이터 패치
```
FETCH cursor_name INTO variable1, variable2, ...;
```
- 현재 데이터 행을 OUTPUT 변수에 리턴
- 한 라인씩 데이터 패치
- 주의
  - 커서의 SELECT문의 컬럼의 수와 OUTPUT 변수의 수가 동일해야 함
  - 커서 컬럼의 변수의 타입과 OUTPUT 변수의 데이터 타입이 동일해야 함

커서 닫기
```
CLOSE cursor_name;
```
- 사용을 끝낸 커서는 반드시 닫아주어야 함
- 필요하다면 커서를 OPEN을 통해 다시 열 수 있음
- 커서를 닫은 상태에서 패치할 수 없음

예시

DECLARE
    CURSOR dept_avg IS
        SELECT b.dname, COUNT(a.empno) cnt, AVG(a.sal)
        FROM emp a, dept b
        WHERE a.deptno = b.deptno AND b.deptno = 20
        GROUP BY b.dname;

    v_dname dept.dname%TYPE;
    emp_cnt NUMBER;
    sal_avg NUMBER;
BEGIN
    OPEN dept_avg;

    FETCH dept_avg INTO v_dname, emp_cnt, sal_avg;

    DBMS_OUTPUT.PUT_LINE('부서명: '||v_dname);
    DBMS_OUTPUT.PUT_LINE('사원수: '||emp_cnt);
    DBMS_OUTPUT.PUT_LINE('급여평균: '||sal_avg);

    CLOSE dept_avg;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM||' 에러 발생');
END;

명시적 커서 속성

속성	설명
cursor_name%ROWCOUNT	현재까지 반환된 모든 데이터 행의 수
cursor_name%FOUND	Fetch한 데이터가 행을 리턴하면 TRUE
cursor_name%NOTFOUND	Fetch한 데이터가 행을 리턴하지 않으면 TRUE
cursor_name%ISOPEN	커서가 열려있으면 TRUE

예시

DECLARE
    v_empno emp.empno%TYPE;
    v_ename emp.ename%TYPE;
    v_sal emp.sal%TYPE;

    CURSOR emp_list IS
        SELECT empno, ename, sal
        FROM emp;
BEGIN
    OPEN emp_list;

    LOOP
        FETCH emp_list INTO v_empno, v_ename, v_sal;
        EXIT WHEN emp_list%NOTFOUND;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('전체데이터 수: '||emp_list%ROWCOUNT);

    CLOSE emp_list;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERR MESSAGE: '||SQLERRM);
END;

🌳 FOR문에서 커서 사용

특징

FOR문을 사용하면 커서의 OPEN, FETCH, CLOSE가 자동 발생 - 따로 기술할 필요 X
레코드 (이름)타입이 자동 선언 - 따로 선언할 필요 X
즉, 레코드는 커서가 fetch하는 레코드의 %RECORDTYPE을 데이터 타입으로 가짐

형식

FOR recored_name IN cursor_name LOOT
    statement1;
END LOOP;

- cursor의 행들에 대해 반복!

예시

DECLARE
    CURSOR dept_sum IS
        SELECT b.dname, COUNT(a.empno) cnt
        FROM emp a, dept b
        WHERE a.deptno = b.deptno
        GROUP BY b.dname;
BEGIN
    FOR emp_list IN dept_sum LOOP
        DBMS_OUTPUT.PUT_LINE('부서명: '||emp_list.dname);
        DBMS_OUTPUT.PUT_LINE('사원수: '||emp_list.cnt);
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생');
END;

🌳 파라미터가 있는 커서

특징

커서가 OPEN되고 질의가 실행되면 파라미터 값을 커서에 전달

형식

CURSOR cursor_name [(parameter_name datatype, ...)] IS
    SELECT statement;

예시

DECLARE
    CURSOR emp_list (v_deptno emp.deptno%TYPE) IS
        SELECT ename
        FROM emp
        WHERE deptno = v_deptno;
BEGIN
    DBMS_OUTPUT.PUT_LINE('** 입력한 부서 사람들 **');

    -- Parameter 변수의 값을 전달 (OPEN될 때 값 전달)
    FOR emplst IN emp_list(20) LOOP          -- 20을 파라미터에 전달 / 부서 번호가 20번인 사람들
        DBMS_OUTPUT.PUT_LINE('이름: '||emplst.ename);
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERR MESSAGE: '||SQLERRM);
END;

🌳 WHERE CURRENT OF

특징

ROWID를 이용하지 않고도 현재 참조하는 행을 갱신하고 삭제할 수 있게 함
FETCH문에 의해 가장 최근에 처리된 행을 참조하기 위해 WHERE CURRENT OF 커서이름 절로 DELETE나 UPDATE문 작성 가능
주의
- 이 절을 사용할 때 참조하는 커서가 있어야 함
- FOR UPDATE절이 커서 선언 질의문 안에 있어야 함 (없으면 에러 발생)

예시

DECLARE
    CURSOR emp_list IS
        SELECT empno
        FROM emp
        WHERE empno = 7934
        FOR UPDATE;
BEGIN
    FOR emplst IN emp_list LOOP
        -- emp_list 커서에 해당하는 사람의 직업 수정 (행 번호 알 수 없을 때 현재 참조하는 행!)
        UPDATE emp
        SET job = 'SALESMAN'
        WHERE CURRENT OF emp_list;   -- 없으면 emp_list의 모든 행에 대해 update

        DBMS_OUTPUT.PUT_LINE('수정 성공');
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERR MESSAGE: '||SQLERRM);
END;