DECLARE
emp_depid departments.department_id%TYPE := 10;emp_max_depid departments.department_id%TYPE;no_emps NUMBER(3);emp_depname departments.department_name%TYPE;dep_mgname employees.first_name%TYPE;dep_mglname employees.last_name%TYPE;dep_mgid departments.manager_id%TYPE;emp_found_nfound BOOLEAN := FALSE;BEGIN
SELECT Max(department_id)
INTO emp_max_depid
FROM departments;
dbms_output.Put_line(Rpad('Department ID',20)
|| Rpad('Department',20)
|| Rpad('Manager',20)
||'No. of Employees');
dbms_output.Put_line( '---------------------------------------------------------------------------------');
WHILE emp_depid <= emp_max_depid
LOOP
SELECT manager_id
INTO dep_mgid
FROM departments
WHERE department_id = emp_depid;
IF dep_mgid IS NOT NULL THEN
SELECT Count(*)
INTO no_emps
FROM employees
WHERE department_id = emp_depid;
IF no_emps > 5 THEN
SELECT department_name,
first_name,
last_name
INTO emp_depname,
dep_mgname,
dep_mglname
FROM employees e
join departments d
ON (
d.manager_id = e.employee_id)
WHERE d.department_id = emp_depid;
dbms_output.Put_line(Rpad(emp_depid,20)
|| Rpad(emp_depname,20)
||Rpad((dep_mgname
||' '
||dep_mglname),20)
||no_emps);
emp_found_nfound := TRUE;
EXIT;
END IF;
END IF;
emp_depid := emp_depid + 10;
END LOOP;
IF NOT emp_found_nfound THEN
dbms_output.Put_line('No department found with more than 5 employees');
END IF;
END;
/
Sample Output:
SQL> /
Department ID Department Manager No. of Employees
-----------------------------------------------------------------------------
30 Purchasing Den Raphaely 6
PL/SQL procedure successfully completed.
Sample Output:
need an explanation for this answer? contact us directly to get an explanation for this answer