[Oracle] PL/SQL(3) 예외처리, Cursor

 

예외(EXCEPTION)

-PL/SQL의 오류를 예외라고 한다.
-컴파일시 문법적 오류, 실행시 발생하는 오류
-미리 정의된 오라클 서버예외 : 선언할 필요 없고, 발생시 예외절로 자동 이동
-사용자 예외를 강제 발생 : 선언부에서 예외 정의, 실행부에서 RAISE문 사용


-미리 정의된 서버오류 처리

DECLARE
    employee_record employees%ROWTYPE;

BEGIN 
    SELECT * INTO employee_record
    FROM employees
    WHERE first_name = 'Lisa';

SELECT employee_id, last_name, department_id 
            INTO employee_record.employee_id, 
                     employee_record.last_name, 
                     employee_record.department_id 
                        FROM employees
                        WHERE department_id = 50;    

DBMS_OUTPUT.PUT_LINE('사번' || employee_record.employee_id);
DBMS_OUTPUT.PUT_LINE('사원명' || employee_record.last_name);
DBMS_OUTPUT.PUT_LINE('부서번호' || employee_record.department_id);

EXCEPTION
    --UNIQUE 제약을 갖는 컬럼에 중복된 데이터를 insert 시 
WHEN DUP_VAL_ON_INDEX THEN
            DBMS_OUTPUT.PUT_LINE('이미 존재하는 사원입니다.');
    --SELECT문 결과가 2개이상 로우를 반환
WHEN TOO_MANY_ROWS THEN
            DBMS_OUTPUT.PUT_LINE('검색된 로우가 너무 많습니다.');
    --데이터 없을 때
WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('검색된 사원이 없습니다.');
    --그 밖에 예외 이유
WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('몰라요');

END;

 

--사용자가 강제로 예외 발생

DECLARE

    e_user_exception  EXCEPTION;  --예외 정의
    cnt NUMBER :=0;

BEGIN
    SELECT COUNT(employee_id) INTO cnt
                FROM employees
                WHERE department_id = 40;
    IF cnt < 5 THEN
            RAISE e_user_exception;  -- 인위적으로 예외 발생
    END IF;

EXCEPTION
        WHEN e_user_exception THEN
             DBMS_OUTPUT.PUT_LINE('5명 이하 부서 금지');

END;

---

커서(CURSOR)

-질의 결과가 다중로우일 경우 커서를 사용

 

CURSOR 커서명 IS SELECT 문장;

OPEN 커서명;

FETCH 커서명 IS 변수..;

CLOSE 커서명;

DECLARE
     --커서 선언
     CURSOR department_cursors IS
    SELECT department_id, department_name, location_id
    FROM departments;
     department_record department_cursors%ROWTYPE;

BEGIN
     --커서 열기
    OPEN department_cursors;

    LOOP
    FETCH department_cursors
        INTO department_record.department_id,
        department_record.department_name,
        department_record.location_id;
    EXIT WHEN department_cursors%NOTFOUND;  --데이터가 없을 때까지

    dbms_output.put_line(department_record.department_id
        ||'    '||department_record.department_name
        ||'    '||department_record.location_id);
   END LOOP;    

   CLOSE department_cursors;

END;

 

커서를 이용하여 사원의 정보를 출력 (사원번호, 사원이름, 급여, 급여누계)

DECLARE
   CURSOR employee_cursors IS
    SELECT employee_id, first_name, salary
        FROM employees;
   employee_record employee_cursors%ROWTYPE;
   v_totalsalary NUMBER := 0;

BEGIN
OPEN employee_cursors;

   dbms_output.put_line('==========================');
   dbms_output.put_line('사원번호,    사원이름,    급여,    급여누계');   
   dbms_output.put_line('==========================');

  LOOP
    FETCH employee_cursors INTO employee_record;
    EXIT WHEN employee_cursors %NOTFOUND;

 v_totalsalary := v_totalsalary + employee_record.salary;
    dbms_output.put_line(employee_record.employee_id
    ||',    '|| employee_record.first_name||',    '||employee_record.salary
   ||',    '|| v_totalsalary);
  END LOOP;

dbms_output.put_line(employee_cursors%rowcount||'행이 검색');
CLOSE employee_cursors;

END;