代码之家 › 专栏 › 技术社区 › ipavlu

如何使用一个sql参数表示输入数组

parameters oracle sql-server .net sql

0

ipavlu · 技术社区 · 6 年前

有没有为Oracle编写sql的方法,MS sql:

Select * from table where id in(:arr)


Select * from table where id in(@arr)

在sql“arr”中使用一个参数来表示一个项目数组? 我找到了一些例子,从arr到@arr0@arrn和feed数组作为n+1单独的参数,而不是数组,如下所示

Select * from table where id in(:arr0, :arr1, :arr2)


Select * from table where id in(@arr0, @arr1, @arr2)

不是我想要的。这将导致sql查询发生变化,并根据参数数量创建新的执行计划。

我要求。net、c#和Oracle以及MS SQL。

谢谢你的建设性意见!

/知识产权/

1 回复 | 直到 6 年前

1

0

John Cappelletti 6 年前

除了 表值参数 正如Steve提到的,还有一些其他技术可用。例如,您可以分析分隔字符串

实例

Declare @arr varchar(50) = '10,20,35'

Select A.*
 From  YourTable A
 Join string_split(@arr,',') B on A.ID=value

甚至

Select A.*
 From  YourTable A
 Where ID in ( select value from string_split(@arr,',') )

2

0

iVad 6 年前

我相信表值参数是这种情况下的好选择。请看下面SQL Server中的示例代码。

-- Your table
CREATE TABLE SampleTable
(
    ID          INT
)

INSERT INTO SampleTable VALUES
(1010),
(2010),       
(3010),
(4010),
(5010),      
(6010),      
(7010),      
(8030)
GO

-- Create a TABLE type in SQL database which you can fill from front-end code
CREATE TYPE ParameterTableType AS TABLE  
(
    ParameterID INT
    --, some other columns
)
GO

-- Create a stored proc using table type defined above
CREATE PROCEDURE ParameterArrayProcedure
(
    @ParameterTable AS ParameterTableType READONLY
)
AS
BEGIN

    SELECT
        S.*
    FROM SampleTable S
    INNER JOIN @ParameterTable P ON S.ID = P.ParameterID

END
GO

-- Populated table type variable
DECLARE @ParameterTable AS ParameterTableType
INSERT INTO @ParameterTable (ParameterID) VALUES (1010), (4010), (7010)

EXECUTE ParameterArrayProcedure @ParameterTable

DROP PROCEDURE ParameterArrayProcedure
DROP TYPE ParameterTableType
DROP TABLE SampleTable
GO

除了表值参数外,还可以使用 Json 或 XML 值作为SQL参数,但是的,它肯定会相应地更改您的执行计划。

3

0

Community Mohan Dere 6 年前

神谕

在其他语言(如Java)中,您可以 pass an SQL collection as a bind parameter 并直接在SQL语句中使用它。

然而 C# does not support passing SQL collections 只支持传球 OracleCollectionType.PLSQLAssociativeArray ( documentation link )它是一种仅限PL/SQL的数据类型,不能(直接)在SQL语句中使用。

要传递数组,需要传递 PLSQLAssociativeArray 将其转换为可在SQL语句中使用的SQL集合。将PL/SQL关联数组转换为SQL集合的过程示例如下:

CREATE TYPE IntList AS TABLE OF INTEGER
/

CREATE PACKAGE tools IS
  TYPE IntMap IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;

  FUNCTION IntMapToList(
    i_map IntMap
  ) RETURN IntList;
END;
/

CREATE PACKAGE BODY tools IS
  FUNCTION IntMapToList(
    i_map IntMap
  ) RETURN IntList
  IS
    o_list IntList := IntList();
    i      BINARY_INTEGER;
  BEGIN
    IF i_map IS NOT NULL THEN
      i := o_list.FIRST;
      WHILE i IS NOT NULL LOOP
        o_list.EXTEND;
        o_list( o_list.COUNT ) := i_map( i );
        i := i_map.NEXT( i );
      END LOOP;
    END IF;
    RETURN o_list;
  END;
END;
/