PL/SQL Cursor Exercises: Print a report which shows that, the employee id, name, hire date, and the incentive amount they achieved according to their working experiences

PL/SQL Cursor: Exercise-47 with Solution

Write a block in PL/SQL to print a report which shows that, the employee id, name, hire date, and the incentive amount they achieved according to their working experiences, who joined in the month of current date.

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
    emp_id        employees.employee_id%TYPE;
    emp_hiredate  employees.hire_date%TYPE;
    emp_firstname employees.first_name%TYPE;
    emp_lastname  employees.last_name%TYPE;
    no_of_emp     NUMBER;
    inctv_amount  NUMBER;
    work_exp      NUMBER;
    CURSOR emp_cur (
      thismonth NUMBER)IS
      SELECT employee_id,
             first_name,
             last_name,
             hire_date
      FROM   employees
      WHERE  Extract(month FROM hire_date) = thismonth;
BEGIN
    OPEN emp_cur(Extract(month FROM SYSDATE));
    dbms_output.Put_line('Date: '
                         || To_char(SYSDATE, 'DL'));
    dbms_output.Put_line('Employees with yearly incentive amounts:');
    dbms_output.Put_line('------------------------------------------');
    dbms_output.Put_line(Rpad('Employee ID', 15)
                         || Rpad('Name of the Employee', 30)
                         || Rpad('Hire Date', 15)
                         || 'Incentive Amount');
dbms_output.Put_line('------------------------------------------------------------------------------');
LOOP
    FETCH emp_cur INTO emp_id, emp_firstname, emp_lastname, emp_hiredate;
    EXIT WHEN emp_cur%NOTFOUND;
    work_exp := Round(( Months_between(SYSDATE, emp_hiredate) / 12 ));
    IF work_exp > 13 THEN
      inctv_amount := 8000;
    ELSIF work_exp > 11 THEN
      inctv_amount := 5000;
    ELSIF work_exp > 9 THEN
      inctv_amount := 3000;
    ELSIF work_exp > 7 THEN
      inctv_amount := 2000;
    ELSIF work_exp > 4 THEN
      inctv_amount := 1000;
    ELSIF work_exp > 0 THEN
      inctv_amount := 400;
    END IF;
    dbms_output.Put_line(Rpad(emp_id, 15)
                         ||Rpad(( emp_firstname
                                  ||' '
                                  ||emp_lastname ), 30)
                         ||Rpad(emp_hiredate, 15)
                         || To_char(inctv_amount, '9,999'));
END LOOP;

no_of_emp := emp_cur%rowcount;

dbms_output.Put_line('The number of rows fetched is '
                     || no_of_emp);
CLOSE emp_cur;
END;

/ 

Sample Output:

SQL> /
Date: Saturday, May 26, 2018
Employees with yearly incentive amounts:
------------------------------------------
Employee ID    Name of the Employee          Hire Date      Incentive Amount
-----------------------------------------------------------------------------
104            Bruce Ernst                   21-MAY-07       3,000
115            Alexander Khoo                18-MAY-03       8,000
122            Payam Kaufling                01-MAY-03       8,000
174            Ellen Abel                    11-MAY-04       8,000
178            Kimberely Grant               24-MAY-07       3,000
197            Kevin Feeney                  23-MAY-06       5,000
The number of rows fetched is 6

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a block in PL/SQL to display the first department with more than five employees.
Next: Write a block in PL/SQL to shows how are records are declared and initialized.