1、含有參數的游標

declare
?cursor cur_my (mv number) is select * from Person where no<mv;
begin
? for tem in cur_my(4) loop
? DBMS_OUTPUT.put_line('name:'||tem.name);
? end loop;
end;

2、設置引用游標
declare
?? temp_row Person%rowtype;
?? type my_type is ref cursor;
?? cur_my my_type;
begin
?? open cur_my for 'select * from Person ';
?? loop
????? fetch cur_my into temp_row;
????? exit when cur_my%notfound;
????? DBMS_OUTPUT.put_line('name:'||temp_row.name);
?? end loop;
?? close cur_my;
end;

3、for loop循環游標

DECLARE
??? v_id Integer;
??? v_name varchar2(50);
??? v_age Integer;
??? cursor cur_mycursor is select id,name,age from Users;
BEGIN
??? for temp in cur_mycursor loop
?????? v_id :=temp.id;
?????? v_name :=temp.name;
?????? v_age :=temp.age;
?????? dbms_output.put_line('id:'||v_id||'name:'||v_name||'age:'||v_age);
??? end loop;
??? /**dbms_output.put_line('所有記錄數：'||cur_mycursor%rowcount||'條！');*/
END;

4、標準化loop循環游標

DECLARE
??? v_id Integer;
??? v_name varchar2(50);
??? v_age Integer;
??? cursor cur_mycursors is select id,name,age from Users;
BEGIN
???? OPEN cur_mycursors;
?????? dbms_output.put_line('所有記錄數：'||cur_mycursors%rowcount||'條！');
???? LOOP
?????? FETCH cur_mycursors INTO v_id,v_name,v_age;
?????? dbms_output.put_line('id:'||v_id||'name:'||v_name||'age:'||v_age);
?????? IF? cur_mycursors%NOTFOUND THEN
??????????? EXIT;
?????? END IF;
???? END LOOP;
???? dbms_output.put_line('所有記錄數：'||cur_mycursors%rowcount||'條！');
???? CLOSE cur_mycursors;
END;
/

轉載于:https://www.cnblogs.com/boonya/archive/2011/06/28/2092190.html

總結

以上是生活随笔為你收集整理的Oracle数据库游标操作的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。