-Using Oracle SQL-

Print out the number of employees in each department if that number is not zero.

Using the procedure as shown in the code shown further below, edit this code and write one PL/SQL that will invoke standalone procedure.

In the PL/SQL block, you will define a cursor that will return all the department IDs

(... from departments where manager_id is not null),

run a loop, repeatedly invoke that procedure. Your program needs to provide the value of department_id you get from cursor to the procedure, and assign the OUT parameter value from the procedure into your local variable. Then print out the info.

Code:

CREATE OR REPLACE PROCEDURE Dept_Emp_num (Deptno IN NUMBER, Emp_num OUT NUMBER)

IS

BEGIN

SELECT count (*) into Emp_num

FROM employees

WHERE department_id = Deptno;

END Dept_emp_num;

/

DECLARE

Deptno number;

Emp_num number;

BEGIN

Deptno:= 30;

Dept_Emp_num(Deptno , Emp_num);

dbms_output.put_line(' Number of employees : ' || Emp_num);

END;

Data for Departments table:

Data for Employees table: