使用存储函数或存储过程返回 firebird 3.0 中的表 [英] Returning a table in firebird 3.0 with stored function or stored procedure

问题描述

我正在尝试编写一个存储过程/函数，它会返回一个包含一行或多行数据的表.

I'm trying to write a stored procedure/function that returns me a table with one or multiple rows of data.

返回的数据取决于以下sql语句中显示的变量:

The returned data depends on a variable shown in the following sql statement:

  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)

到目前为止，我已经看到一些事情是这样完成的:

So far I have seen that something is done like this:

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 值调用函数/过程.我知道返回类型有问题，但我不知道是什么.

Afterwards you call the function/procedure with a X value. I know that there is something wrong with the returns type but I don't know what.

有人可以帮忙吗?

推荐答案

您正在寻找的是 可选择的存储过程.Firebird 要求您显式声明存储过程返回的列，因此诸如 returns table 之类的东西不是一个选项.例如:

What you are looking for is a selectable stored procedure. Firebird requires you to explicitly declare the columns the stored procedure returns, so something like returns table is not an option. For example:

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 * 不是一个好主意.

You will need to explicitly map the columns, so a simple select * is not a good idea.

注意for select，它选择零个或多个行并遍历游标，以及 suspend，输出一个要从存储过程中获取的行(在本例中为游标的每一行).

Note the use of for select, which selects zero or more rows and iterates over the cursor, and suspend, which outputs a row to be fetched from the stored procedure (in this case for each row of the cursor).

您可以从这个过程中产生值，例如:

You can produce values from this procedure like:

select column1, column2
from sp_test_procedure(10)

这篇关于使用存储函数或存储过程返回 firebird 3.0 中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！