/*
说明:通过CP名称,模糊查询传感网络设备树状结构
输入:CP名称
输出:xml树状结构。节点字段如下
*/
PROCEDURE QU_NET_DEVICE_XML(P_CP_NAME VARCHAR2, P_CUR OUT SYS_REFCURSOR) IS
V_CP_MAC VARCHAR2(20);
V_CP_NAME VARCHAR2(40);
V_CP_IP VARCHAR2(20);
/* V_P_MAC VARCHAR2(20);*/
V_CP_STA VARCHAR2(10);
V_CP_REL_STA VARCHAR2(10);
V_AP_MAC VARCHAR2(20);
V_AP_NAME VARCHAR2(40);
V_AP_IP VARCHAR2(20);
V_AP_STA VARCHAR2(10);
V_AP_REL_STA VARCHAR2(10);
V_STATU VARCHAR2(20); --(0确认,1未确认,2待确认,3待确认失败)
V_AP_MAC2 VARCHAR2(20);
V_AP_MAC3 VARCHAR2(20);
V_AP_MAC4 VARCHAR2(20);
V_ED_MAC VARCHAR2(20);
V_ED_NAME VARCHAR2(40);
V_ED_STA VARCHAR2(10);
V_ED_MAC2 VARCHAR2(20);
V_ED_MAC3 VARCHAR2(20);
V_ED_MAC4 VARCHAR2(20);
xml_clob CLOB;
root_node xmldom.DOMNode;
menu1_node xmldom.DOMNode;
menu2_node xmldom.DOMNode;
menu3_node xmldom.DOMNode;
menu4_node xmldom.DOMNode;
menu5_node xmldom.DOMNode;
menu6_node xmldom.DOMNode;
doc xmldom.DOMDocument;
item_elmt xmldom.DOMElement;
CURSOR C_CP IS
SELECT T.CP_MAC, T.CP_IP, T.CP_NAME, T.STATE, T.REL_STAT, T.STATUS
FROM V_REAL_CP T
WHERE T.CP_NAME LIKE '%' || P_CP_NAME || '%'
ORDER BY T.CP_NAME;
CURSOR C_AP1 IS
SELECT T.AP_MAC,
T.AP_NETWORK,
T.AP_NAME,
T.STATE,
T.REL_STAT,
T.STATUS
FROM V_REAL_AP T
WHERE T.BEL_ID = V_CP_MAC
AND T.BEL_TYPE = 1
ORDER BY T.AP_NAME;
CURSOR C_AP2 IS
SELECT T.AP_MAC,
T.AP_NETWORK,
T.AP_NAME,
T.STATE,
T.REL_STAT,
T.STATUS
FROM V_REAL_AP T
WHERE T.BEL_ID = V_AP_MAC
AND T.BEL_TYPE = 2
ORDER BY T.AP_NAME;
CURSOR C_AP3 IS
SELECT T.AP_MAC,
T.AP_NETWORK,
T.AP_NAME,
T.STATE,
T.REL_STAT,
T.STATUS
FROM V_REAL_AP T
WHERE T.BEL_ID = V_AP_MAC2
AND T.BEL_TYPE = 2
ORDER BY T.AP_NAME;
CURSOR C_AP4 IS
SELECT T.AP_MAC,
T.AP_NETWORK,
T.AP_NAME,
T.STATE,
T.REL_STAT,
T.STATUS
FROM V_REAL_AP T
WHERE T.BEL_ID = V_AP_MAC3
AND T.BEL_TYPE = 2
ORDER BY T.AP_NAME;
CURSOR C_ED IS
SELECT T.ED_MAC, T.ED_NAME, T.STATE, T.STATUS
FROM V_REAL_ED T
WHERE T.AP_ID = V_AP_MAC
ORDER BY T.ED_NAME;
CURSOR C_ED2 IS
SELECT T.ED_MAC, T.ED_NAME, T.STATE, T.STATUS
FROM V_REAL_ED T
WHERE T.AP_ID = V_AP_MAC2
ORDER BY T.ED_NAME;
CURSOR C_ED3 IS
SELECT T.ED_MAC, T.ED_NAME, T.STATE, T.STATUS
FROM V_REAL_ED T
WHERE T.AP_ID = V_AP_MAC3
ORDER BY T.ED_NAME;
CURSOR C_ED4 IS
SELECT T.ED_MAC, T.ED_NAME, T.STATE, T.STATUS
FROM V_REAL_ED T
WHERE T.AP_ID = V_AP_MAC4
ORDER BY T.ED_NAME;
CURSOR AP_ONLY IS
SELECT T.AP_MAC,
T.AP_NETWORK,
T.AP_NAME,
T.STATE,
T.REL_STAT,
T.STATUS
FROM V_REAL_AP T
WHERE T.EQU_RELATION = 2
AND T.BEL_ID = V_CP_MAC
ORDER BY T.AP_NAME;
CURSOR ED_ONLY IS
SELECT T.ED_MAC, T.ED_NAME, T.STATE, T.STATUS
FROM V_REAL_ED T
WHERE T.EQU_RELATION = 2
AND T.AP_ID = V_CP_MAC
ORDER BY T.ED_NAME;
BEGIN
--初始化xml
doc := xmldom.newDOMDocument();
root_node := xmldom.makeNode(doc);
item_elmt := xmldom.createElement(doc, 'Root');
root_node := xmldom.appendChild(root_node, xmldom.makeNode(item_elmt));
--循环cp---------------------------
OPEN C_CP;
LOOP
FETCH C_CP
INTO V_CP_MAC, V_CP_IP, V_CP_NAME, V_CP_STA, V_CP_REL_STA, V_STATU;
EXIT WHEN C_CP%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_CP_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_CP_MAC);
xmldom.setAttribute(item_elmt, 'IP', V_CP_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'CP');
xmldom.setAttribute(item_elmt, 'STAT', V_CP_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', V_CP_REL_STA);
xmldom.setAttribute(item_elmt, 'P_ID', '');
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu1_node := xmldom.appendChild(root_node,
xmldom.makeNode(item_elmt));
--开始循环第一层AP
OPEN C_AP1;
LOOP
FETCH C_AP1
INTO V_AP_MAC, V_AP_IP, V_AP_NAME, V_AP_STA, V_AP_REL_STA, V_STATU;
EXIT WHEN C_AP1%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_AP_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_AP_MAC);
xmldom.setAttribute(item_elmt, 'IP', V_AP_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'AP');
xmldom.setAttribute(item_elmt, 'STAT', V_AP_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', V_AP_REL_STA);
xmldom.setAttribute(item_elmt, 'P_ID', V_CP_MAC);
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu2_node := xmldom.appendChild(menu1_node,
xmldom.makeNode(item_elmt));
--开始循环第二层AP
OPEN C_AP2;
LOOP
FETCH C_AP2
INTO V_AP_MAC2, V_AP_IP, V_AP_NAME, V_AP_STA, V_AP_REL_STA, V_STATU;
EXIT WHEN C_AP2%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_AP_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_AP_MAC2);
xmldom.setAttribute(item_elmt, 'IP', V_AP_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'AP');
xmldom.setAttribute(item_elmt, 'STAT', V_AP_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', V_AP_REL_STA);
xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC);
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu3_node := xmldom.appendChild(menu2_node,
xmldom.makeNode(item_elmt));
--开始循环第三层AP
OPEN C_AP3;
LOOP
FETCH C_AP3
INTO V_AP_MAC3, V_AP_IP, V_AP_NAME, V_AP_STA, V_AP_REL_STA, V_STATU;
EXIT WHEN C_AP3%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_AP_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_AP_MAC3);
xmldom.setAttribute(item_elmt, 'IP', V_AP_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'AP');
xmldom.setAttribute(item_elmt, 'STAT', V_AP_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', V_AP_REL_STA);
xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC2);
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu4_node := xmldom.appendChild(menu3_node,
xmldom.makeNode(item_elmt));
--开始循环第四层AP
OPEN C_AP4;
LOOP
FETCH C_AP4
INTO V_AP_MAC4, V_AP_IP, V_AP_NAME, V_AP_STA, V_AP_REL_STA, V_STATU;
EXIT WHEN C_AP4%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_AP_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_AP_MAC4);
xmldom.setAttribute(item_elmt, 'IP', V_AP_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'AP');
xmldom.setAttribute(item_elmt, 'STAT', V_AP_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', V_AP_REL_STA);
xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC3);
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu5_node := xmldom.appendChild(menu4_node,
xmldom.makeNode(item_elmt));
--开始循环地四层AP下的ED--------------
OPEN C_ED4;
LOOP
FETCH C_ED4
INTO V_ED_MAC4, V_ED_NAME, V_ED_STA, V_STATU;
EXIT WHEN C_ED4%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_ED_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_ED_MAC4);
-- xmldom.setAttribute(item_elmt, 'IP', V_ED_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'ED');
xmldom.setAttribute(item_elmt, 'STAT', V_ED_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', '');
xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC4);
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu6_node := xmldom.appendChild(menu5_node,
xmldom.makeNode(item_elmt));
END LOOP;
CLOSE C_ED4;
END LOOP;
CLOSE C_AP4;
--开始循环地三层AP下的ED--------------
OPEN C_ED3;
LOOP
FETCH C_ED3
INTO V_ED_MAC3, V_ED_NAME, V_ED_STA, V_STATU;
EXIT WHEN C_ED3%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_ED_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_ED_MAC3);
-- xmldom.setAttribute(item_elmt, 'IP', V_ED_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'ED');
xmldom.setAttribute(item_elmt, 'STAT', V_ED_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', '');
xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC3);
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu5_node := xmldom.appendChild(menu4_node,
xmldom.makeNode(item_elmt));
END LOOP;
CLOSE C_ED3;
END LOOP;
CLOSE C_AP3;
--开始循环第二层ap下的ED--------------
OPEN C_ED2;
LOOP
FETCH C_ED2
INTO V_ED_MAC2, V_ED_NAME, V_ED_STA, V_STATU;
EXIT WHEN C_ED2%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_ED_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_ED_MAC2);
-- xmldom.setAttribute(item_elmt, 'IP', V_ED_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'ED');
xmldom.setAttribute(item_elmt, 'STAT', V_ED_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', '');
xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC2);
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu4_node := xmldom.appendChild(menu3_node,
xmldom.makeNode(item_elmt));
END LOOP;
CLOSE C_ED2;
END LOOP;
CLOSE C_AP2;
--开始循环第一层ap下的ED--------------
OPEN C_ED;
LOOP
FETCH C_ED
INTO V_ED_MAC, V_ED_NAME, V_ED_STA, V_STATU;
EXIT WHEN C_ED%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_ED_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_ED_MAC);
-- xmldom.setAttribute(item_elmt, 'IP', V_ED_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'ED');
xmldom.setAttribute(item_elmt, 'STAT', V_ED_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', '');
xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC);
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu3_node := xmldom.appendChild(menu2_node,
xmldom.makeNode(item_elmt));
END LOOP;
CLOSE C_ED;
END LOOP;
CLOSE C_AP1;
--完成第一层AP循环
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', '无拓扑关系设备');
xmldom.setAttribute(item_elmt, 'MAC', V_CP_MAC);
menu2_node := xmldom.appendChild(menu1_node,
xmldom.makeNode(item_elmt));
OPEN AP_ONLY;
LOOP
FETCH AP_ONLY
INTO V_AP_MAC, V_AP_IP, V_AP_NAME, V_AP_STA, V_AP_REL_STA, V_STATU;
EXIT WHEN AP_ONLY%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_AP_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_AP_MAC);
xmldom.setAttribute(item_elmt, 'IP', V_AP_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'AP');
xmldom.setAttribute(item_elmt, 'STAT', V_AP_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', V_AP_REL_STA);
xmldom.setAttribute(item_elmt, 'P_ID', '');
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu3_node := xmldom.appendChild(menu2_node,
xmldom.makeNode(item_elmt));
END LOOP;
CLOSE AP_ONLY;
OPEN ED_ONLY;
LOOP
FETCH ED_ONLY
INTO V_ED_MAC4, V_ED_NAME, V_ED_STA, V_STATU;
EXIT WHEN ED_ONLY%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_ED_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_ED_MAC4);
-- xmldom.setAttribute(item_elmt, 'IP', V_ED_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'ED');
xmldom.setAttribute(item_elmt, 'STAT', V_ED_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', '');
xmldom.setAttribute(item_elmt, 'P_ID', '');
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu3_node := xmldom.appendChild(menu2_node,
xmldom.makeNode(item_elmt));
END LOOP;
CLOSE ED_ONLY;
--完成cp的循环
END LOOP;
CLOSE C_CP;
--
--
dbms_lob.createtemporary(xml_clob, TRUE);
xmldom.writeToClob(root_node, xml_clob);
xmldom.freeDocument(doc);
OPEN P_CUR FOR
SELECT replace(xml_clob, ''', '') out_xml FROM dual;
END QU_NET_DEVICE_XML;
说明:通过CP名称,模糊查询传感网络设备树状结构
输入:CP名称
输出:xml树状结构。节点字段如下
*/
PROCEDURE QU_NET_DEVICE_XML(P_CP_NAME VARCHAR2, P_CUR OUT SYS_REFCURSOR) IS
V_CP_MAC VARCHAR2(20);
V_CP_NAME VARCHAR2(40);
V_CP_IP VARCHAR2(20);
/* V_P_MAC VARCHAR2(20);*/
V_CP_STA VARCHAR2(10);
V_CP_REL_STA VARCHAR2(10);
V_AP_MAC VARCHAR2(20);
V_AP_NAME VARCHAR2(40);
V_AP_IP VARCHAR2(20);
V_AP_STA VARCHAR2(10);
V_AP_REL_STA VARCHAR2(10);
V_STATU VARCHAR2(20); --(0确认,1未确认,2待确认,3待确认失败)
V_AP_MAC2 VARCHAR2(20);
V_AP_MAC3 VARCHAR2(20);
V_AP_MAC4 VARCHAR2(20);
V_ED_MAC VARCHAR2(20);
V_ED_NAME VARCHAR2(40);
V_ED_STA VARCHAR2(10);
V_ED_MAC2 VARCHAR2(20);
V_ED_MAC3 VARCHAR2(20);
V_ED_MAC4 VARCHAR2(20);
xml_clob CLOB;
root_node xmldom.DOMNode;
menu1_node xmldom.DOMNode;
menu2_node xmldom.DOMNode;
menu3_node xmldom.DOMNode;
menu4_node xmldom.DOMNode;
menu5_node xmldom.DOMNode;
menu6_node xmldom.DOMNode;
doc xmldom.DOMDocument;
item_elmt xmldom.DOMElement;
CURSOR C_CP IS
SELECT T.CP_MAC, T.CP_IP, T.CP_NAME, T.STATE, T.REL_STAT, T.STATUS
FROM V_REAL_CP T
WHERE T.CP_NAME LIKE '%' || P_CP_NAME || '%'
ORDER BY T.CP_NAME;
CURSOR C_AP1 IS
SELECT T.AP_MAC,
T.AP_NETWORK,
T.AP_NAME,
T.STATE,
T.REL_STAT,
T.STATUS
FROM V_REAL_AP T
WHERE T.BEL_ID = V_CP_MAC
AND T.BEL_TYPE = 1
ORDER BY T.AP_NAME;
CURSOR C_AP2 IS
SELECT T.AP_MAC,
T.AP_NETWORK,
T.AP_NAME,
T.STATE,
T.REL_STAT,
T.STATUS
FROM V_REAL_AP T
WHERE T.BEL_ID = V_AP_MAC
AND T.BEL_TYPE = 2
ORDER BY T.AP_NAME;
CURSOR C_AP3 IS
SELECT T.AP_MAC,
T.AP_NETWORK,
T.AP_NAME,
T.STATE,
T.REL_STAT,
T.STATUS
FROM V_REAL_AP T
WHERE T.BEL_ID = V_AP_MAC2
AND T.BEL_TYPE = 2
ORDER BY T.AP_NAME;
CURSOR C_AP4 IS
SELECT T.AP_MAC,
T.AP_NETWORK,
T.AP_NAME,
T.STATE,
T.REL_STAT,
T.STATUS
FROM V_REAL_AP T
WHERE T.BEL_ID = V_AP_MAC3
AND T.BEL_TYPE = 2
ORDER BY T.AP_NAME;
CURSOR C_ED IS
SELECT T.ED_MAC, T.ED_NAME, T.STATE, T.STATUS
FROM V_REAL_ED T
WHERE T.AP_ID = V_AP_MAC
ORDER BY T.ED_NAME;
CURSOR C_ED2 IS
SELECT T.ED_MAC, T.ED_NAME, T.STATE, T.STATUS
FROM V_REAL_ED T
WHERE T.AP_ID = V_AP_MAC2
ORDER BY T.ED_NAME;
CURSOR C_ED3 IS
SELECT T.ED_MAC, T.ED_NAME, T.STATE, T.STATUS
FROM V_REAL_ED T
WHERE T.AP_ID = V_AP_MAC3
ORDER BY T.ED_NAME;
CURSOR C_ED4 IS
SELECT T.ED_MAC, T.ED_NAME, T.STATE, T.STATUS
FROM V_REAL_ED T
WHERE T.AP_ID = V_AP_MAC4
ORDER BY T.ED_NAME;
CURSOR AP_ONLY IS
SELECT T.AP_MAC,
T.AP_NETWORK,
T.AP_NAME,
T.STATE,
T.REL_STAT,
T.STATUS
FROM V_REAL_AP T
WHERE T.EQU_RELATION = 2
AND T.BEL_ID = V_CP_MAC
ORDER BY T.AP_NAME;
CURSOR ED_ONLY IS
SELECT T.ED_MAC, T.ED_NAME, T.STATE, T.STATUS
FROM V_REAL_ED T
WHERE T.EQU_RELATION = 2
AND T.AP_ID = V_CP_MAC
ORDER BY T.ED_NAME;
BEGIN
--初始化xml
doc := xmldom.newDOMDocument();
root_node := xmldom.makeNode(doc);
item_elmt := xmldom.createElement(doc, 'Root');
root_node := xmldom.appendChild(root_node, xmldom.makeNode(item_elmt));
--循环cp---------------------------
OPEN C_CP;
LOOP
FETCH C_CP
INTO V_CP_MAC, V_CP_IP, V_CP_NAME, V_CP_STA, V_CP_REL_STA, V_STATU;
EXIT WHEN C_CP%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_CP_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_CP_MAC);
xmldom.setAttribute(item_elmt, 'IP', V_CP_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'CP');
xmldom.setAttribute(item_elmt, 'STAT', V_CP_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', V_CP_REL_STA);
xmldom.setAttribute(item_elmt, 'P_ID', '');
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu1_node := xmldom.appendChild(root_node,
xmldom.makeNode(item_elmt));
--开始循环第一层AP
OPEN C_AP1;
LOOP
FETCH C_AP1
INTO V_AP_MAC, V_AP_IP, V_AP_NAME, V_AP_STA, V_AP_REL_STA, V_STATU;
EXIT WHEN C_AP1%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_AP_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_AP_MAC);
xmldom.setAttribute(item_elmt, 'IP', V_AP_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'AP');
xmldom.setAttribute(item_elmt, 'STAT', V_AP_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', V_AP_REL_STA);
xmldom.setAttribute(item_elmt, 'P_ID', V_CP_MAC);
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu2_node := xmldom.appendChild(menu1_node,
xmldom.makeNode(item_elmt));
--开始循环第二层AP
OPEN C_AP2;
LOOP
FETCH C_AP2
INTO V_AP_MAC2, V_AP_IP, V_AP_NAME, V_AP_STA, V_AP_REL_STA, V_STATU;
EXIT WHEN C_AP2%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_AP_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_AP_MAC2);
xmldom.setAttribute(item_elmt, 'IP', V_AP_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'AP');
xmldom.setAttribute(item_elmt, 'STAT', V_AP_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', V_AP_REL_STA);
xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC);
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu3_node := xmldom.appendChild(menu2_node,
xmldom.makeNode(item_elmt));
--开始循环第三层AP
OPEN C_AP3;
LOOP
FETCH C_AP3
INTO V_AP_MAC3, V_AP_IP, V_AP_NAME, V_AP_STA, V_AP_REL_STA, V_STATU;
EXIT WHEN C_AP3%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_AP_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_AP_MAC3);
xmldom.setAttribute(item_elmt, 'IP', V_AP_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'AP');
xmldom.setAttribute(item_elmt, 'STAT', V_AP_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', V_AP_REL_STA);
xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC2);
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu4_node := xmldom.appendChild(menu3_node,
xmldom.makeNode(item_elmt));
--开始循环第四层AP
OPEN C_AP4;
LOOP
FETCH C_AP4
INTO V_AP_MAC4, V_AP_IP, V_AP_NAME, V_AP_STA, V_AP_REL_STA, V_STATU;
EXIT WHEN C_AP4%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_AP_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_AP_MAC4);
xmldom.setAttribute(item_elmt, 'IP', V_AP_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'AP');
xmldom.setAttribute(item_elmt, 'STAT', V_AP_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', V_AP_REL_STA);
xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC3);
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu5_node := xmldom.appendChild(menu4_node,
xmldom.makeNode(item_elmt));
--开始循环地四层AP下的ED--------------
OPEN C_ED4;
LOOP
FETCH C_ED4
INTO V_ED_MAC4, V_ED_NAME, V_ED_STA, V_STATU;
EXIT WHEN C_ED4%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_ED_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_ED_MAC4);
-- xmldom.setAttribute(item_elmt, 'IP', V_ED_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'ED');
xmldom.setAttribute(item_elmt, 'STAT', V_ED_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', '');
xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC4);
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu6_node := xmldom.appendChild(menu5_node,
xmldom.makeNode(item_elmt));
END LOOP;
CLOSE C_ED4;
END LOOP;
CLOSE C_AP4;
--开始循环地三层AP下的ED--------------
OPEN C_ED3;
LOOP
FETCH C_ED3
INTO V_ED_MAC3, V_ED_NAME, V_ED_STA, V_STATU;
EXIT WHEN C_ED3%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_ED_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_ED_MAC3);
-- xmldom.setAttribute(item_elmt, 'IP', V_ED_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'ED');
xmldom.setAttribute(item_elmt, 'STAT', V_ED_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', '');
xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC3);
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu5_node := xmldom.appendChild(menu4_node,
xmldom.makeNode(item_elmt));
END LOOP;
CLOSE C_ED3;
END LOOP;
CLOSE C_AP3;
--开始循环第二层ap下的ED--------------
OPEN C_ED2;
LOOP
FETCH C_ED2
INTO V_ED_MAC2, V_ED_NAME, V_ED_STA, V_STATU;
EXIT WHEN C_ED2%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_ED_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_ED_MAC2);
-- xmldom.setAttribute(item_elmt, 'IP', V_ED_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'ED');
xmldom.setAttribute(item_elmt, 'STAT', V_ED_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', '');
xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC2);
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu4_node := xmldom.appendChild(menu3_node,
xmldom.makeNode(item_elmt));
END LOOP;
CLOSE C_ED2;
END LOOP;
CLOSE C_AP2;
--开始循环第一层ap下的ED--------------
OPEN C_ED;
LOOP
FETCH C_ED
INTO V_ED_MAC, V_ED_NAME, V_ED_STA, V_STATU;
EXIT WHEN C_ED%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_ED_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_ED_MAC);
-- xmldom.setAttribute(item_elmt, 'IP', V_ED_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'ED');
xmldom.setAttribute(item_elmt, 'STAT', V_ED_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', '');
xmldom.setAttribute(item_elmt, 'P_ID', V_AP_MAC);
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu3_node := xmldom.appendChild(menu2_node,
xmldom.makeNode(item_elmt));
END LOOP;
CLOSE C_ED;
END LOOP;
CLOSE C_AP1;
--完成第一层AP循环
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', '无拓扑关系设备');
xmldom.setAttribute(item_elmt, 'MAC', V_CP_MAC);
menu2_node := xmldom.appendChild(menu1_node,
xmldom.makeNode(item_elmt));
OPEN AP_ONLY;
LOOP
FETCH AP_ONLY
INTO V_AP_MAC, V_AP_IP, V_AP_NAME, V_AP_STA, V_AP_REL_STA, V_STATU;
EXIT WHEN AP_ONLY%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_AP_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_AP_MAC);
xmldom.setAttribute(item_elmt, 'IP', V_AP_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'AP');
xmldom.setAttribute(item_elmt, 'STAT', V_AP_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', V_AP_REL_STA);
xmldom.setAttribute(item_elmt, 'P_ID', '');
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu3_node := xmldom.appendChild(menu2_node,
xmldom.makeNode(item_elmt));
END LOOP;
CLOSE AP_ONLY;
OPEN ED_ONLY;
LOOP
FETCH ED_ONLY
INTO V_ED_MAC4, V_ED_NAME, V_ED_STA, V_STATU;
EXIT WHEN ED_ONLY%NOTFOUND;
item_elmt := xmldom.createElement(doc, 'DEVICE');
xmldom.setAttribute(item_elmt, 'label', V_ED_NAME);
xmldom.setAttribute(item_elmt, 'MAC', V_ED_MAC4);
-- xmldom.setAttribute(item_elmt, 'IP', V_ED_IP);
xmldom.setAttribute(item_elmt, 'TYPE', 'ED');
xmldom.setAttribute(item_elmt, 'STAT', V_ED_STA);
xmldom.setAttribute(item_elmt, 'REL_STAT', '');
xmldom.setAttribute(item_elmt, 'P_ID', '');
xmldom.setAttribute(item_elmt, 'STATUS', V_STATU);
xmldom.setAttribute(item_elmt, 'CHECKED', 'false');
xmldom.setAttribute(item_elmt, 'FLAG', 1);
menu3_node := xmldom.appendChild(menu2_node,
xmldom.makeNode(item_elmt));
END LOOP;
CLOSE ED_ONLY;
--完成cp的循环
END LOOP;
CLOSE C_CP;
--
--
dbms_lob.createtemporary(xml_clob, TRUE);
xmldom.writeToClob(root_node, xml_clob);
xmldom.freeDocument(doc);
OPEN P_CUR FOR
SELECT replace(xml_clob, ''', '') out_xml FROM dual;
END QU_NET_DEVICE_XML;