CHAPTER 18. 커서와 예외처리-DO IT!오라클로 배우는 데이터베이스 입문
CHAPTER 18. 커서와 예외처리
18-1 특정 열을 선택하여 처리하는 커서
커서란?
커서는 SELECT문 또는 데이터 조작어 같은 SQL문을 실행했을 때 해당 SQL문을 처리하는 정보를 저장한 메모리 공간을 뜻함
커서를 사용하면 실행된 SQL문의 결과 값을 사용 할 수 있음.
SELECT문의 결과 값이 여러행으로 나왔을 떄 각 행별로 특정 작업을 수행 하도록 기능을 구현하는 것이 가능
커서는 사용 방법에 따라 명시적커서와 묵시적 커서로 나윔
SELECT INTO방식
SELECT INTO문은 조회되는 데이터가 단 하나의 행일 때 가능
커서는 결과 행이 하나이든 여러 개이든 상관없이 사용 가능
SELECT INTO문은 SELECT절에 명시한 각 열의 결과 값을 변수에 대입해 줌.
SELECT절에 명시한 각 열과 INTO절에 명시한 변수는 그 개수와 자료형이 일치해야 함
결과 행이 하나일지 여러개일지 알수 없는 경우도 존재하므로 대부분 커서를 활용
명시적 커서
명시적 커서는 사용자가 직접 커서를 선언하고 사용하는 커서
단계 명칭 설명
| 1단계 | 커서 선언(DECLATION) | 사용자가 직접 이름을 지정하여 사용할 커서를 SQL문과 함께 선언 |
| 2단계 | 커서 열기(OPEN) | 커서를 선언할 때 작성한 SQL문을 실행.이때 실행한 SQL문에 영향을 받은 행을 ACTIVE SET이라고 함 |
| 3단계 | 커서에서 읽어온 데이터 사용(FETCH) | 실행된 SQL문의 결과 행 정보를 하나씩 읽어 와서 변수에 저장한 후 필요한 작업을 수행. 각행별로 공통 작업을 반복해서 실행 하기 위해 여러 종류의 LOOP문을 함께 사용 |
| 4단계 | 커서 닫기(CLOSE) | 모든 행의 사용이 끝나고 커서를 종료 |
명시적 커서를 작성하는 방법
DECLARE
CURSOR 커서이름 IS SQL문; --커서 선언
변수이름 테이블명%ROWTYPE;
BEGIN
OPEN 커서이름;
FETCH 커서이름 INTO 변수;
CLOSE 커서이름;
END;
명시적 커서 사용
SET SERVEROUTPUT ON
DECLARE
CURSOR emp_cur IS
SELECT *
FROM emp
WHERE deptno = 10 ;
emp_rec EMP_CUR%ROWTYPE ;
BEGIN
OPEN emp_cur ;
FETCH emp_cur INTO emp_rec ;
DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;
FETCH emp_cur INTO emp_rec ;
DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;
CLOSE emp_cur ;
END ;
/
DECLARE
CURSOR emp_cur IS
SELECT * FROM emp WHERE deptno = 10 ;
emp_rec emp%ROWTYPE ;
BEGIN
IF NOT EMP_CUR%ISOPEN THEN
OPEN emp_cur ;
END IF ;
LOOP
FETCH emp_cur INTO emp_rec ;
EXIT WHEN emp_cur%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;
END LOOP ;
CLOSE emp_cur ;
END ;
/
DECLARE
CURSOR emp_cur IS
SELECT * FROM emp WHERE deptno = 10 ;
BEGIN
FOR emp_rec IN emp_cur LOOP
DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;
END LOOP ;
END ;
/
BEGIN
FOR emp_rec IN ( SELECT *
FROM emp
WHERE deptno = 10 ) LOOP
DBMS_OUTPUT.PUT_LINE ( emp_rec.empno || ' ' || emp_rec.ename ) ;
END LOOP ;
END ;
/
하나의 행만 조회 되는 경우 커서를 사용하는 것은 비효율적
커서의 효용성은 조회 되는 행이 여러 개일 때 극대화
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;
-- 명시적 커서 선언(Declaration)
CURSOR c1 IS
SELECT DEPTNO, DNAME, LOC
FROM DEPT;
BEGIN
-- 커서 열기(Open)
OPEN c1;
LOOP
-- 커서로부터 읽어온 데이터 사용(Fetch)
FETCH c1 INTO V_DEPT_ROW;
-- 커서의 모든 행을 읽어오기 위해 %NOTFOUND 속성 지정
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)
CLOSE c1;
END;
/
%NOTFOUND : 실행된 FETCH문에서 행을 추출했으면 FALSE, 추출하지 않았으면 TRUE를 반환
∴ FETCH문을 통해 더 이상 추출한 데이터가 없을 경우 LOOP반복을 종료
속성 설명
| 커서 이름%NOTFOUND | 수행된 FETCH문을 통해 추출된 행이 있으면 FALSE, 없으면 TRUE를 반환 |
| 커서 이름%FOUND | 수행된 FETCH문을 통해 추출된 행이 있으면 TRUE, 없으면 FALSE를 반환 |
| 커서 이름%ROWCOUNT | 현재까지 추출된 행 수를 반환 |
| 커서 이름%ISOPEN | 커서가 열려(OPEN)있으면 TRUE, 닫혀(CLOSE)있으면 FALSE를 반환 |
SQL문장의 처리 과정
- PARSE : 구문 분석 - 실행 계획 확보
- 문법 확인(매번 확인)
- FALSE:실행 계획 생성/ 커서에 저장
- -IF 실행 계획 유/무 (동일 문장 체크)
- BIND : 바인드 변수 사용 시 값 입력
- EXECUTE : 실행
- FETCH : 인출 (SELECT 명령문만!!)
SELECT * FROM EMP WHERE DEPTNO = 10 ; --일일이 저장하면 메모리 낭비
SELECT * FROM EMP WHERE DEPTNO = 20 ;
SELECT * FROM EMP WHERE DEPTNO = :B1 ; --:호출 환경에 선언된 변수,바인드 변수
문장이 실행되려면 실행 계획이 필요함
CREATE OR REPLACE FUNCTION delete_all_rows
(p_table_name VARCHAR2) RETURN NUMBER IS
v_cur_id INTEGER;
v_rows_del NUMBER;
BEGIN
v_cur_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cur_id, 'DELETE FROM '|| p_table_name, DBMS_SQL.NATIVE) ;
v_rows_del := DBMS_SQL.EXECUTE (v_cur_id);
DBMS_SQL.CLOSE_CURSOR(v_cur_id);
RETURN v_rows_del;
END;
/
여러개의 행이 조회되는 경우
LOOP문을 사용하여 커서를 처리하는 방식은 커서 속성을 사용하여 반복 수행을 제어해야함
커서에 FOR LOOP문을 사용하면 좀 더 간편하게 여러 행을 다룰수 있음
FOR 루프 인덱스 이름 IN 커서 이름 LOOP
결과 행별로 반복 수행할 작업;
END LOOP;
루프 인덱스는 커서에 저장된 각 행이 저장되는 변수를 뜻하며 참조 연산자(.)를 통해 행의 각 필드에 접근할 수 있음
커서에 FOR LOOP문을 사용하면 OPEN,FETCH,CLOSE문을 작성하지 않음
FOR LOOP문을 통해 각 명령어를 자동으로 수행하므로 커서 사용 방법이 간단하다는 장점
DECLARE
-- 명시적 커서 선언(Declaration)
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;
/
커서 각 행을 루프 인덱스에 저장하므로 결과 행을 저장하는 변수 선언도 필요하지 않음
커서에 파라미터 사용하기
묵시적 커서
묵시적 커서는 별다른 선언없이 SQL문을 사용했을 때 오라클에서 자동으로 선언되는 커서를 뜻함
사용자가 OPEN,FETCH,CLOSE를 지정하지 않음
PL/SQL문 내부에서 DML명령어나 SELECT INTO문 등이 실행될 때 자동으로 생성 및 처리됨.
묵시적 커서의 속성을 사용하면 현재 커서의 정보를 확인할 수 있음
커서가 자동으로 생성되므로 커서 이름을 지정하지않고 SQL키워드로 속성을 지정하며, 명시적 커서의 속성과 유사한 기능을 가짐
속성 설명
| SQL%NOTFOUND | 묵시적 커서 안에 추출된 행이 있으면 FALSE, 없으면 TRUE를 반환.DML명령어로 영향을 받는 행이 없을 경우에도 TRUE반환 |
| SQL%FOUND | 묵시적 커서 안에 추출된 행이 있으면 TRUE, 없으면 FALSE를 반환.DML명령어로 영향을 받는 행이 있을 경우 TRUE반환 |
| SQL%ROWCOUNT | 묵시적 커서에 현재까지 추출한 행 수 또는 DML명령어로 영향받는 행 수를 반환 |
| SQL%ISOPEN | 묵시적 커서는 자동으로 SQL문을 실행한 후 CLOSE되므로 이 속성은 항상 FALSE를 반환 |
묵시적 커서의 속성 사용하기
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;
/
갱신된 행의 수 : 0 갱신 대상 행 존재 여부 : false 커서의 OPEN 여부 : false
18-2 오류가 발생해도 프로그램이 비정상 종료되지 않도록 하는 예외 처리
오라클에서 SQL또는 PL/SQL이 정상 수행되지 못하는 상황을 오류(ERROR)라고 함
컴파일 오류,문법 오류 : 문법이 잘못 되었거나 오타로 인한 오류
런타임 오류, 실행 오류 : 명령문의 실행 중 발생한 오류→오라클에서는 예외(EXCEPTION)이라고 함
※ 예외 처리
ALTER TABLE EMP
ADD CONSTRAINT EMP_CK CHECK (SAL>0);
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO=10;
UPDATE EMP
SET SAL=0
WHERE EMPNO=7934;
오류 보고 - ORA-02290: 체크 제약조건(ADV121.EMP_CK)이 위배되었습니다
문자열 데이터를 숫자 자료형 변수에 대입하려고 했기 때문에 PL/SQL문은 예외가 발생하고 비정상 종료됨
DECLARE
v_wrong NUMBER;
BEGIN
SELECT DNAME INTO v_wrong
FROM DEPT
WHERE DEPTNO = 10;
END;
/
PL/SQL실행중 예외가 발생 했을 때 프로그램이 비정상 종료되는 것을 막기 위해 특정 명령어를 PL/SQL문 안에 작성하는 데 이를 ‘예외 처리’라고 함
예외처리는 PL/SQL문안에서 EXCEPTION영역에 필요 코드를 작성하는 것을 뜻함
DECLARE
v_wrong NUMBER;
BEGIN
SELECT DNAME INTO v_wrong
FROM DEPT
WHERE DEPTNO = 10;
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('예외 처리 : 수치 또는 값 오류 발생');
END;
/
EXCEPTION 키워드 뒤에 예외 처리를 위해 작성한 코드 부분을 예외 처리부 또는 예외 처리절이라고 함
예외 처리부가 실행되면 예외가 발생한 코드 이후의 내용은 실행 되지 않음
원본
SELECT empno, ename, sal
FROM emp
WHERE empno IN (7788, 7566, 7839, 7369,7499) ;
예외 처리가 없는 경우
모두 rollback됨
BEGIN
UPDATE -- O
BEGIN
UPDATE -- O
UPDATE -- ERROR
UPDATE -- O
END ;
UPDATE -- O
END ;
/
BEGIN
UPDATE emp
SET sal = 7777
WHERE empno = 7788 ;
BEGIN
UPDATE emp
SET sal = 9999
WHERE empno = 7566 ;
UPDATE emp
SET sal = 0
WHERE empno = 7839 ;
UPDATE emp
SET sal = 9999
WHERE empno = 7499 ;
END ;
UPDATE emp
SET sal = 7777
WHERE empno = 7369 ;
END ;
/
SELECT empno, ename, sal
FROM emp
WHERE empno IN (7788, 7566, 7839, 7369, 7499) ;
중첩 pl/sql문에 예외처리가 있는 경우
UPDATE -- ERROR 구문에서 오류가 발생하면 예외 처리 블록으로 넘어가게 되며, 그 후 최상위 블록의 마지막 UPDATE 문이 실행
BEGIN
UPDATE
BEGIN
UPDATE
UPDATE -- ERROR
UPDATE
EXCEPTION
WHEN OTHERS THEN
.....
END ;
UPDATE
END ;
/
SET SERVEROUTPUT ON;
BEGIN
UPDATE emp
SET sal = 7777
WHERE empno = 7788 ;
BEGIN
UPDATE emp
SET sal = 9999
WHERE empno = 7566 ;
UPDATE emp
SET sal = 0
WHERE empno = 7839 ;
UPDATE emp
SET sal = 9999
WHERE empno = 7499 ;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM) ;
END ;
UPDATE emp
SET sal = 7777
WHERE empno = 7369 ;
END ;
/
SELECT empno, ename, sal
FROM emp
WHERE empno IN (7788, 7566, 7839, 7369,7499) ;
ROLLBACK ;
최상위 블록에만 예외처리가 있는 경우
중첩 블록에서 예외가 발생하면 상위블록 예외처리로 넘어가게되고 오류 발생 지점과 상위블록 예외처리 사이 모든 명령문은 실행되지않음.
BEGIN
UPDATE -- O
BEGIN
UPDATE -- O
UPDATE -- ERROR
UPDATE
END ;
UPDATE
EXCEPTION
WHEN OTHERS THEN
.....
END ;
/
SET SERVEROUTPUT ON
BEGIN
UPDATE emp
SET sal = 7777
WHERE empno = 7788 ;
BEGIN
UPDATE emp
SET sal = 9999
WHERE empno = 7566 ;
UPDATE emp
SET sal = 0
WHERE empno = 7839 ;
UPDATE emp
SET sal = 9999
WHERE empno = 7499 ;
END ;
UPDATE emp
SET sal = 7777
WHERE empno = 7369 ;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM) ;
END ;
/
SELECT empno, ename, sal
FROM emp
WHERE empno IN (7788, 7566, 7839, 7369,7499) ;
ROLLBACK ;
네번째,다섯 번째 UPDATE 문은 실행되지 않음.
그 이유는 중첩된 BEGIN-END 블록 내의 세 번째 UPDATE 문에서 예외가 발생할 경우, 해당 예외가 상위 블록으로 전파되기 때문에 나머지 코드가 실행되지 않기 때문
중첩된 블록 내에서 발생한 예외가 상위 블록으로 전파되면, 상위 블록의 EXCEPTION 처리기로 넘어가며, 그 이후의 모든 문장은 실행되지 않음.
예외 종류
내부 예외 : 오라클에서 미리 정의한 예외
사전 정의된 예외
이름이 정해지지 않은 예외
사용자 정의 예외:사용자가 필요에 따라 추가로 정의한 예외
예외 종류 설명
| 사전 정의된 예외(내부 예외) | 내부 예외 중 예외 번호에 해당하는 이름이 존재하는 예외→비교적 자주 발생하는 예외에 이름을 붙여 놓은 것 |
| 이름이 없는 예외(내부 예외) | 내부 예외중 이름이 존재하지 않는 예외(사용자가 필요에 따라 이름을 지정할 수 있음) |
| 사용자 정의 예외 | 사용자가 필요에 따라 직접 정의한 예외 |
사전 정의된 예외
예외명 예외 코드 설명
| ACCESS_INTO_NULL | ORA-06530 | LOB과 같은 객체 초기화 되지 않은 상태에서 사용 |
| CASE_NOT_FOUND | ORA-06592 | CASE문 사용시 구문 오류 |
| CURSOR_ALREADY_OPEN | ORA-06511 | 커서가 이미 OPEN된 상태인데 OPEN 하려고 시도 |
| DUP_VAL_ON_INDEX | ORA-00001 | 유일 인덱스가 있는 컬럼에 중복값으로 INSERT, UPDATE 수행 |
| INVALID_CURSOR | ORA-01001 | 존재하지 않는 커서를 참조 |
| INVALID_NUMBER | ORA-01722 | 문자를 숫자로 변환할 때 실패할 경우 |
| LOGIN_DENIED | ORA-01017 | 잘못된 사용자 이름이나 비밀번호로 로그인을 시도 |
| NO_DATA_FOUND | ORA-01403 | SELECT INTO 시 데이터가 한 건도 없을 경우 |
| NOT_LOGGED_ON | ORA-01012 | 로그온되지 않았는데 DB를 참조할 때 |
| PROGRAM_ERROR | ORA-06501 | PL/SQL 코드상에서 내부 오류를 만났을 때, 이 오류가 발생하면 “오라클에 문의(Contact Oracle Support)”란 메시지가 출력됨 |
| STORAGE_ERROR | ORA-06500 | 프로그램 수행 시 메모리가 부족할 경우 |
| TIMEOUT_ON_RESOURCE | ORA-00051 | 데이터베이스 자원을 기다리는 동안 타임아웃 발생 시 |
| TOO_MANY_ROWS | ORA-01422 | SELECT INTO 절 사용할 때 결과가 한 로우 이상일 때 |
| VALUE_ERROR | ORA-06502 | 수치 또는 값 오류 |
| ZERO_DIVIDE | ORA-01476 | 0으로 나눌 때 |
이와 달리 이름 없는 예외는 ORA-XXXX식으로 예외 번호는 있지만 이름이 정해져 있지 않은 예외를 뜻함
예외 처리부 작성
예외 처리부는 EXCEPTION절에 필요한 코드를 사용하여 작성
WHEN으로 시작하는 절을 예외 핸들러(EXCEPTION HANDLER)라고 하며 , 발생한 예외 이름과 일치하는 WHEN절의 명령어를 수행(IF THEN문 처럼 여러 예외 핸들러 중 일치하는 하나의 예외 핸들러 명령어만 수행)
OTHERS는 먼저 작성한 어느 예외와도 일치하는 예외가 없을 경우에 처리할 내용을 작성(IF 조건문의 ELSE와 비슷)
기본 형식
EXEPTION
WHEN 예외 이름1 [OR 예외 이름2] THEN
예외 처리에 사용할 명령어;
WHEN 예외 이름3 [OR 예외 이름4] THEN
예외 처리에 사용할 명령어;
...
WHEN OTHERS THEN
예외 처리에 사용할 명령어;
사전 정의된 예외 사용
예외 핸들러에 사전 정의된 예외만을 사용할 때는 앞에서 살펴본 작성 방식대로 발생할 수 있는 예외를 명시
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;
/
예외 처리 : 수치 또는 값 오류 발생
※ 미리 정의된 예외 처리
SET SERVEROUTPUT ON
DECLARE
emp_rec emp%ROWTYPE ;
BEGIN
SELECT * INTO emp_rec
FROM emp
WHERE ename = UPPER('&name') ;
DBMS_OUTPUT.PUT_LINE ( emp_rec.sal ) ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('NO_DATA_FOUND') ;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE ('TOO_MANY_ROWS') ;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('OTHERS') ;
END ;
/
SET SERVEROUTPUT ON
DECLARE
emp_rec emp%ROWTYPE ;
BEGIN
SELECT * INTO emp_rec
FROM emp
WHERE DEPTNO = 10 ;
DBMS_OUTPUT.PUT_LINE ( emp_rec.sal ) ;
EXCEPTION
WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM) ;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('OTHERS') ;
END ;
/
이름 없는 예외 사용
이름 없는 내부 예외를 사용해야 한다면 이름을 직접 지정해 주어야 예외 처리부에서 사용할 수 있음
선언부(declare)에서 오라클 예외 번호와 함께 이름을 붙임
DECLARE
예외이름1 EXCEPTION;
PRAGMA EXCEPTION_INIT(예외 이름1,예외 번호)
.
.
.
EXCEPTION
WHEN 예외이름 1 THEN
예외 처리에 사용할 명령어;
...
END;
※ 미리 정의되지 않은 예외 처리
SET SERVEROUTPUT ON
DECLARE
emp_rec emp%ROWTYPE ;
e_ck EXCEPTION ;
PRAGMA EXCEPTION_INIT (e_ck , -2290) ;
BEGIN
SELECT * INTO emp_rec
FROM emp
WHERE ename = UPPER('&name') ;
IF emp_rec.sal < 2000 THEN
UPDATE emp
SET sal = 0
WHERE empno = emp_rec.empno ;
END IF ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('NO DATA') ;
WHEN E_CK THEN
DBMS_OUTPUT.PUT_LINE ('Invalid Salary') ;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM) ;
END ;
/
사용자 정의 예외 사용
사용자 정의 예외는 오라클에 정의되어 있지 않은 특정 상황을 직접 오류로 정의하는 방식
예외 이름을 정해주고 실행 부에서 직접 정의한 오류 상황이 생겼을 때 RAISE키워드를 사용하여 예외를 직접 만듦
DECLARE
사용자 예외 이름 EXCEPTION;
...
BEGIN
IF 사용자 예외를 발생시킬 조건 THEN
RAISE 사용자 예외 이름
...
END IF;
EXCEPTION
WHEN 사용자 예외 이름 THEN
예외 처리에 사용할 명령어;
...
END;
DECLARE
v_deptno NUMBER := 50 ;
v_name VARCHAR2(20) := 'Testing' ;
e_invalid_department EXCEPTION;
BEGIN
UPDATE dept
SET dname = v_name
WHERE deptno = v_deptno ;
IF SQL%NOTFOUND THEN
RAISE e_invalid_department ;
END IF;
COMMIT;
EXCEPTION
WHEN e_invalid_department THEN
DBMS_OUTPUT.PUT_LINE('No such department id.');
END;
/
※ RAISE_APPLICATION_ERROR 사용
오라클에서는 예외를 -숫자로 표시
UPDATE dept
SET dname = 'Testing'
WHERE deptno = 50 ;
BEGIN
UPDATE dept
SET dname = 'Testing'
WHERE deptno = 50 ;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR ( -20001, 'No such department id.' ) ;
END IF;
END;
/
DECLARE
emp_rec emp%ROWTYPE ;
BEGIN
SELECT * INTO emp_rec
FROM emp
WHERE deptno = 10 ;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
RAISE_APPLICATION_ERROR ( -20001, 'Too Many Rows', TRUE ) ;
END ;
/
DECLARE
emp_rec emp%ROWTYPE ;
BEGIN
SELECT * INTO emp_rec
FROM emp
WHERE deptno = 10 ;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
RAISE_APPLICATION_ERROR ( -20001, '조건에 만족하는 행 개수가 2개 이상입니다', FALSE ) ;
END ;
/
오류 코드와 오류 메시지 사용
오류 처리부가 잘 작성 되어 있다면 오류가 발생해도 PL/SQL은 정상 종료
PL/SQL문의 정상 종료 여부와 상관없이 발생한 오류내역을 알고 싶을 때 SQLCODE , SQLERRM함수를 사용(PL/SQL에서만 사용가능)
함수 설명
| SQLCODE | 오류 번호를 반환하는 함수 |
| SQLERRM(SQL ERROR MESSAGE) | 오류 메시지를 반환하는 함수 |
오류 코드와 오류 메시지 사용하기
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;
/
예외 처리 : 사전 정의 외 오류 발생 SQLCODE : -6502 SQLERRM : ORA-06502: PL/SQL: 수치 또는 값 오류: 문자를 숫자로 변환하는데 오류입니다PL/SQL 프로시저가 성공적으로 완료되었습니다.
SET SERVEROUTPUT ON
BEGIN
UPDATE emp
SET sal = 3000
WHERE empno = 7782 ;
UPDATE emp
SET sal = 0
WHERE empno = 7934 ;
COMMIT ;
EXCEPTION
WHEN OTHERS THEN
-- ROLLBACK ;
DBMS_OUTPUT.PUT_LINE ( SQLERRM ) ;
END ;
/
SELECT empno, ename, sal
FROM emp
WHERE deptno = 10 ;
ROLLBACK ;
SELECT empno, ename, sal
FROM emp
WHERE deptno = 10 ;
실습문제
어제 날짜 기준으로 연체날짜 UPDATE하기
방법1.
UPDATE TACCT
SET DLQ_CNT = ((TRUNC(SYSDATE)-1) - DLQ_DT)
WHERE DLQ_DT IS NOT NULL ;
ROLLBACK ;
BEGIN
UPDATE TACCT
SET DLQ_CNT = ((TRUNC(SYSDATE)-1) - DLQ_DT)
WHERE DLQ_DT IS NOT NULL ;
-- COMMIT ;
END ;
/
방법2.
DECLARE
CURSOR CUR_TACCT IS
SELECT * FROM TACCT ;
BEGIN
FOR REC_TACCT IN CUR_TACCT LOOP
IF REC_TACCT.DLQ_DT IS NOT NULL THEN
UPDATE TACCT
SET DLQ_CNT = ((TRUNC(SYSDATE)-1) - DLQ_DT)
WHERE LNACT = REC_TACCT.LNACT
AND LNACT_SEQ = REC_TACCT.LNACT_SEQ ;
END IF ;
END LOOP ;
END ;
/
방법3.
DECLARE
CURSOR CUR_TACCT IS
SELECT * FROM TACCT
WHERE DLQ_DT IS NOT NULL ;
BEGIN
FOR REC_TACCT IN CUR_TACCT LOOP
UPDATE TACCT
SET DLQ_CNT = ((TRUNC(SYSDATE)-1) - DLQ_DT)
WHERE LNACT = REC_TACCT.LNACT
AND LNACT_SEQ = REC_TACCT.LNACT_SEQ ;
END LOOP ;
END ;
/