关于游标

[tigerjacky]

1.ORACLE的游标中执行跳出需要关闭吗?
比如
declare cursor mycur is select col1 from tablea ;
open mycur;
loop
fetch into ...
exit...
/*
do something
*/
if false then
close mycur;
return;
end if;
end loop;
close mycur;
end;
2.如果我要在游标中使用goto,该如何写呢?
比如
declare cursor mycur is select col1 from tablea ;
open mycur;
loop
fetch into ...
exit...
/*
do something
*/
if false then
goto error;
end if;
end loop;
close mycur;
end;

<<error>>
end loop;
close mycur;
return;

[tigerjacky]

beckhambobo(beckham) ( ) 信誉：191
-------------------------------------------------
if false then
return;
close mycur;

end if;
------------------------------------------------------
先return 再关闭close ???

[tigerjacky]

3.if true then
l_value := 1;
end if;
是否能写为
if true then l_value := 1;
4.现在我要从B表中获取数据更新A表的两个字段,假设B表的主键是ID唯一,与A表的FID相关联
那么
4.1.update A SET A.COL1 = (SELECT COL1 FROM B WHERE B.ID = A.FID),A.COL2 = (SELECT COL2 FROM B WHERE B.ID = A.FID)有错吗?
4.2.有没有更简单的写法?因为这样我要SELECT 两次....
4.3.MERGE如何使用?
------------------------------------------------------------------------------------
分不够继续加.

[zhpsam109]

1.需要关闭
2.应该是if...then .... endif ,没有用过goto;
3.最好写成if..then..endif;

4.你写的语法没错！

[beckhambobo]

需要关闲，不需end loop;
goto是早版本传下来，建义楼主使用return.

declare cursor mycur is select col1 from tablea ;
open mycur;
loop
fetch into ...
exit...
/*
do something
*/
if false then
return;
close mycur;

end if;
end loop;
close mycur;
end;

[yown]

1:declare cursor mycur is select col1 from tablea ;
open mycur;
loop
fetch into ...
exit...
/*
do something
*/
if false then
close mycur; --最好关闭
return;
end if;
end loop;
close mycur;
end;

[beckhambobo]

sorry
if false then

close mycur;
return;

end if;

[tigerjacky]

4.有没有更简单的写法?或者说等价的写法?
1.在存储过程最后都需要COMMIT语句吗?

[bluecocoqd]

declare cursor mycur is select col1 from tablea ;
open mycur;
loop
fetch into ...
exit...
/*
do something
*/
if false then
goto error;
end if;
end loop;
close mycur;
end;


<<error>>
NULL;


end loop;
close mycur;
return;
这样试试