如何从批量收集的记录表类型中选择 [英] How do I select from Bulk Collected Table of Records Type

问题描述

出于性能原因，我有一个程序需要缓存一些数据，以便进行下游操作.

I've got a procedure where I need to cache some data, for performance reasons, for downstream operations.

TYPE定义起作用

BULK COLLECT INTO有效

SELECT不起作用

PROCEDURE MYPROC((PARAMS))AS

  TYPE REC_TYPE IS RECORD (
    COLUMN_1 (TABLEA.COLUMN_A)%TYPE,
    COLUMN_2 (TABLEA.COLUMN_B)%TYPE
  );

  TYPE TAB_TYPE IS TABLE OF REC_TYPE;

  TABLE_1 TAB_TYPE;

BEGIN

  SELECT  COLUMN_A, COLUMN_B
  BULK COLLECT INTO TABLE_1 
  FROM  TABLE_A;

  SELECT * FROM TABLE_1;

END MYPROC;

收益率:

错误(#，#):PL/SQL:ORA-00942:表或视图不存在

Error(#,#): PL/SQL: ORA-00942: table or view does not exist

我也尝试过将其包装在表函数中，就像在其他地方处理单列类型一样，但这也不起作用

I've also tried wrapping it in a table function like I do with my single-column types elsewhere, but that did not work either

SELECT * FROM TABLE(TABLE_1);

错误(#，#):PL/SQL:ORA-22905:无法访问非嵌套行 表格项

Error(#,#): PL/SQL: ORA-22905: cannot access rows from a non-nested table item

推荐答案

您的问题实际上是PLS-00642错误，而不是ORA-22905.本质上，您不能在SQL语句中使用本地集合类型.因此，解决方案是在架构级别定义类型.以这种方式定义类型时，我们不能使用％TYPE语法，而必须显式定义列(

Your problem is actually a PLS-00642 error, rather than ORA-22905. Essentially you can't use local collection types in SQL statements. The solution therefore, is to define your types at the schema level. When defining types in this way, we cannot use the %TYPE syntax, and instead must explicitly define the column (Getting PLS-00201 error while creating a type in oracle) i.e.

create or replace type rec_type as object (
  COLUMN_1 integer,
  COLUMN_2 varchar2(128)
);

create or replace type tab_type as table of rec_type;

然后，您需要将值显式转换为相关类型，以执行此处所述的批量收集:

You then need to explicitly convert the values into the relevant type in order to perform the bulk collect as mentioned here: ORA-00947 Not enough values while declaring type globally.

因此，您的过程将如下所示:

Your procedure would therefore look something like this:

PROCEDURE MYPROC((PARAMS))AS
  TABLE_1 TAB_TYPE;
  lCount  integer;
BEGIN

  SELECT  REC_TYPE(COLUMN_A, COLUMN_B)
  BULK COLLECT INTO TABLE_1 
  FROM  TABLE_A;

  SELECT COUNT(*) INTO lCount FROM TABLE(TABLE_1);
END MYPROC;

这篇关于如何从批量收集的记录表类型中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！