Oracle中游标Cursor的用法详解

fetch cursor_name into variable1,varibale2,...;

fetch cursor_name bulk collect into collect1,collect2,...[limit rows];

declare
          v_bookname varchar2(100);
          cursor c_book(i_id number) is select bookname from book where id = i_id;
    begin
        Open c_book(10);--打开游标
        Loop
            Fetch c_book into v_bookname; --提取游标
            exit when c_book%notfound;
            update book set price = '33' where bookname = v_bookname;
        End Loop;
        Close c_book;--关闭游标
    end;

declare
          v_bookname varchar2(100);
          cursor c_book(i_id number) is select bookname from book where id = i_id;
begin
          Open c_book(10);
          Fetch c_book into v_bookname;--预先Fetch一次
          While c_book%found Loop
              update book set price = '33' where bookname = v_bookname;
               Fetch c_book into v_bookname;
          End Loop;
         Close c_book;
end;

declare
    cursor emp_cursor is select ename,sal from emp;
    emp_record emp_cursor%rowtype;
  begin
    open emp_cursor;
    loop
     fetch emp_cursor into emp_record;
     exit when emp_cursor%notfound;
     dbms_output.put_line('雇员名:'||emp_record.ename||',雇员工资:'||emp_record.sal);
    end loop;
 end;

close cursor_name;

declare
    cursor emp_cursor(no number) is select ename from emp where deptno=no;
    v_ename emp.ename%type;
  begin
    open emp_cursor(10);
    loop
     fetch emp_cursor into v_ename;
     exit when emp_cursor%notfound;
     dbms_output.put_line(v_ename);
    end loop;
    close emp_cursor;
  end;

--不需要声明v_bookname,Open和Close游标和fetch操作(不用打开游标和关闭游标，实现遍历游标最高效方式)
declare
 cursor c_book(i_id number) is select bookname from book where id = i_id;
begin
   for cur in c_book(10) loop --循环变量cur不需要声明
     update book set price = '53' where bookname = cur.bookname;
   end loop;
end;

begin
     for emp_record in (select ename,sal from emp) loop
        dbms_output.put_line(emp_record.ename);
    end loop;
end;

cursor cursor_name(parameter_name datetype) is select_statement for update [of column_reference] [nowait];

declare
    cursor emp_cursor is select ename,sal from emp for update;
    v_ename emp.ename%type;
    v_sal emp.sal%tyep;
  begin
    open emp_cursor;
    loop
     fetch emp_cursor into v_ename,v_oldsal;
     exit when emp_cursor%notfound;
     if v_oldsal<2000 then
        update emp set sal=sal+100 where current of emp_cursor;--delete from emp where current of emp_cursor;
     end if;
   end loop;
   close emp_cursor;
 end;

declare
   cursor emp_cursor is select ename from emp where deptno=10;
    type ename_table_type is table of varchar2(10);
    ename_table ename_table_type;
  begin
    open emp_cursor;
    fetch emp_cursor bulk collect into ename_table;
    for i in 1..ename_table.count loop
       dbms_output.put_line(ename_table(i));
    end loop;
    close emp_cursor;
  end;

declare
    type name_array_type is varray(5) of varchar2(10);
    name_array name_array_type;
    cursor emp_cursor is select ename from emp;
    rows int:=5;
    v_count int:=0;
  begin
    open emp_cursor;
    loop
     fetch emp_cursor bulk collect into name_array limit rows;
     dbms_output.pur('雇员名');
     for i in 1..(emp_currsor%rowcount-v_count) loop
       dbms_output.put(name_array(i)||' ');
     end loop;
     dbms_output.new_line;
    v_count:=emp_cursor%rowcount;
    exit when emp_cursor%notfound;
    end loop;
    close emp_cursor;
  end;

type ref_type_name is ref cursor [return return_type];

cursor_varibale ref_type_name;

open cursor_variable for select_statement;

fetch cursor_varibale into variable1,variable2,...;

fetch cursor_varibale bulk collect into collect1,collect2,...[limit rows]

close cursor_varibale;

declare
    type emp_cursor_type is ref cursor;
    emp_cursor emp_cursor_type;
    emp_record emp%rowtype;
  begin
    open emp_cursor for select * from emp where deptno=10;
    loop
     fetch emp_cursor into emp_record;
     exit when emp_cursor%notfound;
     dbms_output.put_line('第'||emp_curosr%rowcount||'个雇员: '||emp_record.ename);
    end loop;
    close emp_cursor;
  end;

declare
    type emp_record_type is record(name varchar2(10),salary number(6,2));
    type emp_cursor_type is ref cursor return emp_record_type;
    emp_cursor emp_cursor_type;
    emp_record emp_record_type;
  begin
    open emp_cursor for select ename,sal from emp where deptno=20;
    loop
     fetch emp_cursor into emp_record;
     exit when emp_cursor%notfound;
     dbms_output.put_line('第'||emp_curosr%rowcount||'个雇员: '||emp_record.ename);
    end loop;
    close emp_cursor;
 end;