场景

余额表历史数据处理，例如202510期间的数据依赖于202509期间的数据，通过循环从启用期间202101开始，依次更新下一个期间的余额。

代码实现

CREATE OR REPLACE FUNCTION compute_balance_amount ( ) returns setof dateList AS $$ 
	DECLARE r dateList % rowtype;
	declare sql_text text;
BEGIN
		FOR r IN SELECT * FROM dateList loop
		
		--上一期的期末余额
		sql_text := 'create or replace view tempBeginAmount
		as 
			select sett_org_id, contact_dept, currency_id, temest_end_period_balance_local, temest_end_period_balance
			from ap_balance_record
			where start_date >= ''' || r.last_start || ''' and end_date <= ''' || r.last_end||'''';
		execute sql_text;
		
		--当前期的合计金额
		sql_text := 'create or replace view tempCurrentAmount
		as 
			select sett_org_id, contact_dept, currency_id, round(sum(arap_amount), 2) arap_amount_sum, round(sum(arap_amount_local), 2) arap_amount_local_sum
			from ap_detail_record
			where biz_date >= '''||r.curr_start||''' and biz_date <= '''||r.curr_end||'''
				and document_type in (''pay'', ''red_pay'', ''other_payable'', ''other_payable_red'', ''ap_er'')
				and sett_org_id = 1
				and biz_type = ''tem_est''
			group by sett_org_id, contact_dept, currency_id';
		execute sql_text;
		
		-- 将待更新的值存入视图
		sql_text := 'CREATE OR REPLACE VIEW tempAllAmount AS
			SELECT
				balance.sett_org_id as sett_org_id, 
				balance.contact_dept as contact_dept, 
				balance.currency_id as currency_id,
				tempBegin.temest_end_period_balance as temest_begin_period_balance,
				tempBegin.temest_end_period_balance_local as temest_begin_period_balance_local,
				COALESCE(NULLIF(arap_amount_sum, NULl), 0) as temest_current_amount,
				COALESCE(NULLIF(arap_amount_local_sum, NULl), 0) as temest_current_amount_local,
				tempBegin.temest_end_period_balance + COALESCE(NULLIF(arap_amount_sum, NULl), 0) as temest_end_period_balance,
				tempBegin.temest_end_period_balance_local + COALESCE(NULLIF(arap_amount_local_sum, NULl), 0) as temest_end_period_balance_local
			FROM ap_balance_record AS balance
				LEFT JOIN tempcurrentamount AS tempCurrent 
					ON balance.sett_org_id = tempCurrent.sett_org_id 
					AND balance.contact_dept = tempCurrent.contact_dept 
					AND balance.currency_id = tempCurrent.currency_id
				RIGHT JOIN tempBeginAmount AS tempBegin 
					ON balance.sett_org_id = tempBegin.sett_org_id 
					AND balance.contact_dept = tempBegin.contact_dept 
					AND balance.currency_id = tempBegin.currency_id 
			WHERE
				balance.start_date >= '''||r.curr_start||''' AND balance.end_date <= '''||r.curr_end||'''';
			execute sql_text;
		
		--更新余额表
		update ap_balance_record balance
		set temest_begin_period_balance = tempAll.temest_begin_period_balance,
			temest_begin_period_balance_local = tempAll.temest_begin_period_balance_local,
			temest_current_amount = tempAll.temest_current_amount, 
			temest_current_amount_local = tempAll.temest_current_amount_local,
			temest_end_period_balance = tempAll.temest_end_period_balance,
			temest_end_period_balance_local = tempAll.temest_end_period_balance_local
		from tempAllAmount tempAll
		where balance.sett_org_id = tempAll.sett_org_id
			and balance.contact_dept = tempAll.contact_dept
			and balance.currency_id = tempAll.currency_id
			and balance.start_date >= r.curr_start AND balance.end_date <= r.curr_end;
			
	RETURN NEXT r;
END loop;
END;
$$ LANGUAGE plpgsql;

--执行存储过程
SELECT compute_balance_amount ();

其他代码技巧

-- 存储过程获取查询结果
CREATE OR REPLACE FUNCTION func_test ( ) 
returns setof table_name AS $$
BEGIN
	-- 如果存储过程返回 setof sometype，则返回值必须在 return next 或者 return query 中声明，然后有一个不带参数的 retrun 命令，告诉函数执行完毕；setof 就意味着多行；
    return query(select * from table_name limit 10);
	return;
END;
$$ LANGUAGE plpgsql;

-- 删除存储过程
DROP FUNCTION func_test();

-- 执行存储过程
SELECT func_test ();  -- 将所有列用,拼接在一起作为返回结果
select * from func_test ();  -- 表作为返回结果

-- returns table() 自定义返回类型
CREATE OR REPLACE FUNCTION update_acct_sys_id() returns table(result text) AS $$
    DECLARE r acct_sys_id_model_view % rowtype;
    declare sql_text text;
BEGIN
    FOR r IN SELECT * FROM acct_sys_id_model_view loop
		-- 利用动态表名更新数据
        sql_text := 'update ' || r.table_name || ' set acct_sys_id = (
            select id from mdm_accounting_system where is_major_accting_sys = true and state = ''audit'' and delete_state = ''normal'') 
            where acct_sys_id is null';
        execute sql_text;
		-- 返回某个查询的结果
        RETURN query(select concat(r.table_name, '的', r.field_name, '字段值更新成功'));
    END loop;
END;
$$ LANGUAGE plpgsql;