[Oracle] CH8 : 패키지

패키지

패키지 : 복수의 서브 프로그램들을 하나로 통합(패키지화)하는 오브젝트

통합 관리하면 오브젝트 간 의존성 관리 등을 간단히 처리할 수 있음

패키지는 Oracle에 저장되는 오브젝트이므로 이름을 정의해야 하고 프로시저나 함수와는 달리 정의부(.plh)와 본체(.plb)를 별도로 작성해야 함

 

정의부 : 본체에 포함된 프로그램명 등 기술

---

패키지의 장점

- 애플리케이션 설계의 용이성(의존성 관리 용이)

정의부에는 본체의 서브 프로그램을 실행하는 데 필요한 정보를 기술

ㄴ 정의부만 정의되어 있으면 패키지 내의 오브젝트를 실행하는 프로그램은 패키지 본체가 미완성이라 하더라도 정상적으로 컴파일

패키지화하면 패키지 본체는 실행 쪽의 프로그램에서 직접 참조하지 않음

ㄴ 패키지 본체의 정의를 변경해도 실행 측의 프로그램을 다시 컴파일할 필요가 없기 때문에 프로그램 간의 의존성 관리가 간단해짐

- 정보 은폐

패키지는 보통 실행 측의 프로그램에서 패키지 내의 오브젝트를 참조할 수 있도록 오브젝트 정보를 정의부에 정의하지만, 패키지 본체에서만 오브젝트를 작성하는 것도 가능

ㄴ 패키지 본체에 오브젝트를 정의하면 실행 측 프로그램에서 객체를 참조할 수 없음 -> 이 특징을 이용하여 공개할 수 없는 정보는 패키지 본체에만 정의하여 은폐하는 것이 가능 -> 보안상 위험한 데이터를 처리하는 프로그램이 있다고 해도, 그 존재를 숨김으로써 악의적인 사용자의 공격을 피할 수 있음

- 디스크 I/O 절감을 통한 성능 향상

패키지를 사용하면 패키지 내의 오브젝트가 처음 실행될 때 메모리에 패키지 전체가 한꺼번에 호출되기 대문에 두 번째 이후는 성능이 향상됨 -> 패키지 내의 오브젝트 한 개를 호출하면 패키지 내의 모든 오브젝트가 메모리로 호출됨

이와 같이 어떤 처리를 실행하면서 다음 프로그램을 호출할 때, 디스크에서 호출보다 I/O를 감소시켜 성능을 향상할 수 있음

- 오브젝트의 지속성

패키지의 정의부에서는 서브 프로그램 이외에도 변수나 상수, 커서, 예외를 정의할 수 있음

공용 오브젝트(Public Object) : 정의부에서 정의된 오브젝트

ㄴ 사용하는 각 세션에서 지속적으로 유효하게 사용 가능 (ex. 정의부에서 정의된 변수에 값을 대입하고 프로그램을 종료해도 해당 세션을 종료하지 않으면 그 변수의 값이 보존된 상태로 유지)

---

패키지 작성 - 정의부

패키지를 만들기 위해서 정의부(Specification part)와 본체를 따로 작성해야 함

패키지에 포함된 오브젝트의 프로그램명 등을 기술

정의부에서 선언된 오브젝트(공용 오브젝트)는 패키지 내의 프로그램뿐만 아니라 패키지 외부 프로그램에서도 참조 가능

-- 패키지 작성 - 정의부
CREATE [OR REPLACE] PACKAGE <패키지명>
{IS | AS}
  <정의부>
END [<패키지명>];

CREATE OR REPLACE PACKAGE test_pack
IS
    PROCEDURE employee_ename(name VARCHAR2); -- 정의부에서 세 개의 공용 오브젝트 선언
    PROCEDURE employee_empno(eno NUBMER);
    pub_no NUMBER := 0;
END;
/

패키지가 생성되었습니다.

오브젝트의 종류	오브젝트명
프로시저	employee_ename
프로시저	employee_empno
변수	pub_no

작성한 패키지의 정의부 정보는 DESCRIBE 명령으로 확인 가능

---

패키지 작성 - 본체

패키지 본체 : 정의부에서 기술한 프로그램의 소스 코드 기술

정의부에서 선언하지 않은 개체를 전용 오브젝트로 작성 가능. 전용 오브젝트는 패키지 내에서만 사용 가능(패키지 외부 프로그램에서는 참조 불가)

CREATE [OR REPLACE] PACKEAGE BODY <패키지명>
{IS | AS}
  <본체>
END [<패키지명>];

CREATE OR REPLACE PACKAGE BODY test_pack
IS
    pri_no NUBMER := 0; -- 전용 오브젝트인 변수 pri_no 선언(패키지 내에서만 사용 가능)
    
    PROCEDURE employee_ename(name VARCHAR2)
        IS
        BEGIN
            SELECT sal INTO pri_no FROM emp WHERE ename = name;
            DBMS_OUTPUT.PUT_LINE(pri_no);
        END employee_ename;
    PROCEDURE employee_empno(eno NUMBER)
        IS
        BEGIN
            SELECT sal INTO pub_no FROM emp WHERE empno = eno;
            DBMS_OUTPUT.PUT_LINE(pub_no);
        END employee_empno;
END;
/

패키지 본문이 생성되었습니다.

---

패키지 오브젝트 사용 방법

-- 패키지 내 오브젝트 지정 방법
<패키지명>.<오브젝트명>;

BEGIN
	test_pack.employee_empno(7369); -- 패키지 test_pack 내의 프로시저 employee_empno 실행
END;
/
800

PL/SQL 처리가 정상적으로 완료되었습니다.

 

특정 프로그램에서만 호출이 가능하게 하려면, 프로그램의 정의부에 ACCESSIBLE BY절 지정

CREATE OR REPLACE PACKAGE <패키지명> ACCESSIBLE BY (<패키지명>) ...

---

오브젝트의 지속성

공용 변수(패키지 정의부에서 정의된 변수) 등은 패키지의 외부에서 호출 가능, 세션을 통해 변경 내용이 적용됨

CREATE OR REPLACE PACKAGE pub_pack
IS
    pub_no NUBMER;
END;
/

패키지가 생성되었습니다.


BEGIN
    pub_pack.pub_no := 1000; -- pub_pack의 정의부에 정의된 공용 변수 pub_no에 1000 대입
    DBMS_OUTPUT.PUT_LINE(pub_pack.pub_no);
END;
/
1000

PL/SQL 처리가 정상적으로 완료되었습니다.


BEGIN
    DBMS_OUTPUT.PUT_LINE(pub_pack.pub_no + 1000); --프로그램은 종료되어도 공용 변수에 대입된 값은 세션이 끊어질 때까지 유효하므로 다음 프로그램 내에서도 값에 액세스 가능
END;
/
2000

PL/SQL 처리가 정상적으로 완료되었습니다.

---

오버로드(Overload)

오버로드 : 같은 이름의 서브 프로그램을 정의할 수 있는 기능

파라미터의 수와 순서, 데이터 타입이 다른 경우에 오버로드 사용 가능

- 오버로드는 동일한 타입의 서브 프로그램만 사용 가능(프로시저와 함수로 오버로드 사용 불가)

-- 패키지 오버로드 : 정의부
CREATE OR REPLACE PACKAGE over
IS
    PROCEDURE employee(name VARCHAR2); -- 파라미터에 어떤 것이 전달되든
    PROCEDURE employee(eno NUMBER);    -- 하나의 프로그램으로 통합하여 구현 가능
    pub_no NUMBER := 0;
 END;
 /
 
 패키지가 생성되었습니다.
 
 
 -- 패키지 오버로드 : 본체
CREATE OR REPLACE PACKAGE BODY over
IS
    pri_no NUMBER := 0;
     
PROCEDURE employee(name VARCHAR2)
IS
BEGIN
    SELECT sal INTO pri_no FROM emp WHERE ename = name;
    DBMS_OUTPUT.PUT_LINE(pri_no);
END employee;
 
PROCEDURE employee(eno NUMBER)
IS
BEGIN
    SELECT sal INTO pub_no FROM emp WHERE empno = eno;
    DBMS_OUTPUT.PUT_LINE(pub_no);
    END employee;
END;
/

패키지 본문이 생성되었습니다.

 

오버로드한 프로시저 실행

EXECUTE over.employee('SMITH')
800

PL/SQL 처리가 정상적으로 완료되었습니다.

EXECUTE over.employee(7369)
800

PL/SQL 처리가 정상적으로 완료되었습니다.

---

패키지 재컴파일

패키지가 의존하는 오브젝트의 정의가 변경된 경우, 패키지를 재컴파일해야 함

패키지는 정의부와 본체로 구성되어 있기 때문에 패키지 전체를 재컴파일해야할 수도, 본체만 재컴파일해야할 수도 있음.

ㄴ 본체만 변경하고 정의부는 변경하지 않았다면, 본체만 재컴파일(본체만 재컴파일하려면 BODY 지정)

ALTER PACKAGE <패키지명> COMPILE [BODY];

 

패키지 의존 관계 확인과 재컴파일

CREATE OR REPLACE PROCEDURE call_test_pack
IS
BEGIN
    test_pack.employee_empno(7369); -- 패키지 test_pack을 호출하는 프로시저 call_test_pack
END;
/

프로시저가 생성되었습니다.

-- 패키지 본체만 재컴파일
ALTER PACKAGE test_pack COMPILE BODY;

패키지 본문이 변경되었습니다.

SELECT object_name, object_type, status
FROM   user_objects
WHERE  object_name IN ('TEST_PACK', 'CALL_TEST_PACK');

OBJECT_NAME     OBJECT_TYPE   STATUS
-----------     -----------   -----------
TEST_PACK       PACKAGE BODY  VALID
TEST_PACK       PACKAGE       VALID
CALL_TEST_PACK  PROCEDURE     VALID  -- 의존 오브젝트인 call_test_pack의 STATUS는 VALID

-- 패키지 전체 재컴파일
ALTER PACKAGE test_pack COMPILE;

패키지가 변경되었습니다.

SELECT object_name, object_type, status
FROM   user_objects
WHERE object_name IN ('TEST_PACK', 'CALL_TEST_PACK');

OBJECT_NAME     OBJECT_TYPE      STATUS
----------      -----------     ---------
TEST_PACK       PACKAGE BODY     VALID
TEST_PACK       PACKAGE          VALID
CALL_TEST_PACK  PROCEDURE        INVALID -- 패키지 전체를 재컴파일했기 때문에 의존 오브젝트 INVALID

---

패키지 삭제

DROP PACKAGE [BODY] <패키지명>;

- 패키지를 삭제하면 해당 패키지 내의 오브젝트를 참조하는 다른 프로그램은 STATUS가 INVALID로 변경

- 패키지 내의 오브젝트는 개별 삭제 불가 -> 패키지 정의부와 본체 소스 변경 해야 함

- 본체만 삭제하는 것은 가능하고(BODY 지정), 본체만 삭제한 경우 패키지의 정의부가 영향을 받지 않는 한 패키지를 참조하는 다른 프로그램의 STATUS는 VALID 상태 유지

 

예외 처리에서 패키지 활용

사전에 이름이 정해져 있는 내부 예외 외에 패키지를 활용하여 프로그램에서 예외명 정의

패키지의 정의부에서 예외명을 정의하면 정의된 예외명은 공용 오브젝트가 되고, 어떤 프로그램에서도 공통적으로 사용 가능

CREATE OR REPLACE PACKAGE err_pack
IS
    null_error EXCEPTION;
    PRAGMA EXCEPTION_INIT(null_error, -01400);
    fetch_out EXCEPTION;
    PRAGMA EXCEPTION_INIT(fetch_error, -1002);
    lock_error EXCEPTION;
    PRAGMA_EXCEPTION_INIT(lock_error, -00060);
END;
/

패키지가 생성되었습니다.


CREATE OR REPLACE PROCEDURE err_test
IS
BEGIN
    INSERT INTO dept VALUES(null, 'SE', 'TOKYO');
EXCEPTION
    WHEN err_pack.null_error THEN
        DBMS_OUTPUT.PUT_LINE('NULL_ERROR!');
END;
/

프로시저가 생성되었습니다.


EXECUTE err_test
NULL_ERROR!

PL/SQL 처리가 정상적으로 완료되었습니다.

---

유틸리티 패키지

유틸리티 패키지(Utility Package) : Oracle에서 사전에 준비되어 있는 패키지

-- DBMS_LOCK 패키지의 SLEEP 프로시저 사용
-- DMBS_LOCK 패키지를 사용하려면 SYS 사용자로부터 EXECUTE 권한 필요
CREATE OR REPLACE PROCEDURE lock_pro
IS
BEGIN
    FOR i in 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'HH24:MI:SS'));
        DBMS_LOCK.SLEEP(1);
    END LOOP;
END;
/

프로시저가 생성되었습니다.

EXECUTE lock_pro
18:18:02
18:18:03
18:18:04
18:18:05
18:18:06

PL/SQL 처리가 정상적으로 완료되었습니다.

대부분의 유틸리티 패키지는 Oracle DB 생성 시 catproc.sql 스크립트에 의해 자동으로 생성되며, 모든 사용자에게 사용 권한이 부여됨

 

패키지명	개요
DBMS_SQL	8i 이전 Oracle에서 동적 SQL, 데이터 정의문 실행
DBMS_ALERT	데이터베이스 이벤트 알림을 프로그램 간에 비동기적으로 송수신
DBMS_PIPE	동일한 인스턴스에 연결된 세션 간에 통신을 수행
DBMS_JOB	Stored 서브 프로그램을 정기적으로 실행
DBMS_SCHEDULER	Stored 서브 프로그램을 정기적으로 실행
DBMS_LOCK	사용자가 애플리케이션 잠금(Lock)을 실행
UTL_MAIL	Stored 서브 프로그램에서 메일을 송신

---

UTL_FILE 패키지

UTL_FILE 패키지를 사용하면 OS 파일의 읽고 쓰기가 가능함

액세스 가능한 디렉터리를 설정하여 해당 디렉터리에서 파일에 대한 읽기 및 쓰기 수행

 

UTL_FILE 패키지를 사용하기 전, 액세스를 허용할 디렉터리를 디렉터리 오브젝트(Directory Object)로 설정

- 디렉터리 오브젝트 생성 및 권한 부여

CREATE DIRECTORY 명령을 사용하여 디렉터리 오브젝트 생성

디렉터리 오브젝트는 사용자에 대해 읽기 및 쓰기 권한을 부여할 수 있기 때문에 보안 수준(Security Level) 제어 가능

AS 키워드를 지정하고, IS 키워드는 사용 불가

CREATE [OR REPLACE] DIRECTORY <디렉터리명>
AS '<디렉터리 위치(Path)>';

 

디렉터리 오브젝트를 생성한 후, 그 디렉터리에 대한 읽기 및 쓰기 권한 부여

CREATE OR REPLACE DIRECTORY utl_data
AS 'C:\work';

디렉토리가 생성되었습니다.

GRANT READ, WRITE ON DIRECTORY utl_data TO scott; -- 사용자 SCOTT에게 권한 부여

권한이 부여되었습니다.

 

UTL_FILE 패키지의 사용 방법

CREATE OR REPLACE PROCEDURE file_unload_dept
IS
    CURSOR dept_cur IS SELECT dname FROM dept;
    file_hand          UTL_FILE.FILE_TYPE; -- 파일 조작을 수행하는 데 필요한 파일 핸들을 받을 변수 지정
    day             DATE;
    name            VARCHAR2(20);
BEGIN
    SELECT sysdate INTO day FROM dual;
    name := TO_DATE(day) || 'dept.txt';
    file_hand := UTL_FILE.FOPEN('UTL_DATA', name, 'a'); -- FOPEN 함수를 사용하여 OS 파일 오픈
    FOR dept_rec IN dept_cur LOOP
        UTL_FILE.PUT_LINE(file_hand, dept_rec.danme); -- 파일을 오픈하면 1행의 데이터 기록
    END LOOP;
    UTL_FILE.FCLOSE(file_hand); -- 기록을 마치면 FCLOSE 프로시저로 OS 파일 닫기
END;
/

프로시저가 생성되었습니다.

EXECUTE file_unload_dept

PL/SQL 처리가 정상적으로 완료되었습니다.

 

FOPEN 함수 설정 값

설정 값	개요
UTL_DATA	디렉터리 오브젝트명을 지정
name	파일명을 지정
a	OS 파일에 대해 추가 기록을 수행

 

파일 핸들 선언(UTL_FILE.FILE_TYPE 데이터 타입)

파일 조작에 필요한 파일 핸들을 받을 변수를 선언해야 함.

파일 핸들을 받는 데이터 타입: FILE_TYPE 타입

<파일 핸들명> UTL_FILE.FILE_TYPE;

 

파일 열기(UTL_FILE.FOPEN 함수)

FOPEN 함수를 이용하여 입력용, 출력용 파일 열기 가능. 반환 값으로 파일 핸들 리턴

파일이 존재하지 않은 경우에는 출력파일을 자동으로 작성

-- 파일 오픈
UTL_FILE.FOEN(
    <location IN VARCHAR2>,
    <filename IN VARCHAR2>,
    <open_mode IN VARCHAR2>,
    [,<maxline_size IN BINARY_INTEGER>]
    )
RETURN <UTL_FILE.FILE_TYPE>;

파라미터	개요
location	디렉터리 오브젝트명 또는 파일을 OPEN할 디렉터리 경로를 지정. 미리 설정한 디렉터리만 지정 가능
filename	OPEN할 파일명 지정
open_mode	파일 OPEN 방법 지정 r: 텍스트 읽기 w: 텍스트 쓰기 a: 텍스트 추가 쓰기 rb: 바이트 읽기 wb: 바이트 쓰기 ab: 바이트 추가 쓰기
maxline_size	줄 바꿈 문자가 포함된 파일의 한 행당 최대 문자 수 지정. 기본값은 1024자

 

파일에 기록(UTL_FILE.PUT 프로시저)

PUT 프로시저를 사용하여 파일에 데이터 기록

- 행 종료 기호(엔터)을 추가하지 않기 때문에 연속으로 PUT 프로시저를 사용하면 같은 행에 데이터가 계속 추가됨

- 행 종료를 위해 UTL_FILE.NEW_LINE 프로시저 이용

UTL_FILE.PUT(
    <file IN FILE_TYPE>,
    <buffer IN VARCHAR2>
);

파라미터	개요
file	파일 핸들을 지정
buffer	파일에 기록할 텍스트를 저장할 버퍼를 지정

 

행 종료(UTL_FILE.NEW_LINE 프로시저)

NEW_LINE 프로시저를 이용해 행 종료(엔터) 기록

UTL_FILE.NEW_LINE(
    <file IN FILE_TYPE>,
    <line IN NATURAL := 1>
);

파라미터	개요
file	파일 핸들을 지정
line	파일을 기록할 행 종료 기호 수 지정 현재 행을 종료시키고 다음 행도 공백으로 하고 싶은 경우에는 '2' 지정, 기본값 : 1

 

데이터의 기록과 행 종료(UTL_FILE.PUT_LINE 프로시저)

PUT_LINE 프로시저를 이용하여 파일에 데이터를 기록한 뒤, 행 종료 기호 기록

UTL_FILE.PUT_LINE(
    <file IN FILE_TYPE>,
    <buffer IN VARCHAR2>,
    <autoflush IN BOOLEAN DEFAULT FALSE>
);

파라미터	개요
file	파일 핸들을 지정
buffer	파일에 기록할 텍스트를 저장할 버퍼를 지정
autoflush	기록 후에 버퍼를 디스크에 Flush할지 여부를 지정. 기본값은 FALSE

 

파일로부터 데이터 읽기(UTL_FILE.GET_LINE 프로시저)

GET_LINE 프로시저를 이용하여 파일에서 텍스트 한 행씩 가져옴

한 번에 한 행만 읽을 수 있기 때문에 보통 루프 내에서 처리

NO_DATA_FOUND 예외 : 읽을 수 있는 행이 없을 때 예외 발생(모든 데이터를 읽어 들인 끝)

UTL_FILE.GET_LINE(
    <file IN FILE_TYPE>,
    <buffer OUT VARCHAR2>,
    <len IN PLS_INTEGER DEFAULT NULL>
);

파라미터	개요
file	파일 핸들을 지정
buffer	파일에 기록할 텍스트를 저장할 버퍼를 지정
len	파일로부터 읽어들일 바이트 수 지정

 

파일 닫기(UTL_FILE.FCLOSE 프로시저)

UTL_FILE.FCLOSE( <file IN OUT FILE_TYPE> );

파라미터	개요
file	파일 핸들을 지정

 

 

UTL_FILE 패키지의 주요 프로시저와 함수

기능	개요
FOPEN 함수	입력용. 출력용 파일을 OPEN
PUT 프로시저	파일에 데이터를 기록. 단, 행 종료 기호는 추가 X
NEW_LINE 프로시저	행 종료 기호를 기록
PUT_LINE 프로시저	파일에 데이터를 기록하고 행 종료 기호를 추가
GET_LINE 프로시저	파일로부터 텍스트를 한 행씩 읽어들임
FCLOSE 프로시저	파일을 닫음

 

 

 

 

참조 : 책 '전문가를 위한 오라클 PL/SQL 입문'