Oracle数据库游标操作

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