update a
set columna1 = (select columnc2
from b,c
where columnb2 = columna2
and columnc1 = columnb1
and rownum = 1 --本行用于处理超过一个结果);
另外,相关联的字段最好有索引,以提高效率
set columna1 = (select columnc2
from b,c
where columnb2 = columna2
and columnc1 = columnb1
and rownum = 1 --本行用于处理超过一个结果);
另外,相关联的字段最好有索引,以提高效率
语句我是这样写的:
DECLARE
cursor cur1 IS SELECT columna1 FROM a;
a1 a.columna1%type;
c1 c.columnc1%type;
BEGIN
OPEN cur1;
LOOP
FETCH cur1 INTO a1;
EXIT WHEN cur1%notfound;
BEGIN
SELECT columnc2 into c2
FROM c
WHERE columncc = (select b.columnb1
from b,a
where a.columna1 = a1 and a.columna2 = b.columnb2);
UPDATE a SET columna1 = c2
WHERE columna1 = a1;
END;
END LOOP;
CLOSE cur1;
END;当数据少时,程序很快执行完毕
而当对40万条数据进行处理的时候,则运行时间很长,程序“死掉”
请问各位,象这种情况,是不是不能用游标?
如果不可以,那该怎么做呢?
1.自己控制事务,每1000条COMMIT一次,当然去掉数据库事务也可;
2.注意索引;
select a.columna1, c.columnc2
from
a
right outer join
b
on a.columna2 = b.columnb2
left outer join
c
on b.columnb1 = c.columnc1