添加:入参:IN nodename VARCHAR(50),IN pageUrl VARCHAR(50),IN type INT,IN pid INT
BEGIN
DECLARE declLID INT default -1;
DECLARE declRID INT default -1;
DECLARE declNID INT default 0;
DECLARE declTree INT default -1;
DECLARE declNkID INT default -1;
DECLARE declCNT INT default 0;
set declNkID=(select count(*)+1 from t_plat_admin_left_tree);
SET declCNT =(select count(*) from t_plat_admin_left_tree);
SELECT declNkID;
IF declCNT <=0 THEN
-- 根节点赋初值
SELECT declLID,declRID,declNID,declTree,declNkID,declCNT;
INSERT INTO t_plat_admin_left_tree(id,LID,RID,Tree,NodeName,fid,tp) VALUES (0,1,2,0,'根节点',-1,0) ;
else
BEGIN
IF pid = 0 then
SELECT tpal.RID into declRID FROM t_plat_admin_left_tree tpal WHERE ID=pid;
SET declNID=declRID/2;
UPDATE t_plat_admin_left_tree SET RID = RID + 2 WHERE id=0;
INSERT INTO t_plat_admin_left_tree(id,LID,RID,Tree,NodeName,fid,tp) VALUES (declNID,declRID,declRID + 1,1,NodeName,PID,TYPE) ;
ELSE
SELECT LID, RID, Tree + 1 INTO declLID,declRID,declTree
FROM t_plat_admin_left_tree WHERE ID = PID;
SET declNID=pid+(declRID-declLID+1)/2;
UPDATE t_plat_admin_left_tree SET RID = RID + 2 WHERE RID >= declRID ;
UPDATE t_plat_admin_left_tree SET LID = LID + 2 WHERE LID > declRID ;
UPDATE t_plat_admin_left_tree SET ID = ID + 1 WHERE LID > declRID;
UPDATE t_plat_admin_left_tree SET fID = fID + 1 WHERE fid> pid AND RID>declRID;
INSERT INTO t_plat_admin_left_tree(id,LID,RID,Tree,NodeName,fid,tp) VALUES (declNID,declRID,declRID + 1,Tree,NodeName,pid,type) ;
end if;
END;
end if;
SELECT declNkID ;
select * from t_plat_admin_left_tree;
END
删除:入参:IN `inid` int
BEGIN
DECLARE delcLID INT DEFAULT 0;
DECLARE delcRID INT DEFAULT 0;
DECLARE delcWID INT DEFAULT 0;
DECLARE delcDID INT DEFAULT 0;
DECLARE delcNUM int DEFAULT 0;
-- set @NUM=delcNUM;
-- select id,lid,rid,wid from t_plat_admin_left_tree tpaf where tpaf.id=id;
SELECT tpaf.ID, tpaf.LID, tpaf.RID, RID - LID + 1 into delcDID ,delcLID ,delcRID,delcWID FROM t_plat_admin_left_tree tpaf WHERE tpaf.ID = inid ;
IF delcDID != 0 THEN
select delcWID/2 into delcNUM;
SELECT delclid,delcrid,delcWID,inid;
SELECT * FROM t_plat_admin_left_tree WHERE LID BETWEEN delcLID AND delcRID;
DELETE FROM t_plat_admin_left_tree WHERE LID BETWEEN delcLID AND delcRID;
UPDATE t_plat_admin_left_tree SET RID = RID - delcWID WHERE RID > delcRID;
UPDATE t_plat_admin_left_tree SET LID = LID - delcWID WHERE LID > delcRID ;
update t_plat_admin_left_tree set ID=ID-delcNUM where ID>inid;
update t_plat_admin_left_tree set FID=FID-delcNUM where FID>delcID;
END IF;
SELECT delcDID ;
END
查询:入参:IN pid int ,IN delctree int
BEGIN
SELECT Z.ID,z.lid,z.rid,Z.Tree,Z.NodeName,z.fid,z.tp
FROM t_plat_admin_left_tree Z,t_plat_admin_left_tree P
WHERE P.ID=pid AND Z.LID>=P.LID AND Z.RID <=P.RID AND (delctree = 0 OR Z.Tree<=P.Tree+delctree)
ORDER BY Z.LID ASC;
END