The MySQL stored procedure realizes the infinite classification

DROP TABLE IF EXISTS ' Pcms_channel ';

CREATE TABLE IF not EXISTS ' Pcms_channel ' (

' CID ' tinyint (3) unsigned not NULL auto_increment,

' Name ' char ' not NULL COMMENT ' channel name ',

' ParentID ' tinyint (4) not NULL COMMENT ' parent id ',

' LfT ' tinyint (4) Not NULL COMMENT ' left value ',

' RGT ' tinyint (4) Not NULL COMMENT ' right value ',

' LV ' tinyint (3) unsigned not NULL default ' 0 ' COMMENT ' tier ',

' Themeid ' tinyint (3) unsigned not NULL default ' 1 ' COMMENT ' uses the subject's ID ',

PRIMARY KEY (' CID '),

KEY ' ParentID ' (' ParentID ', ' lft ', ' RGT ')

) Engine=innodb DEFAULT Charset=utf8 auto_increment=7;

--

--Export the data in the table ' Pcms_channel '

--

INSERT into ' pcms_channel ' (' CID ', ' name ', ' ParentID ', ' lft ', ' RGT ', ' lv ', ' Themeid ') VALUES

(1, ' Phpoocms ', 0, 1, 12, 0, 1),

(2, ' Test ', 1, 2, 7, 1, 1),

(3, ' Te ', 2, 3, 6, 2, 1),

(4, ' tes ', 1, 8, 9, 1, 1),

(5, ' DD ', 3, 4, 5, 3, 1),

(6, ' fromphp ', 1, 10, 11, 1, 1);
Copy Code emptying table record TRUNCATE table ' Pcms_channel '
Copy code to add nodes to the specified node DROP PROCEDURE IF EXISTS addchannel//

CREATE PROCEDURE AddChannel (in PID int,in name varchar (20))

BEGIN

DECLARE PR int;/* Right value * *

DECLARE LVV int;/* Level *

DECLARE AFF int;/* Counter * *

DECLARE af INT DEFAULT 0;

SET @result = null;

SELECT ' RGT ', ' LV ' into PR,LVV from ' Pcms_channel ' WHERE ' cid ' = pid;

IF PR THEN

START TRANSACTION;

UPDATE ' Pcms_channel ' SET ' lft ' = ' lft ' +2 WHERE ' lft ' >pr;

SELECT Row_count () into AFF;

SET af = AFF+AF;

UPDATE ' Pcms_channel ' SET ' rgt ' = ' rgt ' +2 WHERE ' RGT ' >=pr;

SELECT Row_count () into AFF;

SET af = AFF+AF;

INSERT into ' pcms_channel ' (' name ', ' ParentID ', ' lft ', ' RGT ', ' LV ') VALUES (name,pid,pr,pr+1,lvv+1);

SELECT Row_count () into AFF;

SET af = AFF+AF;

IF af >= 2 THEN

COMMIT;

SET @result = 1000;

SELECT 1000 as result;

ELSE

ROLLBACK;

SET @result = 1002;

SELECT 1002 as result;

End IF;

ELSE

SET @result = 1001;

SELECT 1001 as result;

End IF;

end//

Call AddChannel (5, "AA")//
Copy code deletes the specified node drop PROCEDURE IF EXISTS delchannel//

CREATE PROCEDURE Delchannel (in PID int)

BEGIN

DECLARE PL INT;

DECLARE PN INT;

DECLARE AFF INT;

DECLARE af INT DEFAULT 0;

SET @result = null;

SET @parentid = null;

SET @name = null;

SELECT A. ' LfT ', Ifnull (COUNT (b. ' CID '), 0), a. ' ParentID ', A. ' Name ' into the PL,PN, @parentid, @name from ' Pcms_channel ' as a left JOIN ' Pcms_channel ' as B on a. ' CID ' =b ' ParentID ' WHERE A. ' CID ' =pid GROUP by B. ' ParentID ';

IF PL&&!PN THEN

IF pl!=1 THEN

START TRANSACTION;

UPDATE ' Pcms_channel ' SET ' lft ' = ' lft '-2 WHERE ' LfT ' >pl;

SELECT Row_count () into AFF;

SET af = AFF+AF;

UPDATE ' Pcms_channel ' SET ' rgt ' = ' RGT '-2 WHERE ' RGT ' >pl;

SELECT Row_count () into AFF;

SET af = AFF+AF;

DELETE from ' pcms_channel ' WHERE ' cid ' = pid;

SELECT Row_count () into AFF;

SET af = AFF+AF;

IF af >= 2 THEN

COMMIT;

SET @result = 1000;

SELECT 1000 as result;

ELSE

ROLLBACK;

SET @result = 1002;

SELECT 1002 as result;

End IF;

ELSE

SET @result = 1004;

SELECT 1004 as result;

End IF;

ELSEIF PN&&PL THEN

SET @result = 1003;

SELECT 1003 as result;

ELSE

SET @result = 1001;

SELECT 1001 as result;

End IF;

end//

Call Delchannel (1)//
Copy Code move node DROP PROCEDURE IF EXISTS movechannel//

CREATE PROCEDURE Movechannel (PID Int,tid int)

BEGIN

IF pid=1 THEN

SELECT 1004 as result;

ELSE

IF Pid!=tid THEN

Call Delchannel (PID);

IF @result =1000 THEN

Call AddChannel (Tid, @name);

IF @result THEN

SELECT 1000 as result;

ELSE

Call AddChannel (@parentid, @name);

SELECT @result as result;

End IF;

ELSE

SELECT @result as result;

End IF;

ELSE

SELECT 1005 as result;

End IF;

End IF;

SET @result =null;

SET @parentid =null;

SET @name =null;

end//

Call Movechannel (1,1)//
Copy code query out tree structure Select Cid,concat (Repeat ("", LV)) from Pcms_channel