오라클[sql] 커서 CURSOR

CURSOR

▶ SQL 문장을 실행 할 떄 오라클 서버측에서 명령어를 분석하고 싱행되는 곳

▶ 분석하고 실행하는 영역을 커서 : 메모리를 항당하는 작업

▶ 오라클에서 지원하는 기능인데 자동 저장되는 데이터가 1행임. : 묵시적커서
 

▶ SQL%종류

  SQL%ROWCOUNT : 최근수행한 SQL의 문장에 의해서 영향을 받은 행의 수

  SQL%FOUND    : 최근수행한 SQL의 문장이 하나 이상의 행에 영향을 주면 TRUE

  SQL%NOTFOUND : 최근수행한 SQL의 문장이 어떤행에도 영향을 주지 못하면 TRUE

  SQL%ISOPEN   : 자동으로 커서를 닫기 때문에 항상 FALSE

 

▶ DELETE > 영향을 미칠겁니다.

>> IF SQL%FOUND : 영향이 있다면 true

>> THEN SQL%ROWCOUNT 몇개의 행이 추가, 수정, 삭제 되었습니다.
 

▶ 명시적 커서 : 사용자가 직접 컨트롤하는 커서

 - SELECT 문장을 실행하면 출력되는 여러건의 결과가 집합으로 추출

  >> 각각의 개별데이터로 접근하기 위해서 커서를 사용

 

▶   1. 커서 선언

    - SELECT 한 결과를 커서에 저장

    - CURSOP 커서명 IS SELECT 문장;

▶   2. 커서 열기

    - OPEN 커서명

    - 실제 커서가 사용 할 메모리공간이 할당

▶   3. 패치
    - 커서에 담긴 값을 읽어서 변수에 할당 ( 한줄씩 + LOOP )

    - FETCH 커서명 INTO 변수

▶   4. 커서 닫기

    - CLOES 커서명;
 

예제

ACCEPT p_deptno PROMPT '부서번호: ';

DECLARE

  v_deptno emp.deptno%TYPE := &p_deptno;

  v_empno emp.empno%TYPE;

  v_ename emp.ename%TYPE;

  v_sal emp.sal%TYPE;
  -- 커서 선언

  CURSOR emp_cursor IS

    SELECT empno, ename, sal FROM emp WHERE deptno = v_deptno ORDER BY empno;

BEGIN

  -- 커서 열기

  OPEN emp_cursor;

    DBMS_OUTPUT.put_line('사번      이름      급여');

    DBMS_OUTPUT.put_line('-----------------------');

  

  LOOP

    -- 커서 패치 : 변수와 커서를 연결

    FETCH emp_cursor

      INTO v_empno, v_ename, v_sal; 

    EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.put_line(lpad(v_empno,6) || lpad(v_ename,10) || lpad(v_sal,8));

  END LOOP;

  -- 커서 닫기

  CLOSE emp_cursor;

END;

/

 

예제

>> 안됨... 다시 해보기

>> 각 업무별 평균급여와 최소, 최대급여를 

>> SELECT job, avg(sal), min(sal), max(sal) FROM emp GROUP BY job;

 

DECLARE

  v_job emp.job%TYPE;

  v_avg NUMBER;

  v_min NUMBER;

  v_max NUMBER;

  -- 커서 선언

  CURSOR emp_cursor IS

    SELECT job, ROUND(avg(sal),2), min(sal), max(sal) FROM emp GROUP BY job;

BEGIN
  -- 커서 열기

  OPEN emp_cursor;

    DBMS_OUTPUT.put_line('업무      평균급여      최소급여      최대급여');

    DBMS_OUTPUT.put_line('-----------------------------------------');

  LOOP

    -- 커서 패치 : 변수와 커서를 연결

    FETCH emp_cursor

      INTO v_job, v_avg, v_min, v_max; 

    EXIT WHEN emp_cursor%NOTFOUND;

    IF v_job <> null

      THEN DBMS_OUTPUT.put_line(lpad(v_job,10) || lpad(v_avg,10) || lpad(v_min,10) || lpad(v_max,10));

    END IF;

  END LOOP;

  -- 커서 닫기

  CLOSE emp_cursor;

END;

/