Oracle COLLECT 函数和类型

Question

我在 Oracle 10g 和 COLLECT 函数的使用方面遇到问题。我今天早上才发现它的存在，但有一个问题可以通过将它与 member of 条件结合使用来解决。

最初，我编写了如下所示的代码，该代码返回错误“ORA_00932：不一致的数据类型：预期的 UDT 得到 -”。

with my_tab as (
  select 1 as cola, 1 as colb from dual union all
  select 1 as cola, 2 as colb from dual union all
  select 2 as cola, 3 as colb from dual union all
  select 2 as cola, 4 as colb from dual union all
  select 3 as cola, 3 as colb from dual union all
  select 3 as cola, 4 as colb from dual union all
  select 4 as cola, 1 as colb from dual union all
  select 4 as cola, 2 as colb from dual 
)
select 
  cola, 
  colb_vals
from (
  select 
    cola, 
    collect(colb) as colb_vals
  from my_tab
  group by cola
)
where 2 member of colb_vals

我发现这有点奇怪，因为在 Oracle 10.2.4.0 中，数据库似乎会创建一个临时的系统生成的用户定义类型，并使用它。如果我删除条件（where 2 member of colb_vals），则代码将运行并显示检索到的数据，其中包括临时 UDT（名为 SYSTPblahblahblah==）。

经过更多搜索后，我意识到可以使用 CREATE TYPE 解决此问题，然后使用 CAST 函数更改嵌套表的类型。这有效。

这是使用 CREATE TYPE number_ntt as TABLE OF NUMBER; 并将 collect(colb) 替换为 cast(collect(colb) as number_ntt)

然后我尝试使用在包中创建的嵌套表类型，因为我只需要此类型可用于单个包中的一个过程中的一个特定查询。我无法让它发挥作用。

create or replace package mike_temp_pkg as
  type number_ntt is table of number;
end mike_temp_pkg;

这次将 collect(colb) 替换为 cast(collect(colb) as mike_temp_pkg.number_ntt)

这导致了 ORA-00932: invalid datatype。

所以我的问题实际上分为两部分：

1. 为什么系统生成用户 定义类型适用于 select 但不适用于 member of？

2. 为什么类型需要是 SQL 类型而不是 PL/SQL 类型 包裹？我并没有真正定义类型 经常这样，所以可能有一个 这个问题的简单答案。

Answer 1

(1)

COLLECT 函数文档指出“要获取此函数的结果，您必须在 CAST 函数中使用它”。我怀疑它根本不被设计为支持任何用途，除了其内容的简单转储之外，除非您将其转换为定义的类型。

(2)

SQL 解析器不知道或无法访问 PL/SQL 块中定义的类型。即使当您在 PL/SQL 代码内部执行 SQL 语句时，该语句本质上也会被传递给独立的解析器（PL/SQL 变量名称被绑定变量占位符替换）。