231108 PL/SQL 복습_ PROCEDURE, TRIGGER

CREATE OR REPLACE PROCEDURE check_salary(
    p_job IN VARCHAR2, 
    p_sal IN NUMBER ) 
IS
    v_min_sal NUMBER;
    v_max_sal NUMBER;
BEGIN 
    SELECT min_salary, max_salary
    INTO v_min_sal, v_max_sal
    FROM hr.jobs
    WHERE job_id = upper(p_job);
    
    IF p_sal NOT BETWEEN v_min_sal AND v_max_sal THEN
        RAISE_APPLICATION_ERROR(-20000, 'Invalid salary $'||p_sal||'. '||'Salaries for job '||p_job||' must be between $'||v_min_sal||' and $'||v_max_sal);
    END IF;
    
END check_salary;
/

 

# 호출방식

execute check_salary('IT_PROC', 20000);

 

# 트리거 확인

SELECT * FROM user_triggers WHERE table_name = 'EMPLOYEES';

 

# 트리거 생성 

CREATE OR REPLACE TRIGGER check_salary_trg
AFTER
    INSERT OR 
    UPDATE OF salary ON hr.employees
    FOR EACH ROW
BEGIN
    check_salary(:new.job_id, :new.salary);
END check_salary_trg;
/

 

# 오류메시지 확인 (트리거 발생)

/* 오류 보고 -
ORA-20100: Invalid salary $6000. Salaries for job PU_CLERK must be between $2500 and $5500 */
UPDATE hr.employees
SET salary = 6000
WHERE employee_id = 115;

 

/*오류 보고 -
ORA-20100: Invalid salary $20000. Salaries for job IT_PROG must be between $4000 and $10000 */
INSERT INTO hr.employees (employee_id, last_name, email, hire_date, job_id, salary)
VALUES (300, 'ORACLE', 'ORACLE', SYSDATE, 'IT_PROG', 20000);

 

# BEGIN-END 절 = CALL 절

CREATE OR REPLACE TRIGGER check_salary_trg 
AFTER
    INSERT OR UPDATE OF salary ON hr.employees
    FOR EACH ROW
CALL check_salary(:new.job_id, :new.salary)
/