游标只能检索,不能更新,或者可以用检索到某个值的条件可以利用更新语句更新. loop fetch v_sor into 变量; exit when v_sor%notfound; ..... update table_name set col_name=值 where id=v_sor.id; end loop;
更新游标不代表更新数据,这些楼上(beckhambobo)的都已经说过了. 但是,游标可以象变量一样的来赋值,如下. open vCur_Line ; loop fetch vCur_Line into vRecord_Line; exit when vCur_Line%notfound or vCur_Line%notfound is null; vRecord_Line.C7 := null ; vRecord_Line.C8 := null ; vRecord_Line.C10 := null ; -- 此时vRecord_Line.C7 ,C8,C10都为null ..... end loop; close vCur_Line;
在游标中update没问题,但是有commit时,可能会报错,好象是在816中没问题,但是在817中会出错的,oracle 的pdf文档中提到这个问题,即决方法是(记不太清了) 用rowid定位 cursor cursor_name is select a,b for update 改为 cursor cursor_name is select rowid as rid ,a,b for update; loop fetch cursor_name into 变量; exit when cursor_name%notfound; ..... update table_name set col_name=值 where rowid=cursor_name.rid; (update table_name set col_name=值 where rowid=to_rowid(cursor_name.rid); ?) end loop;
如果你要更新表,使用update语句,如果你仅仅是取出值后要进行变化,必须使用变量。
cursor cursor_name is select ... for update;
...
update ... where current of cursor_name;
...
commit;
...
loop
fetch v_sor into 变量;
exit when v_sor%notfound;
.....
update table_name set col_name=值 where id=v_sor.id;
end loop;
但是,游标可以象变量一样的来赋值,如下.
open vCur_Line ;
loop
fetch vCur_Line into vRecord_Line;
exit when vCur_Line%notfound or vCur_Line%notfound is null;
vRecord_Line.C7 := null ;
vRecord_Line.C8 := null ;
vRecord_Line.C10 := null ;
-- 此时vRecord_Line.C7 ,C8,C10都为null
.....
end loop;
close vCur_Line;
用rowid定位
cursor cursor_name is select a,b for update
改为
cursor cursor_name is select rowid as rid ,a,b for update;
loop
fetch cursor_name into 变量;
exit when cursor_name%notfound;
.....
update table_name set col_name=值 where rowid=cursor_name.rid;
(update table_name set col_name=值 where rowid=to_rowid(cursor_name.rid); ?)
end loop;