Oracle - 从 SYS_REFCURSOR 中选择 pl sql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2575653/
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
Oracle - pl sql selecting from SYS_REFCURSOR
提问by Einstein
I have a function that returns a SYS_REFCURSOR that has a single row but multiple columns. What I'm looking to do is to be able to have a SQL query that has nested sub-queries using the column values returned in the SYS_REFCURSOR. Alternative ideas such as types, etc would be appreciated. Code below is me writing on-the-fly and hasn't been validated for syntax.
我有一个函数，它返回一个 SYS_REFCURSOR，它有一个单行多列。我想要做的是能够有一个使用 SYS_REFCURSOR 中返回的列值嵌套子查询的 SQL 查询。诸如类型等的替代想法将不胜感激。下面的代码是我即时编写的，尚未经过语法验证。
--Oracle function
CREATE DummyFunction(dummyValue AS NUMBER) RETURN SYS_REFCURSOR
IS
RETURN_DATA SYS_REFCURSOR;
BEGIN
OPEN RETURN_DATA
SELECT
TO_CHAR(dummyValue) || 'A' AS ColumnA
,TO_CHAR(dummyValue) || 'B' AS ColumnB
FROM
DUAL;
RETURN RETURN_DATA;
END;
--sample query with sub-queries; does not work
SELECT
SELECT ColumnA FROM DummyFunction(1) FROM DUAL AS ColumnA
,SELECT ColumnB FROM DummyFunction(1) FROM DUAL AS ColumnB
FROM
DUAL;
回答by OMG Ponies
A SYS_REFCURSOR won't work for the intended use - you need to create an Oracle TYPE:
SYS_REFCURSOR 不适用于预期用途 - 您需要创建一个 Oracle TYPE：
CREATE TYPE your_type IS OBJECT (
ColumnA VARCHAR2(100),
ColumnB VARCHAR2(100)
)
Update your function:
更新您的功能：
CREATE DummyFunction(dummyValue AS NUMBER)
RETURN your_type
IS
BEGIN
INSERT INTO your_type
SELECT TO_CHAR(dummyValue) || 'A' AS ColumnA,
TO_CHAR(dummyValue) || 'B' AS ColumnB
FROM DUAL;
RETURN your_type;
END;
Then you can use:
然后你可以使用：
SELECT (SELECT ColumnA FROM table(DummyFunction(1))) AS ColumnA,
(SELECT ColumnB FROM table(DummyFunction(1))) AS ColumnB
FROM DUAL
The example is overcomplicated - all you need to use is:
该示例过于复杂 - 您需要使用的只是：
SELECT x.columna,
x.columnb
FROM table(DummyFunction(1)) x
