[Oracle] Cursor

어떤 상황에 CURSOR를 사용할까?

 

DB 프로그래밍을 한다면 커서는 결과 집합의 한 로우에 쉽게 접근할 수 있는 가장 쉬운 방법이다. SQL문 자체는 집합적으로 데이터를 처리하기에 각 ROW에 대한 연산을 하기 위해선 Cursor를 사용하여 쉽게 각 로우에 접근할 수 있다.

 

커서는 특정 SQL 문장을 처리한 결과 집합을 담고 있는 메모리 영역을 가리키는 포인터 개념이다.

 

오라클 서버에서는 SQL문을 실행할 때마다 처리를 위한 메모리 공간을 사용합니다.  사용자가 요청하는 데이터를 데이터베이스 버퍼 캐시에서 커서로 복사해온 다음 커서에서 원하는 데이터를 추출하여 후속 작업을 진행합니다.

 

SQL커서는 묵시적 커서와 명시적 커서로 나뉘게 됩니다.

 

묵시적 커서는 오라클 내부에서 SQL 문장이 실행될 때 자동으로 생성-OPEN-FETCH-CLOSE를 수행하며, 개발자가 커서의 동작에는 관여를 하지 못하지만 커서 속성을 사용해 다양한 정보를 얻을 수 있습니다.

 

묵시적 커서의 속성은 SQL%라는 접두사가 고정되어 있으며 SQL% NOTFOUND, SQL% ROWCOUNT 속성을 이용해 다양한 정보를 얻을 수 있습니다.

 

명시적 커서는 묵시적 커서와달리 선언-OPEN-FETCH-CLOSE를 직접 해주어야 합니다.

선언부에 커서를 선언합니다

DECLARE
CURSOR 커서이름(arg...)
IS
SELECT문;

커서 이름 사용자가 정의하며 매개변수는 생략이 가능합니다.

 

커서를 사용하기 위해선 실행부에서 커서를 열어야 합니다.

 

OPEN 커서이름[(매개변수;

 

커서를 열었다면 해당 커서를 이용해 FETCH작업을 진행할 수 있습니다.

결과 집합의 하나의 로우에 접근하기 위해서는 반복문(LOOP, WHILE, FOR)을 사용합니다.

DECLARE

 v_emp2 emp2.NAME%TYPE;

 CURSOR s_emp2 
 IS 
 SELECT NAME FROM emp2;

BEGIN
	
	OPEN s_emp2;
	
	LOOP
	FETCH s_emp2 INTO v_emp2;
	EXIT WHEN s_emp2%NOTFOUND;
	DBMS_OUTPUT.PUT_LINE(v_emp2);
	END LOOP;
	
	CLOSE s_emp2;

END;

 

 

FETCH INTO문을 통해 반환하는 각 칼럼의 값을 변수에 할당할 수 있으며, 이때 변수는 반환하는 칼럼의 수와 타입이 일치해야 합니다.

 

이제 커서가 더이상 패치할 데이터가 없을 경우 LOOP문을 빠져나오기 때문에 다음과 같은 문법으로 메모리상에 존재하는 커서의 쿼리 결과를 소멸시켜야 합니다. 사실 오라클이 커서를 자동적으로 닫아주지만 오버헤드가 발생하기 때문에 명시적으로 닫아주어야 합니다.

 

CLOSE 커서명;

 

회사에서도 저장모듈을 사용하는 경우가 있어 프로시저를 사용하는데 다음과 같은 문법을 사용해 열고 닫고 패치하는 과정을 줄여 코드를 깔끔하게 이용합니다. 

DECLARE
 v_emp2 emp2.NAME%TYPE;
 CURSOR s_emp2 
 IS 
 SELECT NAME FROM emp2;
BEGIN
	FOR v_emp2 IN s_emp2
	LOOP
	DBMS_OUTPUT.PUT_LINE(v_emp2.name);
	END LOOP;
END;

 

원래의 FOR문과 비교하자면 인덱스가 들어가는 자리에 레코드 타입의 변수를 사용하며, 시작과 끝을 정하는 부분에 커서를 사용합니다.

 

커서를 열고 패치하고 닫는 부분은 오라클에서 자동적으로 해줍니다. 물론 선언부에 커서를 선언하지 않고 IN(SELECT문)을 통해 커서 선언을 할 수도 있습니다.

 

사실 변수라는 것은 할당과 해제가 자유로워야합니다만 커서는 상수 변수와 같은 특징을 가지고 있습니다. 할당한 쿼리문을 재할당할 수 없기 때문입니다. 그렇기에 한 개의 이상 쿼리를 연결하여 사용할 수 있고, 매개변수로도 활용, 커서 속성을 사용할 수 있는 커서 변수를 사용합니다.

 

커서 변수는 빌트인 커서타입을 활용하거나 TYPE을 정의해요 TYPE을 커서 변수에 할당하는 방법이 있습니다.

 

 

우선 참조용 커서 타입을 생성하고나서 해당 타입에 대한 커서 변수 선언 방법입니다.

TYPE 커서_타입명 IS REF CURSOR[RETURN 반환타입];

이경우 RETURN을 정할시 강한 커서 타입, 생략 시 약한 커서 타입으로 약한 커서 타입은 타입이 정해지지 않아서 여러 타입을 저장할 수 있습니다.

 

커서변수명 커서타입명;

다음과 같이 생성한 커서타입을 통해 커서를 생성합니다.

 

 

그럼 커서 변수에 SELECT문을 할당해야 하는데 다음과 같은 OPEN.. FOR문을 많이 사용합니다.

 

OPEN 커서변수명 FOR [SELECT문];

여기서 확인할 수 있는데 커서변수가 약한 커서 타입이라면 SELECT문의 다양한 결과를 가질 수 있는 걸 확인할 수 있습니다.

 

커서 변수에 담긴 결과 집합을 변수에 옮길 때도 FETCH INTO문을 사용합니다. 혹은 그냥 OUT변수로 설정해서 결괏값을 다른 시스템 혹은 프로그램으로도 전달해 줄 수 있습니다.

 

 

for문을 이용해 커서결과를 패치할 때 인덱스 대신 레코드 변수를 사용한다고 했습니다.

 

레코드는 PL/SQL에서 제공하는 데이터 타입 중 하나로 복합형 구조를 가집니다. 대신 하나의 로우에 다양한 타입의 변수들이 줄줄이 저장되어 있는 형태입니다. 다양한 칼럼을 저장하기 때문이죠.

 

레코드는 직접 사용자가 정의할수도 테이블의 모든 칼럼을 받아 사용할 수도 있고 커서의 결과 집합을 레코드 변수에 담을 수도 있습니다.

 

직접 정의할 경우

TYPE emp_rec IS RECODE(
	id emp.id%TYPE,
    name emp.name%TYPE
);

 

테이블형 레코드

vr_emp emp%ROWTYPE;

 

커서형

  CURSOR c1 IS
      SELECT emp_id, emp_name 
      FROM departments;

      vr_emp c1%ROWTYPE;

만약 이렇게 레코드변수에 테이블과 칼람 수, 칼람 타입, 칼람 순서가 같다면 insert문과 update문에 자유롭게 이용할 수 있습니다.