[오라클로 배우는 데이터베이스 입문] 18. 커서와 예외처리

커서

커서(cursor)는 select문 또는 DML같은 sql문을 실행했을때 sql문을 처리하는 정보를 저장한 메모리공간을 의미한다.

커서를 사용하면 sql문의 결과값을 사용할수있다.
커서는 사용 방법에 따라 명시적커서와 묵시적커서로 나눈다.

SELECT INTO

커서를 사용하기앞서 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;
/

SELECT INTO문은 SELECT절에 명시한 각 열의 결과 값을 변수에 대입.
SELECT절과 INTO절에 명시한 변수의 수가 일치해야한다.

명시적 커서

명시적 커서는 사용자가 직접 커서를 선언하고 사용하는커서다.
명시적 커서는 다음과같은단계를 거친다.

• 커서 선언(declare) : 사용자가 직접 이름을 지정하여 사용할 커서를 sql문과 함께 선언
• 커서 열기(open) : 커서를 선언 할 때 작성한 sql문을 실행.
• 커서에서 읽어온 데이터 사용(fetch) : 실행된 sql문의 결과 행 정보를 하나씩 읽어와서 변수에 저장한 후 필요한 작업 수행
• 커서 닫기(close) : 커서 종료

단일행 데이터를 저장하는 커서

DECLARE
    V_DEPT_ROW DEPT%ROWTYPE;
    
    CURSOR c1 IS
        SELECT DEPTNO, DNAME,LOC
        FROM DEPT
        WHERE DEPTNO = 40;
BEGIN
    OPEN c1;
    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 c1;
END;
/

여러 행의 데이터를 커서에 저장하여 사용하기

DECLARE
    V_DEPT_ROW DEPT%ROWTYPE;
    
    CURSOR c1 IS
        SELECT DEPTNO, DNAME, LOC
        FROM DEPT;
BEGIN
    OPEN c1;
LOOP
    FETCH c1 INTO V_DEPT_ROW;
    EXIT WHEN C1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('DEPTNO : '|| V_DEPT_ROW.DEPTNO || 'DNAME : '|| V_DEPT_ROW.DNAME || 'LOC : '|| V_DEPT_ROW.LOC);
END LOOP;
    CLOSE c1;
END;
/

for문 활용

DECLARE
    CURSOR c1 IS
    SELECT DEPTNO, DNAME, LOC
    FROM DEPT;
    
BEGIN
    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;
/

커서에 파라미터를 넣어 사용하기

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('10번 부서 -> '|| 'DEPTNO : '|| V_DEPT_ROW.DEPTNO || ', DNAME : '|| V_DEPT_ROW.DNAME || ', LOC : '|| V_DEPT_ROW.LOC);
    END LOOP;
END;
/

파라미터를 전달해 원하는 값만 추출할수있다.

묵시적 커서

묵시적 커서는 별다른 선언 없이 sql문을 사용했을때 자동으로 선언되는 커서를 의미.

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

예외처리

오류가 발생할때 exception명령어로 프로그램이 비정상적으로 종료되지않고 에러를 예외처리할 수 있다.

사전에 정의된 예외 사용하기.

DECLARE
    V_WRONG NUMBER;
BEGIN
    SELECT DNAME INTO V_WRONG
    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;
/

에러코드를 포함하여 예외처리하기

DECLARE
    V_WRONG NUMBER;
BEGIN
    SELECT DNAME INTO V_WRONG
    FROM DEPT
    WHERE DEPTNO = 10;
    
    DBMS_OUTPUT.PUT_LINE('예외발생시 이문장은 실행되지않습니다.');
    
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('예외처리 : 사전 정의 외 오류 발생');
        DBMS_OUTPUT.PUT_LINE('SQLCODE : '|| TO_CHAR(SQLCODE));
        DBMS_OUTPUT.PUT_LINE('SQLERRM : '|| SQLERRM);
END;
/