오라클 PLSQL 예외(Exception)처리, Predefined Exception, Non-Predefined Exception, ,사용자 정의 오류, RAISE, , RAISE_APPLICATION_ERROR

오라클 PLSQL 예외(Exception)처리, Predefined Exception, Non-Predefined Exception, ,사용자 정의 오류, RAISE, , RAISE_APPLICATION_ERROR

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=580

 

오라클 PLSQL 예외(Exception)처리, Predefined Exception, Non-Predefined Exception, ,사용자 정의 오류, RAISE, , RAISE_APPLICATION_ERROR

오라클 PLSQL 예외(Exception)처리, Predefined Exception, Non-Predefined Exception, ,사용자 정의 오류, RAISE, , RAISE_APPLICATION_ERROR6. PL/SQL Exceptionn 예외(Exception)란? PL/SQL블록 실행중 발생하는 오류이며, 예외가 발생했을 때 적절히 처리를 해…

ojc.asia

6. PL/SQL Exception

n 예외(Exception)란? PL/SQL블록 실행중 발생하는 오류이며, 예외가 발생했을 때 적절히 처리를 해주어야만 원할한 PL/SQL 프로그램이 된다.

n Exception절은 반드시 정의해야 하는 것은 아니지만 추후 발생될 수 있는 문제를 위해서 반드시 기술하는 것이 좋다.

n PL/SQL에서 예외가 발생하는 경우는 서버에 의해 자동으로 발생되는 경우와 사용자가 RAISE, RAISE_APPLICATION_ERROR등을 기술하여 예외를 발생시키는 두가지 경우가 있다.

[형식]

DECLARE 변수 선언 BEGIN 실행문장; …… EXCEPTION WHEN 예외명1 THEN 예외처리부; WHEN 예외명2 THEN 예외처리부; …… …… WHEN others THEN 기타 예외처리부; END;

6.1 Predefined Exception

n 오라클 서버에 사전에 정의된 예외, 자주 발생하므로 오라클 서버 오류번호와 예외명을 미리 매칭시켜 둔 것이다.

Exception	Oracle Error	SQLCODE	Description
ACCESS_INTO_NULL	06530	-6530	NULL Object가 할당되는 경우
CASE_NOT_FOUND	06592	-6592	CASE문을 ELSE없이 사용시 WHEN절에 만족하는 경우가 없을 때
DUP_VAL_ON_INDEX	00001	-1	UNIQUE INDEX 중복오류
INVALID_CURSOR	01001	-1001	커서 조작 실수
INVALID_NUMBER	01722	-1722	숫자를 문자로 바꾸는 경우에 숫자로 바꿀수 없는 문자를 숫자로 바꾸는 경우
LOGIN_DENIED	01017	-1017	사용자명 or 비밀번호 맞지 않은 경우
NO_DATA_FOUND	01403	+100	SELECT 했는데 데이터가 한건도 추출되지 않는 경우
NOT_LOGGED_ON	01012	-1012	로그온 되지 않은 상황에서 SQL문을 실행했을 때
PROGRAM_ERROR	06501	-6501	PL/SQL 내부 오류
ROWTYPE_MISMATCH	06504	-6504	커서 패치하는 경우 데이터 타입이 불일치
STORAGE_ERROR	06500	-6500	PL/SQL등에서 OUT OF MEMORY 발생시
TOO_MANY_ROWS	01422	-1422	BEGIN내부에서 SELECT 사용시 여러건의 데이터가 추출되는 경우
VALUE_ERROR	06502	-6502	산술연산의 변환, 절삭, 사이즈 오류시 발생
ZERO_DIVIDE	01476	1476	숫자를 0으로 나누는 경우에 발생

-- 예외처리를 하지 않은 경우 SQL> SET SERVEROUTPUT ON SQL> create or replace procedure showempbyjob (v_job in emp.job%type) is v_ename emp.ename%type; v_sal emp.sal%type; begin select ename, sal into v_ename, v_sal from emp where job = v_job; dbms_output.put_line('이름'||'급여'); dbms_output.put_line('--------------------'); dbms_output.put_line(v_ename || v_sal); end; / 프로시저가 생성되었습니다. SQL> exec showempbyjob('CLERK') BEGIN showempbyjob('CLERK'); END; * 1행에 오류: ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다 ORA-06512: "SCOTT.SHOWEMPBYJOB", 7행 ORA-06512: 1행 -- 사전에 정의된 예외를 이용하여 예외처리를 한 경우 SQL> create or replace procedure showempbyjob (v_job in emp.job%type) is v_ename emp.ename%type; v_sal emp.sal%type; begin select ename, sal into v_ename, v_sal from emp where job = v_job; dbms_output.put_line('이름'||'급여'); dbms_output.put_line('--------------------'); dbms_output.put_line(v_ename || v_sal); exception when no_data_found then dbms_output.put_line('해당 직종의 사원은 없음...'); when too_many_rows then dbms_output.put_line('해당 직종을 가진 사원이 2건 이상...'); when others then dbms_output.put_line(SQLCODE || ':' || SQLERRM); end; / 프로시저가 생성되었습니다. SQL> exec showempbyjob('CLERK') 해당 직종을 가진 사원이 2건 이상... -- 예외처리를 하지 않은 경우 SQL> create or replace procedure insertemp (v_empno in emp.empno%type, v_ename in emp.ename%TYPE, v_sal in emp.sal%TYPE) is begin insert into emp(empno, ename, sal) values (v_empno, v_ename, v_sal); commit; end; / 프로시저가 생성되었습니다. SQL> exec insertemp(7369,'오자커',9999) BEGIN insertemp(7369,'오자커',9999); END; * 1행에 오류: ORA-00001: 무결성 제약 조건(SCOTT.PK_EMP)에 위배됩니다 ORA-06512: "SCOTT.INSERTEMP", 7행 ORA-06512: 1행 -- 예외처리를 한 경우 SQL> create or replace procedure insertemp (v_empno in emp.empno%type, v_ename in emp.ename%TYPE, v_sal in emp.sal%TYPE) is begin insert into emp(empno, ename, sal) values (v_empno, v_ename, v_sal); commit; exception when dup_val_on_index then dbms_output.put_line('데이터가 중복 됩니다...'); end; / 프로시저가 생성되었습니다. SQL> set serverout on SQL> exec insertemp(7368,'오자커736',9999) 데이터가 중복 됩니다...

6.2 Non-Predefined Exception

n 오라클 서버 오류중 서버에 미리 정의되지 않은 예외

n Declare절에서 예외명을 정의하고 EXCEPTION_INIT 함수를 이용하여 예외와 오라클 서버 오류코드를 매핑하면 된다.

SQL> SET SERVEROUTPUT ON SQL> create or replace procedure deldept (p_deptno in dept.deptno%type) as child_error exception; pragma exception_init(child_error, -2292); begin delete from dept where deptno=p_deptno; commit; exception when child_error then rollback; dbms_output.put_line('자식 레코드가 있으므로 삭제할 수 없습니다.'); end; / 프로시저가 생성되었습니다. SQL> exec deldept(10) 자식 레코드가 있으므로 삭제할 수 없습니다.

6.3 사용자 정의 오류(User Defined Exception)

6.3.1 RAISE 구문을 이용하는 방법

n 오라클 서버 오류가 아니라 사용자가 정의한 논리적인 오류.

n Declare절에서 예외명을 정의하고 예외를 발생시키고 싶은 곳에서 RAISE 구문을 이용하면 된다. 물론 EXCEPTION절에서는 해당 예외명으로 받아서 처리하면 된다.

SQL> SET SERVEROUTPUT ON SQL> CREATE OR REPLACE PROCEDURE insertemp2(v_empno IN emp.empno%TYPE , v_ename IN emp.ename%TYPE, v_sal IN emp.sal%TYPE) IS highsal_err EXCEPTION ; BEGIN IF v_sal < 5000 THEN INSERT INTO emp (empno, ename, sal) VALUES(v_empno, v_ename, v_sal) ; DBMS_OUTPUT.PUT_LINE ('정상입력 OK...') ; ELSE RAISE highsal_err ; END IF ; EXCEPTION WHEN highsal_err THEN DBMS_OUTPUT.PUT_LINE ('급여를 5000 미만으로 입력하세요!') ; END; / 프로시저가 생성되었습니다. SQL> exec insertemp2(2222,'오자커',2222) 정상입력 OK... SQL> exec insertemp2(5555,'오자커',5555) 급여를 5000 미만으로 입력하세요!

6.3.2 RAISE_APPLICATION_ERROR 구문을 이용하는 방법

n 사용자가 임의의 조건으로 SQLCODE와 SQLERRM을 정의할 수 있다.

n SQLCODE는 -20999번에서 -20000번 사이 정수로 정의하며 EXCEPTION절을 사용하지 않아도 된다.

SQL> SET SERVEROUTPUT ON SQL> CREATE OR REPLACE PROCEDURE insertemp3(v_empno IN emp.empno%TYPE , v_ename IN emp.ename%TYPE, v_sal IN emp.sal%TYPE) IS BEGIN IF v_sal < 5000 THEN INSERT INTO emp (empno, ename, sal) VALUES(v_empno, v_ename, v_sal) ; DBMS_OUTPUT.PUT_LINE ('정상입력OK...') ; ELSE RAISE_APPLICATION_ERROR(-20000,'급여를 5000 미만으로 입력하세요!'); END IF ; END; / 프로시저가 생성되었습니다. SQL> exec insertemp3(5555,'오자커',5555) BEGIN insertemp3(5555,'오자커',5555); END; * 1행에 오류: ORA-20000: 급여를 5000 미만으로 입력하세요! ORA-06512: "SCOTT.INSERTEMP3", 11행 ORA-06512: 1행

​