sql - 如何将值的数组参数输入到 Firebird 存储过程？

Question

我想将 ID 的数组参数输入到 Firebird 存储过程。

:INPUT_LIST_ID = [1, 2, 12, 45, 75, 45]

我需要执行这个 SQL 命令:

SELECT *
FROM CITY
WHERE ID_CITY IN (:INPUT_LIST_ID)

这可能吗？谢谢!

Answer 1

你也可以使用这样的东西:

SELECT *
FROM CITY
WHERE ID_CITY IN (SELECT ID FROM GetIntegerList('1, 2, 12, 45, 75, 45'))

您必须创建一个名为“GetIntegerList”的新 Firebird 过程，如下所示:

CREATE OR ALTER PROCEDURE "GETINTEGERLIST"("AINTEGERLIST" VARCHAR(32000))
returns (
  ID integer
)
as
  declare variable IntegerList varchar(32000);
  declare variable CommaPos integer;
  declare variable IntegerVal varchar(10);
begin
  IntegerList = AIntegerList || ' ';
  CommaPos = Position(',', IntegerList);

  while (CommaPos > 0) do
  begin
    IntegerVal = Trim(SubString(IntegerList from 1 for CommaPos - 1));

    if (Char_Length(IntegerVal) > 0) then
    begin
      if (IntegerVal similar to '[0-9]*') then
      begin
        ID = Cast(IntegerVal as integer);
        suspend;
      end
    end

    if (Char_Length(IntegerList) > CommaPos) then
      IntegerList = SubString(IntegerList from CommaPos + 1);
    else
      IntegerList = '';

    CommaPos = Position(',', IntegerList);
  end

  IntegerList = Trim(IntegerList);

  if (Char_Length(IntegerList) > 0) then
  begin
    if (IntegerList similar to '[0-9]*') then
    begin
      ID = Cast(IntegerList as integer);
      suspend;
    end
  end
end

请注意，这是在 Firebird 2.5.2 中完成的。