如何从嵌套的Oracle过程检索值? [英] How do I retrieve values from a nested Oracle procedure?
问题描述
我遇到了一个棘手的Oracle问题.我正在尝试选择一组数据，我们将其称为项目.对于每个项目，我想调用另一个过程并返回一个库存项目.我不确定如何执行两项操作.
I have kind of a tricky Oracle problem. I am trying to select one set of data, we'll call items. For each item I want to call another procedure and return an Inventory Item. I have two operations I am not sure on how to perform.
-
如何从嵌套过程中检索值?
How do I retrieve a value from the nested procedure?
如何以SYS_REFCURSOR的形式返回这些检索到的值?
How do I return those retrieved values in the form of SYS_REFCURSOR?
我在这里的尝试是将spSelect_Inv_Search的结果放入称为ITEMS_TABLE的嵌套表中.这不起作用.
My attempt here was to put the results from spSelect_Inv_Search into a nested table called ITEMS_TABLE. This is not working.
下面的代码
PROCEDURE SPSELECT_ITEM (IO_CURSOR OUT SYS_REFCURSOR)
AS
MY_CURSOR SYS_REFCURSOR;
TYPE ITEM_TYPE IS TABLE OF ITEMS.ITEM_NO%TYPE;
ITEM_TABLE ITEM_TYPE := ITEM_TYPE();
CURSOR ITEMS_CURSOR IS
SELECT ITEM_NO
FROM ITEMS;
V_COUNTER INTEGER := 0;
BEGIN
FOR ITEM_REC IN ITEM_CURSOR LOOP
V_COUNTER := V_COUNTER + 1;
ITEM_TABLE.EXTEND;
ITEM_TABLE(V_COUNTER) := spSelect_Inv_Search(ITEM_REC.ITEM_NO, MY_CURSOR);
END LOOP;
END SPSELECT_ITEMS;
感谢您的帮助.
推荐答案
您似乎想将未知数量的SYS_REFCURSOR结果集合并为一个大集合.如果您知道从spSelect_Inv_Search返回的游标的结构，则可以使用中间的流水线函数来实现.
You seem to be wanting to merge an unknown number of SYS_REFCURSOR result sets into one big one. If you know the structure of the cursor returned from spSelect_Inv_Search you can do this with an intermediate pipelined function.
create package p as
type tmp_rec_type is record (owner all_objects.owner%type,
object_type all_objects.object_type%type,
objects number);
type tmp_rec_table is table of tmp_rec_type;
procedure proc1(p_owner in varchar2, p_cursor out sys_refcursor);
function func2 return tmp_rec_table pipelined;
procedure proc3(p_cursor out sys_refcursor);
end;
/
可以在此处定义类型，它们不必处于SQL级别，因为您无需在包外引用它们.
The types can be defined here, they don't have to be at SQL level as you won't ever need to reference them outside the package.
create package body p as
procedure proc1(p_owner in varchar2, p_cursor out sys_refcursor) as
begin
open p_cursor for select owner, object_type, count(*)
from all_objects
where owner = p_owner
group by owner, object_type;
end;
function func2 return tmp_rec_table pipelined as
cursor c1 is select distinct owner
from all_tables where owner in ('SYS','SYSTEM');
tmp_cursor sys_refcursor;
tmp_rec tmp_rec_type;
begin
for r1 in c1 loop
proc1(r1.owner, tmp_cursor);
loop
fetch tmp_cursor into tmp_rec;
exit when tmp_cursor%notfound;
pipe row(tmp_rec);
end loop;
end loop;
end;
procedure proc3(p_cursor out sys_refcursor) as
begin
open p_cursor for select * from table(func2);
end;
end p;
/
然后执行，尽管中间阶段使用的类型也可以在包外部执行，您可以在SQL * Plus或SQL Developer中进行测试:
Then to execute, which you can do outside the package despite the types used for the intermediate stage, you can do this to test in SQL*Plus or SQL Developer:
var rc refcursor;
exec p.proc3(:rc);
print rc;
对于我的数据库，它给出:
For my database this gives:
OWNER OBJECT_TYPE OBJECTS
------------------------------ ------------------- ----------------------
SYSTEM VIEW 1
SYSTEM TABLE 5
SYS VIEW 1056
SYS CONSUMER GROUP 2
SYS PROCEDURE 11
SYS FUNCTION 56
SYS SEQUENCE 1
SYS OPERATOR 6
SYS EVALUATION CONTEXT 1
SYS TABLE 13
SYS WINDOW GROUP 1
SYS PACKAGE 162
SYS WINDOW 2
SYS TYPE 529
SYS JOB CLASS 1
SYS SCHEDULE 1
这显然是非常人为的，因为您可以将其作为单个查询来执行，但是我假设您的内部过程需要做一些更复杂的事情.
This is obviously very contrived as you'd do this as a single query, but I'm assuming your inner procedure needs to do something more complicated.
这篇关于如何从嵌套的Oracle过程检索值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
