sys_refcursor的使用（oracle自定义游标）

最新推荐文章于 2023-08-06 19:45:48 发布

原创最新推荐文章于 2023-08-06 19:45:48 发布 · 6.2k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #table #insert #loops #output

oracle 相关专栏收录该内容

7 篇文章

订阅专栏

本文介绍如何在Oracle中使用SYS_REFCURSOR类型来从存储过程传递游标。通过创建两个存储过程，一个用于打开游标并选择数据，另一个用于接受游标并打印结果，展示了SYS_REFCURSOR的用法。

sys_refcursor can be used to pass cursors from and to a stored precedure.

The following procedure proc_ref_cursor accepts a sys_refcursor and loops on that cursor and prints out ( dbms_output) what it fetches:

create or replace procedure 
  proc_ref_cursor (rc in sys_refcursor) as

  v_a number;
  v_b varchar2(10);
  
begin
  loop
    fetch rc into v_a, v_b;
    exit when rc%notfound;
    dbms_output.put_line(v_a || ' ' || v_b);
  end loop;
end;
/

Here's a table that we will use to select from:

create table table_ref_cursor (
  a number,
  b varchar2(10)
);

insert into table_ref_cursor values(1, 'one');
insert into table_ref_cursor values(2, 'two');
insert into table_ref_cursor values(3, 'three');
insert into table_ref_cursor values(4, 'four');

commit;

Here's another stored procedure ( call_proc_ref_cursor) that opens a cursor ( select * from table_ref_cursor) and passes that cursor to proc_ref_cursor.

create or replace procedure 
  call_proc_ref_cursor as

  v_rc sys_refcursor;

begin

  open v_rc for 
    select * from table_ref_cursor;

  proc_ref_cursor(v_rc);

  close v_rc;
end;
/
转自：http://www.adp-gmbh.ch/ora/sql/sys_refcursor.html