mysql存储过程的增删_树形菜单 的mysql 存储过程实现增删查



添加：入参：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