如何在两个 PL/SQL 函数的结果之间创建联接

Question

我有两个函数都返回一个TYPE，即：

CREATE OR REPLACE FUNCTION myUser.f_myFunction
(
    myId IN RAW := NULL
) RETURN myUser.myType
AS
    ResultTable myUser.myType;
BEGIN
    ...
    -- fill ResultTable
    ...
    RETURN ResultTable;
END;

现在我想加入他们的SELECT声明：

SELECT *
FROM myUser.f_myFunction1() f1
JOIN myUser.f_myFunction2() f2 ON f1.xy = f2.yz;

但是如果我在 SELECT 语句中包含函数，则会出现错误：

SELECT * FROM myUser.f_myFunction();
                                 *
ERROR in Line 1:
ORA-00933: SQL command not properly ended
    ORA-04044: procedure, function, package, or type is not allowed here

.

编辑：将函数调用嵌套在TABLE()子句中会给出以下错误：

SELECT * FROM TABLE(myUser.f_myFunction())
              *
ERROR at line 1:
ORA-22905: cannot access rows from a non-nested table item

然后我尝试投掷它，但是：

SELECT * FROM TABLE(CAST(myUser.f_myFunction() AS myUser.myType))
                             *
ERROR at line 1:
ORA-22907: invalid CAST to a type that is not a nested table or VARRAY

和：

SELECT * FROM TABLE(CAST(myUser.f_myFunction() AS myUser.myTypeTable))
                             *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got myUser.myType

.

编辑2：以下是类型的定义（对不起，应该早点包含它们）：

CREATE TYPE myUser.myType AS OBJECT (
    ....
);
CREATE TYPE myUser.myTypeTable IS TABLE OF myUser.myType;

.

编辑3：让它像这样工作：

CREATE OR REPLACE FUNCTION myUser.f_myFunction
(
    myId IN RAW := NULL
) RETURN myUser.myTypeTable
AS
    ResultTable myUser.myTypeTable;
BEGIN
    SELECT myUser.myType(x.Column1, x.Column2, ...)
        BULK COLLECT INTO ResultTable
        FROM myUser.myTable x
        WHERE ...
    RETURN ResultTable;
END;

Answer 1

至少有三种方法可以做到这一点：1） CC重新配置嵌套表 2） 多次调用函数 3） 创建一个函数来访问每个属性。

示例对象

create or replace type myType as object
(
    wx number,
    xy number
);

CREATE OR REPLACE FUNCTION f_myFunction
(
    myId IN RAW := NULL
) RETURN myType
AS
    ResultTable myType := myType(1, 2);
BEGIN
    RETURN ResultTable;
END;
/

方法 1：创建嵌套表

正如 Luke Woodward 所指出的，TABLE 和 CAST 功能需要一个嵌套的类型表。 即使您只打算使用单个元素。

create or replace type myType_nt is table of myType;
CREATE OR REPLACE FUNCTION f_myFunction_nt
(
    myId IN RAW := NULL
) RETURN myType_nt
AS
    ResultTable myType_nt := myType_nt(myType(1, 2));
BEGIN
    RETURN ResultTable;
END;
/
select wx, xy from table(f_myFunction_nt());
WX  XY
--  --
1   2

方法 2：对每个属性重复函数调用

如果为每个属性调用一次函数，则不需要额外的对象。 这是重复的，并且可能很昂贵。 将函数设置为确定性可能会阻止函数实际被多次调用。

select wx, xy from
(
    select f_myFunction().wx wx, f_myFunction().xy xy from dual
);
WX  XY
--  --
1   2

似乎应该有一种更简单的方法可以做到这一点。 Oracle 支持调用该函数一次并返回具有这两个属性的类型。 但是没有办法有意义地使用该类型。 这些属性不容易从 SQL 访问。

SQL> select * from (select f_myFunction() from dual);
F_MYFUNCTION()(WX, XY)
-----------------------------------------------------
MYTYPE(1, 2)

简单地使用每个属性名称的直观方法行不通。

SQL> select wx, xy from (select f_myFunction() from dual);
select wx, xy from (select f_myFunction() from dual)
           *
ERROR at line 1:
ORA-00904: "XY": invalid identifier

方法 3：为每个属性创建一个函数

您可以使用函数访问属性。 这需要添加新对象，但它使您的主要功能保持不变。

create or replace function get_wx(p_myType myType) return number is
begin
    return p_myType.wx;
end;
/
create or replace function get_xy(p_myType myType) return number is
begin
    return p_myType.xy;
end;
/
select get_wx(asdf) wx, get_xy(asdf) xy
from (select f_myFunction() asdf from dual);
WX  XY
--  --
1   2