Возврат таблицы в firebird 3.0 с сохраненной функцией или хранимой процедурой

#sql #stored-procedures #return-type #stored-functions #firebird-3.0

#sql #хранимые процедуры #возвращаемый тип #хранимые функции #firebird-3.0

Вопрос:

Я пытаюсь написать хранимую процедуру / функцию, которая возвращает мне таблицу с одной или несколькими строками данных.

Возвращаемые данные зависят от переменной, показанной в следующей инструкции sql:

   SELECT * FROM table_name AS SD WHERE EXISTS
  (SELECT DISTINCT S.PARENT_ID FROM table_name AS S 
  WHERE S.COMPONENT_ID = 10011 AND S.CARRIER_GROUP_ID = X AND SD.SD_ID = S.PARENT_ID)

До сих пор я видел, что что-то делается так:

 CREATE FUNCTION f_test_function (X INT)
RETURNS TABLE
AS
RETURN
   (SELECT * FROM table_name AS SD WHERE EXISTS
      (SELECT DISTINCT S.PARENT_ID FROM table_name AS S 
      WHERE S.COMPONENT_ID = 10011 AND S.CARRIER_GROUP_ID = X AND SD.SD_ID = S.PARENT_ID));

После этого вы вызываете функцию / процедуру со значением X. Я знаю, что с типом возвращаемого значения что-то не так, но я не знаю, что именно.

Кто-нибудь может помочь?

1. Насколько я знаю, Firebird теперь поддерживает скалярные функции, а не табличные функции: firebirdsql.org/file/documentation/release_notes/html/en/3_0 /. …

2. @GordonLinoff Firebird имеет выбираемые хранимые процедуры, которые чем-то похожи на табличные функции.

Ответ №1:

То, что вы ищете, — это выбираемая хранимая процедура. Firebird требует, чтобы вы явно объявляли столбцы, которые возвращает хранимая процедура, поэтому что-то вроде returns table этого не вариант. Например:

 create procedure sp_test_procedure (x integer) 
  returns (column1 integer, column2 varchar(50))
as
begin
  for select value1, value2 
      from table_name SD
      where exists (
        SELECT DISTINCT S.PARENT_ID 
        FROM table_name AS S 
        WHERE S.COMPONENT_ID = 10011 
        AND S.CARRIER_GROUP_ID = :X 
        AND SD.SD_ID = S.PARENT_ID)
      into column1, column2
  do
  begin
    suspend;
  end
end

Вам нужно будет явно сопоставить столбцы, поэтому простой select * вариант не является хорошей идеей.

Обратите внимание на использование for select , который выбирает ноль или более строк и выполняет итерацию по курсору, и suspend , который выводит строку, которая будет извлечена из хранимой процедуры (в данном случае для каждой строки курсора).

Вы можете создавать значения из этой процедуры, например:

 select column1, column2
from sp_test_procedure(10)