列表/数组的 PL/pgSQL 控制结构 [英] PL/pgSQL control structures for lists / arrays
问题描述
是否可以在 Postgres 中使用这样的东西?这是我想要做的 PL/SQL 示例:
Is it possible to use something like this in Postgres? This is the example from PL/SQL what I want to do:
PROCEDURE CREATE_PAYMENT(P_AMOUNT IN NUMBER,
P_INVOICE_LIST IN SIMPLEARRAYTYPE,
P_AMOUNT_LIST IN NUMBER_TABLE -- pass list of amounts
.
.
.)
s_chk_amnt NUMBER;
invoice_list SIMPLEARRAYTYPE;
amount_list NUMBER_TABLE;
BEGIN
-- check if amount list is null or contains zeros
IF p_amount_list IS NOT NULL AND p_amount_list.COUNT <> 0 THEN
FOR r IN p_amount_list.FIRST..p_amount_list.LAST
LOOP
s_chk_amnt := s_chk_amnt + p_amount_list(r);
END LOOP;
END IF;
我可以将字符列表和数字列表声明为函数输入参数吗?
我找到了一些带有 FOREACH element
的例子,但我不知道如何像在 Oracle 中使用 p_amount_list(r)
从数字列表中获取某个元素.
Can I declare a list of characters and list of numbers as function input parameters?
I have found some examples with FOREACH element
but I don't know how to grab a certain element from number list like in Oracle with p_amount_list(r)
.
推荐答案
CREATE OR REPLACE FUNCTION CREATE_PAYMENT(p_amount_list numeric[])
RETURNS numeric AS
$func$
DECLARE
s_chk_amnt numeric := 0; -- init variable!
r numeric;
BEGIN
-- IF p_amount_list <> '{}' THEN -- just noise
FOREACH r IN ARRAY p_amount_list
LOOP
s_chk_amnt := s_chk_amnt + r;
END LOOP;
-- END IF;
RETURN s_chk_amnt;
END
$func$ LANGUAGE plpgsql
要点
Oracle 的
number
是数字 .但是如果你没有小数,你更愿意在 Postgres 中使用int
或bigint
.关于 Oracle 和 Postgres 之间的类型映射.Postgres 没有 "表类型像甲骨文.使用 array types,一个
numeric
的数组在这种情况下:numeric[]
.Postgres does not have "table types" like Oracle. Use array types, an array of
numeric
in this case:numeric[]
.表达式
IF p_amount_list <>'{}' ...
将排除 NULL 和空数组"等.无需像原件那样进行第二次检查.但是IF
根本不需要.对于 NULL 或空数组,无论如何都不会进入循环.The expression
IF p_amount_list <> '{}' ...
would rule out NULL and "empty array" alike. No need for a second check like in your original. But theIF
is not needed at all. For NULL or empty array, the loop isn't entered anyway.r
保存元素本身,而不是它的索引.(因此它必须是匹配的数据类型.)r
holds the element itself, not an index to it. (Therefore it must be a matching data type.)这将演示 plpgsql 函数中
FOREACH
循环的基本语法.否则,这将是昂贵的废话,最好用更简单、更快捷的方式代替:This goes to demonstrate basic syntax of a
FOREACH
loop in a plpgsql function. Otherwise it would be expensive nonsense, better replaced with a much simpler and faster:SELECT sum(elem) AS sum_amount FROM unnest(p_amount_list) elem;
这篇关于列表/数组的 PL/pgSQL 控制结构的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!