[오라클] PACKAGE(패키지)

• PACKAGE(패키지) : 여러개의 Procedure 와 여러개의 Function 들의 묶음

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

1) 문법

create or replace package 패키지명
is 선언부(함수/프로시저 선언)
end 패키지명;

2) 실습

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); -- info 출력 함수
    function   func_gender(p_jubun in employees.jubun%type) return Nvarchar2; -- 성별을 구하는 함수, 타입에는 자리수를 쓰면 안된다! 
end employee_pack;
-- Package EMPLOYEE_PACK이(가) 컴파일되었습니다.
-- 껍데기만 만들고 알맹이가 없는 상태.

---

2. PACKAGE(패키지)의 Body(본문) 생성하기

1) 문법

create or replace package body 패키지명
is 선언부
begin 실행부
end 패키지명

2) 실습

create or replace package body employee_pack 
is
    procedure  pcd_emp_info(p_deptno in employees.department_id%type) 
    is  --결과가 복수 행일때는 커서를 쓴다. !! 
        cursor  cur_empinfo -- 커서 사용
          is
          SELECT E.department_id, D.department_name, E.employee_id, E.ename
          FROM
          (
            select department_id, employee_id, first_name || ' ' || last_name AS ename
            from employees
            where department_id = p_deptno
          ) E JOIN departments D
          ON E.department_id = D.department_id;
          
          v_fetch_count  number := 0;
    
    begin
        for v_rcd in cur_empinfo loop
              
          v_fetch_count := cur_empinfo%rowcount;
          -- 커서명%ROWCOUNT ==> 현재까지 FETCH 된 레코드(행)의 갯수를 반환해줌.
          
          if(v_fetch_count = 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_fetch_count = 0) then
           dbms_output.put_line('>> 부서번호 ' || p_deptno || '은 없습니다.<<'); 
        else
           dbms_output.put_line(' ');
           dbms_output.put_line('>> 조회건수 ' || v_fetch_count || '개');
        end if;

    end  pcd_emp_info; -- end는 프로시저의 end

    procedure  pcd_dept_info(p_deptno in departments.department_id%type) -- 고유한 값pk, unique이기 때문에 중복된 값이 나오지 않아  커서를 쓸 필요가 없다.
    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 Nvarchar2
    is
        v_jubun_length  number;
        v_cnt           number(2) := 0; --넘버 자리수 2개, 초기치 0값 
        v_gender        Nvarchar2(1) := ''; -- 자리수 1개, 초기치 null '' / 공백은 ' ' 
        error_jubun     exception;
    begin
        v_jubun_length := length(p_jubun);
       
       if(v_jubun_length != 13) then -- length가 13이 아니라면 에러를 띄운다. 
            raise  error_jubun;
       else
           for i in 1..v_jubun_length loop
             v_cnt := v_cnt + 1; -- 매번 증가
             
             if not (substr(p_jubun, i, 1) between '0' and '9') then 
                raise  error_jubun; -- 주번에서 i번째부터 1글자씩 뽑음. 계~속 돌리고 0부터 9가 아니라면 에러를 띄우고 반복문을 빠져나온다.
             end if;   
           end loop; -- 반복문을 빠져나올때 v_cnt가 13이 되어야 된다.
           
           if(v_cnt = v_jubun_length) then
              if( substr(p_jubun, 7, 1) in('1','3') ) then 
                  v_gender := '남';
              elsif( substr(p_jubun, 7, 1) in('2','4') ) then 
                  v_gender := '여';
              else     
                  raise  error_jubun;
              end if;    
           end if;
       end if;
       return v_gender;
           
       exception 
           when error_jubun then
                raise_application_error(-20001, '>> 주민번호가 올바르지 않습니다. <<');  
                
    end func_gender;

end employee_pack;
-- Package Body EMPLOYEE_PACK이(가) 컴파일되었습니다.

---

3. 생성되어진 패키지 소스 보기

select text
from user_source
where type = 'PACKAGE' and name = 'EMPLOYEE_PACK';
-- 선언부만 보여준다. (패키지에 어떠한 함수나 프로시저가 있는지)

--- *** 생성되어진 패키지 BODY(본문) 소스 보기 *** ---
select line, text -- line은 줄 라인까지 보여준다.
from user_source
where type = 'PACKAGE BODY' and name = 'EMPLOYEE_PACK';