PL/SQL program to display job titles of employees

PL/SQL While Loop: Exercise-2 with Solution

Write a PL/SQL program to display the job titles of all employees. Return a heading of job title.

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_job_titleemployees.job_id%TYPE;
  CURSOR c_employees IS SELECT job_id FROM employees;
BEGIN
  OPEN c_employees;
  DBMS_OUTPUT.PUT_LINE('JOB TITLE');
  DBMS_OUTPUT.PUT_LINE('---------');
  FETCH c_employees INTO v_job_title;
  WHILE c_employees%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(v_job_title);
    FETCH c_employees INTO v_job_title;
  END LOOP;
  CLOSE c_employees;
END;

Sample Output:

JOB TITLE
---------
AD_PRES
AD_VP
AD_VP
IT_PROG
IT_PROG
IT_PROG
IT_PROG
IT_PROG
FI_MGR
FI_ACCOUNT
FI_ACCOUNT
FI_ACCOUNT
FI_ACCOUNT
FI_ACCOUNT
PU_MAN
PU_CLERK
PU_CLERK
PU_CLERK
PU_CLERK
PU_CLERK
.....

Explanation:

The said code in Oracle's PL/SQL that retrieves the job titles of all employees from the 'employees' table and displays them.

A cursor "c_employees" fetches the job titles and stores them in the variable "v_job_title".

The cursor opens and, a loop, fetches each job title into the variable from the cursor displays a heading for the job title, and then loops through the cursor records.

The DBMS_OUTPUT.PUT_LINE procedure displays the job title. The loop continues until all job titles have been fetched.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Display names of all countries using PL/SQL.
Next: PL/SQL program to display location ids and cities.