如何使用 SELECT 从 Oracle 存储过程返回行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2454021/
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 do I return the rows from an Oracle Stored Procedure using SELECT?
提问by Calanus
I have a stored procedure which returns a ref cursor as follows:
我有一个存储过程，它返回一个引用游标，如下所示：
CREATE OR REPLACE PROCEDURE AIRS.GET_LAB_REPORT (ReportCurTyp OUT sys_refcursor)
AS
v_report_cursor sys_refcursor;
report_record v_lab_report%ROWTYPE;
l_sql VARCHAR2 (2000);
BEGIN
l_sql := 'SELECT * FROM V_LAB_REPORT';
OPEN v_report_cursor FOR l_sql;
LOOP
FETCH v_report_cursor INTO report_record;
EXIT WHEN v_report_cursor%NOTFOUND;
END LOOP;
CLOSE v_report_cursor;
END;
I want to use the output from this stored procedure in another select statement like:
我想在另一个选择语句中使用此存储过程的输出，例如：
SELECT * FROM GET_LAB_REPORT()
选择 * 从 GET_LAB_REPORT()
but I can't seem to get my head around the syntax.
但我似乎无法理解语法。
Any ideas?
有任何想法吗？
回答by pierre
Whenever I've had to do this; I've used the Oracle TYPE and CAST features.
每当我不得不这样做时；我使用过 Oracle TYPE 和 CAST 特性。
Something like:
就像是：
SELECT *
FROM TABLE(CAST(F$get_Cassette_Tracking('8029241') AS cass_tracking_tab_type))
You need to setup the TYPE and all the columns you need and have them use:
您需要设置 TYPE 和您需要的所有列并让它们使用：
pipe ROW(out_obj)
to capture your data. There are many ways to do this and if I can dig out a better example I will but this might give you an idea.
捕获您的数据。有很多方法可以做到这一点，如果我能找出一个更好的例子，我会的，但这可能会给你一个想法。
回答by Vincent Malgrat
See this SO for a working example: Oracle Parameters with IN statement?
有关工作示例，请参阅此 SO：Oracle Parameters with IN 语句？
