create or replace procedure test as
b number(20):=0;
bb number(20):=0;
cc number(20):=0;
cursor test1 is
select ID from A;
cursor test2 is
select B from B where ID=b;
begin
open test1;
loop
fetch test1 into b;
open test2;
fetch test2 into bb;
loop
exit when test2%notfound;
cc:=cc+bb;
end loop;
close test2;
exit when test1%notfound;
update A set A=cc where ID=b;
end loop;
close test1;
end;我用两个游标循环取值插入另一个表.第一次就多了一倍的数值.这一次就直接插入0,晕死了.求大侠指导一下,万分感激.
--------------------------------------------
两个表A和B
A为:create table A(A number,ID number);
B为:create table B(B number,ID number);
---------------------------------------------------------
A表
---------------
A ID
--------------
null 1
-------------------
null 2
--------------
null 3B表
------------------
B ID
----------------
11 1
----------------
11 1
---------------
11 1
---------------
22 2
--------------
22 2
--------------
22 2
-------------
33 3
------------
33 3
------------
33 3要实现的功能实际上就是11+11+11插入到A表A列where ID=1中,22+22+22插入到A表A列where ID=2中,33+33+33插入到A表A列Where ID=3中..谢谢了~~!!!!
b number(20):=0;
bb number(20):=0;
cc number(20):=0;
cursor test1 is
select ID from A;
cursor test2 is
select B from B where ID=b;
begin
open test1;
loop
fetch test1 into b;
open test2;
fetch test2 into bb;
loop
exit when test2%notfound;
cc:=cc+bb;
end loop;
close test2;
exit when test1%notfound;
update A set A=cc where ID=b;
end loop;
close test1;
end;我用两个游标循环取值插入另一个表.第一次就多了一倍的数值.这一次就直接插入0,晕死了.求大侠指导一下,万分感激.
--------------------------------------------
两个表A和B
A为:create table A(A number,ID number);
B为:create table B(B number,ID number);
---------------------------------------------------------
A表
---------------
A ID
--------------
null 1
-------------------
null 2
--------------
null 3B表
------------------
B ID
----------------
11 1
----------------
11 1
---------------
11 1
---------------
22 2
--------------
22 2
--------------
22 2
-------------
33 3
------------
33 3
------------
33 3要实现的功能实际上就是11+11+11插入到A表A列where ID=1中,22+22+22插入到A表A列where ID=2中,33+33+33插入到A表A列Where ID=3中..谢谢了~~!!!!
loop
exit when test2%notfound;
晕,,这里更正一下,,放在下面是一样的.. loop
fetch test2 into bb;
exit when test2%notfound;
b number(20):=0;
bb number(20):=0;
cc number(20):=0;
cursor test1 is
select ID from A;
cursor test2 is
select B from B where ID=b;
begin
open test1;
loop
fetch test1 into b;
exit when test1%notfound;
open test2;
loop
fetch test2 into bb;
exit when test2%notfound;
cc:=cc+bb;
end loop;
close test2;
update A set A=cc where ID=b;
end loop;
close test1;
end;
b number(20):=0;
bb number(20):=0;
cc number(20):=0;
cursor test1 is
select ID from A;
cursor test2 is
select B from B where ID=b;
begin
open test1;
loop
fetch test1 into b;
exit when test1%notfound;
open test2;
cc:=0;
loop
fetch test2 into bb;
exit when test2%notfound;
cc:=cc+bb;
end loop;
close test2;
update A set A=cc where ID=b;
end loop;
close test1;
end;