Oracle-从SQL SYS_REFCURSOR中选择pl sql [英] Oracle - pl sql selecting from SYS_REFCURSOR

问题描述

我有一个返回SYS_REFCURSOR的函数，该SYS_REFCURSOR具有单行但多列.我想要做的是能够使用SYS_REFCURSOR中返回的列值进行具有嵌套子查询的SQL查询.诸如类型等替代性想法将不胜感激.下面的代码是我即时编写的，尚未经过语法验证.

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.

--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;

推荐答案

SYS_REFCURSOR不能用于预期的用途-您需要创建Oracle TYPE:

A SYS_REFCURSOR won't work for the intended use - you need to create an Oracle TYPE:

CREATE TYPE your_type IS OBJECT (
  ColumnA VARCHAR2(100),
  ColumnB VARCHAR2(100)
)

更新您的功能:

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;

然后您可以使用:

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

这篇关于Oracle-从SQL SYS_REFCURSOR中选择pl sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！