i try to solve 2 requests below but i only code for the first one,, can any one help to solve the second one
- Write SQL statements using cursor to update salary of all employees base on update conditionals
- Make sure all update records have been updated completely. The database transaction will be rolled back if any update failure).
x
declare
cur_empno int;
cursor salary_increment IS
select employee.empno from EMPLOYEE
where emplevel in(
select EmpNo from EMP_SKILL
group by EmpNo
having count(*)>1
);
begin
open salary_increment;
loop
fetch salary_increment into cur_empno;
exit when salary_increment%notfound;
update EMPLOYEE set Salary=Salary+300000
where EmpNo=cur_empno and empLevel=2 and add_months(startdate,24)<=current_date;
update EMPLOYEE set Salary=Salary+500000
where EmpNo=cur_empno and empLevel=3 and add_months(startdate,36)<=current_date;
end loop;
close salary_increment;
end;
Advertisement
Answer
Try this.
declare
cur_empno int;
cursor salary_increment IS
select employee.empno from EMPLOYEE
where emplevel in(
select EmpNo from EMP_SKILL
group by EmpNo
having count(*)>1
);
begin
open salary_increment;
loop
fetch salary_increment into cur_empno;
exit when salary_increment%notfound;
update EMPLOYEE set Salary=Salary+300000
where EmpNo=cur_empno and empLevel=2 and add_months(startdate,24)<=current_date;
update EMPLOYEE set Salary=Salary+500000
where EmpNo=cur_empno and empLevel=3 and add_months(startdate,36)<=current_date;
end loop;
close salary_increment;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
COMMIT;
END;