PL/SQL Cursor Exercises: Find out the start date for current job of a specific employee

PL/SQL Cursor: Exercise-40 with Solution

Write a PL/SQL block to find out the start date for current job of a specific employee.

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

employee_id			integer
start_date			date
end_date			date
job_id				varchar(25)
department_id			integer

PL/SQL Code:

DECLARE
    emp_st_date DATE;
    wr_emp_id   employees.employee_id%TYPE := &enter_employee_id;
BEGIN
    SELECT Max(end_date) + 1
    INTO   emp_st_date
    FROM   job_history
    WHERE  employee_id = wr_emp_id;

    IF emp_st_date IS NULL THEN
      SELECT hire_date
      INTO   emp_st_date
      FROM   employees
      WHERE  employee_id = wr_emp_id;
    END IF;

dbms_output.Put_line('----------------------------------------------------------------------');

dbms_output.Put_line('The starting date of current job for the employee  '
                     ||wr_emp_id
                     ||' is: '
                     ||emp_st_date);
END;
/

Sample Output:

SQL> /
Enter value for enter_employee_id: 189
old   3:     wr_emp_id   employees.employee_id%TYPE := &enter_employee_id;
new   3:     wr_emp_id   employees.employee_id%TYPE := 189;
----------------------------------------------------------------------
The starting date of current job for the employee  189 is: 13-AUG-05

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL block to display the last name of manager, and their departments for a particular city, using parameters with a default value in explicit cursor.
Next: 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