공부했던 자료 정리하는 용도입니다.
재배포, 수정하지 마세요.
예제는 Oracle Database에서 기본으로 제공되는 SCOTT계정 데이터로 진행됩니다.
패키지(package)
패키지는 업무나 기능 면에서 연관성이 높은 프로시저, 함수 등 여러 개의 PL/SQL 서브 프로그램을 하나의 논리 그룹으로 묶어 통합 · 관리하는 데 사용하는 객체를 의미한다.
■ 패키지의 장점
| 장 점 | 설 명 |
| 모듈성 | 서브프로그램을 포함한 여러 PL/SQL 구성 요소를 모듈화하여 PL/SQL로 제작한 프로그램의 사용 및 관리에 도움 모듈성 : 잘 묶어 둔다는 의미로 프로그램의 이해를 쉽게 하고 패키지 사이의 상호 작용을 더 간편하고 명료하게 하는 역할 |
| 쉬운 응용 프로그램 설계 | 패키지에 포함할 서브 프로그램은 완벽히 완성되지 않아도 정의가 가능 (전체 소스코드를 다 작성하기 전에 미리 패키지에 저장할 서브 프로그램을 지정할 수 있어 설계가 쉬워짐) |
| 정보 은닉 | 제작 방식에 따라 패키지에 포함하는 서브 프로그램의 외부 노출 여부나 접근 여부를 지정할 수 있어 보안 강화 |
| 기능성 향상 | 패키지 내부에는 서브프로그램 외에 변수 · 커서 · 예외 등도 각 세션이 유지되는 동안 선언해서 공용(public)으로 사용 가능. ex) 특정 커서 데이터는 세션이 종료될 때까지 보존되어 여러 서브 프로그램에서 사용가능 |
| 성능 향상 | 패키지를 사용할 때 패키지에 포함한 모든 서브프로그램이 메모리에 한번에 로딩되는데 메모리에 로딩된 후의 호출은 디스크 I/O를 일으키지 않으므로 성능이 향상됨 |
■ 패키지 구조
패키지는 프로시저나 함수와 달리 명세(specification)와 본문(body) 두 부분으로 나누어 제작한다.
1) 패키지 명세(specification)
CREATE [OR REPLACE] PACKAGE [패키지 이름]
IS | AS
[서브프로그램을 포함한 다양한 객체 선언]
END [패키지 이름];패키지에 포함할 변수, 상수, 예외, 커서 그리고 PL/SQL 서브 프로그램을 선언하는 용도로 작성한다. 패키지 명세에 선언한 여러 객체는 패키지 내부뿐만 아니라 외부에서도 참조할 수 있다.
-- 패키지 명세 예시 (변수 1개, 프로시저 2개, 함수 1개 선언)
CREATE OR REPLACE PACKAGE pkg_example
IS
spec_no NUMBER := 10;
FUNCTION func_aftertax(sal NUMBER) RETURN NUMBER;
PROCEDURE pro_emp(in_empno IN EMP.EMPNO%TYPE);
PROCEDURE pro_dept(in_deptno IN DEPT.DEPTNO%TYPE);
END;
/
+ 패키지 명세 확인 & 선언한 서브 프로그램 학인
-- 1) USER_SOURCE 데이터 사전으로 조회
SELECT TEXT
FROM USER_SOURCE
WHERE TYPE = 'PACKAGE'
AND NAME = 'PKG_EXAMPLE';-- 2) DESC 명령어로 조회
DESC pkg_example;이미 생성되어 있는 패키지 명세의 코드를 확인하거나 선언한 서브 프로그램을 확인하려면 USER_SOURCE 데이터 사전을 조회하거나 DESC 명령어를 이용하면 된다.
2) 패키지 본문(body)
CREATE [OR REPLACE] PACKAGE BODY [패키지 이름]
IS | AS
패키지 명세에서 선언한 서브프로그램을 포함한 여러 객체를 정의
경우에 따라 패키지 명세에 존재하지 않는 객체 및 서브프로그램도 정의 가능
END [패키지 이름];패키지 본문에는 패키지 명세에서 선언한 서브 프로그램 코드를 작성한다. 패키지 본문 이름은 패키지 명세 이름과 같게 지정해야하며 패키지 명세에 선언하지 않은 객체나 서브 프로그램을 정의하는 것도 가능하다. 대신 패키지 본문에만 존재하는 프로그램은 패키지 내부에서만 사용 가능하다.
-- 패키지 본문 생성 예시(pkg_example)
CREATE OR REPLACE PACKAGE BODY pkg_example
IS
body_no NUMBER := 10; -- 본문(body)에만 있기 때문에 pkg_examle 패키지 안에서만 사용가능
FUNCTION func_aftertax(sal NUMBER) RETURN NUMBER
IS
tax NUMBER := 0.05;
BEGIN
RETURN (ROUND(sal - (sal * tax)));
END func_aftertax;
PROCEDURE pro_emp(in_empno IN EMP.EMPNO%TYPE)
IS
out_ename EMP.ENAME%TYPE;
out_sal EMP.SAL%TYPE;
BEGIN
SELECT ENAME, SAL INTO out_ename, out_sal
FROM EMP
WHERE EMPNO = in_empno;
DBMS_OUTPUT.PUT_LINE('ENAME : ' || out_ename);
DBMS_OUTPUT.PUT_LINE('SAL : ' || out_sal);
END pro_emp;
PROCEDURE pro_dept(in_deptno IN 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('DNAME : ' || out_dname);
DBMS_OUTPUT.PUT_LINE('LOC : ' || out_loc);
END pro_dept;
END;
/패키지 명세에서 선언했던 pkg_example 의 본문을 생성하는 예시이다.
■ 서브 프로그램 오버로드
CREATE [OR REPLACE] PACKAGE [패키지 이름]
IS | AS
[서브프로그램 종류] [서브 프로그램 이름](파라미터 정의);
[서브프로그램 종류] [서브 프로그램 이름](개수나 자료형, 순서가 다른 파라미터 정의);
END [패키지 이름];기본적으로 서브 프로그램 이름은 중복될 수 없지만 같은 패키지 내에서 사용하는 파라미터의 개수, 자료형, 순서가 다를 경우에 한해서만 이름이 같은 서브 프로그램을 정의할 수 있다. (서브 프로그램 종류는 같아야 함) 이를 서브 프로그램 오버로드(subprogram overload)라고 한다. 보통 같은 기능을 하는 서브 프로그램의 입력 데이터를 각각 다르게 정의할 때 사용한다.
-- 서브프로그램 오버로드 예시(명세)
CREATE OR REPLACE PACKAGE pkg_overload
IS
PROCEDURE pro_emp(in_empno IN EMP.EMPNO%TYPE);
PROCEDURE pro_emp(in_ename IN EMP.ENAME%TYPE);
END;
/위의 패키지는 사원번호나 사원 이름을 입력받아 사원 이름과 급여를 출력하기 위해 pro_emp 를 2개의 프로시저로 오버로드한 것이다. (명세 부분)
-- 서브 프로그램 오버로드 예시(본문)
CREATE OR REPLACE PACKAGE BODY pkg_overload
IS
PROCEDURE pro_emp(in_empno IN EMP.EMPNO%TYPE) -- 서브 프로그램 오버로드
IS
out_ename EMP.ENAME%TYPE;
out_sal EMP.SAL%TYPE;
BEGIN
SELECT ENAME, SAL INTO out_ename, out_sal
FROM EMP
WHERE EMPNO = in_empno;
DBMS_OUTPUT.PUT_LINE('ENAME : ' || out_ename);
DBMS_OUTPUT.PUT_LINE('SAL : ' || out_sal);
END pro_emp;
PROCEDURE pro_emp(in_ename IN EMP.ENAME%TYPE) -- 서브 프로그램 오버로드
IS
out_ename EMP.ENAME%TYPE;
out_sal EMP.SAL%TYPE;
BEGIN
SELECT ENAME, SAL INTO out_ename, out_sal
FROM EMP
WHERE ENAME = in_ename;
DBMS_OUTPUT.PUT_LINE('ENAME : ' || out_ename);
DBMS_OUTPUT.PUT_LINE('SAL : ' || out_sal);
END pro_emp;
END;
/pkg_overload 패키지의 본문을 작성한다.
오버로드된 2개의 pro_emp 함수는 인자를 받는 부분과 WHERE 절을 제외한 모든 부분이 같다.
■ 패키지 사용
패키지를 통해 그룹화된 변수, 상수, 예외, 커서, PL/SQL 서브 프로그램은 [패키지 이름].[객체 이름]; 으로 사용할 수 있다.
-- 패키지에 포함된 서브프로그램 실행 예시
BEGIN
DBMS_OUTPUT.PUT_LINE('--pkg_example.func_aftertax(3000)--');
DBMS_OUTPUT.PUT_LINE('after-tax : ' || pkg_example.func_aftertax(3000));
DBMS_OUTPUT.PUT_LINE('--pkg_example.pro_emp(7788)--');
pkg_example.pro_emp(7788);
DBMS_OUTPUT.PUT_LINE('--pkg_example.pro_dept(10)--');
pkg_example.pro_dept(10);
DBMS_OUTPUT.PUT_LINE('--pkg_overload.pro_emp(7788)--');
pkg_overload.pro_emp(7788);
DBMS_OUTPUT.PUT_LINE('--pkg_overload.pro_emp(''SCOTT'')--');
pkg_overload.pro_emp('SCOTT');
END;
/pkg_example , pkg_overload 패키지의 서브 프로그램을 실행하는 예시이다.
■ 패키지 수정/삭제
-- 패키지 명세와 본문을 한번에 삭제
DROP PACKAGE [패키지 이름];
-- 패키지의 본문만을 삭제
DROP PACKAGE BODY [패키지 이름];패키지 삭제에는 두 가지 방식이 있다. 하나는 패키지 명세와 본문을 한 번에 삭제하거나 패키지 본문만 삭제할 수도 있다. 패키지에 포함된 서브 프로그램을 따로 삭제하는 것은 불가능하고 CREATE OR REPLACE 문을 활용하여 패키지 안의 객체나 서브 프로그램을 수정 및 삭제할 수 있다.
■ 오라클 제공 패키지
사용자가 지정하는 패키지 외에도 오라클에서 기본으로 제공하는 패키지도 있다. (오라클 공식문서 참고)
트리거(trigger)
트리거는 이벤트(데이터베이스 안의 특정 상황이나 동작)가 발생할 경우에 자동으로 실행되는 기능을 정의하는 PL/SQL 서브 프로그램이다. 트리거를 이용해서 연관 데이터 작업을 잘 정의하면 여러 가지 이점이 있지만 트리거는 특정 작업 또는 이벤트 발생으로 다른 데이터 작업을 추가로 실행하기 때문에 무분별하게 사용하면 데이터베이스 성능을 떨어뜨리는 원인이 되므로 주의해야 한다. 트리거는 특정 이벤트가 발생할 때 자동으로 발생하는 서브 프로그램이므로 프로시저나 함수와 같이 EXECUTE 또는 PL/SQL 블록에서 따로 실행하지 못한다.
트리거를 통한 연관 데이터 작업의 장점
- 데이터와 연관된 여러 작업을 수행하기 위해 PL/SQL문 또는 서브 프로그램을 일일이 실행해야 하는 번거로움을 줄여줌.
- 제약 조건(constraints)만으로 구현이 어렵거나 불가능한 좀 더 복잡한 데이터 규칙을 정할 수 있어 더 수준 높은 데이터 정의가 가능
- 데이터 변경과 관련된 일련의 정보를 기록해 둘 수 있어 여러 사용자가 공유하는 데이터 보안성과 안정성, 문제가 발생했을 때 대처 능력을 높일 수 있다.
(테이블 · 뷰 · 스키마 · 데이터베이스 수준에서) 트리거로 지정할 수 있는 이벤트 - 데이터 정의어(DDL) : CREATE , ALTER , DROP - 데이터 조작어(DML) : INSERT , UPDATE , DELETE - 데이터 베이스 동작 : SERVERERROR , LOGON , LOGOFF , STARTUP , SHUTDOWN |
■ 트리거의 종류
| 종 류 | 설 명 |
| DDL 트리거 | CREATE , ALTER , DROP 과 같은 DDL 명령어를 기점으로 동작 |
| DML 트리거 | INSERT , UPDATE , DELETE 와 같은 DML 명령어를 기점으로 동작 |
| INSTEAD OF 트리거 | 뷰(View)에 사용하는 DML 명령어를 기점으로 동작 |
| 시스템(system) 트리거 | 데이터베이스나 스키마 이벤트로 동작 |
| 단순(simple) 트리거 | 다음 각 시점(timing point)에 동작 - 트리거를 작동시킬 문장이 실행되기 전 시점 - 트리거를 작동시킬 문장이 실행된 후 시점 - 트리거를 작동시킬 문장이 행에 영향을 미치기 전 시점 - 트리거를 작동시킬 문장이 행에 영향을 준 후 시점 |
| 복합(compound) 트리거 | 단순 트리거의 여러 시점에 동작 |
■ DML 트리거
CREATE [OR REPLACE] TRIGGER [트리거 이름]
BEFORE | AFTER
INSERT | UPDATE | DELETE ON [테이블 이름]
REFERENCING OLD as old | New as new
FOR EACH ROW WHEN [조건식]
FOLLOWS [트리거 이름 2], [트리거 이름 3] ...
ENABLE | DISABLE
DECLARE
[선언부]
BEGIN
[실행부]
EXCEPTION
[예외 처리부]
END;DML 트리거는 특정 테이블에 DML 명령어를 실행했을 때 작동하는 트리거이다.
- [트리거 이름] : 트리거 이름을 명시하고 트리거를 생성
OR REPLACE : 트리거 내용 갱신 - BEFORE | AFTER : 트리거가 작동할 타이밍 지정
BEFORE : DML 명령어가 실행되기 전
AFTER : DML 명령어가 실행된 후 - INSERT | UPDATE | DELETE ON [테이블 이름]
: 지정한 테이블에 트리거가 작동할 DML 명령어를 작성. 여러 개인 경우 OR 로 구분 - REFERENCING OLD as old | New as new (선택)
: DML로 변경되는 행의 변경 전 값과 변경 후 값을 참조하는 데 사용 - FOR EACH ROW WHEN [조건식] (선택)
: 트리거를 실행할 DML 문장에 한 번만 실행할지 DML 문장에 의해 영향받는 행별로 실행할지 지정
생략할 경우 : 트리거는 DML 명령어가 실행할 때 한 번만 실행
생략하지 않을 경우 : DML 명령어에 영향받는 행별로 트리거를 작동하되 WHEN 키워드를 함께 사용하면 DML 명령어에 영향받는 행 중 트리거를 작동시킬 행을 조건식으로 지정 가능 - FOLLOWS (선택) : 여러 관련 트리거의 실행 순서를 지정 (오라클 11g부터 사용 가능)
- ENABLE | DISABLE (선택) : 트리거의 활성화/비활성화를 지정 (오라클 11g부터 사용 가능)
1) DML 트리거 생성 및 사용 (BEFORE)
-- DML 트리거 생성 및 사용 예시(BEFORE)
-- 1) 트리거를 적용할 대상이 되는 EMP_TRG 테이블 생성
CREATE TABLE EMP_TRG
AS SELECT * FROM EMP;
-- 2) DML 실행 전에 수행할 트리거 trg_emp_nodml_weekend생성
CREATE OR REPLACE TRIGGER trg_emp_nodml_weekend
BEFORE -- DML 명령어가 실행되기 전 시점에 트리거가 작동함
INSERT OR UPDATE OR DELETE ON EMP_TRG -- EMP_TRG 테이블에 INSERT, UPDATE, DELETE 명령어가 실행될 때 트리거가 작동
BEGIN
IF TO_CHAR(sysdate, 'DY') IN ('토', '일') THEN -- DML을 실행하는 시점이 주말(토, 일)인 경우
IF INSERTING THEN -- INSERT가 실행되었을 경우
raise_application_error(-20000, '주말 사원정보 추가 불가'); -- raise_application_error 프로시저를 사용하여 사용자 정의 예외를 발생시킴
ELSIF UPDATING THEN -- UPDATE가 실행되었을 경우 -- 사용자 정의 예외의 첫 번째 파라미터는 예외코드를 의미(-20000 ~ 20999범위 사용 가능)
raise_application_error(-20001, '주말 사원정보 수정 불가');
ELSIF DELETING THEN -- DELETE가 실행되었을 경우
raise_application_error(-20002, '주말 사원정보 삭제 불가');
ELSE
raise_application_error(-20003, '주말 사원정보 변경 불가');
END IF;
END IF;
END;
/
-- 3) 트리거 동작 확인
-- 오라클이 설치된 OS의 날짜를 평일과 주말로 바꿔서 아래 SQL문 실행(윈도우인 경우 제어판에서 설정)
UPDATE emp_trg SET sal = 3500 WHERE empno = 7788;BEFORE 트리거 예시이다. DML을 실행하는 시점이 주말(토, 일)일 경우 raise_application_error 프로시저를 사용하여 사용자 정의 예외를 발생시킨다. (사용자 정의 예외의 첫 번째 파라미터는 예외 코드를 의미, 사용자 정의 예외 코드는 -20000 ~ -20999 범위 사용 가능)
주말에 DML 명령어가 실행되면 트리거가 오류를 발생시켜 오류 메시지와 함께 DML 명령어가 취소된다.
2) DML 트리거 생성 및 사용 (AFTER)
-- DML 트리거 생성 및 사용 예시(AFTER)
-- 1) DML이 실행된 경우 로그를 저장할 EMP_TRG_LOG 테이블 생성
CREATE TABLE EMP_TRG_LOG(
TABLENAME VARCHAR2(10), -- DML이 수행된 테이블 이름
DML_TYPE VARCHAR2(10), -- DML 명령어의 종류
EMPNO NUMBER(4), -- DML 대상이 된 사원 번호
USER_NAME VARCHAR2(30), -- DML을 수행한 USER 이름
CHANGE_DATE DATE -- DML이 수행된 날짜
);
-- 2) DML 실행 후 수행할 트리거 trg_emp_log 생성
CREATE OR REPLACE TRIGGER trg_emp_log
AFTER -- DML 명령어가 실행된 후 시점에 트리거가 작동
INSERT OR UPDATE OR DELETE ON EMP_TRG -- EMP_TRG 테이블에 INSERT, UPDATE, DELETE 명령어가 실행될 때 트리거가 작동
FOR EACH ROW -- DML 명령어에 영향을 받는 행별로 트리거를 작동
BEGIN
IF INSERTING THEN
INSERT INTO emp_trg_log -- INSERT가 실행되었을 경우 emp_trg_log에 로그 데이터 추가
VALUES('EMP_TRG', 'INSERT', :new.empno, -- new.empno는 새로 추가된 empno
SYS_CONTEXT('USERENV', 'SESSION_USER'), sysdate ); -- SYS_CONTEXT('USERENV', 'SESSION_USER')는 현재 데이터베이스에 접속중인 사용자
ELSIF UPDATING THEN
INSERT INTO emp_trg_log -- UPDATE가 실행되었을 경우 emp_trg_log에 로그 데이터 추가
VALUES('EMP_TRG', 'UPDATE', :old.empno, -- old.empno는 변경되기 전 empno
SYS_CONTEXT('USERENV', 'SESSION_USER'), sysdate );
ELSIF DELETING THEN
INSERT INTO emp_trg_log -- DELETE가 실행되었을 경우 emp_trg_log에 로그 데이터 추가
VALUES('EMP_TRG', 'DELETE', :old.empno,
SYS_CONTEXT('USERENV', 'SESSION_USER'), sysdate );
END IF;
END;
/
-- 3) 트리거 동작 확인 (INSERT문)
-- [3-1] EMP_TRG 테이블에 INSERT 실행 (앞서 했던 trg_emp_nodml_weekend트리거가 등록되어 있는 상태면 주말에 INSERT문 실행안됨)
INSERT INTO EMP_TRG
VALUES(9999, 'TestEmp', 'CLERK', 7788, TO_DATE('2018-03-03', 'YYYY-MM-DD'), 1200, NULL, 20);
-- [3-2] COMMIT 하기
COMMIT;
-- [3-3] EMP_TRG에 INSERT 결과 확인
SELECT * FROM EMP_TRG;
-- [3-4] EMP_TRG_LOG 테이블의 INSERT 로그 확인
SELECT * FROM EMP_TRG_LOG;AFTER 트리거 예시이다. DML문이 실행되면 EMP_TRG_LOG 테이블에 관련 기록을 추가한다.
- new.empno : 새로 추가된 empno
- old.empno : 변경되기 전 empno
- SYS_CONTEXT('USERENV', 'SESSION_USER')
: 세션 정보를 얻어오는 함수. USERENV 는 현재 세션의 환경정보를 반환하는 네임스페이스
자세한 내용은 https://hyunki1019.tistory.com/9 참고
EMP_TRG 테이블에 사원이 추가되었고 EMP_TRG_LOG 테이블에 INSERT 가 실행된 내용이 저장된 것을 확인할 수 있다.
-- 트리거 동작 확인(UPDATE문) (앞서 했던 trg_emp_nodml_weekend트리거가 등록되어 있는 상태면 주말에 INSERT문 실행안됨)
-- 1) UPDATE문 실행()
UPDATE EMP_TRG
SET SAL = 1300
WHERE MGR = 7788;
-- 2) COMMIT하기
COMMIT;
-- 3) EMP_TRG에 UPDATE 결과 확인
SELECT * FROM EMP_TRG;
-- [3-4] EMP_TRG_LOG 테이블의 UPDATE 로그 확인
SELECT * FROM EMP_TRG_LOG;UPDATE 문을 실행해도 똑같이 EMP_TRG_LOG 에 UPDATE 문에 대한 정보가 저장된다.
트리거의 옵션이 FOR EACH ROW 로이기 때문에 DML문에 영향을 받는 행별로 트리거가 적용된다. (2번 적용됨)
■ 트리거 정보 조회
SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_NAME, STATUS
FROM USER_TRIGGERS;트리거 정보를 확인하려면 USER_TRIGGERS 데이터 사전을 조회하면 된다.
■ 트리거 변경
ALTER TRIGGER [트리거 이름] ENABLE | DISABLE;ALTER TRIGGER 명령어로 트리거 상태를 변경할 수 있다.
특정 트리거를 활성화 또는 비활성화하려면 ENABLE 또는 DISABLE 옵션을 지정한다.
-- 특정 테이블과 관련된 모든 트리거의 상태 활성화
ALTER TABLE [테이블 이름] ENABLE ALL TRIGGERS;
-- 특정 테이블과 관련된 모든 트리거의 상태 비활성화
ALTER TABLE [테이블 이름] DISABLE ALL TRIGGERS;특정 테이블과 관련된 모든 트리거의 상태를 변경하려면 ALTER TABLE 을 사용한다.
■ 트리거 삭제
DROP TRIGGER [트리거 이름];DROP 문을 사용하여 트리거를 삭제할 수 있다.
'Back end > Database' 카테고리의 다른 글
| [Oracle] 저장 서브프로그램 - 저장 프로시저(stored procedure), 함수(function) (0) | 2019.10.22 |
|---|---|
| [Oracle] PL/SQL - 커서(cursor)와 예외 처리 (0) | 2019.10.21 |
| [Oracle] 복합 자료형 (레코드, 컬렉션) (0) | 2019.10.19 |
| [Oracle] PL/SQL - 조건 제어문, 반복 제어문, 순차 제어문 (0) | 2019.10.18 |
| [Oracle] PL/SQL (0) | 2019.10.16 |