Как получить колонки из представления с функцией, возвращающей объектный тип?

Question

Имею представление с функцией:

PRIMARY_KEY,
FUNCTION (PARAMETER) AS COLUMN_ONE
--остальной код

Сейчас вывод выглядит так:

|PRIMARY_KEY|COLUMN_ONE|
|-----------|----------|
|1          |John      |

Функция возвращает только один колонку со скалярного типа - COLUMN_ONE.

Теперь, хочу использовать функцию, которая возвращает объектный тип, который имеет несколько атрибутов, чтобы получить несколько колонок, например:

|PRIMARY_KEY|COLUMN_ONE|COLUMN_TWO|COLUMN_THREE|
|-----------|----------|----------|------------|
|1          |John      |  Doctor  |123         |

То есть, нужны отдельные колонки, а не одна колонка со значением объектного типа:

|FUNC_RESULT(COLUMN_ONE,COLUMN_TWO,COLUMN_THREE)|
|-----------------------------------------------|
|OBJTYPE('John','Doctor',123)                   |

Как можно этого добиться?

_{Свободный перевод вопроса Multiple columns in Oracle view by using same function that returns object type от участника @Valkyrie-30}

Answer 1

Один из простых способов добиться этого - поместить результат функции в коллекцию. При выводе атрибуты элементов коллекции будут представлены отдельными колонками. С этим решением, нет необходимости указывать все атрибуты объектного типа.

Попробуйте следующий воспроизводимый пример:

create table t (id, memo) as
    select 1, 'aaa'  from dual union all
    select 2, 'bbbb' from dual
/
create or replace type o force as object (len int, val varchar2(16))
/
create or replace type coll as table of o
/
create or replace function f (val varchar2) return o is
begin
    return o (length (val), substr (val, 1, 16));
end;
/
create or replace view v as
    select * from t, coll (f(t.memo))  
/

Результат:

select * from v;

        ID MEMO                    LEN VAL             
---------- ---------------- ---------- ----------------
         1 aaa                       3 aaa             
         2 bbbb                      4 bbbb

_{Свободный перевод ответа от участника @0xdb}

Answer 2

Рекомендуемое решение -- получить отдельные колонки, указав для них явно атрибуты объектного типа, полученные в результате выполнения функции в подзапросе.

Воспроизводимый пример:

create or replace type o force as object (col1 int, col2 varchar2(16))
/
create or replace function f (val varchar2) return o is
begin
    return o (length (val), substr (val, 2, 16));
end;
/
create or replace view v as
    select id, memo, q.r.col1 col1, q.r.col2 col2 
    from (select t.*, f(t.memo) r from t) q
/

Запрос и его результат:

select * from v;

        ID MEMO       COL1 COL2
---------- ---- ---------- ----------------
         1 aaa           3 aa
         2 bbbb          4 bbb

БЛОГ НА HUSL

Как получить колонки из представления с функцией, возвращающей объектный тип?

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