如何将值的数组参数输入到Firebird存储过程? [英] How to input an array parameter of values to Firebird Stored Procedure?

问题描述

我想向 Firebird存储过程输入ID的 array参数.

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

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

我需要执行以下SQL命令:

I'm need to execute this SQL command:

SELECT *
FROM CITY
WHERE ID_CITY IN (:INPUT_LIST_ID)

有可能吗? 谢谢！

推荐答案

您还可以使用类似这样的东西:

You could also use something like this:

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

您将必须创建一个名为"GetIntegerList"的新Firebird过程，该过程类似于以下内容:

You would have to create a new Firebird Procedure called "GetIntegerList" which would look something like this:

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中完成的.

Note, this was done in Firebird 2.5.2.

这篇关于如何将值的数组参数输入到Firebird存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！