************** 부서/직원 리포트 ******************

부서 : 부서번호       부서명             위치

        10         ACCOUNTING        NEW YORK

사원 : 사번     이름         급여          급여 등급

       7871    Anderson    2000         ***   
      7890    Andrew       700         * 
      7870    Scott       2400         **** 
      7866    David        900         * 
      7891    Sylvia      3200         *****

2명의 급여가 인상되었습니다.


위와 같은 리포트를 뽑아내고 $1000보다 적은 급여를 받는 직원의 급여를 10%인상하는 프로그램을 작성하여 봅니다.

ACCEPT p_deptno PROMPT 'Please enter the department number : '

DECLARE

   v_deptno    NUMBER(2);

   v_dname     CHAR(14);

   v_loc       CHAR(13);

   v_empno     emp.empno%TYPE;

   v_ename     emp.ename%TYPE;

   v_sal       emp.sal%TYPE;

   v_grade     salgrade.grade%TYPE;

   v_dno       emp.deptno%TYPE := &p_deptno;

   CURSOR emp_cursor IS

          SELECLT empno, ename, sal

          FROM emp

          WHERE deptno = &p_deptno;

   e_no_emp    EXCEPTION;

BEGIN

   -- print out the report title

   DBMS_OUTPUT.PUT_LINE('*********부서/직원 리포트*********');

   SELECT  deptno, dname, loc

      INTO v_deptno, v_dname, v_loc

      FROM dept

      WHERE   deptno = v_dno;

   DBMS_OUTPUT.PUT_LINE('부서 :  부서번호   부서명        위치');

   DBMS_OUTPUT.PUT_LINE('     '∥v_deptno∥'    '∥v_dname∥
                        '   '∥v_loc∥'   ');

   DBMS_OUTPUT.PUT_LINE('사원 : 사번   이름   급여    급여등급');

   OPEN emp_cursor;

   FETCH emp_cursor INTO v_empno, v_ename, v_sal;

   /* according to the salary amount,

      print out the stars */

   WHILE emp_cursor%FOUND LOOP

      SELECT grade

         INTO v_grade

         FROM salgrade

         WHERE v_sal > losal AND v_sal <hisal;

      DBMS_OUTPUT.PUT('      '∥v_empno∥'   '∥v_ename∥
                      '      '∥v_sal∥'     ');

      FOR I in 1..v_grade LOOP

         DBMS_OUTPUT.PUT('*');

      END LOOP;

      DBMS_OUTPUT.PUT_LINE('');

      FETCH emp_cursor INTO v_empno, v_ename, v_sal;

   END LOOP;

   CLOSE emp_cursor;

   UPDATE emp

      SET     sal = sal * 1.1

      WHERE   sal < 1000;

   IF SQL%ROWCOUNT = 0 THEN

      RAISE e_no_emp;

   ELSE

      DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT ∥'명의 급여가 ' ||
                           '인상되었습니다.');

   END IF;

   COMMIT;

EXCEPTION

   WHEN no_data_found THEN

      DBMS_OUTPUT.PUT_LINE('There is no matching data.');

   WHEN too_many_rows THEN

      DBMS_OUTPUT.PUT_LINE('More than one matching data. ');

   WHEN e_no_emp THEN

      DBMS_OUTPUT.PUT_LINE('No employee with a smaller ' ||
                           'than  $1000.');

   --in case of any other exception

   WHEN others THEN

      DBMS_OUTPUT.PUT_LINE('Some other error occurred.');

END;  

 

개요

   예외는 PL/SQL 블록의 실행 중에 발생하여 블록의 주요 작업 부분을 중단시킵니다.    항상PL/SQL 예외가 발생할 때 블록은 항상 종료되지만, 마지막 조치 작업을 수행하도    록 예외 처리기 부분을 작성할 수 있습니다.

   예외를 일으키는 두 가지 방법

예외 유형

실행 중에  프로그램이 중단되지 않도록  예외에 대한 프로그램을 할 수 있습니다. 3가지 종류의 예외가 있습니다.
 

예외

설명

처리용 지시

미리 정의된 오라클 서버오류

PL/SQL코드에서 자주 발생하는 약 20까지 오류

선언할 수 없고, 오라클 서버가 암시적으로 그것을 일으키도록 합니다.

미리 정의되지않은 오라클 서버오류

기타 표준 오라클 서버 오류

선언 섹션 내에서 선언하고, 오라클 서버가 암시적으로 그것을 일으키도록 합니다.

사용자 정의 오류

개발자가 정한 조건이 만족되지 않을 경우

선언섹션에서 선언하고, 명시적으로 일으킵니다.

주: Developer Forms와 같이 클라이언트에  PL/SQL문이 있는 어플리케이션은 독자적인 예외를 가집니다.


예외 트래핑(trapping)

   PL/SQL 블록의 예외 처리 섹션 내에서 해당 루틴을 포함함으로써 모든 에러를 트랩할    수 있습니다. 각각의 에러 처리기는 WHEN 절로 구성되는데 그곳에 에러는 명시하고,    WHEN 절 뒤에는 예외가 발생될 때 실행되기 위한 문장들이 옵니다.

   구문에서,

     exception    선언섹션에서 선언된 미리 정의된 예외의 표준 이름 이거나 사용자                         정의예외의 이름입니다.

     statement    하나 이상의 PL/SQL 또는 SQL문장입니다.

     OTHERS      명시적으로 선언되지 않은 모든 예외를 트랩하는  예외 처리절입니다.

WHEN OTHERS 예외 처리기

   예외처리 섹션은 지정된 예외만 트랩합니다. OTHERS 예외 처리기를 사용하지 않으면    다른 예외들은 트랩되지 않습니다. 이것은 아직 처리되지 않은 모든 예외를 트랩합니    다. 그러므로, OTHERS는 마지막에 정의되는 예외 처리기입니다.

   OTHERS 처리기는 아직 트랩되지 않은 모든 예외들을 트랩합니다. 일부 오라클 툴들은    어플리케이션에서 이벤트를 발생시키기 위해 일으키는 개별적인 미리 정의된 예외들을    가지고 있습니다. OTHERS는 또한 이 예외들도 트랩합니다.

미리 정의된 오라클 서버 에러

해당 예외처리 루틴에서 표준 이름을 참조함으로써  미리 정의된 오라클 서버 에러를    트랩합니다.

미리  정의된 예외 리스트의 자세한 내용들은 PL/SQL User’s Guide and Reference, Release 7.3 또는 8.0, “Error Handling.”를 참조하십시오.

주: PL/SQL 은 STANDARD 패키지에  미리 정의된 예외를 선언합니다. 가장 일반적인 예외인 NO_DATA_FOUND 와 TOO_MANY_ROWS 예외는 사용하는 것이  좋습니다.

미리 정의된 오라클 서버 에러 트랩

각 예외에 대한 슬라이드의 예에서, 사용자를 위해 메시지가  인쇄 됩니다.

어떤 시점에서든지 하나의 예외만이 발생되고 처리됩니다.

미리 정의되지 않은 오라클 서버 에러

우선 에러를 선언하고 나서 OTHERS 처리기를 사용하여 미리 정의되지 않은 오라클 서버 에러를 트랩합니다. 선언된 예외는 암시적으로  발생됩니다. PL/SQL에서, pragma EXCEPTION_INIT  는 오라클  에러 번호와  예외 이름을 관련시키기 위해 컴파일러에게  알려  줍니다. 이름으로 임의의 내부 예외를 참조하고 지정 처리기를 사용합니다.

주: PRAGMA (pseudoinstruction이라고도 함)는 PL/SQL 블록이 실행될 때 처리되지 않는 컴파일러 명령문임을 의미하는 키워드입니다. 블록 내에서 예외 이름이 발생하면 그것을 관련된  오라클 서버  에러 번호로  해독하기 위해  PL/SQL 컴파일러에게 지시합니다.

미리 정의되지 않은 오라클 서버 에러 트랩

1. 선언섹션에서 예외 이름을 선언합니다.
      
    구문
 

exception       EXCEPTION;

    구문에서:       exception               예외의 이름입니다.

2. PRAGMA EXCEPTION_INIT 문장을 사용하여 표준 오라클 서버 에러 번호와 선언된 예외를 관련시킵니다.
        
    구문
 

PRAGMA EXCEPTION_INIT(exception, error_number);

     구문에서:       exception               이전에 선언된 예외입니다.
                        error_number          표준 오라클 서버 에러 번호입니다.

3. 해당 예외처리 루틴에서 선언된 예외를 참조합니다.
    슬라이드예에서 , 재고품이 있다면 프로세싱을 중지하고 사용자에게 메시지를 보여 줍니다.

자세한 내용은 Oracle Server Messages, Release 7.3 또는 8.0 을 참조하십시오.

사용자 정의 예외 트래핑

PL/SQL 에서는 개별적으로 예외를 정의할 수 있습니다. 사용자 정의 PL/SQL 예외는 다음을 준수해야 합니다.

사용자 정의 예외 트래핑

명시적으로 선언하고  발생시킴으로써 사용자 정의 예외를 트랩할 수 있습니다.

1. 선언 섹션에서 사용자 정의된 예외에 대한 이름을 선언합니다.
   
     구문
 

exception       EXCEPTION;

      구문에서:       exception               예외명입니다.

2. 실행 섹션에서 명시적으로 예외를 발생시키기 위해 RAISE 문장을 사용합니다.
   
      구문

RAISE exception;

      구문에서:       exception               앞에 선언된 예외명입니다.
 
3. 해당 예외 처리기 안에 선언된 예외를 참조합니다.  슬라이드 예에서 , 이 고객은 만일 제품에 대한 어떤 재고가 남아 있다면, 데이터 베이스에서 제품을 제거할 수 없는 규칙을 가지고 있습니다. 이 규칙을 수행시키기 위해 어떠한 제약조건도 넣을 수 없기 때문에, 개발자는 어플리케이션에서 그것을 명시적으로 처리합니다. PRODUCT 테이블에서 DELETE를 수행하기 전에, 제품의 재고가 있을 때는 INVENTORY 테이블을 질의합니다.
     
주: 동일한 예외를 다시 호출 환경으로 동일한 예외를 발생시키기 위해 예외 처리기에서 RAISE 문장을 사용합니다.

에러 트래핑 함수

   예외가 발생할 때, 두 함수를 사용하여 관련된 에러코드 또는 에러 메시지를 확인할 수    있습니다.
   
   코드 또는 메시지 값에 따라, 에러에 대해 취할 작업을 정할 수 있습니다. SQLCODE로    내부 예외에 대한 오라클 에러 번호를 리턴합니다. 이때 에러 번호를 SQLERRM으로    전달하면, 그 에러 번호와 관련된 메시지를 리턴합니다.
 

함수

설명

SQLCODE

에러코드에  대한 숫자 값을 리턴합니다.(NUMBER 변수에 저장할 수 있습니다.)

SQLERRM

에러번호와 관련된 메시지를 갖는 문자데이터를 리턴합니다.


SQLCODE 값 예

SQLCODE 값

설명

0

예외가 없습니다.

1

사용자 정의 예외.

+100

NO_DATA_FOUND 예외.

negative number

다른 오라클 서버 에러 번호.

예외 트래핑 함수

WHEN OTHERS 절에서 예외가 트랩될 때, 이 에러들을 식별하기 위한 함수를 사용할 수 있습니다. 슬라이드 예에서는  변수에 지정된 SQLCODE  값과 SQLERRM 값을 기술합니다. 이때 이 변수들을 SQL 문장에서 사용합니다.

SQLERRM 값을 변수에 저장하려 하기 전에 알려진 길이로  SQLERRM 값을 절단시킵니다.

서브블록에서 예외 전달

   예외를 처리한 후 서브블록은 정상적으로 종료되고 제어가 서브 블록의 END 문장으로    가서 바로 둘러싸는 블록(상위블록)으로 넘어갑니다. 그러나 PL/SQL 블록이 예외를    일으키고 그 블록이 예외에 대한 처리기를 가지고 있지 않다면, 예외는 처리기를 발견    할 때까지 계속해서  둘러싸는 블록에게 전달됩니다. 예외를 처리하는 블록이 하나도    없으면, 호스트 환경에 처리되지 않은 예외로 나타납니다. 예외가 둘러싸는 블록(상위    블록)으로 전달될 때, 그 블록 안에 남아 있는 나머지 실행 작업은 수행되지 않고 통과    됩니다.이러한 처리 방법의 장점 가운데 하나는 둘러싸는 블록에 대해서는 보다 일반적    예외처리를 하게 하는 반면, 개별 블록에서는 독점적인 에러의 처리를  하는 문장을 쓸    수 있다는 것입니다.

표준화되지 않은 에러 코드와 에러 메시지를 리턴하는 RAISE_APPLICATION_ERROR프로시저를 사용합니다. RAISE_APPLICATION_ERROR 로 어플리케이션에 대한 에러를 보고할 수 있고 처리되지 않은 예외가 리턴되지 않도록 합니다.
구문에서,

error_number

-20000 과  20999 사이의, 예외에 대해 지정된 번호입니다.

message

예외에 대한 사용자 지정 메시지입니다.  2048바이트 길이까지의 문자열입니다.

TRUE |  FALSE

선택적 부울 매개변수입니다. TRUE이면, 에러는 이전의 에러 스택에 위치하게 됩니다.  FALSE (디폴트)이면, 에러는 모든 이전의 에러를 대체합니다.


예

...
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR (-20201,
        'Manager is not a valid employee.');
END;

예

...
DELETE FROM emp
WHERE mgr = v_mgr ;
IF SQL%NOTFOUND THEN
   RAISE_APPLICATION_ERROR(-20202,'This is not a valid manager') ;
END IF ;
...

************** 부서/직원 리포트 ******************

부서 : 부서번호       부서명             위치

        10         ACCOUNTING        NEW YORK

사원 : 사번     이름         급여          급여 등급

       7871    Anderson    2000         ***   
      7890    Andrew       700         * 
      7870    Scott       2400         **** 
      7866    David        900         * 
      7891    Sylvia      3200         *****

2명의 급여가 인상되었습니다.


위와 같은 리포트를 뽑아내고 $1000보다 적은 급여를 받는 직원의 급여를 10%인상하는 프로그램을 작성하여 봅니다.

ACCEPT p_deptno PROMPT 'Please enter the department number : '

DECLARE

   v_deptno    NUMBER(2);

   v_dname     CHAR(14);

   v_loc       CHAR(13);

   v_empno     emp.empno%TYPE;

   v_ename     emp.ename%TYPE;

   v_sal       emp.sal%TYPE;

   v_grade     salgrade.grade%TYPE;

   v_dno       emp.deptno%TYPE := &p_deptno;

   CURSOR emp_cursor IS

          SELECLT empno, ename, sal

          FROM emp

          WHERE deptno = &p_deptno;

   e_no_emp    EXCEPTION;

BEGIN

   -- print out the report title

   DBMS_OUTPUT.PUT_LINE('*********부서/직원 리포트*********');

   SELECT  deptno, dname, loc

      INTO v_deptno, v_dname, v_loc

      FROM dept

      WHERE   deptno = v_dno;

   DBMS_OUTPUT.PUT_LINE('부서 :  부서번호   부서명        위치');

   DBMS_OUTPUT.PUT_LINE('     '∥v_deptno∥'    '∥v_dname∥
                        '   '∥v_loc∥'   ');

   DBMS_OUTPUT.PUT_LINE('사원 : 사번   이름   급여    급여등급');

   OPEN emp_cursor;

   FETCH emp_cursor INTO v_empno, v_ename, v_sal;

   /* according to the salary amount,

      print out the stars */

   WHILE emp_cursor%FOUND LOOP

      SELECT grade

         INTO v_grade

         FROM salgrade

         WHERE v_sal > losal AND v_sal <hisal;

      DBMS_OUTPUT.PUT('      '∥v_empno∥'   '∥v_ename∥
                      '      '∥v_sal∥'     ');

      FOR I in 1..v_grade LOOP

         DBMS_OUTPUT.PUT('*');

      END LOOP;

      DBMS_OUTPUT.PUT_LINE('');

      FETCH emp_cursor INTO v_empno, v_ename, v_sal;

   END LOOP;

   CLOSE emp_cursor;

   UPDATE emp

      SET     sal = sal * 1.1

      WHERE   sal < 1000;

   IF SQL%ROWCOUNT = 0 THEN

      RAISE e_no_emp;

   ELSE

      DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT ∥'명의 급여가 ' ||
                           '인상되었습니다.');

   END IF;

   COMMIT;

EXCEPTION

   WHEN no_data_found THEN

      DBMS_OUTPUT.PUT_LINE('There is no matching data.');

   WHEN too_many_rows THEN

      DBMS_OUTPUT.PUT_LINE('More than one matching data. ');

   WHEN e_no_emp THEN

      DBMS_OUTPUT.PUT_LINE('No employee with a smaller ' ||
                           'than  $1000.');

   --in case of any other exception

   WHEN others THEN

      DBMS_OUTPUT.PUT_LINE('Some other error occurred.');

END;  

 

X 정답:B


X 정답:B


X 정답:B


X 정답:B


X 정답:C


X 정답:B


X 정답:ABD


X 정답:A