Write a block in PL/SQL to print a report which shows that, the employee id, name, hire date,
- pl/sql
- 2021-09-28
- mhanasmh00489829403
الأجوبة
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.
أسئلة مشابهة
القوائم الدراسية التي ينتمي لها السؤال
معلومات ذات صلة