PostgreSQL函数中使用数组/存储过程案例
数据库中实付存在某个日期汇率错误,导致本位币金额计算错误。通过函数重新计算本位币金额以及汇率修正。
CREATE FUNCTION "public"."update_actully_amount_by_rate"("param1" text, "patem2" numeric, "from_" numeric, "to_" numeric) RETURNS "pg_catalog"."void" AS $BODY$ DECLARE opdata record;--主表数据 adjust record;--调整单数据 tempAmount_ numeric; totalAmount_ numeric; actuallys_ text[]; actually_id text; BEGIN -- 汇率更新之后,应收本位币金额修改 raise notice '发布日期: %',param1; raise notice '汇率: %',patem2; raise notice '原币: %',from_; raise notice '本位币: %',to_; --查询收款单(不是代付) FOR opdata IN SELECT apd.* FROM tb_actually_paid_detail apd LEFT JOIN tb_actually_paid ap on apd.actually_paid_id=ap.id WHERE ap.payment_currency=from_--42 AND ap.payment_status=2 -- 已付款 AND ap.delete=false --未删除 AND ap.payment_base_currency=to_--38 付款账号本位币币种 AND to_char(ap.payment_date,'yyyy-mm-dd')=param1 LOOP--'2018-03-12' raise notice 'code: %',opdata.id; UPDATE tb_actually_paid_detail SET exchange_rate=patem2, payment_base_amount=payment_amount*patem2, in_push=1 WHERE id=opdata.id; -- 将主表ID存到数组,去重复 IF actuallys_ @> ARRAY[opdata.actually_paid_id::text] THEN ELSE SELECT array_append(actuallys_, opdata.actually_paid_id::text) INTO actuallys_; END IF; raise notice 'actuallys_: %',actuallys_; END LOOP; --查询收款单(是代付) FOR opdata IN SELECT apd.* FROM tb_actually_paid_detail apd LEFT JOIN tb_actually_paid ap on apd.actually_paid_id=ap.id WHERE ap.payment_currency=from_--42 AND ap.payment_status=2 -- 已付款 AND ap.delete=false --未删除 AND apd.replace_pay_base_currency=to_--38 代付本位币币种(团所属币种) AND to_char(ap.payment_date,'yyyy-mm-dd')=param1 LOOP--'2018-03-12' raise notice 'code: %',opdata.id; UPDATE tb_actually_paid_detail SET exchange_rate=patem2, payment_base_amount=payment_amount*patem2, in_push=1 WHERE id=opdata.id; -- 将主表ID存到数组,去重复 IF actuallys_ @> ARRAY[opdata.actually_paid_id::text] THEN ELSE SELECT array_append(actuallys_, opdata.actually_paid_id::text) INTO actuallys_; END IF; raise notice 'actuallys_: %',actuallys_; END LOOP; --循环实付主表 FOREACH actually_id IN ARRAY actuallys_ LOOP SELECT sum(payment_base_amount) FROM tb_actually_paid_detail WHERE actually_paid_id=actually_id::bigint INTO totalAmount_; raise notice 'totalAmount_: %',totalAmount_; UPDATE tb_actually_paid SET payment_base_amount=totalAmount_ WHERE id=actually_id::bigint; END LOOP; RETURN; END$BODY$ LANGUAGE plpgsql VOLATILE COST 100
逻辑:循环付款明细,根据付款日期,付款币种,付款本位币,付款状态,删除状态查询付款明细。再循环每个明细进行计算修改,再循环过程中保存付款主表ID,这里申明了一个数组来存储,并且过滤重复得数据。
第二个循环是查询了代付的数据,如果有用到错误汇率的也做同样的处理,第三个循环是计算主表的本位币金额。
明细表中同一付款ID的原币金额相加等于主表的原币金额。
写这篇文章的时候想在函数中使用数组类型,但是没有找到好的文章。虽然比较基础,也是给遇到同样问题的人节约时间吧。