커서 ( Cursor )

커서 (Cursor)

 

 - SQL문을 실행하기 위한 메모리 공간

 

     -> 특정 SQL 문장을 처리한 결과를 담고 있는 영역을 가리키는 일종의 포인터

 

 - 커서를 사용하면 처리된 SQL 문장

 

 - 기존에 사용한 SQL 문은 묵시적 커서를 사용

 

---

 종류

 

 1. 묵시적(암시적) 커서

 

   1) 오라클 내부에서 자동으로 생성되어 사용하는 커서

 

   2) PL/SQL 블록에서 실행되는 문장

 

   3) ( INSERT, UPDATE, MERGE, DELETE, SELECT INTO )가 실행될 때마다 자동으로 만들어져 사용 됌

 

   4) 개발자 입장에서는 이러한 커서의 동작에 관여할 수 없음

 

   5) 커서 속성을 이용하면 해당 커서에 대한 여러가지 정보를 알아낼 수 있음

 

   * 묵시적 커서 속성

      1) SQL%FOUND : 결과 집합의 패치 로우 수가 1개 이상이면 TRUE, 아니면 FALSE

 

      2) SQL%NOTFOUND : 결과 집합의 패치 ROW수가 0이면 TRUE, 아니면 FALSE 

 

      3) SQL%ROWCOUNT : 영향 받은 결과 집합의 ROW 수 반환, 없으면 0 반환

 

      4) SQL%ISOPEN : 묵시적 커서는 항상 FALSE를 반환  

            

                              (이 속성으로 참조할 때는 이미 해당 묵시적 커서는 닫힌 상태 이후이기 때문)

 

   예제

DECLARE

    V_dept_id  emp.dept_id%type := 80;
    
BEGIN

	UPDATE emp SET ename = ename;
    
    WHERE dept_id = V_dept_id;
    
    DBMS_OUTPUT.PUT.LINE(SQL%ROWCOUNT);  -> 몇 건의 데이터가 갱신 되었는지 출력
    
    COMMIT;

END;
/

 

 

 2. 명시적 커서

 

  1) 사용자가 직접 정의해서 사용하는 커서

 

  2) 사용할 커서에 이름을 부여하고 이 커서에 대한 쿼리를 선언해야 함

 

  3) 명시적 커서는 결과 데이터 집합을 ROW별로 참조해서 어떠한 작업을 하기 위한 용도이므로 

 

     커서를 정의해 사용하는 문장은 SELECT이다.

 

  4) SELECT 결과 여러건을 TABLE 타입의 변수에 저장할 수 있지만 메모리는 한정적이기 때문에

 

      많은 양의 데이터를 담기에는 제한이 따름

 

       -> SQL 커서를 통해 개발자가 직접 데이터를 FETCH함으로써  SELECT 결과를 전부 불러오지 않고도 개발이 가능

 

 

 

---

 

  - 커서 선언 방법

DECLARE  -> 선언부에서 선언

	CURSOR 커서이름 IS

   		제어할 쿼리;

 

 - 커서 열기 

BEGIN -> 실행부에서 커서 열기

  OPEN 커서이름;

 

 - 커서로부터 데이터 FETCH

FETCH 커서이름 INTO 변수;  -> 위 실행부의 커서 열기에 이어서

 

- 커서 닫기

CLOSE 커서이름; -> 실행부에서 커서 닫기 까지

 

---

실습

 

 - 부서 테이블을 활용하여 모든 행에 대해 부서번호와 부서 이름을 cursor를 통해 FETCH하고 결과 확인

SET SERVEROUTPUT ON;

DECLARE 

    v_deptno dept.deptno%TYPE;
    
    v_dname dept.dname%TYPE;
    
    CURSOR dept_cursor IS
    
        SELECT deptno, dname
        
        FROM dept;
        
BEGIN

    OPEN dept_cursor;
    
    LOOP
    
        FETCH dept_cursor INTO v_deptno, v_dname;
        
        EXIT WHEN dept_cursor%NOTFOUND;   
        
        DBMS_OUTPUT.PUT_LINE(v_deptno || ' : ' || v_dname);
        
    END LOOP;
    
END;    
/

 

※ 커서를 열고 닫는 과정이 다소 길기 때문에 Cursor는 일반적으로 loop와 함께 사용함

 

   => 명시적 커서를 FOR LOOP에서 사용할 수 있게 끔 문법으로 제공

 

     * JAVA의 향상된 FOR문과 유사한 형태

JAVA

for(String UserName : userNameList){

    UserName 값을 사용..;
    
}

 

PL/SQL

FOR record_name(한 행의 정보를 담을 변수이름 / 변수를 직접 선언안함) IN 커서이름 LOOP

    record_name.컬럼명
    
END LOOP;

 

---

실습

 

DECLARE

    v_deptno dept.deptno%TYPE;
    
    v_dname dept.dname%TYPE;
    
    CURSOR dept_cursor IS
    
        SELECT deptno, dname
        
        FROM dept;
        
BEGIN

    FOR rec IN dept_cursor LOOP
    
        DBMS_OUTPUT.PUT_LINE(rec.deptno || ' : ' || rec.dname);
        
    END LOOP;
    
END;
/

 

---

 

인자가 있는 커서

 

기존의 커서 선언 방법

CURSOR 커서이름 IS

        서브쿼리..;

 

인자가 있는 커서 선언 방법

CURSOR 커서이름(인자1 인자1 타입, 인자2 인자2 타입, ...) IS

        서브쿼리;
        
            (커서 선언시에 작성한 인자를 서브쿼리에서 사용할 수 있다.)

 

---

실습

DECLARE

    v_deptno dept.deptno%TYPE;
    
    v_dname dept.dname%TYPE;
    
    CURSOR dept_cursor(p_deptno dept.deptno%TYPE) IS
    
        SELECT deptno, dname
        
        FROM dept
        
        WHERE deptno <= p_deptno;
        
BEGIN

    FOR rec IN dept_cursor(:deptno) LOOP
    
        DBMS_OUTPUT.PUT_LINE(rec.deptno || ' : ' || rec.dname);
        
    END LOOP;
    
END;
/

 

더 간단하게 FOR LOOP 에서 커서를 인라인 형태로 작성

 

FOR 레코드 이름 IN 커서이름

==>

FOR 레코드 이름 IN (서브쿼리);

 

실습

DECLARE

BEGIN

    FOR rec IN (SELECT deptno, dname FROM dept) LOOP
    
        DBMS_OUTPUT.PUT_LINE(rec.deptno || ' : ' || rec.dname);
        
    END LOOP;
    
END;
/

 

심화실습

 CREATE TABLE DT
 
(	DT DATE);

insert into dt
select trunc(sysdate + 10) from dual union all
select trunc(sysdate + 5) from dual union all
select trunc(sysdate) from dual union all
select trunc(sysdate - 5) from dual union all
select trunc(sysdate - 10) from dual union all
select trunc(sysdate - 15) from dual union all
select trunc(sysdate - 20) from dual union all
select trunc(sysdate - 25) from dual;

commit;
SELECT *
FROM dt;

  위의 테이블 데이터들의 평균차이값을 구해라

 

DECLARE 

    TYPE dt_tab IS TABLE OF dt%ROWTYPE INDEX BY BINARY_INTEGER;
    
    v_dt_tab dt_tab;
    
    v_sum number(5) := 0;
    
    v_avg number(5) := 0;
    
BEGIN

    SELECT * BULK COLLECT INTO v_dt_tab
    
    FROM dt;

    FOR i IN 1..(v_dt_tab.count-1) LOOP
    
        v_sum := v_sum + TO_NUMBER(TO_CHAR(v_dt_tab(i).dt - v_dt_tab(i+1).dt));
        
        DBMS_OUTPUT.PUT_LINE(v_sum);
        
    END LOOP;
    
    v_avg := v_sum/(v_dt_tab.count-1);
    
    DBMS_OUTPUT.PUT_LINE(v_avg || ' , '|| v_sum);
    
END;
/

 

위의 쿼리를 분석함수를 써서 SQL로 작성

SELECT avg(gap)

from

(

SELECT DT, lead_dt, dt-lead_dt GAP

FROM

(

SELECT dt, 

        LEAD(DT) OVER(ORDER BY DT DESC) lead_dt
        
FROM dt));

SELECT (MAX(dt) - MIN(dt)) / (COUNT(dt)-1) avg

FROM dt;