Sort by stored procedure

Create or replace procedure sort_node www.2cto.com (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 -- query the node information of devices at all points based on the line ID, find the first NODE information, and use the first NODE that does not have a forward NODE and is not empty as the first NODE. -- The first node can be multiple, because the connection link may contain multiple segments during the specified process. -- If such a node is not found after the traversal, it indicates that the cabling relationship has not been specified for the device, the sorting number is not updated and sorted by the collected node name. For rec in (select. global_id globalid,. par_id parid,. sbmc name from DP_COMPONENT_COMMON a, DP_XL_SB_RE B, EM_EQUIPMENT c where. global_id = B. GLOBAL_ID and. par_id is null and. EQUIPMENT_FNO = c. EQUIPMENT_FNO and c. is_point = 1 and B. xl_GLOBAL_ID = node_xlid) loop -- determine whether the node is the first node and obtain the GLOBAL_ID of the device at the current node. It is empty according to the pre-order in the line device inclusion relationship, not blank later -- add all the first nodes to the table used to store the first node. 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 the set length is not empty, the first node exists and the first node in the set is traversed, traverse each vertex device starting from the first node. Sort each segment and assign values to the order number. if (v_start_node is not null and len Trim (v_start_node ))! = 0) then if (startnodes_set.count! = 0) then -- first, set the order numbers under the line to null. update dp_xl_sb_re set sortno = null where xl_global_id = node_xlid; -- first iteration node, get the first node for I in 1 .. startnodes_set.count loop v_start_node: = startnodes_set (I); -- Re-number 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 if; -- end of sorting;