001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039

delimiter ~
DROP FUNCTION getPath_v2 ~

CREATE FUNCTION getPath_v2 (inID INT) RETURNS VARCHAR(300)  DETERMINISTIC

begin
	DECLARE gParentID INT DEFAULT 0;
	DECLARE gTitle VARCHAR(255) DEFAULT '';
	DECLARE gID INT DEFAULT 0;
	DECLARE gSubseq INT DEFAULT 0;
	DECLARE curSubseq INT DEFAULT 0;
	DECLARE curID INT DEFAULT 0;

	DECLARE subseqPath VARCHAR(255) DEFAULT '';
	DECLARE titlePath VARCHAR(255) DEFAULT '';
	DECLARE idPath VARCHAR(255) DEFAULT '';

	DECLARE returnValue VARCHAR(300) DEFAULT '';

	SELECT parent,sub_seq,name,id INTO gParentID,gSubseq,gTitle,gID FROM gubun_tree_v2 WHERE ID = inID;
		SET titlePath = gTitle ;
		SET idPath = CAST(gID AS CHAR(100));
		SET subseqPath = CONCAT(LPAD(gSubseq,3,'0'), '.', subseqPath);
		SET curSubseq = gSubseq;

	WHILE gParentID > 0 DO
		SELECT parent,sub_seq,name,id INTO gParentID,gSubseq,gTitle,gID FROM gubun_tree_v2 WHERE ID = gParentID LIMIT 1;
			SET titlePath = CONCAT(gTitle,'::',titlePath);
			SET idPath = CONCAT(CAST(gID AS CHAR(100)),'::',idPath);
			SET subseqPath = CONCAT(LPAD(gSubseq,3,'0'), '.', subseqPath);

	END WHILE;

	SET returnValue =  CONCAT(curSubseq,'|',subseqPath,'|',titlePath,'|',idPath) ;

	RETURN returnValue;
end~

delimiter ;