游标的属性:
%FOUND :找到数据
%ISOPEN :游标是否已打开
%NOTFOUND :没有找到数据
%ROWCOUNT :当前行号
1 游标传递变量
declare cursor mycur(id varchar2) is select * from tAgent where cAgentid=id;
myrec tagent%rowtype;
begin
open mycur('9999')
fecth mycur into myrec;
while mycur%FOUND LOOP
DBMS_PUTPUT.PUT_LINE('记录为:' || mycur%ROWCOUNT);
fecth mycur into myrec;
END LOOP;
CLOSE mycur;
end;
2 游标修改值
DECLARE CURSOR mycur IS
SELECT cAgentName from tAgent for update;
strName varchar2(50);
begin
open mycur;
fetch mycur into strName;
while mycur%FOUND LOOP
UPDATE TAGENT SET cAgentName='' where current of mycur;
fetch mycur into strName;
END LOOP;
close mycur;
end;
3 隐式游标
begin
for cur in (select cAgentName from tAgent) loop
dbms_output.put_line(cur.cAgentName);
end loop
end;
数据量大的时候,不建议使用游标,因为其效率较低。
%FOUND :找到数据
%ISOPEN :游标是否已打开
%NOTFOUND :没有找到数据
%ROWCOUNT :当前行号
1 游标传递变量
declare cursor mycur(id varchar2) is select * from tAgent where cAgentid=id;
myrec tagent%rowtype;
begin
open mycur('9999')
fecth mycur into myrec;
while mycur%FOUND LOOP
DBMS_PUTPUT.PUT_LINE('记录为:' || mycur%ROWCOUNT);
fecth mycur into myrec;
END LOOP;
CLOSE mycur;
end;
2 游标修改值
DECLARE CURSOR mycur IS
SELECT cAgentName from tAgent for update;
strName varchar2(50);
begin
open mycur;
fetch mycur into strName;
while mycur%FOUND LOOP
UPDATE TAGENT SET cAgentName='' where current of mycur;
fetch mycur into strName;
END LOOP;
close mycur;
end;
3 隐式游标
begin
for cur in (select cAgentName from tAgent) loop
dbms_output.put_line(cur.cAgentName);
end loop
end;
数据量大的时候,不建议使用游标,因为其效率较低。