oracle机构树方式展现,Oracle树形结构数据--前台Ztree显示

Oracle树形结构数据CONNECT BY PRIOR查询及Ztree前端页面数据展示

每本书基本都有目录，打开word也会出现目录。如树枝一样，目录下还有子目录和同级目录。如图所示

目录结构：图1

创建表：

CREATE TABLE Z_TEEE(

ID          NUMBER(8) NOT NULL,

P_ID  NUMBER(8) NOT NULL,

NAME        VARCHAR2(32) NOT NULL,

DEPTH      NUMBER(8) NOT NULL,

RANK        NUMBER(8) NOT NULL,

PRIMARY KEY (ID)

);

COMMENT ON TABLE Z_TEEE  IS '表面';

COMMENT ON COLUMN Z_TEEE.ID  IS '表ID';

COMMENT ON COLUMN Z_TEEE.P_ID  IS '父级ID';

COMMENT ON COLUMN Z_TEEE.NAME  IS 目录名称';

COMMENT ON COLUMN Z_TEEE.DEPTH  IS '菜单层级';

COMMENT ON COLUMN Z_TEEE.RANK  IS '菜单排序';

注意：CONNECT BY PRIOR主要依据ID与pid即目录ID与父级id进行查询，而前端ztree依靠id与pId进行识别，可以在setting 中进行设置。

插入数据：

insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (2,1,'目录1',1,3);

insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (3,1,'目录2',1,2);

insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (4,1,'目录3',1,1);

insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (5,2,'目录1.1',2,1);

insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (6,2,'目录1.2',2,2);

insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (7,2,'目录1.3',2,3);

insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (8,5,'目录1.1.1',3,1);

insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (9,5,'目录1.1.2',3,2);

insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (10,4,'目录3.1',2,1);

insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (11,4,'目录3.2',2,2);

查询结果：  SELECT * FROM Z_TREE ;

全部结果

树状查询：

SELECT *  FROM Z_TEEE  START WITH ID IN (SELECT ID FROM Z_TEEE WHERE p_id = 1)CONNECT BY PRIOR ID = p_id ORDER SIBLINGS BY RANK ;

树状结果

解释：p_id=1查询子目录为1的节点，在插入数据id是从2开始，使用pid为1，就可查询全部列表。此处是rank进行排序的结果，可以记录目录排序。depth字段为预留字段，可以去掉。同事很郁闷的是MySQL不支持 CONNECT BY PRIOR方法。

查询返回字符串如下：

[{"depth":1,"id":4,"name":"目录3","pId":1,"rank":1},

{"depth":2,"id":10,"name":"目录3.1","pId":4,"rank":1},

{"depth":2,"id":11,"name":"目录3.2","pId":4,"rank":2},

{"depth":1,"id":3,"name":"目录2","pId":1,"rank":2},

{"depth":1,"id":2,"name":"目录1","pId":1,"rank":3},

{"depth":2,"id":5,"name":"目录1.1","pId":2,"rank":1},

{"depth":3,"id":8,"name":"目录1.1.1","pId":5,"rank":1},

{"depth":3,"id":9,"name":"目录1.1.2","pId":5,"rank":2},

{"depth":2,"id":6,"name":"目录1.2","pId":2,"rank":2},

{"depth":2,"id":7,"name":"目录1.3","pId":2,"rank":3}]

该结构可以使用ztree简单模式即可如图1所示，tree可以查看ztree官网demo演示加载

ajax请求

var setting = {

data: {

simpleData: {

enable: true,//是否可编辑

idKey: "id",//当前目录id

pIdKey: "pId"//当前目录父类id，可以根据自己需求修改该值

},

key: {

name: "name"//目录名称，在前台显示字段

}

}

};

$(document).ready(function(){

var zNodes;

$.ajax({

type:"post",

url:"接口url",

data: { id: "1" },

dataType: "text",

ContentType: "application/json; charset=utf-8",

success: function (data) {

zNodes = data;

$.fn.zTree.init($("#ztree"), setting, eval('(' + zNodes + ')'));

},

error: function (msg) {

alert("失败");

}

});

});

即可将ID为ztree的div用ztree显示。