dato datuashvili dato datuashvili - 1 year ago 96
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


. 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
, then what should be its parameters? We can use of course loop, like

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

But how to combine it together?

Answer Source

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

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download