Как вставить запрос в пользовательский табличный тип без использования цикла и extend?

Question

Версия Oracle 12.1

Добрый день! Мне нужно доработать функцию, ускорить получение коллекции с данными. Сейчас используется запись циклом. Хочу впихнуть сразу весь запрос. Текущий рабочий вариант выглядит как-то так (оригинал показать не могу, это в упрощенном виде).

function GET_TABLE()
  return test_type_table is

  N   test_type_table := test_type_table();
  CNT number := 1;

begin
       for REC in (select t.q,
                          t.w,
                          t.e,
                          t.r
                    from table1 T loop
        begin
          N.EXTEND;        
          N(CNT) := test_type(REC.q,
                              REC.w,
                              TO_NUMBER(REC.e),
                              REC.r);
          CNT := CNT + 1;
        end;
      end loop;
end;

Есть тип.

create or replace type test_type is object
(
  a number,
  b number,
  c number,
  d varchar2(64)
);

Есть таблица такого типа.

create or replace type test_type_table is
    table of test_type;

Тестирую функции Хочу сделать как-то так но не понимаю почему не работает

В этом варианте выдается ошибка

"Error: PL/SQL: ORA-00947: не хватает значений для данных"

function GET_TABLE()
  return test_type_table is

  N   test_type_table := test_type_table();

begin
    select q,
           w,
           e,
           r
      bulk collect
      into N
      from table1 t; 
end;

В интернете пишут что надо делать так.

В таком варианте выдается ошибка

"Error: PL/SQL: ORA-00932: несовместимые типы данных: ожидается UDT, получено NUMBER"

Хотя типы полностью совпадают

function GET_TABLE()
  return test_type_table is

  N   test_type_table := test_type_table();

begin
    select test_type_table(a,b,c,d)
      bulk collect
      into N
      from (select t.q as a,
                   t.w as b,
                   t.e as c,
                   t.r as d
              from table1 t); 
end;

Answer 1

Вместо test_type_table надо вызывать конструктор по-умолчанию для объектного типа test_type:

function GET_TABLE()
  return test_type_table is

  N   test_type_table := test_type_table();

begin
    select test_type(a,b,c,d)
      bulk collect
      into N
      from (select t.q as a,
                   t.w as b,
                   t.e as c,
                   t.r as d
              from table1 t); 
end;

Answer 2

Как сказано в сообщении об ошибке, конструктор коллекции ожидает элементы пользовательского типа, а получает числa:

select test_type_table(a,b,c,d) 
                     --^
select test_typе(a,b,c,d) -- так правильно

Если результат запроса содержит сравнительно небольшой набор данных, то его можно сразу преобразовать в коллекцию (воспроизводимый пример):

create table t1  (a, b, c, d) as
    select 1, 2, 3, 'test row 1' from dual union all
    select 4, 5, 6, 'test row 2' from dual
/
create or replace function gettab return test_type_table is
    ret test_type_table;
begin 
    select cast ( 
        multiset (select * from t1) as test_type_table) into ret
    from dual;
    return (ret);
end;
/

Получится

select gettab () res
from dual;

RES(A, B, C, D)
------------------------------------------------------------------------------------
TEST_TYPE_TABLE(TEST_TYPE(1, 2, 3, 'test row 1'), TEST_TYPE(4, 5, 6, 'test row 2'))

БЛОГ НА HUSL

Как вставить запрос в пользовательский табличный тип без использования цикла и extend?

Ответы (2 шт):