博客

——问题

在oracle中存储过程可随时commit，但是postgresql中不能随时commit，无法控制执行存储过程，如果发生意外事故或是异常情况，中断了存储过程，则在postgresql中会全部回滚

——解决

在数据量大情况下，循环游标控制数量，每次循环3000条数据，commit；至少保证了每循环一次有3000条数据入库，而不是全部回滚；

do $$
declare
    --count_num  integer;
    cur_point  refcursor;
    count_num  integer;
    keyvalue   integer := 0;
    insert_num integer := 0;
    record_id  integer;
    v_point    record;
begin
    execute 'select count(1) from dept_point'
        into count_num;
    raise notice 'the number of dept_point is %',count_num;
    if count_num = 0 then
        raise exception 'the number of dept_point is 0';
    end if;
    while keyvalue < count_num
        loop
            begin
                open cur_point for execute 'select building_code ::text
        from dept_point
        order by building_code limit 3000 offset $1' using
                    keyvalue;
                loop
                    fetch cur_point into v_point;
                    exit when not found;
                    select count(1)
                    into record_id
                    from point_insert
                    where formno1 = v_point.building_code;
                    if record_id = 0 then
                        --insert
                        begin
                            insert into point_insert
                            (formno1)
                            values (v_point.building_code);
                            insert_num := insert_num + 1;
                        exception
                            when others then
                                raise notice 'insert errors % ,(%)', v_point.building_code,sqlerrm;
                        end;
                    end if;
                end loop;
                close cur_point;
                keyvalue := keyvalue + 3000;
				commit;
            end;
        end loop;
    raise notice 'line_count_add is %',insert_num;
end
$$