SQL 如何从一个存储过程中的 SYS_REFCURSOR 获取数据并在另一个过程中使用它？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10034266/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I fetch the data from the SYS_REFCURSOR from one stored proc and use it in another?
提问by dee
I have a stored proc with the basic layout below that returns a sys_refcursor as a result set. (Technically it reurns four but for the sake of clarity I just say one). The result set is a selection from a temp table.
我有一个带有基本布局的存储过程，它返回一个 sys_refcursor 作为结果集。（从技术上讲，它会返回四个，但为了清楚起见，我只说一个）。结果集是从临时表中选择的。
procedure aProcedure
( C1 in out sys_refcursor
) is
begin
--populate Temp_Table here with a stored proc call;
OPEN C1 FOR
SELECT Cols
FROM TEMP_TABLE;
I need to insert this result set from C1 into a permanent table using a different stored procedure. Is this Do-able or do I need to re-build the result set all over again?
我需要使用不同的存储过程将这个结果集从 C1 插入到永久表中。这是可行的还是我需要重新构建结果集？
I've been able to find information on working with cursors and result sets outside of oracle but not for working with them within itself.
我已经能够找到有关在 oracle 之外使用游标和结果集的信息，但不能在其内部找到有关使用它们的信息。
I know it might make sense to just do the insert from the first stored proc but that's not really how I need it to happen. It's an optional requirement to save the result set permanently.
我知道从第一个存储的过程中插入可能是有意义的，但这并不是我真正需要它发生的方式。永久保存结果集是一个可选要求。
Thanks for any helpful info.
感谢您提供任何有用的信息。
回答by Justin Cave
Assuming that the caller knows the structure of the cursor that aProcedureis opening, you can do something like this.
假设调用者知道aProcedure正在打开的游标的结构，你可以做这样的事情。
declare
l_rc sys_refcursor;
l_rec temp_table%rowtype;
begin
aProcedure( l_rc );
loop
fetch l_rc
into l_rec;
exit when l_rc%notfound;
dbms_output.put_line( l_rec.col1 );
end loop;
close l_rc;
end;
/
If you can't fetch into a record type, you can also fetch into a number of other scalar local variables (the number and type have to match the number and type of columns that aProcedurespecifies in its SELECTlist). In my case, I defined aProcedureto return two numeric columns
如果您无法获取记录类型，您还可以获取许多其他标量局部变量（数字和类型必须aProcedure与其SELECT列表中指定的列数和类型相匹配）。就我而言，我定义aProcedure返回两个数字列
declare
l_rc sys_refcursor;
l_col1 number;
l_col2 number;
begin
aProcedure( l_rc );
loop
fetch l_rc
into l_col1, l_col2;
exit when l_rc%notfound;
dbms_output.put_line( l_col1 );
end loop;
close l_rc;
end;
