Self-Check

1. hr_pkg에는 사원이름 반환, 신규사원 입력, 퇴사 사원을 처리하는 서브 프로그램이 있다. hr_pkg2라는 패키지를 만들고, 이번에는 부서이름 반환, 신규부서 입력, 부서를 삭제하는 서브 프로그램을 만들어 보자(단, 부서를 삭제할 때는 삭제할 부서에 속한 사원이 있는지 체크해서, 있으면 메시지 처리하고 없을 때만 삭제해야 한다).

2. 오라클에서는 새로운 번호를 매길 때, 보통 시퀀스 객체를 사용한다. 예를 들어, 신규사원 입력 시 사번을 가져와야 하는데, 이 때 사번 시퀀스를 생성해 놓으면 “사번시퀀스.NEXTVAL”로 다음 순번의 사번을 가져올 수 있다. ch12_seq_pkg란 이름의 패키지에 시퀀스처럼 동작하도록 get_nextval이란 함수를 만들어 보자. 이 함수는 세션별 시퀀스 값을 가져와야 하며 초깃값은 1, 1씩 증가하고 세션이 달라지면 다시 초깃값을 가져와야 한다.

3. 패키지 커서를 사용할 때는 주의해야 한다. 즉 패키지 커서를 사용한 후 커서를 닫지 않으면, 같은 세션에서 해당 커서를 다시 사용할 때 오류가 발생한다. 다음은 ch12_exacur_pkg 패키지의 선언부인데, pc_depname_cur 커서를 사용해 부서정보를 추출하는 프로시저를 추가해 보자. 단, 이 프로시저 내부에서는 해당 커서의 ‘열기-패치-닫기’ 작업을 수행해야 하며, 해당 커서를 열때 혹시라도 닫혀 있지 않으면 오류를 발생시키지 말고 닫는 작업까지 수행하도록 작성해야 한다.

입력

    CREATE OR REPLACE PACKAGE ch12_exacur_pkg IS

         -- ROWTYPE형 커서 헤더선언
         CURSOR pc_depname_cur ( dep_id IN departments.department_id%TYPE )
             RETURN departments%ROWTYPE;

    END ch12_exacur_pkg;

4. 다음과 같은 기능을 수행하는 get_dep_hierarchy란 이름으로 2개의 함수를 만들어 보자.

    (1) 매개변수 : department_id
        출력     : 부서 번호와 부서명

    (2) 매개변수 : department_id, parent_id
        출력     : 해당 부서번호와 부서명,
        parent_id에 속하는 모든 부서번호와 부서명

5. 시스템 패키지인 DBMS_METADATA를 사용해 DBMS_OUTPUT 패키지 소스를 추출해 보자.