[SQL] 오라클 커서(CURSOR)

커서

특정SQL문장을 처리한 결과를 담고있는 영역을 가르키는 일종의 포인터

커서를 사용하면 처리된 SQL문장의 결과 집합에 접근할수있다.

 

커서에는 묵시적 커서와 명시적 커서가 있다.

묵시적 커서 : 오라클 내부에서 자동 생성되어 사용하는 커서

개발자 입장에선 이러한 커서의 동작에 관여할수없지만, 속성을 이용하면 여러가지 정보를 얻어낼수있다.

명시적 커서 : 사용자가 직접 정의해서 사용하는 커서를 말한다.

 

 

묵시적 커서

묵시적 커서 속성

SQL%FOUND	결과 집합의 패치 로우수가 1개이상이면 TRUE, 아니면 FALSE를 반환
SQL%NOTFOUND	결과 집합의 패치 로우수가 0이면 TRUE, 아니면 FALSE를 반환
SQL%ROWCOUNT	영향 받은 결과 집합의 로우 수 반환, 없으면 0을 반환
SQL%ISOPEN	묵시적 커서는 항상 FALSE를 반환(이 속성으로 참조할때는 이미 해당 묵시적 커서는 닫힌 상태 이후이기 때문)

 

SQL%ROWCOUNT 예

DECLARE 
	vn_department_id employee.department_id%TYPE := 80;
BEGIN
	--80번 부서의 사원이름을 자신의 이름으로 갱신
    UPDATE employees
    SET emp_name = emp_name
    WHERE department_id = vn_department_id;
    
    --몇건의 데이터가 갱신되었는지 출력
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
    
    COMMIT;
END;

--결과 
--34

 

 

명시적 커서

명시적 커서를 사용하기 위해 필요한 4단계 작업 : 

1. 커서 선언

CURSOR 커서명 [{매개변수1, 매개변수2 ...}]
IS 
SELECT 문장;

 

2. 커서 열기

OPEN 커서명 [{매개변수1, 매개변수2...}];

 

3. 패치에서 커서 사용

정의한 커서를 열고난 후에야 SELECT문의 결과로 반환되는 로우에 접근할수있다.

결과 집합의 로우수는 보통 1개 이상이므로, 개별로우에 접근하기 위해서는 반복문(LOOP, WHILE, ROR문)을

사용해야한다. 예를들어 LOOP문에서는 다음과 같이 사용한다.

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

- FETCH ... INTO를 통해 커서에서 반환되는 각 컬럼 값을 변수에 할당할수있다.

- 이때 변수는 반환된 커럼수와 타입이 일치해야된다.

즉 커서 선언 시 연결했던 SELECT문의 컬럼수 만큼 변수를 명시해야하고, 타입도맞춰줘야한다.

- 반복문을 사용하므로 해당 커서의 참조가 모두 끝났을때 반복문을 빠져나와야하는데,

이때 '커서명%NOTFOUND' 커서속성을 사용해 루프를 벗어난다.

 

4. 커서 닫기

CLOSE 커서명;

 

명시적 커서 예제

DECLARE
    -- 사원명을 받아오기 위한 변수 선언
    VS_EMP_NAME EMPLOYEES.EMP_NAME%TYPE;
    
    -- 커서 선언, 매개변수로 부서코드를 받기
    CURSOR  CUR_EMP_DEP ( CP_DEPARTMENT_ID  EMPLOYEES.DEPARTMENT_ID%TYPE )
    IS
    SELECT  EMP_NAME
    FROM    EMPLOYEES
    WHERE   DEPARTMENT_ID = CP_DEPARTMENT_ID;
BEGIN
    -- 커서 오픈
    OPEN    CUR_EMP_DEP(90);
    
    -- 반복문을 통한 커서 패치 작업
    LOOP
        -- 커서 결과로 나온 로우를 패치함
        FETCH   CUR_EMP_DEP INTO VS_EMP_NAME;
        -- 패치된 참조 로우가 더 없으면 LOOP 탈출
        EXIT    WHEN CUR_EMP_DEP%NOTFOUND;
        -- 사원명을 출력
        DBMS_OUTPUT.PUT_LINE(VS_EMP_NAME);
    END LOOP;
    
    CLOSE CUR_EMP_DEP;
END;

주의 ! : 커서를 사용할 때는 반드시 먼저 커서를 열고 사용이 끝나면 닫아야한다. 

('닫는다'는 것은 메모리상에 존재하는 커서의 쿼리 결과를 소멸시키는 것을 의미)

 

 

 

출처 : 오라클SQL과 PL/SQL을 다루는 기술 - 홍현경지음