SQL 22편: 커서

커서

SELECT문 또는 데이터 조작어 같은 SQL문을 실행했을 때 해당 SQL문을 처리하는 정보를 저장한 메모리 공간이다. SQL문의 결과 값을 사용할 수 있다. SELECT 문의 결과 값이 여러 행으로 나왔을 때, 각 행별로 특정 작업을 수행하도록 기능을 구현하는 것이다. 사용 방법에 따라 명시적 커서와 묵시적 커서로 나뉜다. 

*메모리 공간: =Private SQL Area, 커서는 이 메모리의 포인터를 말한다.

 

SELECT INTO 방식이 조회되는 데이터가 단 하나의 행일 때 사용 가능한 방식인 것에 비해 커서는 결과 행이 하나이든 여러 개이든 상관 없이 사용할 수 있다.

 

SELECT 열1, 열2, .... INTO 변수1, 변수2, ...

FROM ...

 

 

 

 

1. 명시적 커서

기본 형식

DECLARE 

CURSOR 커서이름(변수1 자료형, ... ) IS SQL문; --커서 선언

 

BEGIN 

OPEN 커서 이름; --커서 열기

FETCH 커서이름 INTO 변수 --커서로부터 읽어온 데이터 사용

CLOSE 커서이름; --커서 닫기

 

END; 

 

 

커서 사용 단계

커서 선언
커서 열기	커서를 선언할 때 작성한 SQL문을 실행한다. 이 때 실행한 SQL문에 영향을 받는 행을 active set이라고 한다.
커서 FETCH	결과 행 정보를 하나씩 읽어 와서 변수에 저장한 후, 필요한 작업을 수행한다.
커서 닫기	모든 행의 사용이 끝난 후 커서를 종료한다.

 

DECLARE
   -- 커서 데이터를 입력할 변수 선언
   V_DEPT_ROW DEPT%ROWTYPE;

   -- 명시적 커서 선언(Declaration)
   CURSOR c1 IS
      SELECT DEPTNO, DNAME, LOC
        FROM DEPT;

BEGIN
   -- 커서 열기(Open)
   OPEN c1;

   LOOP
      -- 커서로부터 읽어온 데이터 사용(Fetch)
      FETCH c1 INTO V_DEPT_ROW;

      -- 커서의 모든 행을 읽어오기 위해 %NOTFOUND 속성 지정
      EXIT WHEN c1%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || V_DEPT_ROW.DEPTNO
                        || ', DNAME : ' || V_DEPT_ROW.DNAME
                        || ', LOC : ' || V_DEPT_ROW.LOC);
   END LOOP;

   -- 커서 닫기(Close)
   CLOSE c1;

END;
/

 

 

커서 속성

커서이름%NOTFOUND	FETCH문을 통해 추출된 행이 있으면 FALSE, 없으면 TRUE
커서이름%FOUND	FETCH문을 통해 추출된 행이 있으면 TRUE, 없으면 FALSE
커서이름%ROWCOUNT	현재까지 추출된 행 수를 반환
커서이름%ISOPEN	커서가 열려 있으면 true, 닫혀 있으면 false를 반환

 

 

FOR LOOP문

커서를 이용하여 여러 행을 조회하는 경우 FOR LOOP문을 사용하면 OPEN, FETCH, CLOSE문을 작성하지 않기 때문에 커서 사용 방법이 간단해진다. 

 

FOR 루프인덱스이름 IN 커서이름 LOOP

결과 행별로 반복 수행할 작업;

END LOOP 

 

*루프인덱스: 커서에 저장된 각 행이 저장되는 변수를 뜻하며, '.' 를 통해 행의 각 필드에 접근할 수 있다.

 

DECLARE
   -- 명시적 커서 선언(Declaration)
   CURSOR c1 IS
   SELECT DEPTNO, DNAME, LOC
     FROM DEPT;

BEGIN
   -- 커서 FOR LOOP 시작 (자동 Open, Fetch, Close)
   FOR c1_rec IN c1 LOOP
      DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || c1_rec.DEPTNO
                      || ', DNAME : ' || c1_rec.DNAME
                      || ', LOC : ' || c1_rec.LOC);
   END LOOP;

END;
/

 

 

사용자에게서 커서의 파라미터 값 직접 입력 받기

커서 실행에 필요한 파라미터 값을 사용자에게 직접 입력받고 싶은 경우 &기호와 치환 변수를 사용할 수 있다. 

DECLARE
   -- 사용자가 입력한 부서 번호를 저장하는 변수선언
   v_deptno DEPT.DEPTNO%TYPE;
   -- 명시적 커서 선언(Declaration)
   CURSOR c1 (p_deptno DEPT.DEPTNO%TYPE) IS
      SELECT DEPTNO, DNAME, LOC
        FROM DEPT
       WHERE DEPTNO = p_deptno;
BEGIN
   -- INPUT_DEPTNO에 부서 번호 입력받고 v_deptno에 대입
   v_deptno := &INPUT_DEPTNO;
   -- 커서 FOR LOOP 시작. c1 커서에 v_deptno를 대입
   FOR c1_rec IN c1(v_deptno) LOOP
      DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || c1_rec.DEPTNO
                      || ', DNAME : ' || c1_rec.DNAME
                      || ', LOC : ' || c1_rec.LOC);
   END LOOP;
END;
/

 

 

 

 

 

2. 묵시적 커서

별 다른 선언 없이 sql문을 사용했을 때 오라클에서 자동으로 선언되는 커서를 뜻한다. 따라서 사용자가 OPEN, FETCH, CLOSE 를 지정하지 않는다. PL/SQL 문 내부에서 DML 명령어나 SELECT INTO 문이 실행될 때 자동으로 생성 및 처리된다. 커서가 자동으로 생성되므로 커서 이름을 지정하지 않고 SQL 키워드로 속성을 지정하며, 명시적 커서의 속성과 유사한 기능을 갖는다.

 

커서 속성

SQL%NOTFOUND	FETCH문을 통해 추출된 행이 있으면 FALSE, 없으면 TRUE
SQL%FOUND	FETCH문을 통해 추출된 행이 있으면 TRUE, 없으면 FALSE
SQL%ROWCOUNT	현재까지 추출된 행 수를 반환
SQL%ISOPEN	커서가 열려 있으면 true, 닫혀 있으면 false를 반환