Promote a clerk if his salary is greater than 1000 and update his salary by 10%

Write a Parameter Cursor program that promotes CLERK who earn more than 1000 to SR CLERK and increase the salary by 10%. Pass CLERK as a parameter to the Cursor. Use cursor with Update Clause.

My code:

declare
  cursor c_promote(v_job varchar2) is 
  select empno from emp 
  where job=v_job and sal>1000 
  for update of job,sal nowait;
begin
  for emp_record in c_promote('CLERK') loop
    update emp set job='SR CLERK' 
    where empno=emp_record.empno;
    update emp set sal=sal+sal*0.1 
    where empno=emp_record.empno;
  end loop;
end;

My code does the work but I don’t find it correct so can someone suggest an alternative?

Answer

You can create a small procedure and pass v_job and update it in a single update statement rather than using cursor

 Update emp set job='SR CLERK' , sal=sal+sal*0.1
    where sal>1000 and job = v_job;