SQL - Cursor (커서)

커서

Select문 또는 데이터 조작어 같은 SQL문을 실행 했을 때 해당 SQL문을 처리하는 정보를 저장한 메모리 공간 (Private SQL Area)

• 커서를 사용하면 실행된 SQL문의 결과값을 사용할 수 있음
• 결과 행이 여러 개여도 상관없음

---

Select Into

Select 열1, 열2, ... Into 변수1, 변수2, ...
From ...

Declare
  V_DEPT_ROW DEPT%ROWTYPE;
Begin
  Select DEPTNO, DNAME Into V_DEPT_ROW
  FROM DEPT
  WHERE DEPTNO = 40;
End;
/

• 조회되는 행이 여러 개일 때만 유효하므로 여러 행이 조회될 때는 커서를 사용하게 된다.

---

명시적 커서

사용자가 직접 커서를 선언하고 사용하는 커서

1. 커서 선언
2. 커서 열기
3. 커서에서 읽어온 데이터 사용
4. 커서 닫기

   Declare
   Cursor 커서명 Is SQL문; # 1
   Begin
   Open 커서명; # 2
   Fetch 커서이름 Into 변수 # 3
   Close 커서명; # 4
   End;

하나의 행만 조회되는 경우

커서에 지정한 Select 문이 하나의 행만 조회되는 경우, Select Into 문보다 여러 단계를 작성해야 하므로 번거로울 수 있다.

Delcare
  V_DEPT_ROW DEPT%ROWTYPE;

Cursor c1 Is
  Select DEPTNO, DNAME
    From DEPT
  Where DEPTNO = 40;

Begin
  Open c1;
  Fetch c1 Into V_DEPT_ROW;
  -- 읽어온 데이터를 사용
  Close c1;
End;
/

여러 행이 조회되어 LOOP문 사용하기

Delcare
  V_DEPT_ROW DEPT%ROWTYPE;

Cursor c1 Is
  Select DEPTNO, DNAME
    From DEPT

Begin
  Open c1;
  Loop
    Fetch c1 Into V_DEPT_ROW;
    Exit When c1%Notfound; -- Loop 문의 탈출 조건
    -- 읽어온 데이터를 사용
  End Loop;
  Close c1;
End;
/

• %Notfound 는 실행한 Fetch문에서 행을 추출하면 false, 행을 추출하지 않았으면 true를 의미 <-> %Found
• %Rowdount 는 현재까지 추출된 행 수
• %Isopen 커서가 열려 있으면 ture, 닫혀있으면 false

For Loop

For 루프인덱스명 In 커서명 Loop
  결과 별로 반복 수행할 작업;
End Loop;

• 루프 인덱스는 커서에 저장된 각 행이 저장되는 변수를 의미
• 루프인덱스명.필드 로 접근할 수 있다.
• 커서에 For Loop문을 사용하면 Open, Close, Fetch문을 작성하지 않는다. -> 즉 해당 명령어를 자동으로 수행하여 커서 사용이 간단해진다.

Declare
  Cursor c1 Is
  Select DEPTNO, DNAME
    From DEPT;

Begin
  For c1_rec In c1 Loop
    DBMS_OUTPUT.PUT_LINE('DEPTNO: ' || c1_rec.DEPTNO);
  End Loop;
End;
/

커서에 파라미터 사용하기

고정 값이 아닌 직접 입력한 값 또는 상황에 따라 여러 값을 번걸아 사용하려면 다음과 같이 커서에 파라미터를 저장할 수 있다.

Cursor 커서명(파라미터명 타입, ...) Is
Select ...

Declare
  V_DEPT_ROW DEPT%ROWTYPE;
  Cursor c1 (p_deptno DEPT.DEPTNO%TYPE) Is
    Select DEPTNO, DNAME
      From DEPT
    Where DEPTNO = p_deptno;

Begin
  Open c1 (10); -- 10번 부서
    Loop
      Fetch c1 Into V_DEPT_ROW;
      Exit When c1%Notfound;
      DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || V_DEPT_ROW.DEPTNO
                           'DNAME : ' || V_DEPT_ROW.DNAME)
    End Loop;
  Close c1;

  Open c1 (20); -- 20번 부서
    Loop
      Fetch c1 Into V_DEPT_ROW;
      Exit When c1%Notfound;
      DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || V_DEPT_ROW.DEPTNO
                           'DNAME : ' || V_DEPT_ROW.DNAME)
    End Loop;
  Close c1;
End;
/

---

묵시적 커서

• 별다른 선언 없이 SQL문을 사용했을 때 오라클에서 자동으로 선언되는 커서

• 사용자가 Open, Fetch, Close 를 지정하지 않음

• DML명령어나 Select into 문 등이 실행될 때 자동으로 생성 및 처리됨

• 자동으로 커서가 생성되므로 묵시적 커서의 속성을 사용하여 현재 커서의 정보를 확인할 수 있음

  • SQL%Notfound : 묵시적 커서 안에 추출한 행이 있으면 false, 없으면 true
  • SQL%Found : 묵시적 커서 안에 추출한 행이 있으면 true
  • SQL%Rowcount : 묵시적 커서에 현재까지 추출한 행 수 또는 DML 명령어로 영향을 받는 행 수 반환
  • SQL%Isopen : 묵시적 커서는 자동으로 SQL문을 실행한 후 Close 되므로 항상 false 를 반환