我怎样才能传递一个“数组"?我的存储过程的值?
我希望能够将值的数组"传递给我的存储过程,而不是连续调用添加值"过程.
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?
我将使用 PostgreSQL/MySQL,我还没有决定.
I will be using PostgreSQL / MySQL, I haven't decided yet.
推荐答案
正如 Chris 所指出的,在 PostgreSQL 中这没问题 - 任何基本类型(如 int、text)都有自己的数组子类型,您还可以创建自定义类型,包括复合的.例如:
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:
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
];
您可以编写一个函数,将数组中的每一项乘以 n*m,并返回乘积之和:
You can write a function that will multiply n*m for each item in array, and return sum of products:
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;
并运行它:
# SELECT test_test(
ARRAY[
row(1, 2)::test,
row(3,4)::test,
row(5,6)::test
]
);
test_test
-----------
44
(1 row)
相关文章