dato datuashvili dato datuashvili - 9 months ago 44
SQL Question

Update employees table

Guys I have the following problem:

Increase salary 15% for employees whose salary is less than 50% of their manager's salary.
Write PL/SQL procedure using cursor, loop and update.

Procedure header
Create or replace procedure

inc_salary
is:

. Exception if their salary after increase is more than 50% of their manager's salary.

Actually, we can do it directly like this:

update emp e
set e.salary+=e.salary*0.15
where e.salary<(select e.mgr from emp e, group by e.mgr)


Here is a picture of this table:
enter image description here

But I don't understand how to use the procedure. If I declare it like this, create or replace procedure
inc_salary
, then what should be its parameters? We can use of course loop, like

declare
for r in (select * from emp e) loop
update emp e
set r.salary+=r.salary*0.15;
where r.salary<r.mgr
exception
if r.salary >r.mgr*1.15 then
dbms.output_putline(' it can't increase');
end loop;
end;


But how to combine it together?

Answer Source

Why would you need a PL/SQL procedure? A simple query would do this job!

UPDATE emp
SET salary = salary * 1.15
WHERE empno IN (
    SELECT e.empno
      FROM emp e
      JOIN emp m ON e.mgr = m.empno
     WHERE e.salary < m.salary * 0.5
)

That's it!

But, if at all you need to use a procedure, you have to decide for yourself what exactly you want to do with it.

Every procedure has a set of formal parameters, which can even be an empty set. It is you who decides what to pass to a procedure. Consult your manager or architect for these situations.