create or replace procedure sort_node
(
node_xlid in varchar2
)
as
v_start_node varchar2(100);
v_node1 varchar2(100);
v_node2 varchar2(100);
v_count number := 0;
v_index number :=0;
type start_nodes_table is table of varchar2(100) index by binary_integer;
startnodes_set start_nodes_table;
begin
--根据线路ID来查询所有点设备的节点信息,找到首节点信息,以找到的第一个没有前序节点且后序节点不为空的NODE作为首节点。
--首节点可以为多个,因为连接关系在指定的过程当中可能出现多段。
--如果遍历完都没有找到这样的节点,说明还没有对点设备指定敷线关系,那么就不更新排序号,按照采集的节点名称排序。
for rec in (select a.global_id globalid,a.par_id parid,a.sbmc name from
DP_COMPONENT_COMMON a, DP_XL_SB_RE b,EM_EQUIPMENT c
where a.global_id = b.GLOBAL_ID and a.par_id is null and a.EQUIPMENT_FNO=c.EQUIPMENT_FNO and c.is_point=1
and b.xl_GLOBAL_ID =node_xlid )
loop
--判断是否为首节点,获取到当前点设备的GLOBAL_ID,依据线路设备包含关系中前序为空,后续不为空
--同时把所有的首节点添加到用于存放首节点的表中。
select node1_id,node2_id into v_node1,v_node2 from dp_xl_sb_re where global_id = rec.globalid and xl_global_id = node_xlid;
if(v_node1 is null and v_node2 is not null) then
v_index :=v_index+1;
--v_start_node := rec.globalid;
startnodes_set(v_index):=rec.globalid;
--exit;
end if;
end loop;
--如果集合长度不为空,那么就说明存在首节点,遍历集合中的首节点,对从首节点开始的每个点设备进行遍历,
--对每一段进行排序,对排序号赋值排序
--if(v_start_node is not null and length(trim(v_start_node))!=0)then
if(startnodes_set.count!=0)then
--首先把线路下面的排序号都置为空
update dp_xl_sb_re set sortno = null where xl_global_id = node_xlid;
--迭代首节点,获取到首节点
for i in 1..startnodes_set.count loop
v_start_node := startnodes_set(i);
--重新编号
for rec in (select global_id,node1_id from dp_xl_sb_re where xl_global_id = node_xlid
start with global_id = v_start_node and xl_global_id = node_xlid
connect by prior global_id = node1_id )
loop
v_count:=v_count+1;
update dp_xl_sb_re set sortno = v_count where global_id = rec.global_id and xl_global_id = node_xlid;
end loop;
end loop;
end if;
--排序结束
end ;