Employee data retrieval using cursor in PL/SQL

PL/SQL While Loop: Exercise-8 with Solution

Write a PL/SQL program to display the employee IDs, names, and job titles 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
  CURSOR employees_cursor IS
    SELECT employee_id, first_name
    FROM employees;
v_employee_idemployees.employee_id%TYPE;
v_employee_nameemployees.first_name%TYPE;
v_job_titlejobs.job_title%TYPE;
BEGIN
  -- Initialize the variables
v_employee_id := NULL;
v_employee_name := NULL;
v_job_title := NULL;
    -- Open the cursor to retrieve employee data
  OPEN employees_cursor;
    -- Print the header
  DBMS_OUTPUT.PUT_LINE('Employee ID' || CHR(9) || 'Employee Name' || CHR(9) || 'Job Title');
  DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');
    -- Fetch the first row
  FETCH employees_cursor INTO v_employee_id, v_employee_name;
    -- Loop through the cursor data
  WHILE employees_cursor%FOUND LOOP
    -- Retrieve the job title for the current employee
    SELECT job_title INTO v_job_title
    FROM jobs
    WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = v_employee_id);
        -- Display the employee data in tabular form
    DBMS_OUTPUT.PUT_LINE(v_employee_id || CHR(9) || v_employee_name || CHR(9) || v_job_title);
        -- Fetch the next row
    FETCH employees_cursor INTO v_employee_id, v_employee_name;
  END LOOP;
    -- Close the cursor
  CLOSE employees_cursor;
  EXCEPTION
  WHEN OTHERS THEN
    -- Handle exceptions
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

Sample Output:

Employee ID Employee Name Job Title
----------------------------------------------------
100 Steven President
101 Neena Administration Vice President
102 Lex Administration Vice President
103 Alexander Programmer
104 Bruce Programmer
105 David Programmer
106 Valli Programmer
107 Diana Programmer
108 Nancy Finance Manager
109 Daniel Accountant
110 John Accountant
111 Ismael Accountant
112 Jose Manue Accountant
113 Luis Accountant
114 Den Purchasing Manager

.....

Explanation:

The said code in Oracle's PL/SQL that retrieves data from the 'employees' table and displays it along with the corresponding job title from the 'jobs' table.

The cursor "employees_cursor" is declared that holds the result set of a query selects employee_id and first_name from the 'employees' table.

The three variables v_employee_id, v_employee_name, and v_job_title are declared to store the values fetched from the cursor displays the data.

The data is fetched from the cursor are assign to the variables v_employee_id and v_employee_name.

A WHILE loop that continues looping as long as there are rows in the cursor.

In the loop, a SELECT statement that retrieves the job title for the current employee and the job_id compares to result obtained from a subquery, and the subquery returns job_id from the 'employees' by matching the employee_id with the variablev_employee_id.

The employee_id, employee_name, and job_title is displayed in a tabular form using the DBMS_OUTPUT.PUT_LINE function.

The loop continues until there are no more rows in the cursor.

An exception handler is used to catch any errors that may occur during the execution and if it occurs, the error message is displayed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Display job ids, titles, and minimum salaries of all jobs.
Next: Displaying employee information.