随笔-60  评论-138  文章-1  trackbacks-0

今天看见自己前两年前写的代码,觉得自己当时技术真的很垃圾,现在想想都想笑,呵呵。拿出来给大家看一下,希望大家别介意。如果大家觉得有用尽可拿去用。
--数据库建表语句
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;

posted on 2009-05-24 03:42 张氏兄弟 阅读(1779) 评论(5)  编辑  收藏 所属分类: oracle

评论:
# re: oracle pl/sql程序 2009-05-24 13:30 | zzyuuu
您能不能把这些代码简化下,再您看来可以的。
谢谢.  回复  更多评论
  
# re: oracle pl/sql程序 2009-05-24 23:14 | fdsf
感觉个人的技术还是有点不太够,后面的函数之类的东西就有点看不懂了。。。。  回复  更多评论
  
# re: oracle pl/sql程序 2009-05-24 23:26 | redrain
呵呵,两年前我的技术就是这样,其实你仔细看看就懂了,主要变量太多了,看着有点晕。好好里理头绪就懂了@fdsf
  回复  更多评论
  
# re: oracle pl/sql程序 2009-05-25 22:18 | 无量字幕
hehe...  回复  更多评论
  
# re: oracle pl/sql程序 2009-05-26 09:06 | ooxx
哪里不好,博主也要广而告之一下嘛。。  回复  更多评论
  

只有注册用户登录后才能发表评论。


网站导航: