create or replace procedure LINESET_UPDATE is
   V_NODEID point.NODEID%type;
   V_LINEID line.lineid%type;
   V_LINESET point.lineset%type;
   T NUMBER :=0;
   cursor C_NODEID is select NODEID from point;
   cursor C_LINEID(V_NODEID point.NODEID%type) is select LINEID from line where fnode=V_NODEID or tnode=V_NODEID;
   cursor NUM_LINEID is select count(LINEID) from line where fnode=V_NODEID or tnode=V_NODEID;
begin
V_NODEID:=0;
V_LINEID:=0;
<>
   open C_NODEID;
   LOOP
    fetch C_NODEID into V_NODEID;
    EXIT WHEN C_NODEID%NOTFOUND;
      OPEN NUM_LINEID;
      FETCH NUM_LINEID INTO T;
      if T=0 then
         insert into nodeid_error values(V_NODEID);
      elsif T=1 then
          open C_LINEID(V_NODEID);
          fetch C_LINEID INTO V_LINEID;
          close C_LINEID;
          update point set lineset=V_LINEID where nodeid=V_NODEID;
      else
         <>
         open C_LINEID(V_NODEID);
         fetch C_LINEID into V_LINEID;
         while C_LINEID%FOUND LOOP
                V_LINESET:=LTRIM(V_LINESET||','||V_LINEID,',');
         fetch C_LINEID into V_LINEID;
         end loop loop_2;
         close C_LINEID;
         update point set lineset=V_LINESET where nodeid=V_NODEID;
         V_LINESET:='';
       end if;
       close NUM_LINEID;
      T:=0;
      V_NODEID:=0;

    end loop loop_1;
    CLOSE C_NODEID;


end LINESET_UPDATE;

ITPub博客

用PL/SQL更新拓扑点属性

最新文章