百分百源码网-让建站变得如此简单! 登录 注册 签到领金币!

主页 | 如何升级VIP | TAG标签

当前位置: 主页>网站教程>数据库> mysql存储过程实现无限分类
分享本文到:

mysql存储过程实现无限分类

发布时间:01/15 来源: 浏览: 关键词:
mysql存储过程实现无限分类

DROP TABLE IF EXISTS `pcms_channel`;

CREATE TABLE IF NOT EXISTS `pcms_channel` (

  `cid` tinyint(3) unsigned NOT NULL auto_increment,

  `name` char(10) NOT NULL COMMENT '频道名称',

  `parentid` tinyint(4) NOT NULL COMMENT '父级ID',

  `lft` tinyint(4) NOT NULL COMMENT '左值',

  `rgt` tinyint(4) NOT NULL COMMENT '右值',

  `lv` tinyint(3) unsigned NOT NULL default '0' COMMENT '级层',

  `themeid` tinyint(3) unsigned NOT NULL default '1' COMMENT '使用的主题的ID',

  PRIMARY KEY  (`cid`),

  KEY `parentid` (`parentid`,`lft`,`rgt`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--

-- 导出表中的数据 `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);
清空表记录 TRUNCATE TABLE `pcms_channel`
添加节点到指定节点 DROP PROCEDURE IF EXISTS addChannel//

create procedure addChannel (in pid int,in name varchar(20))

BEGIN

DECLARE pr INT;/*右值*/

DECLARE lvv INT;/*层级*/

DECLARE aff INT;/*计数器*/

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")//
删除指定节点 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 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)//
移动节点 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)//
查询出树状结构 select cid,concat(repeat(" ",lv)) from pcms_channel

责任编辑:
打赏

打赏

取消

感谢您的支持,我会继续努力的!

扫码支持
扫码打赏,你说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

百分百源码网 建议打赏1~10元,土豪随意,感谢您的阅读!

共有2人阅读,期待你的评论!发表评论
昵称: 网址: 验证码: 点击我更换图片
最新评论

本文标签

广告赞助

订阅获得更多模板