#15_140703_PL_SQL_PACKAGE

# PACKAGE

l 패키지를 사용하는 이유

1.     정보(코드)를 숨길 수 있다

2.     쉬운 유지보수 관리 – 비슷한 업무의 프로시져들을 하나의 소스로 묶어서 관리

3.     전페 패키지가 메모리에 로드되어 응용프로그램 성능 향상

4.     오버로드 : 동일한 이름의 다중 서브 프로그램

l 패키지 구조

- 명세 : body에서 구현할 프로시져나 전역변수의 이름을 선언(책의 목차)

- 몸체 : 실제 구현 코드(프로시져, 함수)(책의 내용)

l Data Dictionalray 이용해서 패키지 보기

- SELECT text FROM user_source WHERE name='패키지 명칭' AND TYPE='패키지 타입';

- SELECT text FROM user_source WHERE name='SAL_PACK' AND TYPE='PACKAGE BODY';

- SELECT text FROM user_source WHERE name='SAL_PACK' AND TYPE='BODY';

l package 생성시 is나 as 2개 다 된다

l 패키지 포함 시킬 수 있는 오브젝트

- 프로시져

- 함수

문제184. 아래의 프로시져를 패키지로 만드시오

CREATE OR REPLACE PROCEDURE sal_rank

(p_rank IN NUMBER,

p_rank2 IN NUMBER)

IS

CURSOR emp_sal_cursor IS

 SELECT t_name, t_sal, 순위

  FROM (SELECT t_name, t_sal, RANK() OVER(ORDER BY t_sal desc) 순위 FROM emp_sal)

  WHERE 순위 BETWEEN p_rank AND p_rank2;

BEGIN

 FOR emp_record IN emp_sal_cursor LOOP

  dbms_output.put_line('순위 ' || emp_record.순위 ||' '||emp_record.t_name);

  dbms_output.put_line('순위 ' || emp_record.순위 ||' '||emp_record.t_sal);

END LOOP;

END;

/

-----------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE sal_pack AS

 PROCEDURE SAL_RANK(p_rank NUMBER, p_rank2 number);

END sal_pack;

/

CREATE OR REPLACE PACKAGE BODY sal_pack AS

 PROCEDURE SAL_RANK(p_rank NUMBER, p_rank2 number) IS

 CURSOR emp_sal_cursor IS

  SELECT t_name, t_sal, 순위

  FROM (SELECT t_name, t_sal, RANK() OVER(ORDER BY t_sal desc) 순위 FROM emp_sal)

  WHERE 순위 BETWEEN p_rank AND p_rank2;

 BEGIN

  FOR emp_record IN emp_sal_cursor LOOP

   dbms_output.put_line('순위 ' || emp_record.순위 ||' '||emp_record.t_name);

   dbms_output.put_line('순위 ' || emp_record.순위 ||' '||emp_record.t_sal);

 END LOOP;

 END SAL_RANK;

END sal_pack;

/

문제189. 사원번호를 넣으면 해당 사원이 근무하는 부서번호의 최대월급과 평균월급이 출력되는 패키지를 함수로 생성하시오

CREATE OR REPLACE PACKAGE pack_avgsal IS

 FUNCTION get_avgsal(p_empno emp.empno%type) RETURN NUMBER;

 FUNCTION get_maxsal(p_empno emp.empno%type) RETURN NUMBER;

 v_avgsal NUMBER(10);

 v_maxsal NUMBER(10);

END pack_avgsal;

/

CREATE OR REPLACE PACKAGE BODY pack_avgsal IS

 FUNCTION get_avgsal(p_empno emp.empno%TYPE) RETURN NUMBER IS

  BEGIN

   SELECT AVG(sal) INTO v_avgsal

   FROM EMP

   WHERE deptno=(SELECT deptno FROM EMP WHERE empno=p_empno);

   RETURN v_avgsal;  

 END get_avgsal;

 FUNCTION get_maxsal(p_empno emp.empno%type) RETURN NUMBER IS

  BEGIN

   SELECT MAX(sal) INTO v_maxsal

   FROM EMP

   WHERE deptno=(SELECT deptno FROM EMP WHERE empno=p_empno);

  RETURN v_maxsal;

 END get_maxsal;

END pack_avgsal;

/

SELECT empno, ename, deptno, pack_avgsal.get_avgsal(empno), pack_avgsal.get_maxsal(empno) FROM EMP;

문제189. 위의 패키지에 함수를 하나 더 추가하는데 사원번호를 넣으면 해당 사원이 근무하는 부서번호의 최대월급이 출력되는 패키지를 만드시오

CREATE OR REPLACE PACKAGE pack_avgsal IS

 FUNCTION get_avgsal(p_empno emp.empno%type) RETURN NUMBER;

 FUNCTION get_maxsal(p_empno emp.empno%type) RETURN NUMBER;

 v_avgsal NUMBER(10);

 v_maxsal NUMBER(10);

END pack_avgsal;

/

CREATE OR REPLACE PACKAGE BODY pack_avgsal IS

 FUNCTION get_avgsal(p_empno emp.empno%TYPE) RETURN NUMBER IS

  BEGIN

   SELECT AVG(sal) INTO v_avgsal

   FROM EMP

   WHERE deptno=(SELECT deptno FROM EMP WHERE empno=p_empno);

   RETURN v_avgsal;  

 END get_avgsal;

 FUNCTION get_maxsal(p_empno emp.empno%type) RETURN NUMBER IS

  BEGIN

   SELECT MAX(sal) INTO v_maxsal

   FROM EMP

   WHERE deptno=(SELECT deptno FROM EMP WHERE empno=p_empno);

  RETURN v_maxsal;

 END get_maxsal;

END pack_avgsal;

/

SELECT empno, ename, deptno, pack_avgsal.get_avgsal(empno), pack_avgsal.get_maxsal(empno) FROM EMP;

문제190. 아래의 데이터를 입력하고 위에서 만든 패키지를 수정하는데 ORA-01427 에러가 나서 수행이 안되면 똑 같은 사원번호인 사원이 여러명 있어서 수행이 안됩니다. 라는 메시지가 출력되게 예외처리 하시오

INSERT INTO emp(empno,ename,sal) VALUES(7788,'JACK',3400);

CREATE OR REPLACE PACKAGE pack_avgsal IS

 FUNCTION get_avgsal(p_empno emp.empno%type) RETURN NUMBER;

 FUNCTION get_maxsal(p_empno emp.empno%type) RETURN NUMBER;

 v_avgsal NUMBER(10);

 v_maxsal NUMBER(10);

END pack_avgsal;

/

  

CREATE OR REPLACE PACKAGE BODY pack_avgsal IS

 FUNCTION get_avgsal(p_empno emp.empno%TYPE) RETURN NUMBER IS

  expt EXCEPTION;

  PRAGMA exception_init(expt, -01427);

 

  BEGIN

   SELECT AVG(sal) INTO v_avgsal

   FROM EMP

   WHERE deptno=(SELECT deptno FROM EMP WHERE empno=p_empno);

   RETURN v_avgsal;

  

   EXCEPTION

    WHEN expt THEN

    dbms_output.put_line('중복된 데이터가 있습니다');

   

 END get_avgsal;

 FUNCTION get_maxsal(p_empno emp.empno%type) RETURN NUMBER IS

  expt EXCEPTION;

  PRAGMA exception_init(expt, -01427);

  

  BEGIN

   SELECT MAX(sal) INTO v_maxsal

   FROM EMP

   WHERE deptno=(SELECT deptno FROM EMP WHERE empno=p_empno);

  RETURN v_maxsal;

 

  EXCEPTION

   WHEN expt THEN

    dbms_output.put_line('중복된 데이터가 있습니다');

  END get_maxsal;

END pack_avgsal;

/

마지막문제. 패키지를 생성하고 exec 명령어를 실행하여 아래와 같은 결과가 출력되도록 하시오

SELECT file_name FROM dba_data_files;

SELECT NAME FROM v$controlfile;

SELECT member FROM v$logfile;

SQL> exec dbfile.datafile2;

C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF

C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF

C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF

C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF

C:\DATA01.DBF

SQL> exec dbfile.controlfile2;

C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF

SQL> exec dbfile.redofile2;

C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_9PV238OG_.LOG

C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_9PV237TV_.LOG

CREATE OR REPLACE PACKAGE dbfile IS

 PROCEDURE datafile2;

 PROCEDURE controlfile2;

 PROCEDURE redofile2;

END dbfile;

/

CREATE OR REPLACE PACKAGE BODY dbfile IS

 PROCEDURE datafile2 IS

  CURSOR data_cursor IS

   SELECT file_name FROM dba_data_files;

 

  BEGIN

   FOR data_record IN data_cursor LOOP

    dbms_output.put_line(data_record.file_name);

   END LOOP;

 END datafile2;

 PROCEDURE controlfile2 IS

  CURSOR control_cursor IS

   SELECT NAME FROM v$controlfile;

  

  BEGIN

   FOR control_record IN control_cursor LOOP

    dbms_output.put_line(control_record.name);

   END LOOP;

 END controlfile2;

 PROCEDURE redofile2 IS

  CURSOR redo_cursor IS

   SELECT member FROM v$logfile;

 

  BEGIN

   FOR redo_record IN redo_cursor LOOP

    dbms_output.put_line(redo_record.member);

   END LOOP;

  END redofile2;

END dbfile;

/

EXEC dbfile.datafile2;

EXEC dbfile.controlfile2;

EXEC dbfile.redofile2;