OBJECT - CURSOR

CURSOR

• SQL문의 처리 결과(처리 결과가 여러 행)를 담고있는 객체이다.
• 커서 사용시 여러 행으로 나타난 처리 결과에 순차적으로 접근이 가능하다.

묵시적 커서

• 오라클에서 자동으로 생성되어 사용하는 커서이다.
• PL/SQL 블록에서 실행하는 SQL문 실행 시마다 자동으로 만들어져서 사용된다.

-- 실습에 사용한 테이블 생성
CREATE TABLE EMP_COPY 
AS SELECT * FROM EMPLOYEE;

SELECT * FROM EMP_COPY;

-- PL/SQL을 사용해서 EMP_COPY에 BONUS가 NULL인 사원의 BONUS를 0으로 수정
BEGIN
    UPDATE EMP_COPY
    SET BONUS = 0
    WHERE BONUS IS NULL;
    
    -- 묵시적 커서 사용
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || '행 수정됨'); -- ROWCOUNT 몇 개의 행이 영향을 받는지
END;
/

명시적 커서

• 사용자가 직접 선언해서 사용할 수 있는 커서이다.

커서를 만드는 단계

• 커서 선언 → 커서 열기 → 패치 단계에서 커서 사용 → 커서 닫기

CURSOR 커서명 
IS [SELECT문]

OPEN 커서명;
FETCH 커서명 INTO 변수;
...

CLOSE 커서명;

커서 선언

• 커서는 선언은 DECLEAR부분에 이름을 부여하면 됩니다.

CURSOR 커서명(매개변수1, 매개변수2 ... ) 
IS
SELECT문;

• 매개변수는 생략이 가능한데, 매개변수를 명시하면 SELECT문자의 WHERE절에서 조건을 체크하는 변수로 사용된다.

커서 열기

• 선언한 커서를 사용하려면 선언한 커서를 열어주는 작업이 필요합니다.
• 커서 오픈 : 선언 시 작성했던 쿼리를 수행하고 결과를 담아둔다.

OPEN 커서명 (매개변수1, 매개변수2 ...);

패치 단계에서 커서 사용

• 정의한 커서를 열었으면 패치단계에서 커서를 사용하면 되며, 한 행씩 데이터를 가져온다.
• 커서는 SQL 결과 집합이 여러개 이상에서 순차적으로 접근하기 위해 사용하는 방식이므로 거의 필수적으로 LOOP를 비롯한 반복문과 같이 사용하게됩니다.

LOOP
FETCH 커서명 INTO 변수1, 변수2 ...
EXIT WHEN 커서명%NOTFOUND;
END LOOP;

• FETCH INTO를 통해 커서에서 반환되는 각 컬럼 값을 변수에 할당할 수 있다. 이때, 변수는 변환된 컬럼 수와 타입이 일치해야 한다.
• 반복문을 사용하므로 해당 커서의 참조가 모두 끝났을 때 반복문을 빠져 나와야 하는데, 이때 ‘커서명%NOTFOUND’ 커서 속성을 사용해 루프를 벗어난다.

커서 닫기

• 커서를 사용한 모든 동작이 끝났으면 커서를 닫아주어야 합니다.

CLOSE 커서명;

 

-- 급여가 3000000 이상인 사원들의 사번, 이름, 급여를 출력 (PL/SQL)
DECLARE 
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SAL EMPLOYEE.SALARY%TYPE;
    
    CURSOR C1 IS -- 커서 선언 : 아래의 서브쿼리를 담아둔다. 
        SELECT EMP_ID, EMP_NAME, SALARY
        FROM EMPLOYEE
        WHERE SALARY > 3000000;
BEGIN
-- 커서 오픈 : 선언 시 작성했던 쿼리를 수행하고 결과를 담아둔다. 
    OPEN C1; 
    
    LOOP 
        -- 커서 패치 : 한 행씩 차례대로 데이터를 가져온다. 
        FETCH C1 INTO EID, ENAME, SAL; -- 저장할 데이터를 INTO절에 나열
        
        -- 반복문 종료 조건
        EXIT WHEN C1%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(EID || ' ' || ENAME || ' ' || SAL);
        
    END LOOP;
    
    CLOSE C1;
END;
/

 

-- 전체 부서에 대해 부서 코드, 부서명, 지역 조회(RPOCEDURE)
CREATE PROCEDURE CURSOR_DEPT
IS 
    DEPT DEPARTMENT%ROWTYPE;
    CURSOR C1 IS
        SELECT * FROM DEPARTMENT;
BEGIN
    OPEN C1;
    
    LOOP
        FETCH C1 INTO DEPT.DEPT_ID, DEPT.DEPT_TITLE, DEPT.LOCATION_ID;
        
        EXIT WHEN C1%NOTFOUND;
    
        DBMS_OUTPUT.PUT_LINE(DEPT.DEPT_ID || ' ' || DEPT.DEPT_TITLE || ' ' || DEPT.LOCATION_ID);
    END LOOP;
    
    CLOSE C1;
END;
/
-- 프로시저를 실행하는거지 BEGIN절이 실행하는게 아니다. 

EXEC CURSOR_DEPT; -- 출력

FOR LOOP를 이용한 커서 사용

1. LOOP 시작 시 자동으로 커서를 OPEN 한다
2. 반복할 때마다 FETCH도 자동으로 실행된다.
3. LOOP 종료 시 자동으로 커서를 CLOSE 한다.

CREATE OR REPLACE PROCEDURE CURSOR_DEPT
IS 
    DEPT DEPARTMENT%ROWTYPE;
--    CURSOR C1 IS
--        SELECT * FROM DEPARTMENT;
BEGIN
--    FOR DEPT IN C1 -- 커서 오픈과 패치가 동시에 수행

-- CURSOR를 별도로 선언하지 않고 바로 사용하는 방법
    FOR DEPT IN (SELECT * FROM DEPARTMENT)
    LOOP
        DBMS_OUTPUT.PUT_LINE(DEPT.DEPT_ID || ' ' || DEPT.DEPT_TITLE || ' ' || DEPT.LOCATION_ID);
    END LOOP;
END;
/

EXEC CURSOR_DEPT;

 

커서 속성

• 묵시적 커서의 경우 커서명은 SQL로 사용된다.
• 커서명%FOUND : 커서 영역에 남아있는 행의 수가 한 개 이상일 경우 TRUE, 아니면 FALSE
• 커서명%NOTFOUND : 커서 영역에 남아있는 행의 수가 없으면 TRUE, 아니면 FALSE
• 커서명%ISOPEN : 커서가 OPEN 상태인 경우 TRUE, 아니면 FALSE
• 커서명%ROWCOUNT : SQL 처리 결과로 얻어온 행의 수

CURSOR 삭제