231107 PL/SQL 문제

[문제] 사원들의 신규데이터 또는 급여를 수정할 때 그 사원의 job_id 별 최저 임금에서 최고 임금 사이에 급여값으로만 입력, 수정되어야 합니다. 

UPDATE hr.employees 
SET salary = 6000
WHERE employee_id = 115;

 

에러문구

"ERROE at line 1:

ORA-20100 : Invalid salary $6000. Salaries for job PU_CLERK must be between $2500 and $5000"

 

CREATE OR REPLACE TRIGGER hr.sal_trg
AFTER 
    INSERT OR 
    UPDATE OF salary ON hr.employees
    FOR EACH ROW
DECLARE
    v_min NUMBER;
    v_max NUMBER;
BEGIN 
    SELECT min_salary, max_salary
    INTO v_min, v_max
    FROM hr.jobs
    WHERE job_id = :NEW.job_id;

    IF :NEW.salary NOT BETWEEN v_min AND v_max THEN
        raise_application_error(-20100, 'Invalid salary $'||:NEW.salary||'. Salaries for job '||:NEW.job_id||' must be between $'||v_min||' and $'||v_max);
    END IF;
END check_salary_trg;
/

 

1. SELECT문으로 최저 임금~최고 임금을 확인

2. IF문(조건)으로 최저~최고 임금을 조건으로 두고 에러메시지를 전달.