数据库递归查询组织树父节点

MySQL实现

DROP FUNCTION IF EXISTS getParentNode;
CREATE FUNCTION getParentNode(chdId INT)
RETURN VARCHAR(4000) CHARSET UTF8
BEGIN
DECLAR sTemp VARCHAR(4000);
DECLAR nodeName VARCHAR(100);
DECLAR parentId INT;
SET nodeName = '';
SET parentId = null;
SELECT node_name,parent_Id INTO nodeName,parentId FROM nms_node_tree WHERE id = chdId;
SET sTemp = CONCAT(nodeName,',',sTemp);
END WHILE;
RETURN sTemp;
END;

oracle实现

select 
listagg(trim(node_name),'/') 
within group(order by node_level asc) 
from nms_node_tree start with id = '123456' 
connect by nocycle 
prior parent_id = id;