CREATE OR REPLACE PROCEDURE proc_emp_avg_update
(v_empno    IN  NUMBER
, msg       OUT VARCHAR2)
IS
v_avg_sal       NUMBER;
v_sal           NUMBER;
v_ename         VARCHAR2(20);
v_rate          NUMBER;
BEGIN
    SELECT ename, sal INTO v_ename, v_sal
      FROM emp
     WHERE empno = v_empno;
    
    SELECT ROUND(AVG(sal)) INTO v_avg_sal
      FROM emp
     WHERE deptno = (SELECT deptno
                       FROM emp
                      WHERE empno = v_empno); 
         
    IF v_sal > v_avg_sal THEN
        v_rate := 1.1; 
    ELSE
        v_rate := 1.2;
    END IF;
    
    UPDATE emp
       SET sal = ROUND(v_sal * v_rate)  
     WHERE empno = v_empno;
    
    msg := v_ename||'»ç¿øÀÇ ÇöÀç ±Þ¿©'||v_sal||'°¡ '||(v_rate - 1) * 100||'%ÀλóµÇ¾î '||ROUND(v_sal * v_rate)||'À¸·Î ÀλóµÇ¾ú½À´Ï´Ù'; 
    
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END proc_emp_avg_update;



/