我怎样才能传递一个“数组"?我的存储过程的值?

2021-11-18 00:00:00 postgresql arrays database mysql stored-procedures

我希望能够将值的数组"传递给我的存储过程，而不是连续调用添加值"过程.

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)

相关文章