231031 PL/SQL 문제

[문제] 전체 사원 들의 사번, 이름, 급여, 입사일, 근무연수를 출력합니다.
또한 근무연수가 15년 이상이고 급여는 10000 미만인 사원들은 예외사항이 발생하도록 한 후 메시지 출력하고  프로그램 수행이 완료된 후에 분석할수있도록  years 테이블에 정보가 입력이 되도록 프로그램을 작성합니다. 근무연수는 소수점은 버리세요.

SQL> create table years(id number, name varchar2(30), sal number, year number);

<화면 출력>
....

200, Whalen, 5856.4, 03/09/17, 20
사원 200 근무연수는 20년이고 급여는 5856.4 입니다.
201, Hartstein, 13000, 04/02/17, 19
202, Fay, 6000, 05/08/17, 18
사원 202 근무연수는 18년이고 급여는 6000 입니다.
203, Mavris, 6500, 02/06/07, 21
사원 203 근무연수는 21년이고 급여는 6500 입니다.

....

 

1. SQL문으로 실행을 해보자!

SELECT employee_id, last_name, salary, hire_date, trunc(months_between(sysdate, hire_date)/12) year
FROM hr.employees 
WHERE year >= 15
AND salary < 10000;

2. PL/SQL문에서 사원번호, 이름, 급여, 입사일, 근무연수 먼저 출력해보자!

BEGIN
    FOR emp_rec IN (
        SELECT
            employee_id,
            last_name,
            salary,
            hire_date,
            trunc(months_between(sysdate, hire_date) / 12) year
        FROM hr.employees
    ) LOOP
        dbms_output.put_line(emp_rec.employee_id
                             || ', '
                             || emp_rec.last_name
                             || ', '
                             || emp_rec.salary
                             || ', '
                             || emp_rec.hire_date
                             || ', '
                             || emp_rec.year);
    END LOOP;
END;
/

3. 근무연수가 15년 이상이고 급여가 10000 미만인 사원을 출력해보자! 

- IF문 + sub block 을 이용

BEGIN
    FOR emp_rec IN (
        SELECT
            employee_id,
            last_name,
            salary,
            hire_date,
            trunc(months_between(sysdate, hire_date) / 12) year
        FROM hr.employees
    ) LOOP
       dbms_output.put_line(emp_rec.employee_id||', '||emp_rec.last_name||', '||emp_rec.salary||', '||emp_rec.hire_date||', '||emp_rec.year);
        IF emp_rec.year >= 15 AND emp_rec.salary < 10000 THEN
                 dbms_output.put_line('사원 '||emp_rec.employee_id||' 근무연수는 '||emp_rec.year||'년이고 급여는 '||emp_rec.salary||' 입니다.');
        END IF;
    END LOOP;
END;
/

4. 이 조건을 예외사항이 발생하도록 한 후 메시지가 출력해야 한다!

그리고 나서 years 테이블에 insert가 되어야 한다!

DECLARE
    e_raise EXCEPTION; -- 사용자가 지정한 예외변수
BEGIN
    FOR emp_rec IN (
        SELECT
            employee_id,
            last_name,
            salary,
            hire_date,
            trunc(months_between(sysdate, hire_date) / 12) year
        FROM hr.employees
    ) LOOP
       dbms_output.put_line(emp_rec.employee_id||', '||emp_rec.last_name||', '||emp_rec.salary||', '||emp_rec.hire_date||', '||emp_rec.year);
        
        /* sub block */
        BEGIN 
            IF emp_rec.year >= 15 AND emp_rec.salary < 10000 THEN
                RAISE e_raise;
            END IF;
        EXCEPTION 
            WHEN e_raise THEN
                dbms_output.put_line('사원 '||emp_rec.employee_id||' 근무연수는 '||emp_rec.year||'년이고 급여는 '||emp_rec.salary||' 입니다.');
                INSERT INTO hr.years (id, name, sal, year)
                VALUES(emp_rec.employee_id, emp_rec.last_name, emp_rec.salary, emp_rec.year);
                COMMIT;
        END;
        /* end sub block */    
        
    END LOOP;
END;
/

예외사항을 발생하도록 하려면 서브블록이 필요!

+ 예외처리 변수가 필요해서 declare에서 변수 선언.

 

5. years 테이블에 data 확인. (총 88건 확인!)

SELECT * FROM hr.years;