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中..谢谢了~~!!!!
解决方案 »
- 请问分页后如何计算得到了多少条记录啊?
- oracle表结构与表数据的比较(比较急的问题)
- 我的电脑内存太小了,装了一个plsql和Oracle的客户端。但是当连接到其他电脑的数据库的数据库出错??????
- 关于触发器(触发器中不能使用本表吗)
- 监听器
- 请问使用EXP命令能否导出远程服务器上的oracle数据库
- 请问oracle触发器中可以在insert动作完成前将其禁止吗?
- 一个SQL能写出这样的报表样式么?
- 求救一个sql语句或者存储过程
- ORACLE 自定义函数 如何 跨表空间查询
- 用DBA登入ORACLE数据库后不久,实例突然断开连不上了。很有规律经常有这种情况,请各位帮忙解决。
- 各位高手,请教育一个关于中文乱码的问题
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;