w3resource

PL/SQL Cursor Exercises: Display the first name, job title and start date of employees

PL/SQL Cursor: Exercise-42 with Solution

Write a block in PL/SQL to display the first name, job title and start date of employees.

Sample Solution:

PL/SQL Code:

declare
  cursor employees_cur is
   select employee_id, first_name, last_name, job_title , hire_date
   from employees natural join jobs;
   
   emp_st_date  date;
begin
dbms_output.put_line( rpad('Employee Name',30) || rpad('Job Title',35)|| 'Starting Date');
dbms_output.put_line('-----------------------------------------------------------------------------------------');
   for employee_rec in employees_cur
   loop
       -- find out most recent end_date in job_history
       select max(end_date) + 1 into emp_st_date
       from job_history
       where employee_id = employee_rec.employee_id;
       if emp_st_date is null then
             emp_st_date :=  employee_rec.hire_date;      
       end if;
       dbms_output.put_line(rpad((employee_rec.first_name||' '||employee_rec.first_name),30) || rpad(employee_rec.job_title,35)
           || to_char(emp_st_date,'dd-mon-yyyy'));
   end loop;
end; 

Sample Output:

SQL> /
Employee Name                 Job Title                          Starting Date
-------------------------------------------------------------------------------
William William               Public Accountant                  07-jun-2002
Shelley Shelley               Accounting Manager                 07-jun-2002
Jennifer Jennifer             Administration Assistant           01-jan-2007
Steven Steven                 President                          17-jun-2003
Lex Lex                       Administration Vice President      25-jul-2006
Neena Neena                   Administration Vice President      16-mar-2005
John John                     Accountant                         28-sep-2005
Daniel Daniel                 Accountant                         16-aug-2002
Luis Luis                     Accountant                         07-dec-2007
Ismael Ismael                 Accountant                         30-sep-2005
Jose Manuel Jose Manuel       Accountant                         07-mar-2006
Nancy Nancy                   Finance Manager                    17-aug-2002
Susan Susan                   Human Resources Representative     07-jun-2002
... 

Flowchart:

Flowchart: Display the first name, job title and start date of employees

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL block to display the last name, first name and overpaid amount by adding formal parameters and specify a default values for the added parameters.
Next: Write a block in PL/SQL to print the department name, head of the department, city and number of employees are wroking in that department.

What is the difficulty level of this exercise?



Inviting useful, relevant, well-written and unique guest posts