今天看见自己前两年前写的代码，觉得自己当时技术真的很垃圾，现在想想都想笑，呵呵。拿出来给大家看一下，希望大家别介意。如果大家觉得有用尽可拿去用。
--数据库建表语句
create table poi_view(
      lsid number(38),
      lsid_ext varchar2(15),
      name varchar2(128),
      popname varchar2(128),
      longitude number(38),
      latitude number(38),
      tel varchar2(100),
      address varchar2(258),
      postcode varchar2(6),
      claid varchar2(4),
      disid number(38),
      datafrom varchar2(32),
      state varchar2(32),
      url varchar2(512),
      descn varchar2(2000),
      city varchar2(64),
      photo_url varchar2(4000),
      rank number(38),
      cityid varchar2(32)
    );
     
--拼接字符串的函数    

create or replace function str_list( str_in in varchar2)--分类字段
  return varchar2
is
      str_list  varchar2(4000) default null;--连接后字符串
      str  varchar2(20) default null;--连接符号
begin
      for x in ( select photo_url.photourl from photo_url where photo_url.lsid = str_in) loop
          str_list := str_list || str || to_char(x.photourl);
          str := ', ';
      end loop;
      return str_list;
end;

create or replace procedure poidata
  as
   cursor c1 is
   --复杂查询
     select poi.lsid,poi.lsid_ext,poi.name,poi.popname,poi.longitude,
       poi.latitude,poi.tel,
       poi.address,poi.postcode,poi.claid,poi.disid,
       datafrom_category.name as datafrom,
       state_category.name as state,
       poi_ext_search.url,poi_ext_search.descn,
       floor(poi_ext_rank.rankbaidu/1000)+2000000*cla_type.rank as rank,
      city_category.name as city,city_category.disid as cityid
     from
poi,poi_ext_search,cla_type,poi_ext_rank,city_category,dis_type,state_category,datafrom_category
     where poi.lsid=poi_ext_search.lsid(+)
      and poi.claid=cla_type.claid(+)
      and poi.datafrom=datafrom_category.id(+)
      and poi_ext_search.stat=state_category.id(+)
      and poi.lsid=poi_ext_rank.lsid(+)
      and poi.disid=dis_type.disid(+)
      and dis_type.city_id=city_category.id(+)
      and poi.flag='1';
      --variable
    v_rank  number(38);
    v_phurl varchar2(4000);
    v_lsid number(38);
    v_lsid_ext varchar2(15);
    v_name varchar2(128);
    v_popname varchar2(128);
    v_longitude number(38);
    v_tel varchar2(100);
    v_address varchar2(258);
    v_postcode varchar2(6);
    v_claid varchar2(4);
    v_disid number(38);
    v_d_cname varchar2(32);
    v_s_name varchar2(200);
    v_url varchar2(512);
    v_descn varchar2(2000);
    v_city varchar2(64);
    v_latitude number(38);
    v_cityid varchar2(32);
    v_c2 number(20);

   begin
   delete  from poi_view;
   commit;
   open c1;
   loop fetch c1 into
     v_lsid ,v_lsid_ext,v_name ,v_popname ,v_longitude ,v_latitude,
         v_tel ,v_address ,v_postcode ,v_claid ,v_disid ,v_d_cname ,
         v_s_name, v_url ,v_descn ,v_rank,v_city,v_cityid ;
      exit when c1%notfound;     
    insert into poi_view (lsid,lsid_ext,name ,popname ,longitude ,latitude ,tel ,
      address ,postcode ,claid ,disid ,datafrom ,state ,url,descn ,city ,photo_url,rank,cityid)values
       (v_lsid ,v_lsid_ext,v_name ,v_popname ,v_longitude ,v_latitude,
         v_tel ,v_address ,v_postcode ,v_claid ,v_disid ,v_d_cname ,v_s_name, v_url ,v_descn ,v_city,str_list(v_lsid),v_rank,v_cityid);
   commit;
 
 end loop;

 close c1;
end poidata;