PLSQL08_Module(Stored Block3)

PACKAGE

1. PACKAGE 만들기

1-1. HEADER만들기

• HEADER영역에서 정의한 PROCEDURE, FUNCTION, VARIABLE은 PUBLIC이다.

CREATE OR REPLACE PACKAGE P_EMPLOYEE
AS
    -- 사원 퇴사
    PROCEDURE DELETE_EMP(P_EMPNO EMP.EMPNO%TYPE);
    
    -- 신규 입사사원 등록
    PROCEDURE INSERT_EMP(P_EMPNO NUMBER, P_ENAME VARCHAR2, P_JOB VARCHAR2, P_SAL NUMBER, P_DEPTNO NUMBER);
    
    -- MANAGER이름 리턴
    FUNCTION SEARCH_MNG(P_EMPNO EMP.EMPNO%TYPE) RETURN VARCHAR2;
    GV_ROWS NUMBER(6); -- public변수
END P_EMPLOYEE;
/

1-2. BODY만들기

• BODY영역에서만 정의한 PROCEDURE, FUNCTION, VARIABLE은 PRIVATE이다.
• GV_ROWS는 참조가능하지만, V_ROWS는 에러 발생
• HEADER에 정의하면 PUBLIC이 된다. BODY에 정의되어있는데 HEADER에 없다면 그것은 PRIVATE!

CREATE OR REPLACE PACKAGE BODY P_EMPLOYEE
AS
    V_ENAME     EMP.ENAME%TYPE; -- PRIVATE
    V_ROWS      NUMBER(6);      -- PRIVATE
    
    FUNCTION PRVT_FUNC(P_NUM IN NUMBER) RETURN NUMBER IS
    BEGIN
        RETURN P_NUM;
    END PRVT_FUNC;
    
    -- 신규 입사사원 등록
    PROCEDURE INSERT_EMP(P_EMPNO NUMBER, P_ENAME VARCHAR2, P_JOB VARCHAR2, P_SAL NUMBER, P_DEPTNO NUMBER)
    IS
    BEGIN
        INSERT INTO EMP(EMPNO, ENAME, JOB, SAL, DEPTNO) VALUES(P_EMPNO, P_ENAME, P_JOB, P_SAL, P_DEPTNO);
    END INSERT_EMP;
    
    PROCEDURE DELETE_EMP(P_EMPNO EMP.EMPNO%TYPE) IS --PUBLIC PROCEDURE정의
    BEGIN
        DELETE FROM EMP WHERE EMPNO = P_EMPNO;
        COMMIT;
        --IMPLICIT CURSOR ATTRIBUTE, PUBLIC변수 참조
        GV_ROWS := GV_ROWS+SQL%ROWCOUNT;
        V_ROWS := PRVT_FUNC(GV_ROWS); -- PRIVATE FUNCTION 참조
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            WRITE_LOG('P_EMPLOYEE.DELETE', SQLERRM, 'VALUES : [EMPNO] => '||P_EMPNO);
    END DELETE_EMP;
    
    FUNCTION SEARCH_MNG(P_EMPNO EMP.EMPNO%TYPE) RETURN VARCHAR2 IS
        V_ENAME     EMP.ENAME%TYPE;
    BEGIN
        SELECT ENAME INTO V_ENAME FROM EMP
        WHERE EMPNO = (SELECT MGR FROM EMP WHERE EMPNO = P_EMPNO);
        RETURN V_ENAME;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            V_ENAME := 'NO_DATA';
            RETURN V_ENAME;
        WHEN OTHERS THEN
            V_ENAME := 'SUBSTR(SQLERRM,1,12)';
            RETURN V_ENAME;
    END SEARCH_MNG;
BEGIN
    GV_ROWS := 0; -- 초기화
END P_EMPLOYEE;
/

TRIGGER

• 선언적 무결성 제약사항(PK, UK, FK, NN, CHECK)은 정의만 하면 된다. 하지만 단순하다.
• TRIGGER는 PL/SQL로 코딩해야하기 때문에 복잡하지만, 복잡한 비즈니스 로직을 구현
• 이벤트가 발생하면 자동으로 실행.

	INSERT	DELETE	UPDATE
:NEW	O	X	O
:OLD	X	O	O

1. 트리거 사용

CREATE OR REPLACE TRIGGER TRG_CHANGE_SAL
BEFORE UPDATE OF SAL ON EMP
FOR EACH ROW
BEGIN
    IF( :NEW.SAL >9000 ) THEN
        :NEW.SAL := 9000;
    END IF;
END;
/
-- Trigger TRG_CHANGE_SAL이(가) 컴파일되었습니다.


UPDATE EMP SET SAL=9500 WHERE EMPNO IN (7369);
SELECT * FROM EMP WHERE EMPNO IN (7369);

• EMPNO가 7369인 사원의 SAL값을 9500으로 UPDATE했지만, TRIGGER로 인해 9000이 들어감.

2. 트리거 특징

• 파라미터가 없고 ORACLE에 의해 자동으로 호출
• PREVENT INVALID TRANSACTION
• EVENT LOGGING : 트리거 작동을 로그에 남긴다.
• 트리거 안에는 COMMIT, ROLLBACK사용하지 않는다.( 방법은 있음 - 독립트랜잭션 )

3. 트리거 실습

3-0. 상황설명

• 신규 입사자 직군의 CLERK나 SALESMAN인 경우 노조에 자동가입된다.
• 퇴직시 퇴직자 명단에 항상 등록된다.
• 기존 급여 시스템의 기능적 오류로 인해 사원 급여 항목이 종종 마이너스로 바뀐다.
• 마이너스가 될 경우 원래 급여로 되될려야 한다.

3-1. 실습 전 테이블 생성

-- 퇴사자 테이블
CREATE TABLE RETIRED_EMP(
    EMPNO       NUMBER(4) NOT NULL,
    ENAME       VARCHAR2(10),
    JOB         VARCHAR2(9),
    RETIRED_DATE      DATE
);

-- 노조테이블
CREATE TABLE LABOR_UNION(
    EMPNO   NUMBER(4) NOT NULL,
    ENAME   VARCHAR2(10),
    JOB     VARCHAR2(9),
    ENROLL_DATE DATE
);

3-2. 트리거 생성

CREATE OR REPLACE TRIGGER TRG_EMP_CHANGE
BEFORE INSERT OR DELETE OR UPDATE OF SAL ON EMP
FOR EACH ROW
DECLARE
BEGIN
    --TRIGGER EVENT를 구분.(INSERTING, DELETING, UPDATING)
    --CLERK, SALSEMAN인 경우 노조명단등록
    IF INSERTING AND :NEW.JOB IN('CLERK', 'SALESMAN') THEN
        INSERT INTO LABOR_UNION(EMPNO, ENAME, JOB, ENROLL_DATE) VALUES(:NEW.EMPNO, :NEW.ENAME, :NEW.JOB, SYSDATE);
        
    -- 퇴사시 퇴직자 명단에 등록
    ELSIF DELETING THEN
        BEGIN INSERT INTO RETIRED_EMP(EMPNO, ENAME, JOB, RETIRED_DATE)
            VALUES(:OLD.EMPNO, :OLD.ENAME, :OLD.JOB, SYSDATE);
            DELETE FROM LABOR_UNION WHERE EMPNO= :OLD.EMPNO;
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;
    ELSIF UPDATING THEN
        IF :NEW.SAL < 0 THEN -- 마이너스 급여가 될 경우 원래 급여로 치환
            :NEW.SAL := :OLD.SAL;
        END IF;
    END IF;
END;
/

3-3. 패키지와 함께 실습하기

• 우리는 1-2에서 이미 P_EMPLOYEE패키지를 생성했다!

BEGIN
    P_EMPLOYEE.DELETE_EMP(7369); -- 7369직원 퇴사
    P_EMPLOYEE.INSERT_EMP(2025,'JANG', 'PRESIDENT', 8888,10); -- PRESIDENT 입사
    P_EMPLOYEE.INSERT_EMP(10, 'KIM','SALESMAN', 5555, 10); -- SALESMAN입사
END;
/

-- TRIGGER 실습 1 ) 마이너스 급여업데이트시 기존급여로 치환
UPDATE EMP SET SAL = -1000 WHERE EMPNO = 7900;
-- TRIGGER 실습 1-1 ) 마이너스 급여는 기존급여로 치환되어있는지 확인
SELECT EMPNO, SAL FROM EMP WHERE EMPNO = 7900;

-- TRIGGER 실습 2 ) 퇴사직원 퇴사명단에 저장되어있는지 확인
SELECT * FROM RETIRED_EMP WHERE EMPNO = 7369;

-- TRIGGER 실습 3 ) SALESMAN직무 노조 가입되어있는지 확인
SELECT * FROM LABOR_UNION WHERE EMPNO IN(2025,10);