postgresql動(dòng)態(tài)游標(biāo)使用案例

CREATE or REPLACE FUNCTION insert_p_date() RETURNS text as ```

$$
DECLARE
sql_string text;
sdate timestamp without time zone;
ldate timestamp without time zone;
_wtid integer;
--一段時(shí)間內(nèi)每個(gè)月第一天
cur1 cursor for select date(zz) from generate_series(date_trunc('month',to_date('20171001','yyyymmdd')),date_trunc('month',to_date('20180401','yyyymmdd')),'1 month') as tt(zz);
curs2 refcursor;
BEGIN
--打開
open cur1;
loop
fetch cur1 into sdate;
-- 假如沒有數(shù)據(jù)，結(jié)束循環(huán)
Exit when NOT found;
--一段時(shí)間內(nèi)的最后一個(gè)月的最后一天
select (date_trunc('month',sdate) + INTERVAL '1 MONTH - 1 day') into ldate;
--上個(gè)月最后一天
--select date_trunc('day', date_trunc('month',sdate)) - interval '1 day' into ldate;
--斷點(diǎn)拋出一個(gè)月第一天
raise notice 'sdate==================================================================%', sdate;
--定義一個(gè)月內(nèi)的內(nèi)的所有單臺(tái)設(shè)備
open curs2 for select distinct wtid from statisticdata_bak where rectime between sdate and ldate;
loop
--取記錄
fetch curs2 into _wtid;
-- 假如沒有檢索到數(shù)據(jù)，結(jié)束循環(huán)
Exit when NOT found;
--拋出設(shè)備信息
raise notice '_wtid=%', _wtid;
--執(zhí)行數(shù)據(jù)導(dǎo)入按照設(shè)備每個(gè)月
sql_string := 'insert into statisticdata select * from statisticdata_bak where wtid='||_wtid||' and rectime between '''||sdate||''' and '''||ldate||''';';
execute sql_string;
raise notice '執(zhí)行完成=%', _wtid;
end loop;
close curs2;
end loop;
--關(guān)閉游標(biāo)1
close cur1;

--返回結(jié)果
RETURN '導(dǎo)入成功！';
END;
$$
LANGUAGE plpgsql;