#11_140627_PL_SQL_예외처리

# 예외처리

l 예외처리가 필요한 이유

- 오라클 에러메세지를 사용자의 눈높이에 맞춰서 출력할 수 있다.

- 프로그램을 수행되게 할 떄 몇가지 오류가 있는 data 때문에 전체 프로그래밍이 실패하는 현상을 방지하기 위해서 필요하다

l 예외의 종류

- 미리 정의 해놓은 예외처리

- 미리 정의하지 않은 예외처리

- 사용자정의 예외처리

l 미리 정의하지 않은 예외처리

- 우리회사만의 특별한 오라클 에러에 대한 예외처리를 내가 프로그램상에서 직접 구현

문제86. 부서번호를 입력하면 해당 부서번호에 속한 사원들의 토탈월급이 출력되게 하는 PL/SQL 을 작성하는데 없는 부서번호를 넣으면 “해당 부서번호는 없습니다” 라는 메시지가 출력되게 하시오.

set serveroutput on

accept p_deptno prompt '부서번호를 입력하시오.'

declare

 v_deptno emp.deptno%type := &p_deptno;

 v_sumsal emp.sal%type ;

begin

 select sum(sal) into v_sumsal

 from emp

 where deptno = v_deptno

 GROUP BY deptno;

 dbms_output.put_line ('토탈월급은 ' || v_sumsal );

exception

 when no_data_found then

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

end;

/

l group by를 제외한 기존 소스에서 범위 이외의 값이 나와도 예외처리가 안되는 것을 볼 수 있는데 이것은where 절이 거짓이라도 null 값을 출력하기 때문이다

l no_data_found 는 no row selected 란 메시지가 나오는 경우에만 메시지를 출력가능하다.

l 해결방법으로는 기존 소스에 group 함수를 쓰서 null 값이 아닌 no row selected 란 메시지를 출력할 수 있다

sum(sal)에 나오는 데이터는 아무것도 안보이지만 null 값이 들어있다

emp599.txt

문제90. “조류를 입력하시오” 라고 물어보게 하고 조류를 입력하면 가장 큰 피해를 본 농장주소가 출력되게 하는데 없는 조류를 넣으면 “해당 조류는 없습니다” 메시지가 출력되게 하세요

set serveroutput on

accept p_type prompt '조류를 입력하시오.'

declare

v_type emp599.dise_object%type := '&p_type';

v_address emp599.address%type;

begin

select address into v_address

from emp599

where dise_num = (select max(dise_num)

from emp599

where dise_object = v_type);

dbms_output.put_line ('주소는 : ' || v_address );

exception

when no_data_found then

dbms_output.put_line('데이타 없음.');

end;

/

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

ACCEPT p_bird PROMPT '조류를 입력하시오 '

DECLARE

 v_bird emp599.dise_object%TYPE := '&p_bird';

 v_address emp599.address%TYPE;

BEGIN

 SELECT address INTO v_address

 FROM (SELECT address, RANK() OVER(PARTITION BY dise_object ORDER BY dise_num desc) 순위

  FROM emp599 WHERE dise_object = v_bird)

 WHERE 순위=1;

 dbms_output.put_line('주소 ' || v_address);

 EXCEPTION

  WHEN no_data_found THEN

   dbms_output.put_line('해당 조류는 없습니다');

 END;

문제94. sal 컬럼에 체크제약거리 0~9000 사이로 걸고 월급을 9000으로 갱신되게 하려했을 때 월급을9000 이상으로 갱신할 수 없습니다. 라는 메세지가 출력되게 하시오

ALTER TABLE EMP

ADD CONSTRAINT emp_sal_ck CHECK(sal BETWEEN 0 AND 9000);

ACCEPT p_ename PROMPT '이름을 입력하시오 '

DECLARE

 v_ename emp.ename%TYPE := UPPER('&p_ename');

 e_sal_ck EXCEPTION;

 PRAGMA EXCEPTION_INIT(e_sal_ck, -02290);

BEGIN

 UPDATE EMP

 SET sal = sal*2

 WHERE ename = v_ename;

EXCEPTION

 WHEN e_sal_ck THEN

  dbms_output.put_line('월급을 9000 이상으로 수정못합니다');

END;

/

l PRAGMA EXCEPTION_INIT : exception 과 – RAN-02290 에러를 연결시켜주는 역할

문제97. 아래의 테이블을 생성하고 아래의 테이블에 data를 입력하는 프로그램을 작성하는데 조류를 물어보게 하고 조류를 입력하면 해당 조류와 피해입은 총 개체수를 아래 테이블에 입력되게 하시오

CREATE TABLE ai_table

(ai_object VARCHAR2(20),

ai_cnt NUMBER(10));

ACCEPT p_object PROMPT '새 종류를 입력하시오 '

DECLARE

 v_object emp599.dise_object%TYPE := '&p_object';

BEGIN

 INSERT INTO ai_table

  SELECT dise_object, SUM(dise_num)

   FROM emp599

   WHERE dise_object = v_object AND TO_CHAR(START_DATE,'RRRR') = '2011'

   GROUP BY dise_object;

END;

/

문제99. ai_table에 ai_object에 unique 제약을 거시오. 조류를 물어볼 때 닭을 두번이상 입력하시오.그런데 두번이상 입력될 때 아래의 메시지가 출력되게 하시오(“같은 조류가 이미 입력되었습니다”)

ALTER TABLE ai_table

ADD CONSTRAINT ai_table_ai_object_un UNIQUE(ai_object);

accept p_dise_object prompt '조류를 입력하시오!'

declare

v_object emp599.dise_object%type := '&p_dise_object';

v_sum emp599.dise_num%type;

ai_table_ai_object_un exception;

pragma exception_init( ai_table_ai_object_un, -00001);

begin

SELECT SUM(dise_num) into v_sum

 FROM emp599

 WHERE  TO_CHAR(START_DATE, 'RRRR') = 2011

 and dise_object = v_object

 GROUP BY dise_object;

insert into ai_table values(v_object, v_sum);

 

exception

 when ai_table_ai_object_un then

 dbms_output.put_line('같은 조류가 이미 입력되어있습니다.');

end;

/

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

ACCEPT p_object PROMPT '새 종류를 입력하시오 '

DECLARE

 v_object emp599.dise_object%TYPE := '&p_object';

BEGIN

 INSERT INTO ai_table

  SELECT dise_object, SUM(dise_num)

   FROM emp599

   WHERE dise_object = v_object AND TO_CHAR(START_DATE,'RRRR') = '2011'

   GROUP BY dise_object;

EXCEPTION

 WHEN dup_val_on_index  THEN

  dbms_output.put_line('같은 조류가 이미 입력되었습니다');

END;

/

문제100. 아래의 테이블을 생성하고 위의 코드를 실행해서 닭을 넣으면 에러번호와 에러메세지가 아래 테이블에 입력되게 하시오

create table error_table

(error_code varchar number(10),

error_message varchar2(200));

ACCEPT p_object PROMPT '새 종류를 입력하시오 '

DECLARE

 v_object emp599.dise_object%TYPE := '&p_object';

 v_code NUMBER(20);

 v_message varchar2(200);

 ai_table_ai_object_un exception;

 pragma exception_init( ai_table_ai_object_un, -00001);

BEGIN

 INSERT INTO ai_table

  SELECT dise_object, SUM(dise_num)

   FROM emp599

   WHERE dise_object = v_object AND TO_CHAR(START_DATE,'RRRR') = '2011'

   GROUP BY dise_object;

exception

 when ai_table_ai_object_un then

 dbms_output.put_line('같은 조류가 이미 입력되어 있습니다');

 v_code := sqlcode;

 v_message := sqlerrm;

 insert into error_table

 values(v_code, v_message);

END;

/

문제101. 조류 인플루엔자 확산 방지를 위한 빅데이터 활용 프로그램. emp599 테이블에 순위컬럼을 추가하고 햐당 순위를 자동 갱신하는 프로그램을 만드는데 순위는 피해 개체수가 많은 것에 대한 순위인데 전체 순위로 갱신하시오

declare

cursor emp_cursor is

 select num1, rank() over(order by dise_num desc) as "순위"

 from emp600;

begin

for emp_record in emp_cursor loop

 update emp600

 set rnk=emp_record.순위

 where num1=emp_record.num1;

end loop;

end;

/

문제104. 문제103번 예제를 수정해서 부서번호를 물어보게 하고 부서번호를 입력했을 때 해당 부서번호의 인원수가 3명보다 작다면 ‘부서에 인원을 충원해야 한다’ 에러 메시지가 출력되게 하시오

ACCEPT p_deptno PROMPT '부서번호를 입력하시오 '

DECLARE

 v_deptno emp.deptno%TYPE := &p_deptno;

 v_count NUMBER(10);

 v_exception EXCEPTION;

BEGIN

 SELECT COUNT(*) INTO v_count

 FROM EMP

 WHERE deptno = v_deptno;

 IF v_count < 4 THEN

  RAISE v_exception;

 ELSE

  dbms_output.put_line('인원수는' || v_count);

 END IF;

 EXCEPTION

  WHEN v_exception THEN

   raise_application_error(-20001,'인원을 충원하세요');

END;

 /

l 에러번호 20000 번대에는 에러번호가 없음

문제106. 위의 소스를 수정하는데 직업의 토탈월급이 5000보다 작으면 토탈월급이 “너무 작아 입력할 수 없습니다” 에러메세지가 출력되게 하시오

ACCEPT p_job PROMPT '직업을 입력하세요 '

DECLARE

 v_job emp.job%TYPE := UPPER('&p_job');

 v_exception EXCEPTION;

 CURSOR emp_sumsal IS

  SELECT job, SUM(sal) AS sumsal

  FROM EMP

  WHERE job = v_job

  GROUP BY job;

  

BEGIN

 FOR emp_record IN emp_sumsal LOOP

  IF emp_record.sumsal < 5000 then

   RAISE v_exception;

  ELSE

   INSERT INTO emp348 VALUES(emp_record.job, emp_record.sumsal);

  END IF;

 END LOOP;

EXCEPTION

 WHEN v_exception THEN

  raise_application_error(-20001, '너무 작아 입력할 수 없습니다');

END;

/