問題描述
我希望能夠將值的數組"傳遞給我的存儲過程，而不是連續調用添加值"過程.
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)
這篇關于我怎樣才能傳遞一個“數組"?我的存儲過程的值?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！