PL/SQL program: Display department information

PL/SQL While Loop: Exercise-11 with Solution

Write a PL/SQL program to display the department IDs, names, and the name of the city where the department is located of all departments.

Sample Solution:

Table: departments

department_id			integer
department_name			varchar(25)
manager_id			integer
location_id			integer

location_id			integer
street_address			varchar(50)
postal_code			varchar(13)
city				varchar(25)
state_province			varchar(25)
country_id			varchar(2)

PL/SQL Code:

DECLARE
v_department_iddepartments.department_id%TYPE;
v_department_namedepartments.department_name%TYPE;
v_city_namelocations.city%TYPE;
  CURSOR c_departments IS
    SELECT d.department_id, d.department_name, l.city
    FROM departments d
    JOIN locations l ON d.location_id = l.location_id;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Department ID' || CHR(9) || 'Department Name' || CHR(9) || 'City');
  DBMS_OUTPUT.PUT_LINE('-------------------------------------------------');
    OPEN c_departments;
  FETCH c_departments INTO v_department_id, v_department_name, v_city_name;
  WHILE c_departments%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(v_department_id || CHR(9) || v_department_name || CHR(9) || v_city_name);
    FETCH c_departments INTO v_department_id, v_department_name, v_city_name;
  END LOOP;
  CLOSE c_departments;
END;

Sample Output:

Department ID	Department Name	City
-------------------------------------------------
10	Administration	Seattle
20	Marketing	Toronto
30	Purchasing	Seattle
40	Human Resources	London
50	Shipping	South San
60	IT	Southlake
70	Public Relation	Munich
80	Sales	Oxford
90	Executive	Seattle
100	Finance	Seattle
110	Accounting	Seattle
120	Treasury	Seattle
130	Corporate Tax	Seattle
140	Control AndCre	Seattle
150	Shareholder Ser	Seattle
160	Benefits	Seattle
170	Manufacturing	Seattle
180	Construction	Seattle
190	Contracting	Seattle
200	Operations	Seattle
210	IT Support	Seattle

.....

Explanation:

The said code in Oracle's PL/SQL that provides a clear and formatted display of the department IDs, names, and city names for each department.

The variables v_department_id, v_department_name, v_city_name of same datatype as the corresponding columns of the tables are declared to hold the department ID, department name, and city name.

A cursor c_departments is defined that selects the department ID, department name, and city from the 'departments' and 'locations' tables, respectively, by joining them based on the location_id column.

The FETCH statement fetches the first set of data from the cursor into the variables.

The WHILE loop iterates through the fetched result set as long as the cursor finds a row.

Within the loop, the department ID, department name, and city name are displays using the DBMS_OUTPUT.PUT_LINE function.

Flowchart:

Previous: Employee job history report.
Next: Display Departments and Employees.