Q:

Write a block in PL/SQL to print a report which shows that, the employee id, name, hire date,

0

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.

All Answers

need an explanation for this answer? contact us directly to get an explanation for this answer

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.

need an explanation for this answer? contact us directly to get an explanation for this answer

total answers (1)

Similar questions


need a help?


find thousands of online teachers now