231030 PL/SQL 복습 겸 문제

[문제] 배열 변수에 있는 100, 101, 102, 103, 104, 200 사원들의 근무 연수를 출력하고 근무 연수가 20년 이상 되었으면 급여를 10% 인상한 급여로 수정하는 프로그램 작성하세요.

<화면 출력>
100는 근무 연수가 20년 입니다. 급여는 10% 인상되었습니다.
101는 근무 연수가 18년 입니다. 급여는 인상할 수 없습니다.
...
200는 근무 연수가 20년 입니다. 급여는 10% 인상되었습니다.

SELECT trunc(months_between(sysdate, hire_date)/12) 근무연수
FROM hr.employees
WHERE employee_id IN ( 100, 101, 102, 103, 104, 200) ; --배열변수로 변경

/* 배열 변수 사원중에서 근무 연수가 20년 이상인 사원에 대해서 급여 수정 */
/* 20년 이상 급여 10%인상 + 이하면 10% 인상할 수 없습니다. 문구 생성 */
UPDATE hr.employees
SET salary = salary*1.1
WHERE employee_id = (  );

# PL/SQL 코드로 변경

DECLARE
    TYPE id_type IS TABLE OF varchar(20) INDEX BY PLS_INTEGER;
    v_id id_type;
    v_mon number;
BEGIN
    v_id(1) := 100;
    v_id(2) := 101;
    v_id(3) := 102;
    v_id(4) := 103;
    v_id(5) := 104;
    v_id(6) := 200;
    
    FOR i IN v_id.FIRST .. v_id.LAST LOOP
        SELECT TRUNC(months_between(sysdate, hire_date)/12)
        INTO v_mon
        FROM hr.employees
        WHERE employee_id = v_id(i);

        IF v_mon >= 20 THEN        
            UPDATE hr.employees
            SET salary = salary*1.1
            where employee_id = v_id(i);
        
            dbms_output.put_line(v_id(i)||'는 근무 연수가 '||v_mon||'년 입니다. 급여는 10% 인상되었습니다');
        ELSE
            dbms_output.put_line(v_id(i)||'는 근무 연수가 '||v_mon||'년 입니다. 급여는 인상할 수 없습니다');
        END IF;
    END LOOP;
END;
/

 

[문제] 배열변수 안에 있는 사원번호 값을 기준으로 (100,110, 200) 그 사원의 last_name, hire_date, department_name 정보를 레코드변수에 담아놓은 후 화면에 출력하는 프로그램을 작성하세요. 

<화면출력>
100 사원의 이름은 King, 입사한 날짜는 2003-06-17, 근무 부서이름은 Executive 입니다.
..

PL/SQL

SELECT e.last_name, e.hire_date, d.department_name
INTO 레코드변수
FROM hr.employees e, hr.departments d 
WHERE e.department_id = d.department_id
AND e.employee_id = 배열변수 ;

내가 작성한 코드)

DECLARE
    TYPE id_type IS TABLE OF varchar(20) INDEX BY PLS_INTEGER;
    v_id id_type;
    v_date hr.employees.hire_date%TYPE;
    v_name hr.employees.last_name%TYPE;
    v_dept_name hr.departments.department_name%TYPE;
BEGIN
    v_id(1) := 100;
    v_id(2) := 110;
    v_id(3) := 200;
    
    FOR i IN v_id.FIRST .. v_id.LAST LOOP
        SELECT e.last_name, e.hire_date, d.department_name
        INTO v_name, v_date, v_dept_name
        FROM hr.employees e, hr.departments d 
        WHERE e.department_id = d.department_id
        AND employee_id = v_id(i);
        
        DBMS_OUTPUT.PUT_LINE(v_id(i)||'사원의 이름은 '||v_name||', 입사한 날짜는 '||v_date||', 근무 부서이름은 '||v_dept_name||'입니다');
    END LOOP;
END;
/

 

코드의 간결화) 

DECLARE
    TYPE emp_id_type IS TABLE OF number INDEX BY PLS_INTEGER; -- 배열타입
    v_id emp_id_type;
    
    TYPE emp_rec_type IS RECORD ( -- 레코드타입 쓰는 이유: 편리성
        a  VARCHAR2(30), -- e.last_name
        b  DATE, -- e.hire_date
        c  VARCHAR2(50) -- d.department_name
    );
    v_rec emp_rec_type;
    
BEGIN
    v_id(1) := 100;
    v_id(2) := 110;
    v_id(3) := 200;
    
    FOR i IN v_id.FIRST .. v_id.LAST LOOP
        SELECT e.last_name, e.hire_date, d.department_name
        INTO v_rec
        FROM hr.employees e, hr.departments d 
        WHERE e.department_id = d.department_id
        AND employee_id = v_id(i);
        
        DBMS_OUTPUT.PUT_LINE(v_id(i)||' 사원의 이름은 '||v_rec.a||', 입사한 날짜는 '||v_rec.b||', 근무 부서이름은 '||v_rec.c||' 입니다.');
    END LOOP;
END;
/