在Proc中使用Oracle Cursor并返回它吗？(Use Oracle Cursor in Proc and Return it?)

在Proc中使用Oracle Cursor并返回它吗？(Use Oracle Cursor in Proc and Return it?)

我正在开发一个将返回两个游标的包。 一个光标是带有数字主键的项目列表。 另一个光标是与项目关联的文件列表

代码到目前为止：

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;

我遇到的问题包括：

在表（cast（cursor））部分中获取缺少的关键字错误 我可以像我一样访问代码中的光标，还是需要将其复制到内部变量？ 我试图创建一个sys_refcursor类型的变量和一个“set v_cursor：= o_results”但是得到了一个缺失或无效的选项错误。

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

Code so far:

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;

The areas I am running into problems with:

Getting a missing keyword error on the table(cast(cursor)) section Can I access the cursor in the code as I am or do i need to copy it to an internal variable? I tried to create a variable of sys_refcursor type and a "set v_cursor := o_results" but got a missing or invalid option error.

最满意答案

您不能使用O_RESULTS游标打开O_FILES游标。

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

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;

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

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;

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

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

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.