[Oracle] - 커서와 예외처리

* 커서

: SQL문을 실행했을 때 해당 SQL문 처리 정보를 저장한 메모리 공간으로 SELECT문의 결과 행 별로 특정 작업 수행한다.

명시적(explicit) 커서와 묵시적(implicit) 커서로 나뉜다.

 

- SELECT INTO 방식

: 조회되는 행이 하나 일 때 쓰는 방식으로 커서는 조회되는 행의 수와 상관없이 사용가능하다.

 

<예시>

DECLARE 
    V_DEPT_ROW DEPT%ROWTYPE;
BEGIN
    SELECT DEPTNO, DNAME, LOC
      INTO V_DEPT_ROW
      FROM DEPT
     WHERE DEPTNO = 40;
     
     DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || V_DEPT_ROW.DEPTNO);
     DBMS_OUTPUT.PUT_LINE('DNAME : ' || V_DEPT_ROW.DNAME);
     DBMS_OUTPUT.PUT_LINE('LOC : ' || V_DEPT_ROW.LOC);
END;
/

 

 

---

 

 

- 명시적 커서

: 직접 커서 선언해서 사용

 

• 하나의 행만 조회되는 경우
• 여러 행이 조회되는 경우
  • LOOP
  •  FOR LOOP

  

• 커서에 파라미터 사용

 

<예시>

- 하나의 행 조회되는 경우

DECLARE
    -- 커서 데이터를 입력할 변수 선언 
    V_DEPT_ROW DEPT%ROWTYPE;
    -- 명시적 커서 선언(Declaration)
    CURSOR C1 IS
        SELECT DEPTNO, DNAME, LOC
          FROM DEPT
         WHERE DEPTNO = 40;
BEGIN
    -- 커서 열기 (OPEN)
    OPEN C1;
    
    -- 커서로부터 읽어온 데이터 사용(FETCH)
    FETCH C1 INTO V_DEPT_ROW;
    DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || V_DEPT_ROW.DEPTNO );
    DBMS_OUTPUT.PUT_LINE('DNAME : ' || V_DEPT_ROW.DNAME );
    DBMS_OUTPUT.PUT_LINE('LOC : ' || V_DEPT_ROW.LOC );
    
    -- 커서 닫기(CLOSE)
    CLOSE C1;
END;
/

 

- 여러 행 조회되는 경우(LOOP문)

DECLARE 
    -- 변수 선언
    V_DEPT_ROW DEPT%ROWTYPE;
    -- 1. 커서 선언
    CURSOR C1 IS 
        SELECT DEPTNO, DNAME, LOC
          FROM DEPT;
BEGIN
    -- 2. 커서 열기
    OPEN C1;
    
    LOOP
        -- 커서로부터 읽어온 데이터 사용(FETCH)
        FETCH C1 INTO V_DEPT_ROW;
        -- 커서의 모든 행을 읽어온 후 빠지려면 %NOTFOUND 
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE (
           'DEPTNO : '  || V_DEPT_ROW.DEPTNO ||  
           ', DNMAE : ' || V_DEPT_ROW.DNAME  ||
           ', LOC : '   || V_DEPT_ROW.LOC       );
    END LOOP;
    -- 4. 커서 닫기
    CLOSE C1;
END;
/

 

- 여러 행 조회되는 경우(FOR LOOP문) => 일반적으로 이 방법을 제일 많이 씀

DECLARE
    --커서 선언
    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;
/

 

- 파라미터 사용하는 커서(LOOP문)

DECLARE
    -- 커서 데이터를 입력받을 변수 선언
    V_DEPT_ROW DEPT%ROWTYPE;
    -- 파라미터 있는 커서 선언
    CURSOR C1 (P_DEPTNO DEPT.DEPTNO%TYPE) IS
        SELECT DEPTNO, DNAME, LOC
          FROM DEPT
         WHERE DEPTNO = P_DEPTNO;
BEGIN
    OPEN C1(10);
    LOOP
        FETCH C1 INTO V_DEPT_ROW;
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(' 부서번호 : ' || V_DEPT_ROW.DEPTNO
                        ||   ', 부서명 : ' || V_DEPT_ROW.DNAME
                        ||   ', 위치 : ' || V_DEPT_ROW.LOC);
    END LOOP;
    CLOSE C1;
END;

 

- 파라미터 사용하는 커서(FOR LOOP문)

DECLARE
    -- 커서 데이터를 입력받을 변수 선언
    V_DEPTNO DEPT.DEPTNO%TYPE;
    -- 파라미터 있는 커서 선언
    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; --입력창 팝업 데이터 받아들이는 변수 &INPUT_DEPTNO
    
    FOR c1_rec IN C1(V_DEPTNO) LOOP  
        DBMS_OUTPUT.PUT_LINE(' 부서번호 : ' || c1_rec.DEPTNO
                        ||   ', 부서명 : ' || c1_rec.DNAME
                        ||   ', 위치 : ' || c1_rec.LOC);
    END LOOP;
END;
/

 

 

 

---

 

 

 

- 묵시적 커서

: SQL문 사용했을 떄 자동으로 선언되는 커서로 OPEN, FETCH, CLOSE 지정이 없다.

 

 

<예시>

BEGIN
    UPDATE DEPT SET DNAME = 'DATABASE'
     WHERE DEPTNO = 50;
     
    DBMS_OUTPUT.PUT_LINE('갱신된 행의 수 : ' || SQL%ROWCOUNT);
    -- 영향받은 데이터 유무 
    IF(SQL%FOUND) THEN -- 업데이트 된 행 있어?
        DBMS_OUTPUT.PUT_LINE('갱신여부 : TRUE');
    ELSE
        DBMS_OUTPUT.PUT_LINE('갱신여부 : FALSE');
    END IF;
    -- 묵시적 커서 OPEN 유무
    IF(SQL%ISOPEN) THEN     
        DBMS_OUTPUT.PUT_LINE('커서 오픈여부 : TRUE');
    ELSE
        DBMS_OUTPUT.PUT_LINE('커서 오픈여부 : FALSE');
    END IF;
    
END;

 

 

 

 

---

 

 

 

 

* 예외 처리

- 오류란?

: 컴파일 오류 혹은 문법 오류  / 런타임 오류 혹은 실행 오류 - 예외

 

<예시>

 

- 예외처리 

DECLARE
    --변수 선언
    v_wrong NUMBER;
BEGIN
    SELECT DNAME --VARCHAR2
      INTO v_wrong -- NUMBER
      FROM DEPT
     WHERE DEPTNO = 10;
     
     DBMS_OUTPUT.PUT_LINE('예외가 발생하면 다음 문장은 실행되지 않습니다.');
     EXCEPTION
        WHEN VALUE_ERROR THEN
            DBMS_OUTPUT.PUT_LINE('예외처리 : 수치 또는 값 오류 발생');
            
END;
/

 

 

- 예외 종류

 

 

- 예외 처리부 작성

• 오류 처리부가 잘 작성되어 있다면 오류가 발생해도 PL/SQL은 정상종료됨

=> WHEN으로 시작하는 절은 예외 핸들러(exception handler)

 

 

<예시>

 

- 사전 정의된 예외 사용

DECLARE
    --변수 선언
    v_wrong NUMBER;
BEGIN
    SELECT DNAME --VARCHAR2
      INTO v_wrong -- NUMBER
      FROM DEPT
     WHERE DEPTNO = 10;
     
     DBMS_OUTPUT.PUT_LINE('예외가 발생하면 다음 문장은 실행되지 않습니다.');
     EXCEPTION
          WHEN TOO_MANY_ROWS THEN
            DBMS_OUTPUT.PUT_LINE('*예외처리 : 요구보다 많은 행 추출되는 오류 발생');
          WHEN VALUE_ERROR THEN 
            DBMS_OUTPUT.PUT_LINE('예외처리: 수치 또는 값 오류 발생');
          WHEN OTHERS THEN 
            DBMS_OUTPUT.PUT_LINE('예외처리: 사전 정의 외 오류 발생');
END;
/

 

 

    - 이름 없는 예외 사용

=> 이름을 직접 지정 지정

=> 선언부에서 오라클 예외번호와 함께 이름을 붙임

 

 

---

 

 

    - 사용자 정의 예외 사용

=> 오라클에 정의되어있지 않은 특정 상황을 직접 오류로 정의하는 방식

=> RAISE 키워드로 예외를 직접 만들 수 있다.

 

 

 

---

 

 

    - 오류코드와 오류 메세지 사용

: 오류 처리부가 작성되어 있어 오류 내역을 알고 싶을 때 사용하는 함수

 

- 오류 코드와 오류 메시지 사용하기

DECLARE
    --변수 선언
    v_wrong NUMBER;
BEGIN
    SELECT DNAME --VARCHAR2
      INTO v_wrong -- NUMBER
      FROM DEPT
     WHERE DEPTNO = 10;
     
     DBMS_OUTPUT.PUT_LINE('예외가 발생하면 다음 문장은 실행되지 않습니다.');
     EXCEPTION
          WHEN TOO_MANY_ROWS THEN
            DBMS_OUTPUT.PUT_LINE('*예외처리 : 요구보다 많은 행 추출되는 오류 발생');
          WHEN OTHERS THEN 
            DBMS_OUTPUT.PUT_LINE('예외처리: 사전 정의 외 오류 발생');
            DBMS_OUTPUT.PUT_LINE('SQLCODE : ' || TO_CHAR(SQLCODE));
            DBMS_OUTPUT.PUT_LINE('SQLERRM : ' || SQLERRM);
END;
/