[DB] PL/SQL, Exception Handling

* Exception Handling

- 프로그램 개발시 코드에서 발생할 수 있는 사소한 error가 발생했을 때 사용자에게 제공할 내용을 만드는 것

- 가장 마지막줄에 한번만 정의하면 된다.

- 최상위 예외처리객체는 others이다. 모든 예외를 다 잡아서 처리할 수 있다.

- 문법

declare

begin

...

exception 

when 예외처리객체1 then

1예외발생시 제공할 코드;

when 예외처리 객체2 then

2예외발생시 제공할 코드;

...

end;

/

- 예외처리 객체

· DUP_VAL_ON_INDEX : UNIQUE, PRIMARY KEY의 중복된 값이 들어갈 때

· NO_DATA_FOUND : SELECT에서 0행이 조회되었을 때

· TOO_MANY_ROWS : SELECT에서 여러행이 조회될 때

· INVALID_CURSOR : CORSOR가 잘못된 연산을 수행할 때 등

 

* cousor

- PL/SQL내에서 여러행을 조회한다.

- declare~begin 사이에서 정의하고(명시적 커서), begin~end사이에서 열기, 인출, 닫기를 한다.

- 생명주기를 가진다.(선언 -> 열기 -> 인출 -> 닫기)

- for로 커서를 사용하면 간단하게 사용할 수 있다.

- 여러 속성을 사용할 수 있다.(커서명%속성명)

isOpen : 커서가 열려있는지

rowcount : 행의 수

found : 레코드가 있을 때

notfound : 레코가 없을 때

 

1. 선언

cursor 커서명 is select ...;(into절 사용x)

2. 열기

open 커서명;

3. 인출(반복문)

fetch 커서명 into 변수명,,,;

4. 닫기

close 커서명;

declare

cursor test is select deptno, dname,loc from dept;

deptno dept.deptno%type;

dname dept.dname%type;

loc dept.loc%type;

begin

open test;

loop

fetch test into deptno, dname, loc;

변수사용

exit when(test%notfound);

end loop;

-- 문) update : 사원번호, 부서번호, 연봉을 입력 받아서 해당하는 사원의 부서번호화 연봉을 변경하라.

-- [참고] where절에 컬럼명과 변수명이 같다면 항상 참이되므로 잘못된 변경을 수행한다.

 

set serveroutput on

set verify off

 

accept empno prompt '사원번호 : '

accept deptno prompt '부서번호 : '

accept sal prompt '연봉 : '

 

declare

i_empno cp_emp5.empno%type;

deptno cp_emp5.deptno%type := &deptno;

sal cp_emp5.sal%type := &sal;  

cnt number;

begin

i_empno := nvl('&empno',-1);

-- └외부값을 받기 때문에 발생하는 문제 막기. ''로 묶어서 강제로 null로 만든 후 비교값으로 대체한다. 

 

-- 컬럼의 이름과 변수를 구분되게 넣어준다.(set, where절에서 모두 구분되게 해주어야 한다.)

update cp_emp5

set sal = &sal, deptno = &deptno 

where empno = i_empno;

-- where empno = empno;

-- └변수와 컬럼의 이름이 같다면 둘다 참이되므로 모든 레코드를 변경한다.

-- 구분 지어주는 방법으로는 '&' 혹은 이름을 다르게 주는 방법이 있다. 

 

cnt := sql%rowcount;

if cnt != 0 then   

commit;

dbms_output.put_line(cnt || '건 변경되었습니다.');

-- 값이 매칭되지 않아서 변경되지 않은 경우는 있어도 실패하는 경우는 없다. 따라서 0~n건의 변경(update)이 이루어진다.

else

dbms_output.put_line('사원정보가 없습니다.');

end if;

-- └예외처리가 되지않기 때문에 조건문을 사용해서 처리한다.

end;

/

 

---------------------------------------------------------------------------------------------

 

-- 문) delete : 사원번호를 입력받아서 해당 번호의 사원을 삭제하라

 

set verify off

set serveroutput on

 

accept empno prompt '사원번호 : '

declare                          

i_empno cp_emp5.empno%type;

cnt number;

begin                                             

i_empno := nvl('&empno',-1);

if i_empno = -1 then  

dbms_output.put_line('사원번호는 필수 입력입니다.');

-- └사원번호 공백입력을 거르기 위한 문.

/*

입력된 값을 싱글쿼테이션('')으로 묶어 null값인지 확인한다.

만약 사용자가 숫자값을 입력했을 경우 '5'의 상태로 nvl검사를 하게된다.

이때 null이 아니므로 empno의 값 그대로를 반환한다.

그러나 사용자가 아무것도 입력하지 않았을 경우 ''값이 들어가게 되는데

이때 ''는 null을 나타낸다. 따라서 nvl검사를 통해 null임이 맞으므로 -1을 반환한다.

그후 반환된 값을 저장하고 있는 변수의 값을 검사하여 사용자가 공백 입력을 잡아낼 수 있게된다.

[참고] 사용자가 문자를 입력했을 때에는 데이터형이 맞지 않으므로 에러가 발생한다.

*/

else                                                        

delete from cp_emp5 where empno = i_empno;  

 

cnt := sql%rowcount; 

-- └수행된 행의 수를 암시적커서로 얻을 수 있다.

if cnt != 0 then

commit;

dbms_output.put_line(cnt || '건 삭제되었습니다.');

-- └delete문을 수행한 후 commit시켜주어야하는데 delete문의 성공여부를 rowcount의 값으로 비교

-- cnt의 값이 0이면 수행된 행의 수가 0란 뜻이므로 delete문이 실행되지 않았다는 의미

-- 또한 delete문이 성공하면 1~n개의 행을 수행할 수 있기 때문에 !=0을 조건으로 주었다.

else

dbms_output.put_line(i_empno || '번 사원은 존재하지 않습니다.');

end if;

end if;

end;

/

 

---------------------------------------------------------------------------------------------

 

-- 문) select : 부서번호를 입력밭아서 부서명, 부서위치를 출력하고 예외처리를 하라

-- [참고] select는 조회된 값을 저장하기 위해서 into절 추가

 

set verify off

set serveroutput on

 

accept deptno prompt '부서번호 : '

declare

i_deptno cp_dept.deptno%type := nvl('&deptno',-1);

-- └선언과 동시에 입력값의 공백을 비교할 수 있다.

dname cp_dept.dname%type;

loc cp_dept.loc%type;

begin

if i_deptno = -1 then

dbms_output.put_line('조회할 부서번호를 입력하세요.');

else

select dname, loc

into dname, loc

-- └select문을 통해 가져온 데이터를 저장할 into절을 추가해주어야한다.

from cp_dept

where deptno = i_deptno;

-- [참고] select의 결과가 1건이 아니라면 예외가 발생한다.

 

dbms_output.put_line(sql%rowcount);

 

dbms_output.put_line(i_deptno || '번 부서의 조회 결과');

dbms_output.put_line('부서명' || dname || ', 위치 : ' || loc);

end if;   

 

/*

exception

when others then

dbms_output.put_line('조회는 한 행만 가능');

-- └oracle에서의 예외들의 최고조상은 others이다. 모든 예외를 처리한다.  

-- others는 exception문의 최하위에 존재하여야 한다.

*/ 

 

exception

when too_many_rows then

dbms_output.put_line('조회는 한 행만 가능');

-- └select문의 결과가 여러 행일 경우 해당 예외로 걸러진다.

when no_data_found then

dbms_output.put_line('해당 부서는 없습니다.');

-- └select문의 결과가 없다면 해당 예외로 걸러진다.

end;

/

 

---------------------------------------------------------------------------------------------

 

-- 문) cursor : 부서번호를 입력받아 해당부서의 사원번호, 사원명, 연봉, 입사일을 조회

-- [참고] cursor를 이용하여 여러행을 조회할 수 있다.

 

set verify off

set serveroutput on

 

accept deptno prompt '부서번호 : '

 

declare

i_deptno emp.deptno%type := nvl('&deptno',-1);

 

-- 1. 커서 선언

cursor cur_emp is

select empno, ename, sal, to_char(hiredate,'yyyy-mm-dd') hiredate

from emp

where deptno = i_deptno;

 

-- *인출(fetch)시 값을 저장할 변수 선언

empno emp.empno%type;

ename emp.ename%type;

sal emp.sal%type;

hiredate varchar2(10);

begin   

if i_deptno = -1 then

dbms_output.put_line('부서번호는 필수 입력입니다.');

else 

-- 커서 열기전에 검사

-- *커서가 열려있으면

/*

if cur_emp%isopen then

dbms_output.put_line('커서가 열려있음');

else

dbms_output.put_line('커서가 닫혀있음');

end if;

*/

 

-- *커서가 닫혀있으면 커서 열기. 

-- [참고] 열린 커서를 두변 열면 에러

if cur_emp%isopen then

close cur_emp;

end if;

 

-- 2. 커서 열기

open cur_emp;

 

/*

-- *인출(커서가 있는 위치의 값을 변수에 할당)

fetch cur_emp into empno, ename, sal, hiredate;

dbms_output.put_line(empno || ' ' || ename || ' ' || sal || ' ' || hiredate); 

☆★질문 'exit -> fetch -> 출력문'의 순서일때 왜 마지막 행이 한번더 출력되는가? ☆★

*/

 

-- *명시적 커서를 이용한 여러행 인출

loop 

exit when(cur_emp%notfound);

 

-- 3. 인출

fetch cur_emp into empno, ename, sal, hiredate; 

            

dbms_output.put_line(empno || ' ' || ename || ' ' || sal || ' ' || hiredate);

 

-- *loop 탈출문

-- 탈출문1

/*

if cur_emp%notfound then

exit;

end if;

*/

-- 탈출문2

end loop;

 

/*

dbms_output.put_line('조회된 행의 수' || cur_emp%rowcount);

   */

    

   if cur_emp%rowcount = 0 then 

    dbms_output.put_line(i_deptno || '번 부서는 사원이 없습니다. 부서번호를 확인하세요.');

   end if;

   

-- 4. 커서 닫기

close cur_emp;

end if;

end;

/

 

---------------------------------------------------------------------------------------------

 

-- 문) cursor join : 사원번호, 사원명, 부서번호, 부서명, 위치를 조회하라

 

declare

cursor cur_join is

select e.empno, e.ename, d.deptno, d.dname, d.loc

from dept d, emp e

where d.deptno = e.deptno;

 

-- 저장할 변수

empno emp.empno%type;

ename emp.ename%type;

deptno dept.deptno%type;

dname dept.dname%type;

loc dept.loc%type;

begin                 

-- 열기

if cur_join%isopen then

close cur_join;

end if;

open cur_join;

 

-- 인출

loop     

fetch cur_join into empno, ename, deptno, dname, loc;

 

-- 반복물 탈출

exit when(cur_join%notfound);

 

-- 출력문

dbms_output.put_line(empno || ' ' || ename || ' ' || deptno || ' ' || dname || ' ' || loc);

end loop;                                                                                      

 

-- 커서 닫기

close cur_join;

end;

/