소스 파일명 : getpriority_proc.sql
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
delimiter ~
DROP FUNCTION getPriority~

CREATE FUNCTION getPriority (inID INT) RETURNS VARCHAR(300) CHARACTER SET utf8 DETERMINISTIC
begin
  DECLARE gParentID INT DEFAULT 0;
  DECLARE gTitle VARCHAR(255) DEFAULT '';
  DECLARE gPriority1 VARCHAR(255) DEFAULT '';
  DECLARE gPriority2 VARCHAR(255) DEFAULT '';
  DECLARE gPriority3 VARCHAR(255) DEFAULT '';
  DECLARE returnValue VARCHAR(255) DEFAULT '';
  DECLARE gChap INT DEFAULT 0;
  DECLARE gID INT DEFAULT 0;

  SET NAMES utf8;
  SELECT parent,chap,title,id INTO gParentID,gChap,gTitle,gID FROM km WHERE ID = inID;
  SET gPriority1 = LPAD(gChap,3,'0');
  SET gPriority2 = CONVERT(gTitle USING utf8);
  SET gPriority3 = CAST(gID AS CHAR(100));

  WHILE gParentID > 0 DO
    SELECT parent,chap,title,id INTO gParentID,gChap,gTitle,gID FROM km WHERE ID = gParentID LIMIT 1;
    SET gPriority1 = CONCAT(LPAD(gChap,3,'0'), '.', gPriority1);
    SET gPriority2 = CONCAT(CONVERT(gTitle USING utf8),'::',gPriority2);
    SET gPriority3 = CONCAT(CAST(gID AS CHAR(100)),'::',gPriority3);
  END WHILE;
  SET returnValue = CONVERT( CONCAT(gPriority1,'|',gPriority2,'|',gPriority3) USING utf8 );
  RETURN returnValue;
end~

delimiter ;