MySQL 如何将值的“数组”传递给我的存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/404941/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I pass an "array" of values to my stored procedure?
提问by Orentet
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，我还没有决定。
采纳答案by Orentet
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)
回答by Kb.
回答by Weej
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
回答by Mitch Wheat
You didn't indicate, but if you are referring to SQL server, here's one way.
您没有指出，但如果您指的是 SQL 服务器，这是一种方式。
And the MS support ref.
并且 MS 支持ref。
回答by Mitch Wheat
For PostgreSQL, you could do something like this:
对于 PostgreSQL，您可以执行以下操作：
CREATE OR REPLACE FUNCTION fnExplode(in_array anyarray) RETURNS SETOF ANYELEMENT AS
$$
SELECT ()[s] FROM generate_series(1,array_upper(, 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.
然后简单地 CALLspTest_Array('1|2|3|4|5')应该产生与上述 PostgreSQL 查询相同的结果集。
回答by Kevin Barasa
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
回答by MAbraham1
Incidently, here is how you would add the array to a function (stored-proc) call:
顺便说一下，以下是将数组添加到函数（存储过程）调用的方法：
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.
//:
