达梦数据库自定函数-树形结构遍历

达梦数据库自定函数（function）

树形结构遍历

CREATE OR REPLACE FUNCTION "TRSWCMV8"."getChannelPath"("chnlid" IN BIGINT)
RETURN VARCHAR(2000)
AUTHID DEFINER

AS
	/*变量说明部分*/
	parent_Chnlid BIGINT;
	chnl_path varchar(2000);
	chnlName varchar(400);

BEGIN
	/*执行体*/
	parent_Chnlid := chnlid;
 LOOP
  select  CHNLDESC into chnlName from WCMCHANNEL where CHANNELID = parent_Chnlid;
  select  PARENTID into parent_Chnlid from WCMCHANNEL where CHANNELID = parent_Chnlid;
  chnl_path := concat(chnlName,'/',chnl_path);
  if parent_Chnlid=0 then
   return left(chnl_path,char_length(chnl_path)-1);
  END IF;
  exit when parent_Chnlid = 0;
 END LOOP ;
return chnl_path;
END;