[PL/SQL] 커서 (Cursor)

커서란, SQL문을 처리한 결과 집합을 가리키는 일종의 포인터이다. (C에서 나오는 포인터 개념과 유사하다)

이 결과 집합은 오라클 서버 프로세스 내부의 Private SQL Area라는 메모리 영역에 저장되는데,

여기서 유의해야 할 점은 커서는 클라이언트 측 프로세스에서 가리키고 있다는 점이다.

Private SQL Area는 특정 쿼리에 대한 결과를 저장하면서 캐싱 하는 역할을 하는데,

한 세션 안에서 같은 쿼리를 반복적으로 호출한다면,  저장되어 있는 결과 집합을 반환하면서 자원 낭비를 최소화한다.

>> 더 자세한 아키텍쳐를 알고 싶으면 여기를 참고 <<

SELECT문으로 특정 테이블에 있는 데이터를 조회했다고 생각해보자.

이 결과값이 이때 employees라는 테이블의 속성값들이 조회되어 Private SQL Area에 저장되고, 커서로 이 영역을 가리킨다.

여기서  다중행에 대한 추가적인 작업이 필요하면 커서를 호출하여 하면 된다.

커서의 종류

---

묵시적 커서의 사용

---

SQL로 시작되는 속성명을 사용해 참조할 수 있으므로 묵시적 커서를 SQL 커서라고도 한다.

묵시적 커서의 속성에는

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

이 있다.

익명 블록을 작성하여 사용법을 알아보고 넘어가자.

DECLARE
    v_emp_name EMPLOYEES.EMP_NAME%TYPE; 
    v_emp_hiredate EMPLOYEES.HIRE_DATE%TYPE;
BEGIN 
    SELECT emp_name, hire_date INTO v_emp_name, v_emp_hiredate FROM employees WHERE employee_id = 110;
    
    DBMS_OUTPUT.PUT_LINE(v_emp_name);
    DBMS_OUTPUT.PUT_LINE(v_emp_hiredate);
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
    
    IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('패치 로우 수가 1개 이상');
    END IF;
  
    IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('패치 로우 수가 0개');
    END IF;
 
    IF SQL%ISOPEN THEN DBMS_OUTPUT.PUT_LINE('커서가 열림');
    ELSE SYS.dbms_output.put_line('커서가 닫힘');
    END IF;
 
END;

John Chen

05/09/28

1

패치 로우 수가 1개 이상

커서가 닫힘

select를 통해서 실제 로우 하나가 처리된 결과르 보여준다.

명시적 커서의 사용

---

PL/SQL에서 SELECT문을 사용할때는 INTO를 함께 사용해야 하며, 항상 단일행만 리턴 받아야 한다.

커서를 사용하면, SQL로 처리한 결과 집합을 가져와 복수행에 대한 작업도 가능하다.

명시적 커서는 결과 데이터 집합을 로우별로 참조해서 어떤 작업을 할 때 아주 유용하게 사용된다.

커서의 사용은 총 4단계로 나뉘는데, 

커서 선언 - 커서 열기 - 패치 단계에서 커서 사용 - 커서 닫기 로 이루어 진다. 

1. 커서 선언하기

명시적 커서는 선언부에 직접 선언 해야 하며, 이름을 보여하고 이 커서에 대한 쿼리를 선언해야 한다. 쿼리는 SELECT를 사용한다.

CURSOR 커서명 [(param1, param2....)]

IS

SELECT 문장;

매개변수는 생략이 가능하다.

2. 커서 열기

OPEN 커서명 [(param1, param2....)]

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

SELECT문에서 반환되는 결과집합에 대해 반복문을 통해 접근한다.

반복문은 LOOP, WHILE, FOR 모두 사용이 가능하다.

LOOP

FETCH 커서명 INTO 변수1, 변수2....

EXIT WHEN 커서명%NOTFOUND;

END LOOP

커서에서 반환되는 각 컬럼 값을 변수에 할당 할 수 있는데 이때 변수는 반환된 컬럼수와 타입이 일치해야한다.

즉, SELECT문에서 선언했던 컬럼 수만큼 변수를 명시하고, 타입도 일치시켜 줘야한다.

루프를 끝낼때는 커서명%NOTFOUNT를 사용하는데, 이는 묵시적 커서와 동일한 방식이다.

4. 커서 닫기

작업이 종료 되었으면 반드시 커서를 닫아 주어야 한다.

CLOSE 커서명;

DECLARE
--    선언부
    v_emp_name EMPLOYEES.EMP_NAME%TYPE; 
    v_emp_hiredate EMPLOYEES.HIRE_DATE%TYPE;
    
--    커서 선언
    CURSOR cur_emp
    IS
    SELECT EMP_NAME, HIRE_DATE FROM employees WHERE employee_id between 100 and 150;
BEGIN 
--    커서 열기 / 패치
    OPEN cur_emp();
    LOOP
      FETCH cur_emp INTO v_emp_name, v_emp_hiredate;
    EXIT WHEN cur_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp_name);
    DBMS_OUTPUT.PUT_LINE(v_emp_hiredate);
  END LOOP;
  
    DBMS_OUTPUT.PUT_LINE(cur_emp%ROWCOUNT);
    IF cur_emp%ISOPEN THEN DBMS_OUTPUT.PUT_LINE('cursor is opened');
    ELSE DBMS_OUTPUT.PUT_LINE('cursor is closed');
    END IF;
  
--  커서 닫기
  CLOSE cur_emp;
  
   IF cur_emp%ISOPEN THEN DBMS_OUTPUT.PUT_LINE('cursor is opened');
    ELSE DBMS_OUTPUT.PUT_LINE('cursor is closed');
    END IF;
END;

앞의 예제를 커서를 통해 열어보자.

묵시적 커서에서의 예제는 SELECT INTO를 통해 단일 행에 대한 정보만 가지고 올 수 있었다.

사용자 정의 커서는 반복문을 통해 SELECT 연산을 통해 얻어온 다중 행에 대해 접근하며,

로우 하나씩 작업을 수행할 수 있다.

* 커서를 FOR문과 함께 사용하기 (편함!!)

FOR 레코드 IN ( param1, param2, ... )

LOOP

처리문

END LOOP;

커서를 통해 가지고 올 속성을 모두 정의해 주지 않아도, FOR문을 사용하면 레코드를 통해 해당 로우의 속성을 참조할 수 있다.

기존의 FOR문과는 다르게 인덱스 자리에 레코드를 선언해 주어야 한다.

DECLARE
--    커서 선언
    CURSOR cur_emp
    IS
    SELECT * FROM employees WHERE employee_id between 100 and 150;
BEGIN 
--    커서 열기 / 패치
 
FOR v_emp_rec IN cur_emp()
LOOP
 
    DBMS_OUTPUT.PUT_LINE(v_emp_rec.emp_name);
    DBMS_OUTPUT.PUT_LINE(v_emp_rec.hire_date);
    DBMS_OUTPUT.PUT_LINE(v_emp_rec.email);
 
  END LOOP;
 
--  커서 닫기
  CLOSE cur_emp;
  
END;

v_emp_rec  을 통해 마치 구조체를 참조하는 것 처럼 편하게 사용할 수 있다.

커서 변수

---

변수의 특징이 있는 커서.

1. 한 개 이상의 쿼리를 연결해 사용할 수 있다. (재사용 가능하다)

2. 변수처럼 커서 변수를 함수나 프로시저의 매개변수로 전달할 수 있다.

3. 커서 속성을 사용할 수 있다.  (cur_var%FOUND, cur_var%ISOPEN...)

위에서 살펴본 커서는 블록안에서만 사용이 가능하며, 변수보다는 상수의 성격을 가지고 있다.

(한 번 선언후에 변경할 수 없음)

블록이 사라질 때 커서도 함께 사라지며, 다른 블록에서 사용할 수 없다.

커서 변수로 커서를 선언하여 사용하면, 다른 블록에서도 사용이 가능하고 얼마든지 재사용이 가능하다.

커서 변수 선언

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

커서_변수명 커서_타입명;

RETURN 반환타입은 해당 반환하는 결과 집합을 말하고, %ROWTYPE을 통해 타입을 정의한다.

커서가 반환하는 타입은 한 개 이상의 컬럼이 있는 레코드 타입이므로 기존의 %TYPE과는 구별해 주어야 하기 때문이다.

반환타입을 생략하면 약한 커서타입,

반환타입이 있으면 강한 커서타입 이라고 한다.

강한 커서타입은 반환타입이 고정되어 있어 그 타입이 아니면 안되는 것이므로, 강한 타입이라고 하는 것이다.

이해가 잘 안되면 js에서 var 변수를 생각해보자. var 타입으로 선언하고 문자열이든, 정수든, 실수든 그냥 다 넣으면 연산할 때 알아서 적당한 타입으로 변환해 준다. 이런식으로 타입을 선언하면 여러 타입을 유연하게 사용할 수 있고, 각각의 타입과 약하게 결합되어 있다고 이해하면 될 것이다.

TYPE dep_curtype IS REF CURSOR RETURN department%ROWTYPE;

TYPE dep_curtype IS REF CURSOR;  

오라클에서 제공하는 빌트인 커서 타입인 SYS_REFUCRSOR을 사용하면 별도로 커서 타입을 선언 할 필요 없이,

test_cursor SYS_REFCURSOR;

이런식으로 선언하여 사용한다. 리턴타입이 없으니까 당연히 약한 커서 타입임.

커서 변수 사용하기

OPEN 커서 변수명 FOR select문;

 
DECLARE
--    커서 선언
    TYPE emp_curtype IS REF CURSOR RETURN employees%ROWTYPE;
    emp_curvar emp_curtype;
    vr_emp employees%ROWTYPE;
    
BEGIN 
--    커서 열기 / 패치
 
OPEN emp_curvar FOR SELECT * FROM employees WHERE employee_id between 100 and 150;
 
 
LOOP
    FETCH emp_curvar INTO vr_emp;
    DBMS_OUTPUT.PUT_LINE(vr_emp.hire_date);
    DBMS_OUTPUT.PUT_LINE(vr_emp.email);
 
  EXIT WHEN emp_curvar%NOTFOUND;
  END LOOP;
 
END;

Employee 테이블의 레코드 타입으로 변수를 선언하여 커서를 받아 컬럼을 모두 레코드에 담았다.

그리고 레코드 타입 변수로 안에 있는 속성에 접근하여 출력하였다.

여기서 눈여겨 볼 점은 직접 CLOSE 할 필요가 없다는 점이다.

약결합으로 선언하여 사용해 보자.

 
DECLARE
--    커서 선언
    TYPE emp_curtype IS REF CURSOR;
    emp_curvar emp_curtype;
    vr_emp employees%ROWTYPE;
    
BEGIN 
...................

 
DECLARE
--    커서 선언
    emp_curvar SYS_REFCURSOR;
    vr_emp employees%ROWTYPE;
    
BEGIN 
...................

약결합일때는 SYS_REFCURSOR을 사용하면 굳이 두 줄을 사용할 필요없이 간략하게 사용이 가능하다.

물론 결과는 위와 아래 똑같은 결과를 낸다.

PL/SQL 프로시저가 성공적으로 완료되었습니다.

03/06/17

SKING

05/09/21

NKOCHHAR

01/01/13

LDEHAAN

06/01/03

AHUNOLD

07/05/21

BERNST

05/06/25

........

커서는 매개변수로 전달이 가능하다.

매개변수로 전달시에 당연히 정의된 타입으로 전달해야 하고, 

일반 매개변수를 넘겨주는 것 처럼 선언하면 된다.

 
CREATE OR REPLACE PROCEDURE  test_cursor_args (p_curvar IN OUT SYS_REFCURSOR)
DECLARE
BEGIN
...
 
END;
 
 
....
DECLARE
emp_dep_curvar SYS_REFCURSOR;
....
BEGIN
test_cursor_args(emp_dep_curvar);
END;

* 커서 표현식

SELECT문에서 컬럼 형태로 커서를 사용 하는 것.

아래와 같이 사용한다.

CURSOR( 서브쿼리 )

예를 들어보자. 이 밑에 쿼리를 실행하면 어떻게 될까?

SELECT d.department_name,
    (SELECT e.emp_name 
        FROM employees e 
          WHERE e.department_id = d.department_id) AS emp_name
    FROM departments d
    WHERE d.department_id = 90;

이 쿼리문의 스칼라 서브쿼리를 보면 부서 번호에 따른 사원은 여러명이 될 수 있으므로, 이것은 다중행을 리턴한다.

그런데 서브쿼리에서는 다중행을 출력할 수가 없다.

그러므로 컴파일러는 아래와 같은 오류를 뱉는다. 

ORA-01427: single-row subquery returns more than one row

01427. 00000 -  "single-row subquery returns more than one row"

*Cause:    

*Action:

이럴때 CURSOR를 사용하여 안에 서브쿼리를 작성하면 리턴되는 3개의 열을 모두 확인할 수 있다.

SELECT d.department_name,
    CURSOR (SELECT e.emp_name 
        FROM employees e 
          WHERE e.department_id = d.department_id) AS emp_name
    FROM departments d
    WHERE d.department_id = 90;

CURSOR (서브쿼리) 로 들고온 집합은 비록 로우는 하나로 나오지만, 커서가 가리키고 있는 집합에는 총 3개의 로우가 들어있다.

물론 이 커서를 변수에 담아서 출력한다면,

모두 각각의 행으로 출력이 가능하다.

아래의 예제를 보자.

DECLARE 
  --첫 번째 커서의 선언.
  CURSOR test_cur IS 
  SELECT d.department_name,
    CURSOR (SELECT e.emp_name 
        FROM employees e 
          WHERE e.department_id = d.department_id) AS emp_name
    FROM departments d
    WHERE d.department_id = 90;
    
    --부서를 저장하기 위함
    vs_department_name departments.department_name%TYPE;
    --이름을 모두 출력하기 위한 커서 변수
    c_emp_name SYS_REFCURSOR;
    -- 이름을 지정하기 위한 변수
    vs_emp_name employees.emp_name%TYPE;
    
    BEGIN
    OPEN test_cur;
    
    LOOP
      --첫 번째 커서 안에 있는 집합을 가지고 온다. 루프를 돌면서 출력
      FETCH test_cur INTO vs_department_name, c_emp_name;
      EXIT WHEN test_cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('부서명 : ' || vs_department_name);
          LOOP
              --SELECT문 안에서 커서로 스칼라 서브쿼리로 가져온 집합을 가지고 와서 출력한다.
              FETCH c_emp_name INTO vs_emp_name;
              EXIT WHEN c_emp_name%NOTFOUND;
              DBMS_OUTPUT.PUT_LINE(' 사원명 : ' || vs_emp_name);
            END LOOP;
      END LOOP;
    END;

PL/SQL 프로시저가 성공적으로 완료되었습니다.

부서명 : 기획부

 사원명 : Steven King

 사원명 : Neena Kochhar

 사원명 : Lex De Haan

찾아온 부서에 따라 각각의 사원명을 출력한다.

위의 SELECT에서 선언한 것으로 보면 부서 정보와 그 부서에 속해있는 사원들을 서브쿼리로 조회하였고, 이를 가리키는 커서를 함께 조회하였다.

먼저 가장 외부의 커서 (즉, 부서이름 - cursor로 구성된 행들, 개발부 - cursor / 마케팅부 - cursor ... 이런식으로 다른 행이 존재할 수도 있다.) 를 가지고 와서 하나의 행을 먼저 조회한 다음,

그 안에 있는 커서로 다시 안에 있는 집합들을 조회한다.

자바의 컬렉션 중에 Map과 형태가 유사하다.