用mysql用戶端在庫中建立函數queryOrgChildren(查找子節點)和queryOrgLevel(檢視目前節點在樹中的級别):
DROP FUNCTION IF EXISTS `queryOrgChildren`;
CREATE FUNCTION `queryOrgChildren`(id INT)
RETURNS varchar(2000)
BEGIN
DECLARE tempChd VARCHAR(2000);
SET tempChd = cast(id as char);
IF tempChd is not NULL THEN
SELECT group_concat(groupId) INTO tempChd FROM t_user_group TUG where TUG.PARENTUSERGROUPID=tempChd;
end if;
return tempChd;
END;
DROP FUNCTION IF EXISTS `queryOrgLevel`;
CREATE FUNCTION `queryOrgLevel`(id INT)
RETURNS int
BEGIN
DECLARE grouplevel int;
DECLARE tempPar int;
SET grouplevel=0,tempPar=0;
SELECT PARENTUSERGroupId INTO tempPar FROM t_user_group TUG where TUG.GROUPID=id;
WHILE tempPar != 0 DO
SET grouplevel=grouplevel+1;
SELECT PARENTUSERGroupId INTO tempPar FROM t_user_group TUG where TUG.GROUPID=tempPar;
END WHILE;
return grouplevel;
END;
當然使用者可根據上邊2個函數發揮,比如寫擷取所有子孫節點的函數和擷取所有父類節點的函數,有可能還會用到FIND_IN_SET(str,strlist)函數