PL/SQL Program to Display Employee Information

PL/SQL While Loop: Exercise-4 with Solution

Write a PL/SQL program to display the employee IDs, names, job titles, hire dates , and salaries of all employees.

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_employee_idemployees.employee_id%TYPE;
v_full_nameemployees.first_name%TYPE;
v_job_idemployees.job_id%TYPE;
v_hire_dateemployees.hire_date%TYPE;
v_salaryemployees.salary%TYPE;
  CURSOR c_employees IS
    SELECT employee_id, first_name || ' ' || last_name AS full_name, job_id, hire_date, salary
    FROM employees;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Employee ID | Full Name | Job Title | Hire Date | Salary');
  DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------');
  OPEN c_employees;
  FETCH c_employees INTO v_employee_id, v_full_name, v_job_id, v_hire_date, v_salary;
  WHILE c_employees%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(v_employee_id || '        | ' || v_full_name || ' | ' || v_job_id || ' | ' || v_hire_date || ' | ' || v_salary);
    FETCH c_employees INTO v_employee_id, v_full_name, v_job_id, v_hire_date, v_salary;
  END LOOP;
  CLOSE c_employees;
END;
/

Sample Output:

Employee ID | Full Name | Job Title | Hire Date | Salary
-------------------------------------------------------------------
100        | Steven      King         | AD_PRES | 17-JUN-87 | 24000
101        | NeenaKochhar      | AD_VP | 18-JUN-87 | 17000
102        | Lex         De Haan      | AD_VP | 19-JUN-87 | 17000
103        | Alexander   Hunold       | IT_PROG | 20-JUN-87 | 9000
104        | Bruce       Ernst        | IT_PROG | 21-JUN-87 | 6000
105        | David       Austin       | IT_PROG | 22-JUN-87 | 4800
106        | ValliPataballa    | IT_PROG | 23-JUN-87 | 4800
107        | Diana       Lorentz      | IT_PROG | 24-JUN-87 | 4200
108        | Nancy       Greenberg    | FI_MGR | 25-JUN-87 | 12000
109        | Daniel      Faviet       | FI_ACCOUNT | 26-JUN-87 | 9000
110        | John        Chen         | FI_ACCOUNT | 27-JUN-87 | 8200
111        | Ismael      Sciarra      | FI_ACCOUNT | 28-JUN-87 | 7700
112        | Jose ManueUrman        | FI_ACCOUNT | 29-JUN-87 | 7800
113        | Luis        Popp         | FI_ACCOUNT | 30-JUN-87 | 6900
.....

Explanation:

The said code in Oracle's PL/SQL that retrieves and display the employee IDs, names, job titles, hire dates, and salaries for all employees stored in the 'employees' table.

The cursor c_employees is declared that selects the information of employees from the 'employees' table, including the employee_id, first_name, last_name, job_title, hire_date, and salary fields.

The FOR loop iterates through each row of the c_employees cursor and retrieves employees information by the emp record, which corresponds to the current row of the cursor.

The DBMS_OUTPUT.PUT_LINE function display the employee_id, full_name, job_title, hire_date, and salary in a formatted manner.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: PL/SQL program to display location ids and cities.
Next: Employee information retrieval using PL/SQL cursor.