在Proc中使用Oracle Cursor并返回它吗? [英] Use Oracle Cursor in Proc and Return it?

问题描述

我正在处理一个将返回两个游标的程序包.一个光标是带有数字主键的项目列表.另一个光标是与项目关联的文件列表

I am working on a package that will return two cursors. One cursor is a list of items with a numeric primary key. The other cursor is a list of files associated with the items

到目前为止的代码:

procedure get_items_with_files(
           o_results out sys_refcursor,
           o_files out sys_refcursor
) is 
begin

   begin
      open o_results for
          select item_id,
                 item_name
          from items;
   end;

   begin
      open o_files for
           select item_id
                  item_file_name
           from item_files if
           where if.item_id in (select item_id from TABLE(CAST(o_results)));
   end;
end get_items_with_files;

我遇到问题的领域:

1. 在表(cast(cursor))部分获取缺少的关键字错误
2. 我可以按原样访问代码中的游标吗?还是需要将其复制到内部变量中?我试图创建一个sys_refcursor类型的变量和一个"set v_cursor:= o_results"，但是出现了缺失或无效的选项错误.

推荐答案

您不能使用O_RESULTS光标打开O_FILES光标.

You can't use the O_RESULTS cursor to open the O_FILES cursor.

您可以查询ITEMS表以同时打开两个游标，但这会导致在打开O_RESULTS游标与打开O_FILES游标之间的时间以及这两个结果之间某些数据发生变化的可能性.设置不同步.

You can query the ITEMS table in order to open both cursors but that introduces the possibility that some data changes between when you open the O_RESULTS cursor and the time you open the O_FILES cursor and that the two result sets are out of sync.

procedure get_items_with_files(
           o_results out sys_refcursor,
           o_files out sys_refcursor
) is 
begin

   begin
      open o_results for
          select item_id,
                 item_name
          from items;
   end;

   begin
      open o_files for
           select item_id
                  item_file_name
           from item_files if
           where if.item_id in (select item_id from items);
   end;
end get_items_with_files;

返回一个表示两个表联接结果的游标会更常见

It would be much more common to return a single cursor that represents the result of joining the two tables

procedure get_items_with_files(
           o_results out sys_refcursor
) is 
begin
  open o_results for
      select item_id,
             item_name,
             item_file_name
        from items
             join item_files using (item_id);
end get_items_with_files;

但是，如果您的所有过程都在打开游标，则创建视图而不是创建过程然后查询视图而不是调用过程会更为常见.

If all your procedure is doing is opening a cursor, however, it would be more common to create a view rather than creating a procedure and then query the view rather than calling the procedure.

这篇关于在Proc中使用Oracle Cursor并返回它吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！