今天看見自己前兩年前寫的代碼，覺得自己當(dāng)時技術(shù)真的很垃圾，現(xiàn)在想想都想笑，呵呵。拿出來給大家看一下，希望大家別介意。如果大家覺得有用盡可拿去用。
--數(shù)據(jù)庫建表語句
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)
    );
     
--拼接字符串的函數(shù)    

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
   --復(fù)雜查詢
     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;