PL/SQL Cursor Exercises: Print the department name, head of the department, city and number of employees are wroking in that department
PL/SQL Cursor: Exercise-43 with Solution
Write a block in PL/SQL to print the department name, head of the department, city and number of employees are wroking in that department.
Sample Solution:
PL/SQL Code:
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.
Flowchart:
Improve this sample solution and post your code through Disqus
Previous: Write a block in PL/SQL to display the first name, job title and start date of employees.
Next: Write a block in PL/SQL to print the specifc number of rows from a table.
What is the difficulty level of this exercise?
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework