****** PACKAGE(패키지) ******

   -------------------- ****** PACKAGE(패키지) ****** ----------------------
   
   --->   PACKAGE(패키지)란?  여러개의 Procedure 와 여러개의 Function 들의 묶음
   
   -------------------------------------------------------------------------------------

---

 

1. PACKAGE(패키지) 선언하기

   create or replace package employee_pack
   is
      -- employee_pack 패키지에 들어올 프로시저 또는 함수를 선언한다.
      procedure pcd_emp_info(p_deptno IN employees.department_id%type);
      procedure pcd_dept_info(p_deptno IN departments.department_id%type);
      function  func_gender(p_jubun IN employees.jubun%type) return varchar2;
      
   end employee_pack;
   -- Package EMPLOYEE_PACK이(가) 컴파일되었습니다.

 

2. PACKAGE(패키지)의 Body(본문) 선언하기

   create or replace package body employee_pack
   is
      procedure pcd_emp_info(p_deptno IN employees.department_id%type)
      is
         -- select 가 나오면 무조건 cursor
         cursor cur_empinfo
         is
         select D.department_id, D.department_name, 
                  E.employee_id, E.first_name || ' ' || E.last_name AS ENAME
         from departments D join employees E
         on D.department_id = E.department_id
         where E.department_id = p_deptno;
         
         v_cnt number := 0;
         
      begin
         for v_rcd in cur_empinfo loop
         
             v_cnt := cur_empinfo%rowcount;
             
             if ( v_cnt = 1 ) then
                dbms_output.put_line( lpad('-',60,'-') );
                dbms_output.put_line('부서번호  부서명       사원번호    사원명');
                dbms_output.put_line( lpad('-',60,'-') );
             end if;  
             
             dbms_output.put_line( v_rcd.department_id || ' ' || 
                                   v_rcd.department_name || ' ' || 
                                   v_rcd.employee_id || ' ' || 
                                   v_rcd.ename);
         end loop;
         
         if ( v_cnt = 0 ) then 
            dbms_output.put_line('>> 부서번호 ' || p_deptno || '은 없습니다. <<');
         else 
            dbms_output.put_line(' ');
            dbms_output.put_line('>> 조회건수 : ' || v_cnt || '개');
         end if;   
      end pcd_emp_info;
      -----------------------------------------------------------
      procedure pcd_dept_info(p_deptno IN departments.department_id%type)
      is
          v_department_id      departments.department_id%type;
          v_department_name    departments.department_name%type;
          
      begin
             select department_id, department_name
                    into
                    v_department_id, v_department_name
             from departments
             where department_id = p_deptno;
             
             dbms_output.put_line( lpad('-',40,'-') );
             dbms_output.put_line('부서번호  부서명');
             dbms_output.put_line( lpad('-',40,'-') );
             
             dbms_output.put_line(v_department_id || ' ' || v_department_name);
             
             exception
                 when no_data_found then 
                      dbms_output.put_line('>> 부서번호 ' || p_deptno || '은 없습니다. <<');
                      
      end pcd_dept_info;
      -----------------------------------------------------------
      function  func_gender(p_jubun IN employees.jubun%type) 
      return varchar2
      is
           v_gender_num  varchar2(1);
           v_result      varchar2(100);
      begin
           if( length(p_jubun) = 13 ) then
               v_gender_num := substr(p_jubun, 7, 1);
               
               if( v_gender_num in('1','3') ) then
                   v_result := '남';
               elsif( v_gender_num in('2','4') ) then 
                   v_result := '여';
               else 
                   v_result := '주민번호가 올바르지 않습니다.';
               end if;    
               
           else
               v_result := '주민번호의 길이가 13자리 아닙니다.';
           end if;
           
           return v_result;
      end func_gender;
      
   end employee_pack;
   
   -- Package Body EMPLOYEE_PACK이(가) 컴파일되었습니다.

[실행]

   begin
       employee_pack.pcd_emp_info(30);
   end;
   -- 30번 부서의 사원들 정보 조회
   /*
   ------------------------------------------------------------
    부서번호  부서명       사원번호    사원명
    ------------------------------------------------------------
    30 Purchasing 114 Den Raphaely
    30 Purchasing 115 Alexander Khoo
    30 Purchasing 116 Shelli Baida
    30 Purchasing 117 Sigal Tobias
    30 Purchasing 118 Guy Himuro
    30 Purchasing 119 Karen Colmenares
 
>> 조회건수 : 6개

   */
   
   begin
       employee_pack.pcd_emp_info(2342);
   end;
   -- >> 부서번호 2342은 없습니다. <<
   
   ------------------------------------------------------
   ------------------------------------------------------
   
   begin
       employee_pack.pcd_dept_info(30);
   end;
   -- 30번 부서의 부서정보 조회
   /*
   ----------------------------------------
    부서번호  부서명
    ----------------------------------------
    30 Purchasing
   */
   
   begin
       employee_pack.pcd_dept_info(2342);
   end;
   -- >> 부서번호 2342은 없습니다. <<

   ------------------------------------------------------
   ------------------------------------------------------
   
    select employee_pack.func_gender('9007301234567')
        , employee_pack.func_gender('0207303234567')
        , employee_pack.func_gender('0107304234567')
        , employee_pack.func_gender('9107302234567')
   from dual;
   -- 남 남 여 여
   
   select employee_pack.func_gender('900730123456')
        , employee_pack.func_gender('0207309234567')
   from dual;
   -- 주민번호의 길이가 13자리 아닙니다.	주민번호가 올바르지 않습니다.
   
   select employee_id, first_name || ' ' || last_name as ename
        , employee_pack.func_gender(jubun) as gender
   from employees
   order by 1;

 

---

 

[패키지 소스 보기]

   select line, text
   from user_source
   where type = 'PACKAGE' and name = 'EMPLOYEE_PACK';

[패키지 BODY(본문) 소스 보기]

   select line, text
   from user_source
   where type = 'PACKAGE BODY' and name = 'EMPLOYEE_PACK';