[오라클/SQL] Cursor(커서) 종류와 구조 그리고 예제

Cursor(커서)

Cursor는 쿼리 수행으로 반환되는 여러 값을 처리할 때 사용되는데 글로벌 영역 내의 저장공간을 사용해서 사용자가 쿼리를 수행하면 결과 값을 저장 공간에 가지고 있다가 원하는 시기에 순차적으로 가져와서 처리해서 결과를 반환하는 기법이다. 즉, 커서란 특정 SQL 문장을 처리한 결과를 담고 있는 메모리 영역을 가리키는 일종의 포인터로 볼 수 있다. 대부분의 SQL문 결과 ROW는 여러 개인데 커서를 사용하면 이 ROW에 순차적으로 접근이 가능하다.

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

Oracle에서 쿼리를 수행하면 Shared Pool이라는 Oracle 메모리 영역에서 똑같은 쿼리가 있는지 확인한 뒤, 똑같은 쿼리가 있다면 그것을 바로 이용해서 쿼리를 수행하는데 해당 쿼리는 이미 최적의 실행계획이 포함되어있기 때문에 또 다시 계획 등을 만드는 번거로운 작업이 필요 없기 때문이다. 일종의 시스템에서의 버퍼 메모리 기능(캐시)과 유사하다고 볼 수 있다. 

 

Cursor 종류

묵시적(implicit)	명시적(explicit)
오라클 내부에서 자동으로 생성되어 SQL문장이 실행될 때마다 자동으로 만들어져 실행되는 커서이고, 명시적 커서는 사용자가 직접 정의해서 사용하는 커서를 말한다. 일반적으로 SELECT 문, 혹은 다른 속성에서 값을 얻어와서 이 메모리(변수)에 저장하는데 사용한다.	어떠한 결과 값을 글로벌 영역에 저장해놓고 순차적으로 값을 가져와서 이용하는데, 명시적 커서라고 불리는 이유는 묵시적 커서와는 다르게 명시적으로 CURSOR라고 선언하고 사용한다.
▪ SQL%ROWCOUNT : 최근 실행된 쿼리의 행의 개수 반환	▪ CURSOR(커서 선언)
▪ SQL%FOUND : 최근에 실행된 SQL문의 결과 행 존재 유무, 결                                            과 값이 있을 때 TRUE를 리턴	▪ OPEN(커서 열기)
▪SQL%NOTFOUND : SQL%FOUND와 반대	▪ FETCH ~ INTO(커서가 가리키고 있는 곳에 값 넣기)
▪ SQL%ISOPEN : 최근 실행된 SQL문의 묵시적 커서의 종료 유무	▪ CLOSE(커서 닫기)
	▪ %ISOPEN : TRUE, FALSE ▪%FOUND : NULL, TRUE, FALSE, INVALID_CURSOR ▪ %NOTFOUND : NULL, FALSE, TRUE, INVALID_CURSOR ▪ %ROWCOUNT : INVLID_CURSOR ...

 

 

Cursor 구조와 예제

  커서의 구조는 
‘cursor 커서_명 (매개변수1, 매개변수2, ...)
is
select 문’이다.

  커서를 열기 위해서는 loop를 비롯한 반복문을 함께 사용해서 순차적으로 접근하기 때문에
loop
fetech 커서_명 into 변수1, 변수2, ...
exit when 커서_명%NOTFOUND; // 더 이상의 자료가 없으면 
end loop; 식으로 해준다. 
  그리고 커서를 닫기 위해서
close 커서_명; 을 사용한다.

 간단한 커서의 예를 보면
DECLARE                   	## 커서 선언
PRODUCT_ID varchar2(100);
PRODUCT_NAME varchar2(100);
CURSOR EX_CUR 		 	## 커서_명
IS
SELECT PRODUCT_ID, PRODUCT_NAME FROM PRODUCTS;
BEGIN
OPEN EX_CUR; 		  	## 명시적 커서 오픈
DBMS_OUTPUT.PUT_LINE('제품코드  제품명'); ## 내용 출력
LOOP
FETCH EX_CUR INTO PRODUCT_ID, PRODUCT_NAME; # 커서에서 데이터를 가져와서 넣기
EXIT WHEN EX_CUR %NOTFOUND; 	# 커서에 더 이상의 데이터가 없을 경우 종료
DBMS_OUTPUT.PUT_LINE(PRODUCT_ID ||'  '|| PRODUCT_NAME);
END LOOP;
CLOSE EX_CUR; 			# 커서 닫기
END; 해본다.

DECLARE  
   total_rows number(2); 
BEGIN 
   UPDATE customerss  
   SET salary = salary + 500; 
   IF sql%notfound THEN 
      dbms_output.put_line('no customer selected'); 
   ELSIF sql%found THEN 
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' customer selected '); 
   END IF;  
END; 
/

DECLARE                          
   c_id customerss.id%type;      
   c_name customerss.name%type; 
   c_addr customerss.address%type; 
   CURSOR c_customerss is       
      SELECT id, name, address FROM customerss;  
BEGIN 
   OPEN c_customerss; 
   LOOP 
   FETCH c_customerss into c_id, c_name, c_addr; 
      EXIT WHEN c_customerss%notfound; 
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); 
   END LOOP; 
   CLOSE c_customerss; 
END; 
/

create or replace procedure info_hiredd (p_year in char := 0)
# info_Hired는 프로시저_명이고, p_year가 변수_명, CHAR가 변수 타입이며, :=0가 변수_값을 0으로 초기화 한 것이다.
ls l_emp employees%ROWTYPE;
# employees 테이블의 각 항목 타입을 자동으로 맞춰서 l_emp 테이블을 만듦
cursor emp_cur ls
# 커서 선언으로 emp_cur이 커서_명이다.
select employee_id, last_name, salary from employees
where to_char(hire_date, 'yyyy') = p_year;
# employees 테이블에서 특정 항목만 가져와서 emp_cur 커서에 넣음
begin
  open emp_cur;    # 커서 열기
  fetch emp_cur into l_emp.employee_id, l_emp.last_name, l_emp.salary;
   # 커서에서 데이터 읽기
  dbms_output.put_line(l_emp.employee_id || '   ' || l_emp.last_name || '   ' || l_emp.salary);
# 내용 출력. '   ' 사이가 벌어져야 결과문이 예쁘게 보인다.
close emp_cur;
end info_hiredd;
/

컴파일 오류는 왜 생기는 걸까?