oracle 邻接模型,MySQL存储过程实现Oracle邻接模型树形处理的方法实例

数据库对层次结构的处理模型有好多种，可以根据自己的需求来设计模型，当然最简单的也是最容易设计的模型就是所谓的邻接模型。在这方面，其他数据库比如Oracle 提供了现成的分析方法 connect by，而MySQL在这方面就显得有些薄弱了。 不过可以用MySQL的存储过程实现ORACLE类似的分析功能

这样，先来创建一个简单的数表。

代码如下

create table country ( id number(2) not null, name varchar(60) not null);

create table country_relation (id number(2), parentid number(2));

插入一些数据

代码如下

-- Table country.

insert into country (id,name) values (0,'Earth');

insert into country (id,name) values (2,'North America');

insert into country (id,name) values (3,'South America');

insert into country (id,name) values (4,'Europe');

insert into country (id,name) values (5,'Asia');

insert into country (id,name) values (6,'Africa');

insert into country (id,name) values (7,'Australia');

insert into country (id,name) values (8,'Canada');

insert into country (id,name) values (9,'Central America');

insert into country (id,name) values (10,'Island Nations');

insert into country (id,name) values (11,'United States');

insert into country (id,name) values (12,'Alabama');

insert into country (id,name) values (13,'Alaska');

insert into country (id,name) values (14,'Arizona');

insert into country (id,name) values (15,'Arkansas');

insert into country (id,name) values (16,'California');

-- Table country_relation.

insert into country_relation (id,parentid) values (0,NULL);

insert into country_relation (id,parentid) values (2,0);

insert into country_relation (id,parentid) values (3,0);

insert into country_relation (id,parentid) values (4,0);

insert into country_relation (id,parentid) values (5,0);

insert into country_relation (id,parentid) values (6,0);

insert into country_relation (id,parentid) values (7,0);

insert into country_relation (id,parentid) values (8,2);

insert into country_relation (id,parentid) values (9,2);

insert into country_relation (id,parentid) values (10,2);

insert into country_relation (id,parentid) values (11,2);

insert into country_relation (id,parentid) values (12,11);

insert into country_relation (id,parentid) values (13,11);

insert into country_relation (id,parentid) values (14,11);

insert into country_relation (id,parentid) values (15,11);

insert into country_relation (id,parentid) values (16,11);

在Oracle 里面，对这些操作就比较简单了，都是系统提供的。

比如下面四种情形：

1). 查看深度，

代码如下

select max(level) "level" from COUNTRY_RELATION a start with a.parentid is NULL

connect by PRIOR a.id = a.PARENTID

order by level;

level

----------

4

已用时间: 00: 00: 00.03

2). 查看叶子节点

代码如下

select name from

(

select b.name, connect_by_isleaf "isleaf"

from COUNTRY_RELATION a inner join country b on (a.id = b.id)

start with a.parentid is NULL connect by prior a.id = a.PARENTID

) T where T."isleaf" = 1;

NAME

--------------------------------------------------

Canada

Central America

Island Nations

Alabama

Alaska

Arizona

Arkansas

California

South America

Europe

Asia

Africa

Australia

已选择13行。

已用时间: 00: 00: 00.01

3) 查看ROOT节点

代码如下

select connect_by_root b.name

from COUNTRY_RELATION a inner join country b on (a.id = b.id)

start with a.parentid is NULL connect by a.id = a.PARENTID

CONNECT_BY_ROOTB.NAME

--------------------------------------------------

Earth

已用时间: 00: 00: 00.01

4). 查看路径

代码如下

select sys_connect_by_path(b.name,'/') "path"

from COUNTRY_RELATION a inner join country b on (a.id = b.id)

start with a.parentid is NULL connect by prior a.id = a.PARENTID

order by level,a.id;

path

--------------------------------------------------

/Earth

/Earth/North America

/Earth/South America

/Earth/Europe

/Earth/Asia

/Earth/Africa

/Earth/Australia

/Earth/North America/Canada

/Earth/North America/Central America

/Earth/North America/Island Nations

/Earth/North America/United States

/Earth/North America/United States/Alabama

/Earth/North America/United States/Alaska

/Earth/North America/United States/Arizona

/Earth/North America/United States/Arkansas

/Earth/North America/United States/California

已选择16行。

已用时间: 00: 00: 00.01

接下来我们看看在MySQL 里面如何实现上面四种情形：

前三种都比较简单，可以很容易写出SQL。

1)查看深度

代码如下

mysql> SELECT COUNT(DISTINCT IFNULL(parentid,-1)) AS LEVEL FROM country_relation

;

+-------+

| LEVEL |

+-------+

| 4 |

+-------+

1 row in set (0.00 sec)

2)查看ROOT节点

代码如下

mysql> SELECT b.`name` AS root_node FROM

-> (

-> SELECT id FROM country_relation WHERE parentid IS NULL

-> ) AS a, country AS b WHERE a.id = b.id;

+-----------+

| root_node |

+-----------+

| Earth |

+-----------+

1 row in set (0.00 sec)

3).  查看叶子节点

代码如下

mysql> SELECT b.`name` AS leaf_node FROM

-> (

-> SELECT id FROM country_relation WHERE id NOT IN (SELECT IFNULL(parentid,

-1) FROM country_relation)

-> ) AS a, country AS b WHERE a.id = b.id;

+-----------------+

| leaf_node |

+-----------------+

| South America |

| Europe |

| Asia |

| Africa |

| Australia |

| Canada |

| Central America |

| Island Nations |

| Alabama |

| Alaska |

| Arizona |

| Arkansas |

| California |

+-----------------+

13 rows in set (0.00 sec)

mysql>

4) 查看路径

这一块没有简单的SQL实现，不过可以用MySQL的存储过程来实现同样的功能。

存储过程代码如下：

代码如下

DELIMITER $$

USE `t_girl`$$

DROP PROCEDURE IF EXISTS `sp_show_list`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_show_list`()

BEGIN

-- Created by ytt 2014/11/04.

-- Is equal to oracle's connect by syntax.

-- Body.

DROP TABLE IF EXISTS tmp_country_list;

CREATE TEMPORARY TABLE tmp_country_list (node_level INT UNSIGNED NOT NULL, node_path VARCHAR(1000) NOT NULL);

-- Get the root node.

INSERT INTO tmp_country_list SELECT 1, CONCAT('/',id) FROM country_relation WHERE parentid IS NULL;

-- Loop within all parent node.

cursor1:BEGIN

DECLARE done1 INT DEFAULT 0;

DECLARE i1 INT DEFAULT 1;

DECLARE v_parentid INT DEFAULT -1;

DECLARE v_node_path VARCHAR(1000) DEFAULT '';

DECLARE cr1 CURSOR FOR SELECT parentid FROM country_relation WHERE parentid IS NOT NULL GROUP BY parentid ORDER BY parentid ASC;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1;

OPEN cr1;

loop1:LOOP

FETCH cr1 INTO v_parentid;

IF done1 = 1 THEN

LEAVE loop1;

END IF;

SET i1 = i1 + 1;

label_path:BEGIN

DECLARE done2 INT DEFAULT 0;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = 1;

-- Get the upper path.

SELECT node_path FROM tmp_country_list WHERE node_level = i1 - 1 AND LOCATE(v_parentid,node_path) > 0 INTO v_node_path;

-- Escape the outer not found exception.

IF done2 = 1 THEN

SET done2 = 0;

END IF;

INSERT INTO tmp_country_list

SELECT i1,CONCAT(IFNULL(v_node_path,''),'/',id) FROM country_relation WHERE parentid = v_parentid;

END;

END LOOP;

CLOSE cr1;

END;

-- Update node's id to its real name.

update_name_label:BEGIN

DECLARE cnt INT DEFAULT 0;

DECLARE i2 INT DEFAULT 0;

SELECT MAX(node_level) FROM tmp_country_list INTO cnt;

WHILE i2 < cnt

DO

UPDATE tmp_country_list AS a, country AS b

SET a.node_path = REPLACE(a.node_path,CONCAT('/',b.id),CONCAT('/',b.name))

WHERE LOCATE(CONCAT('/',b.id),a.node_path) > 0;

SET i2 = i2 + 1;

END WHILE;

END;

SELECT node_path FROM tmp_country_list;

END$$

DELIMITER ;

调用结果：

代码如下

mysql> CALL sp_show_list();

+-----------------------------------------------+

| node_path |

+-----------------------------------------------+

| /Earth |

| /Earth/North America |

| /Earth/South America |

| /Earth/Europe |

| /Earth/Asia |

| /Earth/Africa |

| /Earth/Australia |

| /Earth/North America/Canada |

| /Earth/North America/Central America |

| /Earth/North America/Island Nations |

| /Earth/North America/United States |

| /Earth/North America/United States/Alabama |

| /Earth/North America/United States/Alaska |

| /Earth/North America/United States/Arizona |

| /Earth/North America/United States/Arkansas |

| /Earth/North America/United States/California |

+-----------------------------------------------+

16 rows in set (0.04 sec)

Query OK, 0 rows affected (0.08 sec)

mysql>