231027 PL/SQL 복습 겸 문제

[문제] 사원번호를 입력값으로 받아서 그 사원의 급여를 출력하는 프로그램을 작성합니다. 또한, 급여 1000당 별(*) 하나를 출력해주세요.

내가 작성한 코드)

DECLARE
    v_id hr.employees.employee_id%TYPE;
    v_sal hr.employees.salary%TYPE;
    v_str varchar2(20) := NULL;
BEGIN
    SELECT employee_id, salary
    INTO v_id, v_sal
    FROM hr.employees
    WHERE employee_id = 200;    
    
    FOR i IN 1 .. (v_sal/1000) LOOP
        v_str := v_str||'*';
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('사원번호 : '||v_id);
    DBMS_OUTPUT.PUT_LINE('급여 : '||v_sal);
    DBMS_OUTPUT.PUT_LINE('별점 : '||v_str);
END;
/

 

가독성이 좋은 코드)

DECLARE
    v_id hr.employees.employee_id%TYPE := 200;
    v_sal hr.employees.salary%TYPE;
    v_star varchar2(30);
BEGIN
    SELECT salary
    INTO v_sal
    FROM hr.employees
    WHERE employee_id = v_id;    
    
    FOR i IN 1 .. trunc(v_sal/1000) LOOP
        v_star := v_star||'*'; -- null||문자 = 문자 
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('사원번호 : '||v_id);
    DBMS_OUTPUT.PUT_LINE('급여 : '||v_sal);
    DBMS_OUTPUT.PUT_LINE('별점 : '||v_star);
END;
/

 

[문제] 사원들의 근속 연수가 20년이상이면 10% 인상 급여로 수정하는 프로그램을 생성해주세요. 

 

<화면결과>
1. 100 사원의 입사일은 03/06/17 근속연수는 20년 입니다.
2. 100 사원의 급여가 26400 -> 29040 수정했습니다. (UPDATE)

 

먼저 SELECT 문으로 TEST 해보자!

SELECT Employee_id,   hire_date,    salary,
    trunc(months_between(sysdate, hire_date) / 12) 근속연수
FROM    hr.employees
WHERE     employee_id = 100;

UPDATE HR.EMPLOYEES
SET SALARY = SALARY * 1.1
WHERE EMPLOYEE_ID = 100;

ROLLBACK;

그리고 나서 PL/SQL 코드로 작성!

DECLARE
    v_id hr.employees.employee_id%TYPE := 100;
    v_hiredate hr.employees.hire_date%TYPE;
    v_sal hr.employees.salary%TYPE;
BEGIN
    SELECT hire_date, salary
    INTO v_hiredate, v_sal
    FROM hr.employees
    WHERE employee_id = v_id;
    
    UPDATE hr.employees
    SET salary = (salary*1.1)
    WHERE employee_id = v_id;
    --dbms_output.put_line(SQL%ROWCOUNT);
    --v_sal := (v_sal*1.1);
    
    --v_hiredate := trunc(months_betweens(sysate, v_hiredate)/12);
    DBMS_OUTPUT.PUT_LINE(v_id||' 사원 입사일은 '||v_hiredate||' 근속연수는 '||trunc(months_between(sysdate, v_hiredate)/12)||'년입니다.');
--    dbms_output.put(v_sal);
--    dbms_output.new_line;
    dbms_output.put_line(v_id||' 사원의 급여가 '||v_sal||' -> '||(v_sal*1.1)||' 수정했습니다.');
    
END;
/

 

코드의 간결화)

SELECT hire_date, salary
FROM hr.employees
WHERE employee_id = 100;

# 근속연수가 20년이상이면
trunc(months_between(sysdate, hire_date)/12)

UPDATE hr.employees
SET salary = salay*1.1
WHERE employee_id = 100;

SELECT salary
FROM hr.employees
WHERE employee_id = 100;

PL/SQL  코드로 작성

DECLARE
    v_id hr.employees.employee_id%TYPE := 100;
    v_day hr.employees.hire_date%TYPE;
    v_years number := trunc(months_between(sysdate, v_day)/12);
    v_sal_before hr.employees.salary%TYPE;
    v_sal_after hr.employees.salary%TYPE;
BEGIN
    SELECT hire_date, salary
    INTO v_day, v_sal_before
    FROM hr.employees
    WHERE employee_id = v_id;
    
    v_years := trunc(months_between(sysdate, v_day)/12);
    DBMS_OUTPUT.PUT_LINE(v_id||' 사원 입사일은 '||v_day||' 근속연수는 '||v_years||'년입니다.');        
    
    IF v_years >= 20 THEN -- 20년 이상 IF문
        UPDATE hr.employees
        SET salary = salary*1.1
        WHERE employee_id = v_id;
       
        IF sql%found THEN -- sql%found IF문
            -- ture 이면 
            SELECT salary
            INTO v_sal_after
            FROM hr.employees
            WHERE employee_id = v_id;
            
            DBMS_OUTPUT.PUT_LINE(v_id||' 사원의 급여가 '||v_sal_before||' → '||v_sal_after||' 수정했습니다.');
            COMMIT;
        ELSE
            ROLLBACK;
        END IF; -- sql%found IF-END
        
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_id||'사원은 급여를 수정할 수 없습니다.');
    END IF; -- 20년 이상 IF-END
    
END;
/