CREATE OR REPLACE FUNCTION build_by_zhoujie() RETURNS INT AS $bbb$
DECLARE
nowdate timestamp := now();
cr1 CURSOR FOR SELECT a.target_value FROM business_fee_detail a where a.zhangqi_type = 'B20002' group by a.target_value FOR UPDATE;
cr2 refcursor;
targervalue varchar;
ammSum float4;
feeType varchar;
BEGIN
OPEN cr1;
Loop
FETCH cr1 INTO targervalue;
open cr2 FOR SELECT b.fee_type, sum(c.ammount) as ammountSum from business_fee_detail c LEFT JOIN (select id,fee_type from fee_subject )b ON c.fee_subject_id=b.id where c.target_value='||targervalue||' group by b.fee_type;
Loop
FETCH cr2 INTO feeType,ammSum;
INSERT INTO business_fee_check (id,target_type,target_value,target_name,fee_type,direction_type,ammount,zhangqi_type,zhangqi_begin,zhangqi_end,create_date,create_name,status,reg_id,reg_dt) values ( (nextval('seq_business_fee_check')) , '机构' , '||targervalue||' , '123' , '||feeType||' , '收', ammSum, 'B20002' , nowdate , nowdate , nowdate , '系统' , '未结算' , 000001 , nowdate);
exit when cr2%notfound;
end loop;
CLOSE cr2;
EXIT WHEN cr1%NOTFOUND;
END Loop;
CLOSE cr1;
RETURN 1;
EXCEPTION
WHEN QUERY_CANCELED THEN
RAISE NOTICE 'query exception';
WHEN OTHERS THEN
RAISE NOTICE 'others';
WHEN invalid_cursor_state THEN
RAISE NOTICE 'no_data exception';
END;
$bbb$ LANGUAGE plpgsql;
DECLARE
nowdate timestamp := now();
cr1 CURSOR FOR SELECT a.target_value FROM business_fee_detail a where a.zhangqi_type = 'B20002' group by a.target_value FOR UPDATE;
cr2 refcursor;
targervalue varchar;
ammSum float4;
feeType varchar;
BEGIN
OPEN cr1;
Loop
FETCH cr1 INTO targervalue;
open cr2 FOR SELECT b.fee_type, sum(c.ammount) as ammountSum from business_fee_detail c LEFT JOIN (select id,fee_type from fee_subject )b ON c.fee_subject_id=b.id where c.target_value='||targervalue||' group by b.fee_type;
Loop
FETCH cr2 INTO feeType,ammSum;
INSERT INTO business_fee_check (id,target_type,target_value,target_name,fee_type,direction_type,ammount,zhangqi_type,zhangqi_begin,zhangqi_end,create_date,create_name,status,reg_id,reg_dt) values ( (nextval('seq_business_fee_check')) , '机构' , '||targervalue||' , '123' , '||feeType||' , '收', ammSum, 'B20002' , nowdate , nowdate , nowdate , '系统' , '未结算' , 000001 , nowdate);
exit when cr2%notfound;
end loop;
CLOSE cr2;
EXIT WHEN cr1%NOTFOUND;
END Loop;
CLOSE cr1;
RETURN 1;
EXCEPTION
WHEN QUERY_CANCELED THEN
RAISE NOTICE 'query exception';
WHEN OTHERS THEN
RAISE NOTICE 'others';
WHEN invalid_cursor_state THEN
RAISE NOTICE 'no_data exception';
END;
$bbb$ LANGUAGE plpgsql;
你在最后加一句这个试试看!