create procedure delete_ums_histroy (p_updated_date character varying)
as $body$
declare
v_p_updated_date timestamp := to_timestamp(p_updated_date, 'yyyy-mm-dd');
commit_counter integer := 0;
conclusion_histroy record;
begin
for conclusion_histroy in (select id from ims_cx_test t where t.updated_date < v_p_updated_date)loop
delete from ims_cx_test n where n.id = conclusion_histroy.id;
commit_counter := commit_counter + 1;
if mod(commit_counter,100) = 0 then
commit;
end if;
end loop;
commit;
end;
$body$;
language 'plpgsql';
//授权
grant execute on procedure public.delete_ums_histroy(character varying) to public;
2.无参存储过程:
create procedure copy_ums_histroy ()
as $body$
declare
commit_counter integer := 0;
conclusion_histroy record;
begin
for conclusion_histroy in (select id,previewDate,deptCode from ims_cx_test t where t.previewDate >= '2022-05-01')loop
insert into ims_cx_test_mgr(ids,preview_date,dept_code) values(conclusion_histroy.id,conclusion_histroy.previewDate,conclusion_histroy.deptCode);
commit_counter := commit_counter + 1;
if mod(commit_counter,100) = 0 then
commit;
end if;
end loop;
commit;
end;
$body$;
language 'plpgsql';
//授权
grant execute on procedure public.delete_ums_histroy(character varying) to public;