Как заменить вложенную таблицу функцией Oracle SQL или хранимой процедурой

В Oracle, если у меня есть следующие две таблицы

CREATE TABLE Test_Persons_A (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255)
);

INSERT INTO Test_Persons_A
    (PersonID,LastName,FirstName)
    values(11,'LN_A1','FN_A1');

INSERT INTO Test_Persons_A
    (PersonID,LastName,FirstName)
    values(12,'LN_A2','FN_A2');

CREATE TABLE Test_Persons_B (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255)
);

INSERT INTO Test_Persons_B
    (PersonID,LastName,FirstName)
    values(21,'LN_B1','FN_B1');

INSERT INTO Test_Persons_B
    (PersonID,LastName,FirstName)
    values(22,'LN_B2','FN_B2');

commit;

и я могу сделать запрос союза, как показано ниже:

with
UNIONED as
(    
    --block A to replaced by calling a function or stored proc---
    select PersonID as PID, LastName as LN, FirstName as FN
        from Test_Persons_A tp
        where tp.LASTNAME like '%1%'

    union

    --block B to replaced by calling a function or stored proc---
    select PersonID as PID, LastName as LN, FirstName as FN
        from Test_Persons_B tp
        where tp.LASTNAME like '%2%'
)

select * from UNIONED;

Мой вопрос в том, как я могу добиться чего-то вроде следующего:

with
UNIONED as
(    
    --block A replaced---
    myFuncOrStoredProc('Test_Persons_A', '%1%');

    union

    --block B replaced---
    myFuncOrStoredProc('Test_Persons_B', '%2%');
)

select * from UNIONED;

Было много сообщений о том, «как вернуть таблицу (или несколько строк данных) из функции или хранимой процедуры». Но из-за многочисленных ответов я чувствую себя еще более растерянным.

Для такого небольшого и простого (и я полагаю, довольно распространенного) сценария и цели, какой самый простой способ сделать это? (в частности, я предпочитаю избегать чтобы указать тип каждого столбца для возвращаемой таблицы.)


sql oracle stored-procedures stored-functions
person Stochastika    schedule 12.07.2018    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Настройка Oracle:

CREATE TABLE Test_Persons_A ( PersonID, LastName, FirstName ) AS
  SELECT 11, 'LN_A1', 'FN_A1' FROM DUAL UNION ALL
  SELECT 12, 'LN_A2', 'FN_A2' FROM DUAL;

CREATE TABLE Test_Persons_B ( PersonID, LastName, FirstName ) AS
 SELECT 21, 'LN_B1', 'FN_B1' FROM DUAL UNION ALL
 SELECT 22, 'LN_B2', 'FN_B2' FROM DUAL;

CREATE TYPE test_obj IS OBJECT(
  PersonID INT,
  FirstName VARCHAR2(255),
  LastName VARCHAR2(255)
);
/

CREATE TYPE test_tbl IS TABLE OF test_obj;
/

CREATE OR REPLACE FUNCTION test_fn(
  table_name VARCHAR2,
  lastNameFilter VARCHAR2
) RETURN test_tbl
IS
  v_tbl test_tbl;
BEGIN
  IF UPPER( table_name ) = 'TEST_PERSONS_A' THEN
    SELECT TEST_OBJ( PersonID, LastName, FirstName )
    BULK COLLECT INTO v_tbl
    FROM   test_persons_a
    WHERE  LastName LIKE lastNameFilter;
  ELSIF UPPER( table_name ) = 'TEST_PERSONS_B' THEN
    SELECT TEST_OBJ( PersonID, LastName, FirstName )
    BULK COLLECT INTO v_tbl
    FROM   test_persons_b
    WHERE  LastName LIKE lastNameFilter;
  END IF;
  RETURN v_tbl;
END;
/

Запрос 1 Используйте MULTSET UNION [ALL|DISTINCT] в выражении одной коллекции таблиц:

SELECT *
FROM   TABLE(
         test_fn( 'TEST_PERSONS_A', '%1%' )
         MULTISET UNION ALL
         test_fn( 'TEST_PERSONS_B', '%2%' )
       );

Запрос 2 Используйте UNION [ALL] и несколько выражений коллекции таблиц:

SELECT * FROM TABLE( test_fn( 'TEST_PERSONS_A', '%1%' ) )
UNION ALL
SELECT * FROM TABLE( test_fn( 'TEST_PERSONS_B', '%2%' ) );

Вывод для обоих запросов:

  PERSONID FIRSTNAME LASTNAME
---------- --------- --------
        11 LN_A1     FN_A1
        22 LN_B2     FN_B2
person MT0    schedule 12.07.2018
comment
большое спасибо МТ0. так что я никак не могу обойти необходимость определения таблицы для каждого из столбцов, даже если я просто хочу вернуть таблицу той же структуры определенной таблицы? (например, если я хочу вернуть select * из Test_Persons) - person Stochastika; 13.07.2018
comment
@Stochastika Вы можете вернуть курсор с той же структурой, что и таблица, без определения объекта для хранения данных, но вы не можете объединить два курсора. Вы можете объединить две коллекции, но если ваша коллекция содержит объекты с несколькими столбцами, вам необходимо определить объект с этими столбцами. Может быть, я не понимаю, что вы пытаетесь сделать, но вы не можете использовать союз и не знать столбцы. - person MT0; 13.07.2018
comment
@MTO, я только что заметил, основная часть логики выбора внутри функции в вашем ответе: «ВЫБЕРИТЕ TEST_OBJ (PersonID, LastName, FirstName) BULK COLLECT INTO v_tbl FROM test_persons_b WHERE LastName LIKE lastNameFilter;», проблема в том, что я все равно надо копировать и вставлять? если да, то единственный способ избежать использования динамического SQL, если я хочу, чтобы имя таблицы было «переменной»? - person Stochastika; 13.07.2018
comment
@Stochastika Вы можете использовать динамический SQL, если хотите - я решил внести в белый список допустимые имена таблиц и использовать статический SQL, поскольку он менее уязвим, чем динамический SQL. - person MT0; 13.07.2018
comment
Это одна вещь, которая меня озадачила на некоторое время: нет ли другого способа использовать переменную для таблицы в блоке кода, подобном вашему выше? особенно если в этом случае мы знаем, что таблицы TEST_PERSONS_A и TEST_PERSONS_B имеют одинаковую структуру? - person Stochastika; 13.07.2018
comment
@Stochastika Да, используйте EXECUTE IMMEDIATE и динамический SQL. - person MT0; 13.07.2018