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.
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.
Sample Output:
need an explanation for this answer? contact us directly to get an explanation for this answer