[ORACLE] 커서(CURSOR) 2221201-01

 

2. 커서(CURSOR)

 - 넓은 의미의 커서는 SQL명령의 영향을 받은 행들의 집합이고, 좁은의미로는 SELECT문의 결과 집합
   을 의미
 - 묵시적 커서(IMPLICITE CURSOR)와 명시적 커서(EXPLICITE CURSOR)
 - 커서는 Query의 결과에 대하여 수정,삭제,검색 할 수 있도록 해주는 도구
 - 개발자가 쿼리의 결과를 PL/SQL블록에서 수동으로 제한할 수 있도록 해줌
 - 커서의 사용 단계
   . 생성(선언영역) => OPEN (실행영역) => FETCH (실행영역 반복문 내부) => CLOSE (실행영역)
   -- OPEN,CLOSE는 한 번 사용, OPEN을 했으면 꼭 CLOSE까지 해주어야 함

 1)묵시적 커서 
   . 이름이 없는 커서로 일반적인 SELECT문의 결과 집합이 대표적인 묵시적 커서
   . 묵시적 커서는 결과가 출력되는 순간 OPEN 되고 출력이 완료되면 자동으로 CLOSE됨
     =>커서 내부에 접근이 불가능

❗커서속성

----------------------------------------------------------------------------------
     속성            내용
   ----------------------------------------------------------------------------------
    SQL%ISOPEN     커서가 OPEN 상태이면 true 반환(묵시적 커서는 항상 false) --커서를 만들 때 SQL 대신에 커서 이름을 넣으면 됨
    SQL%NOTFOUND   커서 내부에 FETCH할 자료가 있으면 false, 없으면 true
    SQL%FOUND      커서 내부에 FETCH할 자료가 없으면 false, 있으면 true  
    SQL%ROWCOUNT   커서 내부의 행의 갯수(결과의 수)

2)명시적 커서
    .선언영역에서 이름을 부여하여 선언한 커서

3)선언형식

CURSOR 커서명 [(변수명 타입명,...)] IS
     SELECT 문;

    . '(변수명 타입명,...)' : 커서에서 사용될 자료를 전달받는 매개변수로 타입명 기술시 크기를
      지정하지 않음
    . 매개변수에 값은 OPEN문에서 설정 함 
      
✔사용예)부서번호를 키보드로 입력 받아 해당 부서에 근무하는 직원들의 사원번호,사원명,입사일,직무코드를
               출력하시오.

 ACCEPT P_DCODE PROMPT '부서번호 입력(10~110) : ' -- ACCEPT 변수명 PROMPT 입력창 문구( ; 사용 X)
  DECLARE -- SELECT문에서 가져오는 것들은 변수가 필요
    L_EID HR.EMPLOYEES.EMPLOYEE_ID%TYPE; --데이터 타입이나 크기를 잘 모르 때 테이블 명으로 작성 
    L_ENAME VARCHAR2(80);
    L_HDATE DATE;
    L_JOB_ID HR.JOBS.JOB_ID%TYPE; 
    CURSOR CUR_EMP_NAME(DID HR.DEPARTMENTS.DEPARTMENT_ID%TYPE) IS -- OPEN 문의 변수는 ACCEPT 변수다
      SELECT EMPLOYEE_ID,EMP_NAME,HIRE_DATE,JOB_ID
        FROM HR.EMPLOYEES
       WHERE DEPARTMENT_ID = DID; 
  BEGIN
  
  END;

 4) OPEN문
  - 커서를 사용하기 위한 명령

 (사용형식)
   OPEN 커서명[(값1,...)]

. '(값1,...)' : 커서 선언문에 사용된 매개변수에 전달될 값을 기술

 

✔사용예)

 ACCEPT P_DCODE PROMPT '부서번호 입력(10~110) : ' 
  DECLARE 
    L_EID HR.EMPLOYEES.EMPLOYEE_ID%TYPE; 
    L_ENAME VARCHAR2(80);
    L_HDATE DATE;
    L_JOB_ID HR.JOBS.JOB_ID%TYPE; 
    CURSOR CUR_EMP_NAME(DID HR.DEPARTMENTS.DEPARTMENT_ID%TYPE) IS 
      SELECT EMPLOYEE_ID,EMP_NAME,HIRE_DATE,JOB_ID
        FROM HR.EMPLOYEES
       WHERE DEPARTMENT_ID = DID; 
  BEGIN
   OPEN CUR_EMP_NAME(TO_NUMBER('&P_DCODE')); -- OPEN 커서명 + OPEN 문의 변수는 ACCEPT 변수다
  END;

5)FETCH
  . 커서 내의 자료를 읽어 블록에 가져오는 명령
  . 보통 반복문 내부에 기술

(사용형식)
  FETCH 커서명 INTO 변수 list;

- 커서 SELECT 문의 SELECT절에 기술된 컬럼들을 차례대로 '변수list'에 할당
- 커서 속성을 이용하여 반복문을 벗어날 수 있음

✔사용예)

 ACCEPT P_DCODE PROMPT '부서번호 입력(10~110) : ' 
  DECLARE 
    L_EID HR.EMPLOYEES.EMPLOYEE_ID%TYPE; 
    L_ENAME VARCHAR2(80);
    L_HDATE DATE;
    L_JOB_ID HR.JOBS.JOB_ID%TYPE; 
    CURSOR CUR_EMP_NAME(DID HR.DEPARTMENTS.DEPARTMENT_ID%TYPE) IS 
      SELECT EMPLOYEE_ID,EMP_NAME,HIRE_DATE,JOB_ID
        FROM HR.EMPLOYEES
       WHERE DEPARTMENT_ID = DID; 
  BEGIN
   OPEN CUR_EMP_NAME(TO_NUMBER('&P_DCODE')); -- OPEN 커서명 + OPEN 문의 변수는 ACCEPT 변수다
   DBMS_OUTPUT.PUT_LINE('부서번호 : '||'&P_DCODE');
   DBMS_OUTPUT.PUT_LINE('================');
   LOOP 
     FETCH CUR_EMP_NAME INTO L_EID,L_ENAME,L_HDATE,L_JOB_ID;
     EXIT WHEN CUR_EMP_NAME%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE('사원번호 : '||L_EID);
     DBMS_OUTPUT.PUT_LINE('사원명 : '||L_ENAME);
     DBMS_OUTPUT.PUT_LINE('입사일 : '||L_HDATE);
     DBMS_OUTPUT.PUT_LINE('직무코드 : '||L_JOB_ID);
     DBMS_OUTPUT.PUT_LINE('-------------------------------------');
  END LOOP; 
     DBMS_OUTPUT.PUT_LINE('&P_DCODE'||'번부서 직원 수 : '||CUR_EMP_NAME%ROWCOUNT||'명');
     DBMS_OUTPUT.PUT_LINE('  ');
 END;

6) CLOSE
  . OPEN된 커서는 CLOSE되어야 다시 OPEN 될 수 있음

 (사용형식)
   CLOSE 커서명;