CREATE OR REPLACE PROCEDURE get_dept_manager ( dept_id IN number )
IS
empl_id number;
manager_name varchar(50);
BEGIN
 select max(employee_id) into empl_id from hr.employees where job_id like '%_MAN' and department_id = dept_id;
 select first_name||' '||last_name into manager_name from hr.employees where employee_id = empl_id;
 DBMS_OUTPUT.PUT_LINE('Manager name of dept '||dept_id||' is '||manager_name);
END;

exec get_dept_manager(50);
 