用PL/SQL计算路段桩号

create or replace procedure P_daniel as
  v_LXBM TM_DLZX_AAT.lxbm%type;
  v_FNODE TM_DLZX_AAT.fnode%type;
  v_TNODE TM_DLZX_AAT.tnode%type;
  v_RLENGTH TM_DLZX_AAT.RLENGTH%type;
  v_FMEAS TM_DLZX_AAT.FMEAS%type;
  v_TMEAS TM_DLZX_AAT.TMEAS%type;
  counts  number(3);
  cursor c_LXBM is select distinct LXBM from TM_DLZX_AAT;
  cursor c_FirstNode is select FNODE, TNODE, RLENGTH from TM_DLZX_AAT where fnode not in(select tnode from TM_DLZX_AAT where lxbm=v_lxbm) AND lxbm=v_lxbm  for update of FMEAS,TMEAS;
  cursor c_RLENGTH  is select fnode,tnode,RLENGTH from TM_DLZX_AAT where LXBM=v_lxbm and FNODE=v_TNODE  for update of FMEAS,TMEAS;
  cursor t is select count(*)-1 a from TM_DLZX_AAT where lxbm=v_LXBM;
 begin
   v_FMEAS := 0;
   v_TMEAS := 0;
   open c_LXBM;
    
   fetch c_LXBM into v_LXBM;
  while c_LXBM%FOUND loop
    
          open c_FirstNode;
        fetch c_FirstNode into v_FNODE, v_TNODE, v_RLENGTH;
          v_FMEAS := v_TMEAS;
          v_TMEAS := v_TMEAS + v_RLENGTH;
        update TM_DLZX_AAT set FMEAS=v_FMEAS, TMEAS=v_TMEAS where current of c_FirstNode;
        close c_FirstNode;
        open t;
        fetch t into counts;
        close t;
        for TT in 1..counts loop
        open c_RLENGTH;
         if c_RLENGTH%notfound then
          insert into errors values(v_LXBM);
           goto daniel;
         else
           fetch c_RLENGTH into v_FNODE,v_TNODE, v_RLENGTH;
                v_FMEAS := v_TMEAS;
                 v_TMEAS := v_TMEAS + v_RLENGTH;
               update TM_DLZX_AAT set FMEAS=v_FMEAS, TMEAS=v_TMEAS where current of c_RLENGTH;
               close c_RLENGTH;
          end if;
         end loop;
      <> 
     v_RLENGTH :=0;
     v_FMEAS := 0;
     v_TMEAS := 0;
      fetch c_LXBM into v_LXBM;
  end loop;
   -- close c_LXBM;
end P_daniel;
/*create or replace procedure P_daniel as
  v_LXBM TM_DLZX_AAT.lxbm%type;
  v_FNODE TM_DLZX_AAT.fnode%type;
  v_TNODE TM_DLZX_AAT.tnode%type;
  v_RLENGTH TM_DLZX_AAT.RLENGTH%type;
  v_FMEAS TM_DLZX_AAT.FMEAS%type;
  v_TMEAS TM_DLZX_AAT.TMEAS%type;
  cursor c_LXBM is select distinct LXBM from TM_DLZX_AAT;
  cursor c_FirstNode is select FNODE, TNODE, RLENGTH from TM_DLZX_AAT where fnode not in(select tnode from TM_DLZX_AAT where lxbm=v_lxbm) AND lxbm=v_lxbm  for update of FMEAS,TMEAS;
  cursor c_RLENGTH  is select tnode,RLENGTH from TM_DLZX_AAT where LXBM=v_lxbm and FNODE=v_TNODE  for update of FMEAS,TMEAS;
 begin
   v_FMEAS := 0;
   v_TMEAS := 0;
  for T_LXBM in c_LXBM loop
     fetch c_LXBM into v_LXBM;
          open c_FirstNode;
         fetch c_FirstNode into v_FNODE, v_TNODE, v_RLENGTH;
             v_FMEAS := v_TMEAS;
             v_TMEAS := v_TMEAS + v_RLENGTH;
         update TM_DLZX_AAT set FMEAS=v_FMEAS, TMEAS=v_TMEAS where current of c_FirstNode;
        close c_FirstNode;
       
        for T_RLENGTH in c_RLENGTH loop
            fetch c_RLENGTH into v_TNODE, v_RLENGTH;
              v_FMEAS := v_TMEAS;
                v_TMEAS := v_TMEAS + v_RLENGTH;
              update TM_DLZX_AAT set FMEAS=v_FMEAS, TMEAS=v_TMEAS where current of c_RLENGTH;
        end loop;
       /* open c_RLENGTH;
        fetch c_RLENGTH into v_TNODE, v_RLENGTH;
          while c_RLENGTH%found loop
             v_FMEAS := v_TMEAS;
              v_TMEAS := v_TMEAS + v_RLENGTH;
             update TM_DLZX_AAT set FMEAS=v_FMEAS, TMEAS=v_TMEAS where current of c_RLENGTH;
             fetch c_RLENGTH into v_TNODE, v_RLENGTH;
         end loop;*/
      /*v_RLENGTH :=0;
      v_FMEAS := 0;
      v_TMEAS := 0;
    
   -- close c_RLENGTH;
  end loop;
   -- close c_LXBM;
end P_daniel;*/

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8720638/viewspace-240046/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8720638/viewspace-240046/