* 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;
/
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
'예전 포스팅 모음' 카테고리의 다른 글
[DB] Cursor For Loop문 활용하기 (0) | 2014.10.08 |
---|---|
[DB ]pl/sql 문 문제 예제 (0) | 2014.10.07 |
[DB] PL/SQL, 커서 (0) | 2014.10.06 |
[DB] 계정, PL(Procedure Language)/SQL (0) | 2014.10.02 |
[DB] sequence, index (0) | 2014.10.01 |