create or replace procedure lyshzyzd_pro is

  cursor temp_cursor is
    select XMBM, substr(CITY, 0, 2)||'市' as city, ZDMC, LATITUDE, LONGITUDE,county
      from LYSHZYZD_TEMP;

  cursor basePoint_cursor(city varchar2,county varchar2) is
    select t.provice_name,
           t.city_name,
           t.city_id,
           t.county_name,
           t.county_id,
           t.zh_label,
           t.zh_code,
           t.address,
           t.strategic_resid,
           t.strategic_resname,
           t.strategic_type,
           t.contact_no,
           t.contact_name,
           t.strategic_agreements_name,
           t.company_name,
           t.company_id,
           t.kc_statue,
           t.xy_statue,
           t.remark,
           t.int_id,
           t.stateflag,
           t.time_stamp,
           t.creator,
           t.creat_time,
           t.modifier,
           t.modify_time,
           t.points_layyer,
           t.res_type,
           t.other_type,
           t.be_res_type,
           t.file_id,
           t.photo,
           t.have_govfile,
           t.govfile_name,
           t.longitude1,
           t.latitude1
      from rmw.res_base_point_resources t
     where city_name = city and county_name=county;

begin
  begin
    for temp in temp_cursor LOOP
     dbms_output.put_line(temp.city||temp.county||temp.xmbm);
      for basePoint in basePoint_cursor(temp.city,temp.county) LOOP
        if getdistance(temp.latitude,
                       temp.longitude,
                       basePoint.latitude1,
                       basePoint.longitude1) <= 20 then
          begin
            insert into RES_BASE_POINT_RESOURCES_TEMP
              (provice_name,
               city_name,
               city_id,
               county_name,
               county_id,
               zh_label,
               zh_code,
               address,
               strategic_resid,
               strategic_resname,
               strategic_type,
               contact_no,
               contact_name,
               strategic_agreements_name,
               company_name,
               company_id,
               kc_statue,
               xy_statue,
               remark,
               int_id,
               stateflag,
               time_stamp,
               creator,
               creat_time,
               modifier,
               modify_time,
               points_layyer,
               res_type,
               other_type,
               be_res_type,
               file_id,
               photo,
               have_govfile,
               govfile_name,
               longitude1,
               latitude1,
               xmbm)
            values
              (basePoint.provice_name,
               basePoint.city_name,
               basePoint.city_id,
               basePoint.county_name,
               basePoint.county_id,
               basePoint.zh_label,
               basePoint.zh_code,
               basePoint.address,
               basePoint.strategic_resid,
               basePoint.strategic_resname,
               basePoint.strategic_type,
               basePoint.contact_no,
               basePoint.contact_name,
               basePoint.strategic_agreements_name,
               basePoint.company_name,
               basePoint.company_id,
               basePoint.kc_statue,
               basePoint.xy_statue,
               basePoint.remark,
               basePoint.int_id,
               basePoint.stateflag,
               basePoint.time_stamp,
               basePoint.creator,
               basePoint.creat_time,
               basePoint.modifier,
               basePoint.modify_time,
               basePoint.points_layyer,
               basePoint.res_type,
               basePoint.other_type,
               basePoint.be_res_type,
               basePoint.file_id,
               basePoint.photo,
               basePoint.have_govfile,
               basePoint.govfile_name,
               basePoint.longitude1,
               basePoint.latitude1,
               temp.xmbm
               );
            commit;
          end;
        end if;
      end Loop;
 
    end loop;
  end;

end lyshzyzd_pro;