????????
?????????????????????????в?????????????α?????oracle????connect by??????mysql????????????????????MySQL????????????????????????????????????????洢??????????
????1?????????????????
DROP TABLE IF EXISTS csdn.channel;
CREATE TABLE csdn.channel (
id INT(11) NOT NULL AUTO_INCREMENT??
cname VARCHAR(200) DEFAULT NULL??
parent_id INT(11) DEFAULT NULL??
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT  INTO channel(id??cname??parent_id)
VALUES (13??'???'??-1)??
(14??'TV580'??-1)??
(15??'????580'??-1)??
(16??'???????'??13)??
(17??'???'??14)??
(18??'??????'??17);
DROP TABLE IF EXISTS channel;
????2?????????????????????????????mysql??UDF????????????
????2.1???????????±???????????????????????
-- pro_cre_childlist
DELIMITER
DROP PROCEDURE IF EXISTS csdn.pro_cre_childlist
CREATE PROCEDURE csdn.pro_cre_childlist(IN rootId INT??IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT id FROM channel WHERE parent_id=rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET max_sp_recursion_depth=12;
INSERT INTO tmpLst VALUES (NULL??rootId??nDepth);
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL pro_cre_childlist(b??nDepth+1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END
????2.2???????????????????????????????????
-- pro_cre_parentlist
DELIMITER
DROP PROCEDURE IF EXISTS csdn.pro_cre_parentlist
CREATE PROCEDURE csdn.pro_cre_parentlist(IN rootId INT??IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT parent_id FROM channel WHERE id=rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET max_sp_recursion_depth=12;
INSERT INTO tmpLst VALUES (NULL??rootId??nDepth);
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL pro_cre_parentlist(b??nDepth+1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END
????2.3?????????Oracle SYS_CONNECT_BY_PATH???????????????????id·??L
-- pro_cre_pathlist
DELIMITER
USE csdn
DROP PROCEDURE IF EXISTS pro_cre_pathlist
CREATE PROCEDURE pro_cre_pathlist(IN nid INT??IN delimit VARCHAR(10)??INOUT pathstr VARCHAR(1000))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE parentid INT DEFAULT 0;
DECLARE cur1 CURSOR FOR
SELECT t.parent_id??CONCAT(CAST(t.parent_id AS CHAR)??delimit??pathstr)
FROM channel AS t WHERE t.id = nid;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET max_sp_recursion_depth=12;
OPEN cur1;
FETCH cur1 INTO parentid??pathstr;
WHILE done=0 DO
CALL pro_cre_pathlist(parentid??delimit??pathstr);
FETCH cur1 INTO parentid??pathstr;
END WHILE;
CLOSE cur1;
END
DELIMITER ;
????2.4???????????????name·??
-- pro_cre_pnlist
DELIMITER
USE csdn
DROP PROCEDURE IF EXISTS pro_cre_pnlist
CREATE PROCEDURE pro_cre_pnlist(IN nid INT??IN delimit VARCHAR(10)??INOUT pathstr VARCHAR(1000))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE parentid INT DEFAULT 0;
DECLARE cur1 CURSOR FOR
SELECT t.parent_id??CONCAT(t.cname??delimit??pathstr)
FROM channel AS t WHERE t.id = nid;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET max_sp_recursion_depth=12;
OPEN cur1;
FETCH cur1 INTO parentid??pathstr;
WHILE done=0 DO
CALL pro_cre_pnlist(parentid??delimit??pathstr);
FETCH cur1 INTO parentid??pathstr;
END WHILE;
CLOSE cur1;
END
DELIMITER ;
????2.5?????ú??????id·??
????-- fn_tree_path
????DELIMITER
????DROP FUNCTION IF EXISTS csdn.fn_tree_path
????CREATE FUNCTION csdn.fn_tree_path(nid INT??delimit VARCHAR(10)) RETURNS VARCHAR(2000) CHARSET utf8
????BEGIN
????DECLARE pathid VARCHAR(1000);
????SET pathid=CAST(nid AS CHAR);
????CALL pro_cre_pathlist(nid??delimit??pathid);
????RETURN pathid;
????END