如何将值的“数组”传递给存储过程?

I want to be able to pass an "array" of values to my stored procedure, instead of calling "Add value" procedure serially.

我希望能够将值的“数组”传递给存储过程，而不是串行地调用“添加值”过程。

Can anyone suggest a way to do it? am I missing something here?

谁能提出一个方法吗?我是不是漏掉了什么?

Edit: I will be using PostgreSQL / MySQL, I haven't decided yet.

编辑:我将使用PostgreSQL / MySQL，我还没有决定。

7 个解决方案

#1

As Chris pointed, in PostgreSQL it's no problem - any base type (like int, text) has it's own array subtype, and you can also create custom types including composite ones. For example:

正如Chris指出的，在PostgreSQL中没有问题——任何基本类型(如int、text)都有自己的数组子类型，还可以创建自定义类型，包括复合类型。例如:

CREATE TYPE test as (
    n int4,
    m int4
);

Now you can easily create array of test:

现在您可以轻松地创建测试数组:

select ARRAY[
    row(1,2)::test,
    row(3,4)::test,
    row(5,6)::test
];

You can write a function that will multiply n*m for each item in array, and return sum of products:

您可以编写一个函数，将数组中的每个项乘以n*m，并返回产品的和:

CREATE OR REPLACE FUNCTION test_test(IN work_array test[]) RETURNS INT4 as $$
DECLARE
    i      INT4;
    result INT4 := 0;
BEGIN
    FOR i IN SELECT generate_subscripts( work_array, 1 ) LOOP
        result := result + work_array[i].n * work_array[i].m;
    END LOOP;
    RETURN result;
END;
$$ language plpgsql;

and run it:

并运行:

# SELECT test_test(
    ARRAY[
        row(1, 2)::test,
        row(3,4)::test,
        row(5,6)::test
    ]
);
 test_test
-----------
        44
(1 row)

#2

If you plan to use MySQL 5.1, it is not possible to pass in an array.
See the MySQL 5.1 faq
If you plan to use PostgreSQL, it is possible look here

如果计划使用MySQL 5.1，则不可能传入数组。如果您打算使用PostgreSQL，请参阅MySQL 5.1 faq

#3

You didn't indicate, but if you are referring to SQL server, here's one way.

您没有指出，但是如果您指的是SQL server，这里有一种方法。

And the MS support ref.

和MS支持ref。

#4

I don't know about passing an actual array into those engines (I work with sqlserver) but here's an idea for passing a delimited string and parsing it in your sproc with this function.

我不知道如何将一个实际的数组传递到这些引擎(我使用sqlserver)，但是这里有一个想法，它可以传递一个分隔的字符串，并在您的sproc中解析这个函数。

CREATE FUNCTION [dbo].[Split]
(
    @ItemList NVARCHAR(4000), 
    @delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))  
AS      

BEGIN    
    DECLARE @tempItemList NVARCHAR(4000)
    SET @tempItemList = @ItemList

    DECLARE @i INT    
    DECLARE @Item NVARCHAR(4000)

    SET @tempItemList = REPLACE (@tempItemList, ' ', '')
    SET @i = CHARINDEX(@delimiter, @tempItemList)

    WHILE (LEN(@tempItemList) > 0)
    BEGIN
        IF @i = 0
            SET @Item = @tempItemList
        ELSE
            SET @Item = LEFT(@tempItemList, @i - 1)
        INSERT INTO @IDTable(Item) VALUES(@Item)
        IF @i = 0
            SET @tempItemList = ''
        ELSE
            SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
        SET @i = CHARINDEX(@delimiter, @tempItemList)
    END 
    RETURN
END

#5

For PostgreSQL, you could do something like this:

对于PostgreSQL，您可以这样做:

CREATE OR REPLACE FUNCTION fnExplode(in_array anyarray) RETURNS SETOF ANYELEMENT AS
$$
    SELECT ($1)[s] FROM generate_series(1,array_upper($1, 1)) AS s;
$$
LANGUAGE SQL IMMUTABLE;

Then, you could pass a delimited string to your stored procedure.

然后，可以将带分隔符的字符串传递给存储过程。

Say, param1 was an input param containing '1|2|3|4|5'

比如，param1是一个输入参数包含1|2|3|4|5

The statement:

声明:

SELECT CAST(fnExplode(string_to_array(param1, '|')) AS INTEGER);

results in a result set that can be joined or inserted.

结果集可以被连接或插入。

Likewise, for MySQL, you could do something like this:

同样，对于MySQL，您也可以这样做:

DELIMITER $$
CREATE PROCEDURE `spTest_Array`
(
    v_id_arr TEXT
)
BEGIN
    DECLARE v_cur_position INT; 
    DECLARE v_remainder TEXT; 
    DECLARE v_cur_string VARCHAR(255); 
    CREATE TEMPORARY TABLE tmp_test
    ( 
        id INT
    ) ENGINE=MEMORY; 

    SET v_remainder = v_id_arr; 
    SET v_cur_position = 1;

    WHILE CHAR_LENGTH(v_remainder) > 0 AND v_cur_position > 0 DO 
        SET v_cur_position = INSTR(v_remainder, '|'); 
        IF v_cur_position = 0 THEN 
            SET v_cur_string = v_remainder; 
        ELSE 
            SET v_cur_string = LEFT(v_remainder, v_cur_position - 1); 
        END IF; 

        IF TRIM(v_cur_string) != '' THEN 
            INSERT INTO tmp_test
                (id)
            VALUES 
                (v_cur_string);                 
        END IF; 

        SET v_remainder = SUBSTRING(v_remainder, v_cur_position + 1); 
    END WHILE; 

    SELECT 
        id
    FROM 
    tmp_test;

    DROP TEMPORARY TABLE tmp_test;
END 
$$

Then simply CALL spTest_Array('1|2|3|4|5') should produce the same result set as the above PostgreSQL query.

然后只需调用spTest_Array('1|2|3|4|5')，就会产生与上述PostgreSQL查询相同的结果集。

#6

Incidently, here is how you would add the array to a function (stored-proc) call:

下面是如何将数组添加到函数(存储-proc)调用中的方法:

CallableStatement proc = null;
List<Integer> faultcd_array = Arrays.asList(1003, 1234, 5678);
//conn - your connection manager
conn = DriverManager.getConnection(connection string here);
proc = conn.prepareCall("{ ? = call procedureName(?) }");
proc.registerOutParameter(1, Types.OTHER);
//This sets-up the array
Integer[] dataFaults = faultcd_array.toArray(new Integer[faultcd_array.size()]);
java.sql.Array sqlFaultsArray = conn.createArrayOf("int4", dataFaults);
proc.setArray(2, sqlFaultsArray);
//:
//add code to retrieve cursor, use the data.
//:

#7

Thanks to JSON support in MySQL you now actually have the ability to pass an array to your MySQL stored procedure. Create a JSON_ARRAY and simply pass it as a JSON argument to your stored procedure. Then in procedure, using MySQL's WHILE loop and MySQL's JSON "pathing" , access each of the elements in the JSON_ARRAY and do as you wish. An example here https://gist.githubusercontent.com/jonathanvx/513066eea8cb5919b648b2453db47890/raw/22f33fdf64a2f292688edbc67392ba2ccf8da47c/json.sql

在MySQL中，由于JSON支持，现在可以将数组传递到MySQL存储过程。创建JSON_ARRAY并将其作为JSON参数传递给存储过程。然后在过程中，使用MySQL的WHILE循环和MySQL的JSON“pathing”，访问JSON_ARRAY中的每个元素并按您的意愿执行。一个例子:https://gist.githubusercontent.com/jonathanvx/513066eea8cb5919b648b2453db47890/raw/22f33fdf64a2f292688edbc67392ba2ccf8da47c/json.sql

#1