树形菜单 的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