pg数据库存储过程

一、存储过程

CREATE OR REPLACE PROCEDURE public.p_dm_stock_fx_hangye_d(IN dt_date character varying)
 LANGUAGE plpgsql
AS $procedure$
    begin

delete from dm_stock_fx_hangye_d where stock_date = dt_date;
commit;

insert into dm_stock_fx_hangye_d
select 
t.stock_date 
,a.industry 
,t.operation_type 
,sum(t.stock_volume)
from dm_stock_base_fex_d t
inner join stock_baseinfo a 
        on substr(t.stock_code,3,6) = substr(a.ts_code,1,6) 
where t.stock_date = dt_date
group by 
a.industry 
,t.operation_type 
,t.stock_date;
commit;

    END;
$procedure$
;
二、循环调用

CREATE OR REPLACE PROCEDURE public.p_tool1(IN start_date integer, IN end_date integer)
 LANGUAGE plpgsql
AS $procedure$
    begin
         for i in start_date..end_date
         loop
         RAISE NOTICE '%', i; -- 打印当前迭代的值
         call p_dm_stock_fx_hangye_d(cast(i as varchar));
         end loop;
    end;

$procedure$
;