Oracle PL/SQL - WHERE和FROM子句内部条件和内部视图的批量收集用法

Question

我在使用批量收集作为FROM子句的元素时遇到了一个奇怪的问题。当我执行此代码示例时，我在运行时收到错误"invalid table name“。

如果我将集合替换为一个表，一切都会正常工作。

我是否遗漏了一些关于批量收集的限制？也许我不能在FROM子句中使用匿名块？在sql调试器中，我看到l_vol(i)有值，但是l_vol(i).FIELD不存在。

谢谢。

 TYPE t_bulk_vol is table of vol%ROWTYPE;
 l_vol t_bulk_vol;
 ...
 cursor cur is SELECT * FROM vol where ... ;

 OPEN CUR;
 LOOP 
     FETCH CUR BULK COLLECT INTO l_vol;
     ....
     insert into dest
     select col1, col2, ... from 
     (inner view with some pivot, unpivot and l_vol(i).FIELD ...) src where l_vol(i).FIELD = src.FIELD;

PS:我不能粘贴原始代码。

Answer 1

我已经按照您所说的创建了类型，但是在同一时刻得到了相同的错误(ORA-00903 -无效的表名)。

这是我所做的一个例子：

  CREATE TYPE REC_VOL AS OBJECT (
       FIELD1    VARCHAR2(25),
       ...
  );
  create TYPE T_BULK IS TABLE OF REC_VOL;
  ....
  l_vol t_bulk;
  ...

这是我收集记录的方式(我不再使用光标)：

    SELECT REC_VOL(FIELD1, ...) BULK COLLECT INTO l_vol
    FROM vol where ...;

在insert-select语句中仍会引发异常。

Answer 2

TYPE t_bulk_vol是一种PL/SQL类型。这意味着您只能在PL/SQL构造中使用它。您不能在SQL中使用它，即使它是PL/SQL程序中的SQL。

如果要在SELECT的FROM子句中使用嵌套表，则需要定义SQL类型。这很痛苦，因为这意味着您不能使用%ROWTYPE定义(这是PL/SQL唯一的关键字)。因此，您必须创建一个签名与表的投影匹配的对象，然后创建该类型的嵌套表。Find out more。

您的光标定义错误。它应该只是一条SELECT语句。

cursor cur is SELECT * FROM vol where ... ;

保存用于实际获取的BULK COLLECT INTO l_vol。

尽管这可能只是你伪造了一些PL/SQL，因为你“不能粘贴原始代码”。