45_[db]커서와 예외처리

6 minute read

커서와 예외처리

🌺 커서란?

SQL 문을 처리하는 정보를 저장한 메모리 공간(Private SQL Area)에서의 포인터(즉, 해당 SQL문에 대한 위치 정보를 담고 있는 것)

커서

1. SELECT INTO 방식

이전에 참조형 변수에서 살펴본 적 있었는데 그 기억과 연계해보자!

커서 vs SELECT INTO

SELECT 절에 명시한 열의 자료형, 갯수 == INTO 절에 명시한 변수의 자료형, 갯수

SELECT 열1, ...,열N INTO 변수1,...,변수N --실행순서5
FROM 테이블명--실행순서1
[WHERE절]--실행순서2
[GROUP BY 절]--실행순서3
[HAVING 절]--실행순서4
[ORDER BY 절]--실행순서6

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;
/

위의 경우는, 한 행 전체를 참조하였고, 그 참조한 행을 담은 변수에 SELECT INTO를 이용해서 데이터 정보를 저장해주었다!

그 결과, 아래와 같이 DEPTNO가 40인 경우의 DEPTNO와 DNAME, LOC을 확인해볼 수 있었다

DEPTNO: 40 DNAME: OPERATIONS LOC :BOSTON

🌟 데이터 조회 결과값은 여러 행일 경우가 흔하고, 그리고 결과 행이 하나일지 여럿일지는 알 수 없는 경우도 존재하기 때문에 대부분 커서를 사용하는 것이 권장된다!

2. 커서의 종류

🌺 명시적 커서

사용자가 직접 커서를 선언하고 사용하는 커서

[커서 사용 방법]

커서 선언(declaration) - 사용자가 직접 이름을 지정하여 사용할 커서를 SQL문과 함께 선언
커서 열기(open) - 커서를 선언할 때 작성한 SQL문을 실행(* active set: 실행한 SQL문에 영향을 받는 행)
커서에서 읽어온 데이터 사용(fetch) - 실행된 SQL문의 결과 행 정보를 하나씩 읽어와서 변수에 저장한 후 필요한 작업을 수행함. 각 행별로 공통 작업을 반복해서 실행하기 위해 여러 종류의 LOOP 문을 함께 사용 가능
커서 닫기(close) - 모든 행의 사용이 끝나고 커서를 종료

[USAGE]

DECLARE
	CURSOR 커서이름 IS SQL문; --커서 선언
BEGIN
	OPEN   커서이름; --커서 열기(open)
	FETCH  커서이름 INTO 변수; --커서로부터 읽어온 데이터 사용(FETCH)
  CLOSE  커서이름; --커서 닫기
END;
/

(1) 단일 행이 조회되는 경우

DECLARE
    V_DEPT_ROW DEPT%ROWTYPE;
    CURSOR CUR1 IS
    SELECT DEPTNO, DNAME, LOC
    FROM DEPT
    WHERE DEPTNO=40;

BEGIN
    OPEN CUR1;
    FETCH CUR1 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 CUR1;   
END;
/

위와 같이, 단일 행이 조회되는 경우에는 굳이 LOOP를 사용하지 않아도 되고, 커서를 변수에 담아서 사용하고, 다 사용한 후에는 닫아주면 된다

명시적 커서- 단일행이 조회되는 경우

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

DECLARE
    V_DEPT_ROW DEPT%ROWTYPE;
    CURSOR CUR2 IS
    SELECT DEPTNO, DNAME, LOC
    FROM DEPT;
    
BEGIN
    OPEN CUR2;
    LOOP
        FETCH CUR2 INTO V_DEPT_ROW;
        --커서의 모든 행을 읽어오기 위해서 %NOTFOUND 속성 지정
        📌**EXIT WHEN CUR2%NOTFOUND;**📌
    DBMS_OUTPUT.PUT_LINE('DEPTNO: '||V_DEPT_ROW.DEPTNO||' | DNAME: ' ||V_DEPT_ROW.DNAME||
                                           ' | LOC :' || V_DEPT_ROW.LOC); 
    END LOOP;
    
    CLOSE CUR2;
END;
/

만약, 여러 행이 조회한다면, 변수에 커서를 담고, 그 후에는 LOOP로 반복작업을 시키되, 위와 같이 더이상 추출할 수 있는 데이터가 없으면 빠져나가도록 하고, 아니라면 해당 주기에서의 데이터를 반환해주도록 한다. 그리고 마침내 데이터가 더이상 없으면 LOOP를 종료하고, 커서도 반환한다

명시적 커서- 여러행이 조회되는 경우

아래는 커서를 fetch를 통해서 데이터를 획득하는 과정 중의 상태와 관련된 몇 가지 속성들이다

커서의 속성

(3) 여러 개의 행이 조회되는 경우- FOR LOOP 문 이용

FOR 루프 인덱스 이름 IN 커서 이름 LOOP
	결과 행별로 반복 수행할 작업;
END LOOP;

*루프 인덱스 : 커서에 저장된 각 행이 저장되는 변수 ▶️ 루프인덱스.필드명 으로 각 행의 필드에 접근 가능 ▶️ FETCH 커서 INTO 변수를 하지 않아도 됨!!

🌟 FOR LOOP를 커서와 함께 사용하면 좋은 점!(여러 행 조회시)

OPEN, FETCH, CLOSE문을 작성하지 않아도 됨!(일련의 자동수행)

DECLARE
    --명시적 커서 선언
    CURSOR CUR1 IS
    SELECT DEPTNO, DNAME, LOC
    FROM DEPT;
BEGIN
    FOR CIDX IN CUR1 LOOP
      DBMS_OUTPUT.PUT_LINE('DEPTNO: '  ||CIDX.DEPTNO||' , DNAME: '||CIDX.DNAME||' , LOC: ' ||CIDX.LOC);
    END LOOP;
END;
/

명시적 커서-여러 행 데이터가 조회될 때 FOR LOOP를 이용하는 경우

위와 같이 FOR LOOP를 이용하게 되면 코드가 간결해질 수 있다!

(4) 커서에 파라미터 사용하기

고정 값이 아닌 직접 입력한 값 또는 상황에 따라 여러 값을 번갈아서 사용할 경우에 사용

CURSOR 커서이름(파라미터 이름  자료형,...) IS
SELECT ~~

DECLARE
    V_DEPT_ROW DEPT%ROWTYPE;
    CURSOR CUR1(PARA DEPT.DEPTNO%TYPE) IS
        SELECT DEPTNO, DNAME, LOC
        FROM DEPT
        WHERE DEPTNO = PARA;
BEGIN
    OPEN CUR1(30);
    LOOP
        FETCH CUR1 INTO V_DEPT_ROW;
        EXIT WHEN CUR1%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('DEPTNO: '||V_DEPT_ROW.DEPTNO||', DNAME: '||V_DEPT_ROW.DNAME||', LOC: '||V_DEPT_ROW.LOC);
    END LOOP;
    CLOSE CUR1;
    OPEN CUR1(20);
    LOOP
        FETCH CUR1 INTO V_DEPT_ROW;
        EXIT WHEN CUR1%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('DEPTNO: '||V_DEPT_ROW.DEPTNO||', DNAME: '||V_DEPT_ROW.DNAME||', LOC: '||V_DEPT_ROW.LOC);
    END LOOP;
    CLOSE CUR1;
END;
/

커서 실행에 필요한 파라미터 값을 사용자에게 직접 입력받고 싶은 경우

&기호를 치환 변수를 사용할 수 있다!

DECLARE
    --사용자가 입력한 부서번호를 저장할 변수
    V_DEPTNO DEPT.DEPTNO%TYPE;
    CURSOR CUR1(PARA DEPT.DEPTNO%TYPE) IS
    SELECT DEPTNO,DNAME,LOC
    FROM DEPT
    WHERE DEPTNO=V_DEPTNO;

BEGIN
    --입력을 위한 임시 변수 역할인 치환변수 INPUT_DEPTNO를 잠시 사용
    V_DEPTNO :=&INPUT_DEPTNO;
    FOR CIDX IN CUR1(V_DEPTNO) LOOP
        DBMS_OUTPUT.PUT_LINE('DEPTNO: ' || CIDX.DEPTNO||', DNAME: ' ||CIDX.DNAME||', LOC: '||CIDX.LOC);
    END LOOP;
END;
/

위와 같이 FOR LOOP를 이용하게 되면 , 앞서 연습해본 것처럼 OPEN, FETCH, CLOSE의 번거로움이 줄어들 수 있다! 그리고 입력을 받는 & 기호와 치환 변수를 이용함으로써 보다 동적인 실행을 해볼 수 있다!

명시적 커서- 파라미터를 입력받기

🌺 묵시적 커서

별다른 선언 없이 SQL 문을 사용했을 때 오라클에서 자동으로 선언되는 커서

▶️ OPEN, FETCH, CLOSE를 지정하지 않아도 됨!

묵시적 커서의 속성

BEGIN
    UPDATE DEPT_IMP
    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;
    
    IF(SQL%ISOPEN) THEN
        DBMS_OUTPUT.PUT_LINE('커서의 OPEN 여부 : TRUE');
    ELSE
        DBMS_OUTPUT.PUT_LINE('커서의 OPEN 여부: FALSE');
    END IF;
    
END;
/

위의 경우는 묵시적 커서를 통해서 사용할 수 있는 속성을 보여주는 예시이다!

위와 같이 DEPTNO가 50인 경우에 대해서 갱신하고자 한다면, DEPTNO가 50인 경우는 존재하지 않기 때문에 FOUND는 FALSE를 반환하게 되고, 갱신된 행이 없으므로 ROWCOUNT도 0을 반환하게 된다. 그리고 묵시적 커서는 자동 실행된 후 CLOSE되므로 FALSE를 반환하는 것을 확인해볼 수 있다.

갱신된 행 수 : 0 갱신 대상 행 존재 여부: FALSE 커서의 OPEN 여부: FALSE

예외처리

🌹 오류(error) : SQL 또는 PL/SQL 문이 정상 수행되지 못하는 상황

[오류의 분류]

컴파일 오류 Compile Error 혹은 문법 오류 Syntax Error : 문법이 잘못되었거나 오타로 인한 오류로 발생
런타임 오류 Runtime Error 혹은 실행 오류 Execute Error : 명령문 실행 중 발생한 오류 ▶️ “예외”

🌺 예외 처리 : PL/SQL 실행 중 예외가 발생했을 때 프로그램의 비정상 종료를 막기 위해서 특정 명령어를 작성하는 행위

[USAGE]

DECLARE
	~~
BEGIN
	예외발생
EXCEPTION
	WHEN 예외종류1[OR 예외이름2] THEN
		예외처리
	WHEN 예외종류3[OR 예외이름4] THEN
		예외처리
  ...
	WHEN OTHERS THEN
		예외처리
END;
/

위에서처럼 EXCEPTION 키워드 뒤에 나온 부분을 예외 처리부 혹은 예외 처리절이라고 한다!

WHEN 절은 “예외 핸들러(Exception Handler)”!

🌟 예외 처리부가 실행되면, 예외가 발생한 코드 이후의 내용은 실행되지 않음!

예외 종류

내부 예외 internal exceptions : 오라클에서 미리 정의한 예외
사용자 정의 예외 user-defined exceptions : 사용자가 필요에 따라 추가로 정의한 예외

예외 분류/

사전 정의된 예외의 종류

https://docs.oracle.com/cd/E11882_01/timesten.112/e21639/exceptions.htm#TTPLS195

사전 정의된 예외는 위와 같이 예외 이름을 지정해주고, 그 예외를 WHEN 절에서 명시해주고 예외처리를 해줌으로써 프로그램의 비정상적인 종료를 막을 수 있다!

사전 정의된 예외

🌺 이름 없는 예외

PRAGMA : 특정 코드의 에러코드와 예외메시지를 연결해줌!

DECLARE 
	예외이름1 EXCEPTION;
	PRAGMA EXCEPTION_INIT(예외이름1,예외번호);
...
EXCEPTION
	WHEN 예외이름1 THEN
	예외처리;
END;
/

이름없는 예외

🌺 사용자 정의 예외 사용

[USAGE]

DECLARE
	사용자 예외 이름 EXCEPTION;
BEGIN
	IF 사용자 예외를 발생시킬 조건 THEN
	  RAISE 사용자 예외 이름
	END IF;

EXCEPTION
	WHEN 사용자 예외 이름 THEN
		예외 처리에 사용할 명령어;
END;
/

사용자 정의 예외는 이름없는 예외(non-predefined exceptions)처럼, 미리 내가 지정한 이름을 통해서 예외를 식별!

DECLARE
    MYERR EXCEPTION;
    V_WRONG NUMBER;
BEGIN
    V_WRONG :=&INPUT_DEPTNO;
    INSERT INTO DEPT_IMP
    VALUES(V_WRONG,'DEVELOPMENT','SEOUL');
    --사용자 예외 조건
    IF V_WRONG > 100 THEN
        RAISE MYERR;
    END IF;
EXCEPTION
    WHEN MYERR THEN
        DBMS_OUTPUT.PUT_LINE('사용자 정의 예외 MYERR 발생');
END;
/

사용자 정의 예외

위와 같이, 사용자 정의 예외는 특정 조건일 때 사용자가 RAISE 키워드를 이용해서 해당 오류가 발생했을 때, 예외를 정의해두었기 때문에 이에 대한 예외 처리를 할 수 있게 해준다!

🌺 오류코드와 오류 메시지 사용

SQLCODE 함수 - 오류 번호 반환
SQLERRM 함수 - 오류 메시지 반환

DECLARE
    V_WRONG VARCHAR2(80);
BEGIN
    SELECT DNAME INTO V_WRONG
    FROM DEPT_IMP
    WHERE DEPTNO=50;
    
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('현재 발생된 오류 코드: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('현재 발생된 오류 메시지: ' || SQLERRM);
END;
/

현재 발생된 오류 코드: 100 현재 발생된 오류 메시지: ORA-01403: no data found

Twitter Facebook LinkedIn

J. S. Jeong

45_[db]커서와 예외처리

커서와 예외처리

커서

1. SELECT INTO 방식

2. 커서의 종류

예외처리

예외 종류

You May Also Enjoy

1_[spring]rest api 구축에 힘을 실어주는 hateoas

2_[spring jpa]m to n 연관관계 중간 테이블을 엔티티로 관리하는 경우

1_[spring jpa]m to n 연관관계 중간 테이블을 엔티티로 관리하지 않는 경우

2_[spring]websockethandler를 이용한 웹소켓 서버 구현