oracle游标使用

 

Q15，oracle的游标使用：PL/SQL包含显式游标和隐式游标两种类型，其中隐式游标用于处理select into和DML语句，而显式游标则专门用于处理select语句返回的多行数据。使用显式游标包括定义游标、打开游标、提取游标和关闭游标四个阶段。其中提取游标在oracle9i之前只有一种方式fetch into，这种方式每次只能提取一行数据；从oracle9i开始，通过使用fetch…bulk collect into 语句可以一次提取多行数据，并且通过limit子句设置可以分批多次提取大量的数据内容。

declare

  cursor cur_tsalary is

    select employeeid,positionid from tsalary where rownum < 10 ;

  type rec_tsalary is record (

 employeeid tsalary.employeeid% type ,

 positionid tsalary.positionid% type );

  type all_rec_tsalary_type is table of rec_tsalary;

 all_rec_tsalary all_rec_tsalary_type;

begin

 -- 一次处理所有

    fetch cur_tsalary bulk collect into all_rec_tsalary limit 5 ;

    for i in 1 ..all_rec_tsalary. count loop       dbms_output.put_line(all_rec_tsalary(i).employeeid||all_rec_tsalary(i).positionid);

    end loop ;

-- 使用 limit 分批提出大量数据

  open cur_tsalary;

  loop

    fetch cur_tsalary bulk collect into all_rec_tsalary limit 5 ;

    for i in 1 ..all_rec_tsalary. count loop       dbms_output.put_line(all_rec_tsalary(i).employeeid||all_rec_tsalary(i).positionid);

    end loop ;

    exit when cur_tsalary% notfound ;

  end loop ;

  close cur_tsalary;

end ;

在游标执行过程中，有时需要传入动态的参数来在执行时根据传入的参数内容打开不同的数据内容。为此，oracle定义了参数游标，在定义了参数游标之后，当使用不同参数多次打开游标时，可以生成不同的结果集，注意定义游标参数时，只能指定数据类型，不能指定长度。

declare

  cursor cur_tsalary(employeeid1 number , employeeid2 number ) is

    select employeeid,positionid from tsalary

    where employeeid between employeeid1 and employeeid2 ;

  type rec_tsalary is record (

 employeeid tsalary.employeeid% type ,

 positionid tsalary.positionid% type );

  type all_rec_tsalary_type is table of rec_tsalary;

 all_rec_tsalary all_rec_tsalary_type;

begin

  open cur_tsalary( 11 , 20 );

  loop

    fetch cur_tsalary bulk collect into all_rec_tsalary limit 5 ;

    for i in 1 ..all_rec_tsalary. count loop       dbms_output.put_line(all_rec_tsalary(i).employeeid||all_rec_tsalary(i).positionid);

    end loop ;

    exit when cur_tsalary% notfound ;

  end loop ;

  close cur_tsalary; 

end ; 

显式游标可以对原表数据执行更新或者删除操作，这可以通过在定义游标时使用for update子句，语法为cursor cursor_name(parameter_name datatype) is select_statement for update[of column_reference] [nowait];for update子句用于在游标结果集数据上加行共享锁，以防止其他用户在相应行上执行dml操作；当select语句引用到多张表时，使用of子句可以确定哪些表要加锁，如果没有of子句，则会在select语句所引用的全部表上加锁；nowait子句用于指定不等待锁。在提取了游标数据之后，为了更新或删除当前游标行数据，必须在update或delete语句引用where current of cursor_name子句。

declare

  type refcursor is ref cursor ;

  cursor tsalary_cursor(employeeid1 number , employeeid2 number ) is

    select employeeid, cursor ( select departid,positionid,salary from tsalary

                             where employeeid = a.employeeid + 1 )

    from tsalary a where employeeid between employeeid1 and employeeid2;

 tsalarycursor refcursor;

 aid tsalary.employeeid% type ;

 adepartid tsalary.departid% type ;

 apositionid tsalary.positionid% type ;

 asalary tsalary.salary% type ;

begin

  open tsalary_cursor( 1 , 10 );

  loop

    fetch tsalary_cursor into aid,tsalarycursor;

    exit when tsalary_cursor% notfound ;

    dbms_output.put_line( 'id ： ' ||aid);

    loop

      fetch tsalarycursor into adepartid,apositionid,asalary;

      exit when tsalarycursor% notfound ;

      dbms_output.put_line(adepartid|| ',' ||apositionid|| ',' ||asalary);

    end loop ;

  end loop ;

end ;