changing data with FOR UPDATE..WHERE CURRENT OF (merged 7) [message #426440] |
Thu, 15 October 2009 08:40  |
thedutchguy
Messages: 13 Registered: October 2009
|
Junior Member |
|
|
Hi,
I've got an assignment and have a question about it:
-------This is the assignment----------------------------
In table employees:
-switch office.nr. 10 and 30
-give employees with a salary off <= 2000 a raise off 8%
-give employees with a salary between 2000 and 2500 a raise off 6%
-give employees with a salary between 2500 and 3500 a raise off 4%
-give employees > 3500 a raise off 2%
-because the company is going internationaly, change the salary
from euro's to dollars (1 euro = 0,95 dollar)
Use FOR UPDATE ....WHERE CURRENT OF
-------End off assignment---------------------------------
I've got the switch off office and changing the currency working (see below) but I can't get the raise in my script working.
And.....how do I let the system give the raises first and then the changing off the currency.
Thanks in advantage,
declare
cursor c_employees is
select *
from employees
for update of officenr;
begin
for r_employees in c_employees loop
update employees
set sal = sal*0.95
where current of c_employees;
if r_employees.officenr = 10 then
update employees
set officenr = 30
where current of c_employees;
elsif r_employees.officenr = 30 then
update employees
set officenr = 10
where current of c_employees;
end if;
end loop;
end;
/
|
|
|
Re: changing data with FOR UPDATE..WHERE CURRENT OF [message #426452 is a reply to message #426440] |
Thu, 15 October 2009 08:51   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
First off, when posting code can you please use code tags rather than italics, it's easier to read - see the orafaq forum guide if you're not sure how.
For the raise you can use a case statement:
UPDATE employees
SET salary = CASE WHEN salary <= 2000 THEN salary * 1.08
WHEN salary BETWEEN 2000 and 2500 THEN salary * 1.06
....
ELSE salary * 1.02
END
WHERE current of
There is an ambiguity in your problem description. Does someone with a salary of 2500 get a raise of 6% or 4%?
You can use the case idea for office number as well rather than doing seperate updates.
As for the currency change - do that update last of course.
|
|
|
|
|
|
|