一个带存储过程的无限级分类数据库设计代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】一个带存储过程的无限级分类数据库设计
作者:Dezai.CN / 发布于2013/2/28/ 729

	数据库保存分类信息的表命名为：cate，其结构和测试用的记录分别如1.jpg和2.jpg
	-- ----------------------------
	-- Table structure for cate
	-- ----------------------------
	CREATE TABLE `cate` (
	`id` int(5) NOT NULL auto_increment,
	`name` char(255) NOT NULL,
	`parent_id` int(4) NOT NULL,
	`nocontext` tinyint(1) NOT NULL,
	`path` char(255) NOT NULL default '0',
	`admin_id` tinyint(2) NOT NULL,
	`editor_id` tinyint(2) NOT NULL,
	`view` char(255) NOT NULL,
	PRIMARY KEY (`id`)
	) ENGINE=InnoDB DEFAULT CHARSET=gbk;

	

	1、新增分类：
	-- ----------------------------
	-- Procedure structure for cate_insert
	-- ----------------------------
	DELIMITER ;;
	CREATE DEFINER=`root`@`localhost` PROCEDURE `cate_insert`(_name CHAR(255), _parent_id INTEGER(4), _nocontext TINYINT(1), _admin_id TINYINT(2),_editor_id TINYINT(2),_view CHAR(255))
	BEGIN
	DECLARE X_max_id int default 0;
	DECLARE X_parent_path char(255);
	DECLARE X_path char(255);
	select max(id) from cate into X_max_id ;
	IF _parent_id!=0 THEN
	select `path` from `cate` where `id`= _parent_id into X_parent_path;
	 SET X_path=CONCAT(X_parent_path,',',(X_max_id+1));
	ELSE
	 SET X_path=X_max_id+1;
	END IF;
	INSERT INTO `cate` ( `name`, `parent_id`, `nocontext`, `path`, `admin_id`, `editor_id`, `view`) VALUES ( _name, _parent_id, _nocontext,X_path, _admin_id, _editor_id, _view);
	END;;
	DELIMITER ;

	

	2、编辑分类
	-- ----------------------------
	-- Procedure structure for cate_update
	-- ----------------------------
	DELIMITER ;;
	CREATE DEFINER=`root`@`localhost` PROCEDURE `cate_update`(_id TINYINT(5),_name CHAR(255), _parent_id INTEGER(4), _nocontext TINYINT(1), _admin_id TINYINT(2),_editor_id TINYINT(2),_view CHAR(255))
	BEGIN
	DECLARE X_parent_path char(255);
	DECLARE X_path_old char(255);
	DECLARE X_path_new char(255);
	DECLARE X_parent_id_old INT;
	SELECT `path` from `cate` WHERE `id`=_id into X_path_old;
	IF _parent_id!=0 THEN
	 select `path` from `cate` where `id`= _parent_id into X_parent_path;
	 SET X_path_new = CONCAT(X_parent_path,',',_id);
	ELSE
	 SET X_path_new = _id;
	END IF;
	UPDATE `cate` set `name`=_name, `parent_id`=_parent_id, `nocontext`=_nocontext, `admin_id`=_admin_id, `editor_id`=_editor_id, `view`=_view WHERE `id`=_id;
	UPDATE `cate` set `path`=REPLACE(`path`,X_path_old,X_path_new) ;
	END;;
	DELIMITER ;

	

	3、取出所有分类
	-- ----------------------------
	-- Procedure structure for cate_selectAll
	-- ----------------------------
	DELIMITER ;;
	CREATE DEFINER=`root`@`localhost` PROCEDURE `cate_selectAll`()
	BEGIN
	SELECT * FROM `cate` ORDER BY `path`;
	END;;
	DELIMITER ;

	

	4、得到一个分类的路径
	-- ----------------------------
	-- Procedure structure for cate_getRoute
	-- ----------------------------
	DELIMITER ;;
	CREATE DEFINER=`root`@`localhost` PROCEDURE `cate_getRoute`(_id INTEGER(5))
	BEGIN
	DECLARE x_path char(255);
	select `path` from `cate` where `id`=_id into x_path;
	select * from `cate` where FIND_IN_SET(`id`,x_path) order by `path`;
	END;;
	DELIMITER ;

	

	5、取出同一级分类
	-- ----------------------------
	-- Procedure structure for cate_getBrother
	-- ----------------------------
	DELIMITER ;;
	CREATE DEFINER=`root`@`localhost` PROCEDURE `cate_getBrother`(_id INTEGER(5))
	BEGIN
	SELECT * FROM `cate` WHERE `parent_id`=(select `parent_id` from `cate` where `id`=_id ) order by `path`;
	END;;
	DELIMITER ;

	

	6、取出单个分类
	-- ----------------------------
	-- Procedure structure for cate_select
	-- ----------------------------
	DELIMITER ;;
	CREATE DEFINER=`root`@`localhost` PROCEDURE `cate_select`(_id INTEGER)
	BEGIN
	SELECT * FROM `cate` WHERE `id`=_id;
	END;;
	DELIMITER ;
	完
试试其它关键字
　无限级分类　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)