Oracle PL/SQL 프로시저 [ 커서]

커서란 특정 SQL 문장을 처리한 결과를 담고 있는 영역 (PRIVATE SQL 이라는 메모리 영역)을 가리키는 일종의 포인터로, 커서를 사용하면 처리된 SQL 문장의 결과 집합에 접근할 수 있다. SQL문은 집합적 언어이므로 임의의 SQL문이 처리된 결과의 로우수는 한개 이상이다.

 

커서의 종류에는 묵시적 커서와 명시적 커서가 있다. 묵시적 커서란 오라클 내부에서 자동으로 생성되어 사용하는 커서로 , PL/SQL 블록에서 실행하는 SQL 문장 (INSRT,UPDATE,MERGE,DELETE,SELECT INTO) 이 실행될 때마다 자동으로 만들어져 사용된다. 개발자 입장에서는 이러한 커서의 동작에 관여할 수는 없지만, 커서 속성을 이용하면 해당 커서에 대한 여러가지 정보를 얻어낼 수 있다. 반면 명시적 커서는 사용자가 직접 정의해서 사용하는 커서를 말한다. 

 

묵시적 커서든 명시적 커서든 커서의 삶은 '커서 열기(open) -패치(fatch)-커서닫기(close) ' 3단계로 진행된다. 다만 명시적 커서는 커서를 선언하는 부분이 추가되고 이후의 세 단계를 직접 구현해야 하지만, 무시적 커서는 커서 선언도 필요 없고 위 3단계도 자동으로 처리된다.

 

묵시적 커서와 커서 속성

모든 SQL 문장이 실행됨과 동시에 내부적으로 묵시적 커서가 만들어져 사용되었다. 개발자 입장에서는 묵시적 커서가 어떤 식으로 사용됐는지 관여할 필요도 굳이 알 필요도 없지만, 커서 속성을 사용하면 묵시적 커서에 대한 몇 가지 정보를 참조할 수 있다. 예를 들어  INSERT,UPDATE,DELETE문을 수행했을때 몇 건의 데이터가 영향을 받았는지, 즉 몇 건의 데이터가 새로 입력되고 갱신되고 삭제되었는지 알 수 있다. 

 

  DECLARE
        VN_DEPARTMENT_ID EMPLOYEES.DEPARTMENT_ID %TYPE := 80;
        BEGIN
            UPDATE EMPLOYEES
                SET FIRST_NAME = FIRST_NAME
                WHERE DEPARTMENT_ID = VN_DEPARTMENT_ID;
                DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
                COMMIT;
                END;

SQL%ROWCOUNT 라는 커서 속성을 사용해서 해당 SQL 문으로 인해 실제 처리된 결과로우 수를 참조한 것이다. SQL%ROWCOUN 에서 

%ROWCOUN가 커서 속성이며 앞에 붙은 SQL은 커서 이름을 뜻한다. 묵시적 커서의 정보를 참조할때는 SQL로 시작되는 속성명을 사용해 참조할 수 있어 묵시적 커서를 SQL 커서라고도 한다. 

 

속성명	설명
SQL%FOUND	결과 집함의 패치 로우 수가 1개 이상이면 TRUE,아니면 FALSE를 반환
SQL%NOTFOUND	결과 집합의 패치 로우 수가 0이면 TRUE 아니면 FALSE를 반환
SQL%ROWCOUNT	영향 받은 결과 집합의 로우 수 반환, 없으면 0을 반환
SQL%ISOPEN	묵시적 커서는 항상FALSE를 반환

 

명시적 커서

 

명시적 커서를 사용하기 위해서는 "커서 선언 - 커서열기 - 패치 단계에서 커서 사용 - 커서 닫기 " 4단계 작업이 필요하다.

 

1단계 커서 선언

사용할 커서를 선언부에 직접 정의해야한다.

사용할 커서에 이름을 부여하고 이 커서에 대한 쿼리를 선언해야 한다. 명시적 커서란 것이 결과 데이터 집합 로우별로 참조해서 무언가 작업을 하기 위한 용도이므로 당연히 커서를 정의해 사용하는 문장은 SELECT 문이다. 커서의 선언 형태는 다음과 같다.

 

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

2단계 커서열기

커서를 선언한 뒤 해당 커서를 사용하려면 먼저 커서를 열어야한다.

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

3단계 패치 단계에서 커서 사용

정의한 커서를 열고 난후해야 SELECT 문의 결과로 반환되는 로우에 접근할수 있다. 결과 집합의 로우수는 1개 이상이므로 개별 로우에 접근하기 위해서는 반복문을 사용해야 하는데, LOOP , WHIE, FOR 문에서 모두 사용가능하다.

 

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

FETCH INTO 를 통해 커서에서 반환되는 각 컬럼 값을 변수에 할당할 수 있다.이때, 변수는 반환된 컬럼 수와 타입이 일치해야 한다. 즉 커서를 선언할때 연결했던 SELECT 문의 컬럼 수만큼 변수를 명시해야 하고 타입도 맞춰줘야 한다. 또한 반복문을 사용하므로 해당 커서의 참조가 모두 끝났을 때 반복문을 빠져나와야 하는데, 이때 '커서명%NOTFOUND' 커서 속성을 사용해야 루프를 벗어난다. 명시적 커서는 SQL% 대신 선언했던 커서명% 형태로 속성을 참조한다.

 

4단계 커서닫기

CLOSE 커서명;

 

 

DECLARE
		--사원명을 받아오기위한 변수선언
        VS_EMP_NAME EMPLOYEES.FIRST_NAME%TYPE;  
        --커서 선언, 매개변수로 부서코드를 받아오기
        CURSOR CUR_EMP_DEP( CP_DEPARTMENT_ID EMPLOYEES.DEPARTMENT_ID %TYPE)
        IS
        SELECT FIRST_NAME
        FROM EMPLOYEES
        WHERE DEPARTMENT_ID = CP_DEPARTMENT_ID;  
        
        BEGIN 
        	-- 커서 오픈 (매개변수 90번 부서를 전달)
            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;

90번 부서에 속한 사원의 이름을 출력하는 쿼리다. 

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

오라클이 PL/SQL 블록이나 서브 프로그램을 실행하면서 닫히지 않는 커서를 닫는 작업 역시 오버헤드를 발생 시키므로 커서는 항상 명시적으로 닫도록 하자.