DROP TABLE emp_temp;
CREATE TABLE emp_temp AS
SELECT *
FROM employees;
DECLARE
CURSOR employees_cur IS
SELECT employee_id,
first_name,
Trunc(Months_between(SYSDATE,hire_date) / 12) expr
FROM emp_temp;
incre_per NUMBER(2);
BEGIN
dbms_output.put_line(rpad('Employee ID',15)||rpad('Name',25) || 'Increment %');
dbms_output.Put_line('-----------------------------------------------------------');
FOR store_emp_rec IN employees_cur
LOOP
incre_per :=
CASE
WHEN store_emp_rec.expr > 10 THEN
15
WHEN store_emp_rec.expr > 5 THEN
10
ELSE
8
END;
UPDATE emp_temp
SET salary = salary + (salary * incre_per / 100)
WHERE employee_id = store_emp_rec.employee_id;
dbms_output.put_line(rpad(store_emp_rec.employee_id,15) ||rpad(store_emp_rec.first_name,25)|| incre_per );
END LOOP;
END;
/
Sample Output:
SQL> /
Employee ID Name Increment %
----------------------------------------------------
100 Steven 15
101 Neena 15
102 Lex 15
103 Alexander 15
104 Bruce 10
105 David 15
106 Valli 15
107 Diana 15
108 Nancy 15
109 Daniel 15
110 John 15
111 Ismael 15
112 Jose Manuel 15
113 Luis 10
114 Den 15
...
Sample Output:
need an explanation for this answer? contact us directly to get an explanation for this answer