I've been searching through books and the web for hours, and I couldn't find any real luck. As Oracle or PL/SQL isn't really my greatest gift in life I though to try it this way.
What I want to ac开发者_如何学Gohieve is to select like all the records in TABLE A and use "each" record within a loop.
In pseudo code, it would be something like this.
x = SELECT * from TABLE_A; for each x UPDATE TABLE_B where kitten = x; end for loop
Help?
If possible, try to write it as a single UPDATE statement.
update table_b b
set (b.col1, b.col2) = (
select a.colx + 10, a.coly / 18
from table_a a
where b.id = a.id);
It is faster than the procedural counterpart and also has less lines of code making it easier to understand and migrate to other database.
Use cursor:
DECLARE
cursor c1 is
select monthly_income
from employees
where name = v_name_in;
BEGIN
FOR employee_rec in c1
LOOP
update tableB set incom_val = employee_rec.monthly_income where ...;
END LOOP;
END;
or:
DECLARE
BEGIN
FOR employee_rec in (select monthly_income
from employees
where name = v_name_in)
LOOP
update tableB set incom_val = employee_rec.monthly_income where ...;
END LOOP;
END;
精彩评论