251114 TIL

2025. 11. 14. 17:47ㆍCourses/아이티윌 오라클 DBA 과정

PACKAGE(Cont.)

상수 표준화

CREATE OR REPLACE PACKAGE global_consts
IS
    c_mile_2_kilo constant number := 1.6093;
    c_kilo_2_mile constant number := 0.6214;
    c_yard_2_meter constant number := 0.9144;
    c_metter_2_yard constant number := 1.0936;
END;
/

exec dbms_output.put_line('20 mile = ' || 20 * global_consts.c_mile_2_kilo || 'km')
exec dbms_output.put_line('20 kilo = ' || 20 * global_consts.c_kilo_2_mile || 'mi')
exec dbms_output.put_line('20 yard = ' || 20 * global_consts.c_yard_2_meter || 'm')
exec dbms_output.put_line('20 meter = ' || 20 * global_consts.c_meter_2_yard || 'yd')

-- 함수에서 사용
CREATE OR REPLACE FUNCTION mtr_to_yrd(p_m IN number)
RETURN number
IS
BEGIN
    RETURN p_m * global_consts.c_meter_2_yard;
END mtr_to_yrd;
/

exec dbms_output.put_line(mtr_to_yrd(20))

다른 사용자 세션에서 사용

-- hr에서 anna에게 권한 부여
GRANT execute ON hr.global_consts TO anna;
GRANT execute ON hr.mtr_to_yrd TO anna;

-- anna에서 실행
SELECT * FROM user_tab_privs;

exec dbms_output.put_line('20 mile = ' || 20 * hr.global_consts.c_mile_2_kilo || 'km')
exec dbms_output.put_line('20 kilo = ' || 20 * hr.global_consts.c_kilo_2_mile || 'mi')
exec dbms_output.put_line('20 yard = ' || 20 * hr.global_consts.c_yard_2_meter || 'm')
exec dbms_output.put_line('20 meter = ' || 20 * hr.global_consts.c_meter_2_yard || 'yd')

SELECT * FROM user_tab_privs;

exec dbms_output.put_line(hr.mtr_to_yrd(20))

예외 이름 표준화

-- 패키지 생성
CREATE OR REPLACE PACKAGE err_pkg
IS
    notnull_err EXCEPTION;
    PRAGMA EXCEPTION_INIT(notnull_err, -1400);
END err_pkg;
/

BEGIN
    INSERT INTO hr.departments(department_id, department_name) VALUES(300, NULL);
EXCEPTION
        -- 패키지에 선언해 둔 예외를 통해 예외 처리
    WHEN err_pkg.notnull_err THEN
        dbms_output.put_line('필수 항목 값을 꼭 입력해주세요.');
    WHEN others THEN
        dbms_output.put_line(sqlcode);
        dbms_output.put_line(sqlerrm);
END;
/

패키지 커서 지속 상태

일반 프로시저 내 커서

DECLARE
    CURSOR emp_cur IS
        SELECT *
        FROM hr.employees
        WHERE department_id = 20;
    v_rec emp_cur%rowtype;
BEGIN
    OPEN emp_cur;

    LOOP
        FETCH emp_cur INTO v_rec;
        EXIT WHEN emp_cur%notfound;
        dbms_output.put_line(v_rec.employee_id || ' ' || v_rec.last_name);
    END LOOP;
END;
/

커서를 open 하지 않은 상태에서 fetch 하면 ORA-01001: invalid cursor
프로그램 호출이 끝나면 커서를 close하지 않았더라도 오라클이 암시적으로 커서를 닫아줌

패키지 내 커서

CREATE OR REPLACE PACKAGE pack_cur
IS
    PROCEDURE open;
    PROCEDURE next(p_num IN number);
    PROCEDURE close;
END pack_cur;
/

CREATE OR REPLACE PACKAGE BODY pack_cur
IS    
    /* private cursor : package body 안에서만 사용하는 cursor */
    CURSOR c1 IS
        SELECT employee_id, last_name
        FROM hr.employees
        ORDER BY employee_id desc;

    /* private variable : package body 안에서만 사용하는 variable */   
    v_id number;
    v_name varchar2(30);

    PROCEDURE open
    IS
    BEGIN
        IF NOT c1%isopen THEN
            OPEN c1;
            dbms_output.put_line('c1 cursor open');
        END IF;
    END open;

    PROCEDURE next(p_num IN number)
    IS
    BEGIN
        LOOP
            EXIT WHEN c1%rowcount >= p_num OR c1%notfound;
            FETCH c1 INTO v_id, v_name;
            dbms_output.put_line(v_id || ' ' || v_name);
        END LOOP;
    END next;

    PROCEDURE close
    IS
    BEGIN
        IF c1%isopen THEN
            CLOSE c1;
            dbms_output.put_line('c1 cursor close');
        END IF;
    END close;
END pack_cur;
/

close 를 명시적으로 하기 전까지는 커서 open 상태를 지속
커서가 열려있으므로 next 프로시저를 통해 다음 행들을 계속해서 fetch할 수 있음
세션이 닫히면 유저 프로세스와의 연결이 끊기면서 커서도 닫힘
그래도 명시적으로 close하는 습관을 가지는 것이 좋음

PUBLIC 변수 지속 상태

기본

specification에서 선언한 public(global)변수는 세션이 열려 있는 동안 변경한 값을 지속적으로 사용

CREATE OR REPLACE PACKAGE comm_pkg
IS
    g_comm number := 0.1;
    PROCEDURE reset_comm(p_comm IN number);
END comm_pkg;
/

CREATE OR REPLACE PACKAGE BODY comm_pkg
IS  
    FUNCTION validate_comm(p_comm IN number)
    RETURN boolean;

    PROCEDURE reset_comm(p_comm IN number)
    IS
    BEGIN
        IF validate_comm(p_comm) THEN
            dbms_output.put_line('OLD : ' || g_comm);
            g_comm := p_comm;
            dbms_output.put_line('NEW : ' || g_comm);
        ELSE
            RAISE_APPLICATION_ERROR(-20000, '기존 최고값을 넘을 수 없습니다');
        END IF;
    END reset_comm;

    FUNCTION validate_comm(p_comm IN number)
    RETURN boolean
    IS
        v_comm number;
    BEGIN
        SELECT max(commission_pct)
        INTO v_comm
        FROM hr.employees;

        IF p_comm > v_comm THEN
            RETURN FALSE;
        ELSE 
            RETURN TRUE;
        END IF;
    END validate_comm;
END comm_pkg;
/

exec comm_pkg.reset_comm(0.2)
exec dbms_output.put_line(comm_pkg.g_comm)

PRAGMA SERIALLY_REUSABLE

specification에서 선언한 public(global)변수를 호출이 끝나면 초기 값으로 되돌아 가도록 수행해야 하는 경우 사용
specification과 body에 모두 작성해야 함
- 한 곳에만 작성되면 오류 발생
- PLS-00709: pragma SERIALLY_REUSABLE must be declared in package specification and body

CREATE OR REPLACE PACKAGE comm_pkg
IS
    PRAGMA SERIALLY_REUSABLE;
    g_comm number := 0.1;
    PROCEDURE reset_comm(p_comm IN number);
END comm_pkg;
/

CREATE OR REPLACE PACKAGE BODY comm_pkg
IS  
    PRAGMA SERIALLY_REUSABLE;
    FUNCTION validate_comm(p_comm IN number)
    RETURN boolean;

    PROCEDURE reset_comm(p_comm IN number)
    IS
    BEGIN
        IF validate_comm(p_comm) THEN
            dbms_output.put_line('OLD : ' || g_comm);
            g_comm := p_comm;
            dbms_output.put_line('NEW : ' || g_comm);
        ELSE
            RAISE_APPLICATION_ERROR(-20000, '기존 최고값을 넘을 수 없습니다');
        END IF;
    END reset_comm;

    FUNCTION validate_comm(p_comm IN number)
    RETURN boolean
    IS
        v_comm number;
    BEGIN
        SELECT max(commission_pct)
        INTO v_comm
        FROM hr.employees;

        IF p_comm > v_comm THEN
            RETURN FALSE;
        ELSE 
            RETURN TRUE;
        END IF;
    END validate_comm;
END comm_pkg;
/

exec comm_pkg.reset_comm(0.2)
exec dbms_output.put_line(comm_pkg.g_comm)

PACKAGE 삭제

DROP PACKAGE comm_pkg;

PACKAGE BODY만 삭제

DROP PACKAGE BODY comm_pkg;

TRIGGER(트리거)

데이터베이스에 저장되고 지정된 이벤트에 대한 응답으로 실행되는 PL/SQL 블록
오라클 데이터베이스는 지정된 조건이 발생할 때 트리거를 자동으로 실행
트리거는 테이블, 뷰, 유저, 데이터베이스 레벨로 정의 가능
트리거 크기(프로그램 사이즈)는 32KB 초과 불가
트리거를 생성하려면 CREATE TRIGGER 시스템 권한 필요

트리거 유형

DML 트리거
DDL 트리거
데이터베이스 시작/종료
특정 오류 메시지
유저 로그인, 로그 오프

DML 트리거

DML 문장 트리거

DML 문장 트리거는 영향을 받은 행이 전혀 없더라도 문장 트리거는 한 번 실행함

-- before 문장 트리거
CREATE OR REPLACE TRIGGER dept_before
BEFORE
INSERT ON hr.dept
BEGIN
    dbms_output.put_line('isnert하기 전에 문장 트리거 수행');
END dept_before;
/

-- after 문장 트리거
CREATE OR REPLACE TRIGGER dept_after
AFTER
INSERT ON hr.dept
BEGIN
    dbms_output.put_line('isnert한 후에 문장 트리거 수행');
END dept_after;
/

DML 행 트리거

DML 행 트리거는 영향을 받은 행이 없을 경우에는 트리거를 수행하지 않음
영향을 받은 각 행에 대해서 트리거 수행
FOR EACH ROW 절을 사용

-- before 행 트리거
CREATE OR REPLACE TRIGGER dept_row_before
BEFORE
INSERT ON hr.dept
FOR EACH ROW
BEGIN
    dbms_output.put_line('isnert하기 전에 행 트리거 수행');
END dept_row_before;
/

-- after 행 트리거
CREATE OR REPLACE TRIGGER dept_row_after
AFTER
INSERT ON hr.dept
FOR EACH ROW
BEGIN
    dbms_output.put_line('isnert한 후에 행 트리거 수행');
END dept_row_after;
/

트리거 정보 조회

-- 트리거 정보 조회
SELECT * FROM user_triggers WHERE table_name = 'DEPT';

-- 트리거 소스 정보 조회
SELECT * FROM user_source WHERE name = 'DEPT_ROW_AFTER';

-- 트리거 상태 정보 조회
SELECT status FROM user_triggers WHERE trigger_name = 'DEPT_ROW_AFTER';

-- 객체 상태 정보 조회
SELECT status FROM user_objects WHERE object_name = 'DEPT_ROW_AFTER';

객체 상태가 INVALID일 경우 실행 불가능한 상태
컴파일 실패시 객체 상태가 INVALID로 되지만 트리거 상태는 여전히 ENABLED 상태임
→ 트리거 비활성화

트리거 비활성화

ALTER TRIGGER dept_row_after DISABLE;

SELECT status FROM user_triggers WHERE trigger_name = 'DEPT_ROW_AFTER';

트리거 활성화

ALTER TRIGGER dept_row_after ENABLE;

SELECT status FROM user_triggers WHERE trigger_name = 'DEPT_ROW_AFTER';

트리거 삭제

DROP TRIGGER dept_before;
DROP TRIGGER dept_after;
DROP TRIGGER dept_row_before;
DROP TRIGGER dept_row_after;

조건부 술어

INSERT, UPDATE, DELETE 이벤트에 따라 다르게 로직 구현을 수행해야 할 경우 사용
inserting, updating, deleting

조건부 술어 사용 X

-- before 문장 트리거
CREATE OR REPLACE TRIGGER secure_dept
BEFORE
INSERT OR UPDATE OR DELETE ON hr.dept
BEGIN
    IF to_char(sysdate, 'hh24:mi') NOT BETWEEN '09:00' AND '15:00' THEN
        RAISE_APPLICATION_ERROR(-20000, 'DML 작업을 수행할 수 없습니다.');
    END IF;
END secure_dept;
/

-- after 문장 트리거
CREATE OR REPLACE TRIGGER secure_dept
AFTER
INSERT OR UPDATE OR DELETE ON hr.dept
BEGIN
    IF to_char(sysdate, 'hh24:mi') NOT BETWEEN '09:00' AND '15:00' THEN
        RAISE_APPLICATION_ERROR(-20000, 'DML 작업을 수행할 수 없습니다.');
    END IF;
END secure_dept;
/

-- ORA-20000: DML 작업을 수행할 수 없습니다.
INSERT INTO hr.dept(department_id, department_name, manager_id, location_id) VALUES(300, 'Data Architect', 100, 1500);

UPDATE hr.dept
SET location_id = 1500
WHERE department_id = 10;

DELETE FROM hr.dept
WHERE department_id = 10;

SELECT * FROM hr.dept;

INSERT, UDPATE, DELETE 모두 처리되는 방식이 동일

조건부 술어 사용

CREATE OR REPLACE TRIGGER secure_dept
BEFORE
INSERT OR UPDATE OR DELETE ON hr.dept
BEGIN
    IF to_char(sysdate, 'hh24:mi') NOT BETWEEN '09:00' AND '15:00' THEN
        IF inserting THEN
            RAISE_APPLICATION_ERROR(-20000, 'insert 작업을 수행할 수 없습니다.');
        ELSIF updating THEN
            RAISE_APPLICATION_ERROR(-20001, 'update 작업을 수행할 수 없습니다.');
        ELSIF deleting THEN
            RAISE_APPLICATION_ERROR(-20002, 'delete 작업을 수행할 수 없습니다.');
        END IF;
    END IF;
END secure_dept;
/

-- ORA-20000: insert 작업을 수행할 수 없습니다.
INSERT INTO hr.dept(department_id, department_name, manager_id, location_id) VALUES(300, 'Data Architect', 100, 1500);

-- ORA-20001: update 작업을 수행할 수 없습니다.
UPDATE hr.dept
SET location_id = 1500
WHERE department_id = 10;

-- ORA-20002: delete 작업을 수행할 수 없습니다.
DELETE FROM hr.dept
WHERE department_id = 10;

INSERT → inserting, UDPATE → updating, DELETE→ deleting 조건부 술어에 따라 처리를 다르게 할 수 있음

DML 영향에 따른 트리거 수행 여부

-- before 문장 트리거
CREATE OR REPLACE TRIGGER emp_before
BEFORE
UPDATE ON hr.emp
BEGIN
    dbms_output.put_line('update하기 전에 문장 트리거 수행');
END emp_before;
/

-- after 문장 트리거
CREATE OR REPLACE TRIGGER emp_after
AFTER
UPDATE ON hr.emp
BEGIN
    dbms_output.put_line('update한 후에 문장 트리거 수행');
END emp_after;
/

-- before 행 트리거
CREATE OR REPLACE TRIGGER emp_row_before
BEFORE
UPDATE ON hr.emp
FOR EACH ROW
BEGIN
    dbms_output.put_line('update하기 전에 행 트리거 수행');
END emp_row_before;
/

-- after 행 트리거
CREATE OR REPLACE TRIGGER emp_row_after
AFTER
UPDATE ON hr.emp
FOR EACH ROW
BEGIN
    dbms_output.put_line('update한 후에 행 트리거 수행');
END emp_row_after;
/

영향을 받은 행이 0개일 경우

UPDATE hr.emp
SET salary = salary * 1.1
WHERE employee_id = 300;

영향을 받은 행이 없더라도 문장 트리거는 수행됨
행 트리거는 수행되지 않음

영향을 받은 행이 2개 이상인 경우

UPDATE hr.emp
SET salary = salary * 1.1
WHERE department_id = 20;

영향을 받은 행이 여러 건이더라도 문장 트리거는 한 번만 수행
영향을 받은 행이 여러 건인 경우 행 트리거는 영향을 받은 행 수만큼 수행

수식자

OLD, NEW
DML 행 트리거에서 사용

데이터작업   이전값(OLD)    새로운값(NEW)
INSERT       NULL          입력된 값
UPDATE       갱신 전 값     갱신 후 값
DELETE       삭제 전 값     NULL

CREATE OR REPLACE TRIGGER emp_trg
BEFORE
INSERT OR DELETE OR UPDATE OF salary ON hr.emp
FOR EACH ROW
WHEN (NEW.department_id = 20 OR OLD.department_id = 10)
DECLARE
    v_sal number;
BEGIN
    IF deleting THEN
        dbms_output.put_line('OLD SALARY : ' || :OLD.salary);
    ELSIF updating THEN
        v_sal := :NEW.salary - :OLD.salary;
        dbms_output.put_line('EMPLOYEE_ID : ' || :OLD.employee_id);
        dbms_output.put_line('OLD SALARY : ' || :OLD.salary);
        dbms_output.put_line('NEW SALARY : ' || :NEW.salary);
        dbms_output.put_line('급여차이 : ' || v_sal);
    ELSIF inserting THEN
        dbms_output.put_line('NEW SALARY : ' || :NEW.salary);
    END IF;
END emp_trg;
/

INSERT INTO hr.emp VALUES(300, 'oracle', 100000, 20);
ROLLBACK;

DELETE FROM hr.emp WHERE department_id = 10;
ROLLBACK;

UPDATE hr.emp
SET salary = salary * 1.1
WHERE department_id = 10;
ROLLBACK;

UPDATE hr.emp
SET salary = salary * 1.1
WHERE department_id = 20;
ROLLBACK;

저작자표시 비영리 변경금지 (새창열림)

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

251113 TIL (0)	2025.11.13
251112 TIL (0)	2025.11.12
251111 TIL (0)	2025.11.11
251110 TIL (0)	2025.11.10
251107 TIL (0)	2025.11.07

Joy's Devlog

Joy's Devlog

태그

최근글

댓글

공지사항

아카이브

PACKAGE(Cont.)

상수 표준화

다른 사용자 세션에서 사용

예외 이름 표준화

패키지 커서 지속 상태

일반 프로시저 내 커서

패키지 내 커서

PUBLIC 변수 지속 상태

기본

PRAGMA SERIALLY_REUSABLE

PACKAGE 삭제

PACKAGE BODY만 삭제

TRIGGER(트리거)

트리거 유형

DML 트리거

DML 문장 트리거

DML 행 트리거

트리거 정보 조회

트리거 비활성화

트리거 활성화

트리거 삭제

조건부 술어

조건부 술어 사용 X

조건부 술어 사용

DML 영향에 따른 트리거 수행 여부

영향을 받은 행이 0개일 경우

영향을 받은 행이 2개 이상인 경우

수식자

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

관련글

티스토리툴바