DECLARE
CURSOR cur_for_dep IS
SELECT *
FROM departments
WHERE manager_id IS NOT NULL;
FUNCTION Count_for_emp (p_deptid NUMBER)
RETURN NUMBER
IS
count_of_emp NUMBER(3);
BEGIN
SELECT Count(*)
INTO count_of_emp
FROM employees
WHERE department_id = p_deptid;
RETURN count_of_emp;
END;
FUNCTION Name_of_hod (p_deptid NUMBER)
RETURN VARCHAR2
IS
emp_as_hod employees.first_name%TYPE;
BEGIN
SELECT first_name
INTO emp_as_hod
FROM employees
WHERE employee_id = (SELECT manager_id
FROM departments
WHERE department_id = p_deptid);
RETURN emp_as_hod;
END;
FUNCTION City_of_dep (p_deptid NUMBER)
RETURN VARCHAR2
IS
city_head_off locations.city%TYPE;
BEGIN
SELECT city
INTO city_head_off
FROM locations
WHERE location_id = (SELECT location_id
FROM departments
WHERE department_id = p_deptid);
RETURN city_head_off;
END;
BEGIN
dbms_output.Put_line(Rpad('Department Name', 30)
||Rpad('Department Head', 20)
||Rpad('Head Office', 20)
||'Number of Employees');
dbms_output.Put_line('----------------------------------------------------------------------------------------');
FOR rows_of_dep IN cur_for_dep LOOP
dbms_output.Put_line(Rpad(rows_of_dep.department_name, 30)
|| Rpad(Name_of_hod(rows_of_dep.department_id), 20)
|| Rpad(City_of_dep(rows_of_dep.department_id), 20)
|| Count_for_emp(rows_of_dep.department_id));
END LOOP;
END;
Sample Output:
SQL> /
Department Name Department Head Head Office Number of Employees
----------------------------------------------------------------------------------------
Administration Jennifer Seattle 1
Marketing Michael Toronto 2
Purchasing Den Seattle 6
Human Resources Susan London 1
Shipping Adam South San Francisco 45
IT Alexander Southlake 5
Public Relations Hermann Munich 1
Sales John Oxford 34
Executive Steven Seattle 3
Finance Nancy Seattle 6
Accounting Shelley Seattle 2
PL/SQL procedure successfully completed.
Sample Output:
need an explanation for this answer? contact us directly to get an explanation for this answer