패키지

PL/SQL 서브프로그램을 그룹화한 객체

-- 패키지 생성 --
CREATE [OR REPLACE] PACKAGE 패키지명
IS
  PROCEDURE 프로시저명;
  FUNCTION 함수명;
END;

-- 패키지 BODY 생성 --
CREATE [OR REPLACE] PACKAGE BODY 패키지명
IS
  선언부
  
  PROCEDURE 프로시저명
    IS ~~
    BEGIN ~~
  END 프로시저명;
  
  FUNCTION 함수명
    IS ~~
    BEGIN ~~
  END 함수명;
END 패키지명;

ex)

CREATE OR REPLACE PACKAGE pkg_example
IS
  spec_no NUMBER := 10;
  
  FUNCTION func_aftertax(sal NUMBER) RETURN NUMBER;
  PROCEDURE pro_emp(in_empno emp.empno%TYPE);
  PROCEDURE pro_dept(in_deptno  dept.deptno%TYPE);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_example
IS
  FUNCTION func_aftertax(sal NUMBER) RETURN NUMBER
  IS
    tax number := 0.05;
  BEGIN
    RETURN sal-sal*tax;
  END func_aftertax;
    
  PROCEDURE pro_emp(in_empno emp.empno%TYPE)
  IS
    out_name  emp.eNAME%TYPE;
    out_sal   emp.sal%TYPE;
  BEGIN
    select ename, sal into out_name, out_sal
    FROM emp
    WHERE empno = in_empno;
    dbms_output.put_line('이름 : ' || out_name || ', 급여 : ' || out_sal);
  END pro_emp;
  
  PROCEDURE pro_dept(in_deptno dept.deptno%TYPE)
  IS
    out_dname dept.dname%TYPE;
    out_loc dept.loc%TYPE;
  BEGIN
    select dname, loc into out_dname, out_loc
    FROM dept
    WHERE deptno = in_deptno;
    dbms_output.put_line('부서명 : ' || out_dname || ', 위치 : ' || out_loc);
  END pro_dept;
END pkg_example;

-- 실행 --
select pkg_example.func_aftertax(1000) from dual;
exec pkg_example.pro_emp(7369);
exec pkg_example.pro_dept(10);