PL/SQL program to display total number of employees hired each year

PL/SQL While Loop: Exercise-16 with Solution

Write a PL/SQL program to display the total number of employees hired each year between 1985 and 2000. Return the result in tabular format.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DECLARE
v_year NUMBER := 1985; -- Starting year
v_total NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Year    Total Employees');
  DBMS_OUTPUT.PUT_LINE('---------------------');
  WHILE v_year<= 2000 LOOP
v_total := 0; -- Reset total for each year
    DECLARE
      CURSOR c_employees IS
        SELECT COUNT(*) AS emp_count
        FROM employees
        WHERE EXTRACT(YEAR FROM hire_date) = v_year;
r_employeec_employees%ROWTYPE;
    BEGIN
      OPEN c_employees;
      FETCH c_employees INTO r_employee;
      CLOSE c_employees;
v_total := r_employee.emp_count;
    END;
    DBMS_OUTPUT.PUT_LINE(v_year || '     ' || v_total);
v_year := v_year + 1;
  END LOOP;
END;
/

Sample Output:

Year    Total Employees
---------------------
1985     0
1986     0
1987     107
1988     0
1989     0
1990     0
1991     0
1992     0
1993     0
1994     0
1995     0
1996     0
1997     0
1998     0
1999     0
2000     0

Explanation:

The said code in Oracle's PL/SQL that calculates and displays the total number of employees hired each year starting from 1985 and ending at 2000.

Within the outer loop, a variable v_total initializes to keep track of the total number of employees hired in each year.

Within the inner loop, a cursor c_employees is defined that fetches the count of employees hired in the year mentioned in the hire_date column and the result is stored in the variable v_total.

The DBMS_OUTPUT.PUT_LINE function displays the year and the corresponding number of employees hired in this year.

Flowchart:

Previous: Count Employees in Each Department.
Next: Employee count by country.