sql_interview_store_proc_dep_wise_max_sal_emp

CREATE OR REPLACE PROCEDURE dep_wise_max_sal_emp(dept_name IN VARCHAR,sal_rnk IN int)
AS
emp_fname hr.employees.first_name%TYPE;
emp_lname hr.employees.last_name%TYPE;
emp_salary hr.employees.salary%TYPE;
BEGIN
    SELECT first_name,last_name,salary INTO emp_fname,emp_lname,emp_salary
    FROM (
        SELECT first_name,last_name,salary,DENSE_RANK() OVER (PARTITION BY department_name ORDER BY salary DESC) AS rnk
        FROM hr.employees,hr.departments
        WHERE hr.employees.department_id=hr.departments.department_id AND department_name=INITCAP(LOWER(dept_name))
    ) WHERE rnk=sal_rnk;

    DBMS_OUTPUT.PUT_LINE('employee_name:'|| emp_fname||' '||emp_lname);
    DBMS_OUTPUT.PUT_LINE('employee_salary:'|| emp_salary);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No records avaialble');
    WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('More than one matches');
END;


exec dep_wise_max_sal_emp('ExeCutive',2);
exec dep_wise_max_sal_emp('HR',2);
exec dep_wise_max_sal_emp('SalEs',2);
exec dep_wise_max_sal_emp('Purchasing',1);