Live ORACLE

rem SERVEROUTPUT must be set to ON to display output with dbms_output

SET SERVEROUTPUT ON FORMAT wrapped


DECLARE

new_sal  NUMBER(8,2);

 emp_id   NUMBER(6) := 126;

 PROCEDURE Adjust_salary

      (emp_id  NUMBER,

       sal     IN OUT NUMBER)

 IS

   emp_job  VARCHAR2(10);

   avg_sal  NUMBER(8,2);

 BEGIN

   SELECT job_id

   INTO   emp_job

   FROM   employees

   WHERE  employee_id = emp_id;



   SELECT Avg(salary)

   INTO   avg_sal

   FROM   employees

   WHERE  job_id = emp_job;



   dbms_output.Put_line('The average salary for '

                        ||emp_job

                        ||' employees: '

                        ||To_char(avg_sal));



   sal := (sal + avg_sal) / 2; -- adjust sal value which is returned



 END;

BEGIN

SELECT Avg(salary)

INTO   new_sal

FROM   employees;



dbms_output.Put_line('The average salary for all employees: '

                     ||To_char(new_sal));



Adjust_salary(emp_id,new_sal); -- assigns a new value to new_sal



dbms_output.Put_line('The adjusted salary for employee '

                     ||To_char(emp_id)

                     ||' is '

                     ||To_char(new_sal)); -- sal has new value



END;

/

Assigning Values to Variables as Parameters of a Subprogram