欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

关于根据[节点]、[父节点]和[节点级别]生成html标记的树型脚本

程序员文章站 2022-04-14 11:20:51
...
[b][u]0.建表语句无私奉上[/u][/b]

-- Create table
create table M_ORG
(
ORG_ID VARCHAR2(32) not null,
SHORT_NAME VARCHAR2(50),
NAME VARCHAR2(100) not null,
FULL_ID VARCHAR2(320),
FULL_NAME VARCHAR2(500) not null,
ENAME VARCHAR2(200),
ORG_CLASS CHAR(1) not null,
BRANCH_LEVEL INTEGER,
NODE_URL VARCHAR2(100) default '#',
IS_LAST_NODE CHAR(1),
PARENT_ORG_ID VARCHAR2(32),
BRANCH_INDEX INTEGER,
LOGO VARCHAR2(100),
DESC_INFO VARCHAR2(1000),
COM_ENABLE CHAR(1) default 1,
COM_DEL_FLG CHAR(1) default 0,
COM_ADD_DATE DATE,
COM_UPD_DATE DATE,
COM_DEL_DATE DATE,
COM_ADD_USER_ID VARCHAR2(32),
COM_UPD_USER_ID VARCHAR2(32),
COM_DEL_USER_ID VARCHAR2(32)
)
tablespace CAH_SMS_TBS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table M_ORG
is '组织机构表。

用于管理组织机构图 organization chart(组织机构树形图)。
存储组织机构树所有节点信息。

组织机构ID规则说明:
1.一级组织由001开始;二级组织由001001开始;依次类推。
2.由于组织机构ID长度限制,最多支持到10级机构。';
-- Add comments to the columns
comment on column M_ORG.ORG_ID
is '组织机构ID(根据类别不同标示为不同单位部门职务的ID)';
comment on column M_ORG.SHORT_NAME
is '短称';
comment on column M_ORG.NAME
is '组织机构中文名称(机构树节点显示用)';
comment on column M_ORG.FULL_ID
is '全ID(#号链接)';
comment on column M_ORG.FULL_NAME
is '组织机构中文全称';
comment on column M_ORG.ENAME
is '组织机构英文名称';
comment on column M_ORG.ORG_CLASS
is '类别(1:单位;2:部门;3:职务)【数据字典:ORG_CLASS】';
comment on column M_ORG.BRANCH_LEVEL
is '级别';
comment on column M_ORG.NODE_URL
is '资源位置';
comment on column M_ORG.IS_LAST_NODE
is '是否末级(Y/N)【数据字典:YES_NO】';
comment on column M_ORG.PARENT_ORG_ID
is '上级组织机构ID';
comment on column M_ORG.BRANCH_INDEX
is '枝顺序';
comment on column M_ORG.LOGO
is '图标名称';
comment on column M_ORG.DESC_INFO
is '描述';
comment on column M_ORG.COM_ENABLE
is '是否可用(1:可用;0:不可用)';
comment on column M_ORG.COM_DEL_FLG
is '是否删除(1:已删除;0:未删除)';
comment on column M_ORG.COM_ADD_DATE
is '创建日期(yyyy-MM-dd hh:mm:ss)';
comment on column M_ORG.COM_UPD_DATE
is '更新日期(yyyy-MM-dd hh:mm:ss)';
comment on column M_ORG.COM_DEL_DATE
is '删除日期(yyyy-MM-dd hh:mm:ss)';
comment on column M_ORG.COM_ADD_USER_ID
is '创建者用户ID';
comment on column M_ORG.COM_UPD_USER_ID
is '更新者用户ID';
comment on column M_ORG.COM_DEL_USER_ID
is '删除者用户ID';
-- Create/Recreate primary, unique and foreign key constraints
alter table M_ORG
add constraint XPK组织机构表 primary key (ORG_ID)
using index
tablespace CAH_SMS_TBS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

[b][u]1.初步格式化组织机构树形结构[/u][/b]

CREATE OR REPLACE VIEW V_ORG_TREE_SUB AS
SELECT REPLACE(LPAD('|-', (LEVEL - 1) * 2), '|-', '<LI>') || NAME AS NAME,
ORG_ID,
PARENT_ORG_ID,
BRANCH_LEVEL,
SHORT_NAME,
FULL_ID,
FULL_NAME,
ENAME,
ORG_CLASS,
NODE_URL,
IS_LAST_NODE,
BRANCH_INDEX,
LOGO,
DESC_INFO
FROM CAHSMS_M_ORG -- 组织机构:单位>部门>职务
CONNECT BY PRIOR ORG_ID = PARENT_ORG_ID
START WITH ORG_ID = '001';

[b][u]2.加工完毕树形结构代码[/u][/b]

CREATE OR REPLACE VIEW V_ORG_TREE AS
SELECT CASE
WHEN ORG.ORG_ID = RN_NEXT.PARENT_ORG_ID THEN
ORG.NAME || '<ul>' -- 该节点是父节点
WHEN ORG.BRANCH_LEVEL - 1 > RN_NEXT.BRANCH_LEVEL OR
RN_NEXT.BRANCH_LEVEL IS NULL THEN
ORG.NAME || '</li> </ul></li>' ||
DECODE(ORG.BRANCH_LEVEL - 1 -
DECODE(RN_NEXT.BRANCH_LEVEL, NULL, 2, RN_NEXT.BRANCH_LEVEL),
1,
' </ul></li>',
2,
' </ul></li></ul></li>',
3,
' </ul></li></ul></li></ul></li>',
4,
' </ul></li></ul></li></ul></li></ul></li>',
5,
' </ul></li></ul></li></ul></li></ul></li></ul></li>',
6,
' </ul></li></ul></li></ul></li></ul></li></ul></li></ul></li>',
7,
' </ul></li></ul></li></ul></li></ul></li></ul></li></ul></li></ul></li>',
8,
' </ul></li></ul></li></ul></li></ul></li></ul></li></ul></li></ul></li></ul></li>',
9,
' </ul></li></ul></li></ul></li></ul></li></ul></li></ul></li></ul></li></ul></li></ul></li>'
, '') -- 与下个节点不在一棵树枝上
WHEN ORG.PARENT_ORG_ID = RN_NEXT.PARENT_ORG_ID THEN
ORG.NAME || '</li>' -- 【所在枝有两个以上节点】非末节点
WHEN ORG.PARENT_ORG_ID = RN_PREV.PARENT_ORG_ID AND
ORG.PARENT_ORG_ID != RN_NEXT.PARENT_ORG_ID THEN
ORG.NAME || '</li> </ul></li>' -- 【所在枝有两个以上节点】末节点
ELSE
ORG.NAME || '</li> </ul></li>'
END AS NAME,
ORG.ORG_ID,
ORG.PARENT_ORG_ID,
RN_PREV.PARENT_ORG_ID AS PARENT_ORG_ID_PREV, -- 上个节点的父节点ID
RN_NEXT.PARENT_ORG_ID AS PARENT_ORG_ID_NEXT, -- 下个节点的父节点ID
ORG.BRANCH_LEVEL AS BRANCH_LEVEL,
RN_PREV.BRANCH_LEVEL AS BRANCH_LEVEL_PREV, -- 上个节点所在枝级别
RN_NEXT.BRANCH_LEVEL AS BRANCH_LEVEL_NEXT, -- 下个节点所在枝级别
ORG.SHORT_NAME,
ORG.FULL_ID,
ORG.FULL_NAME,
ORG.ENAME,
ORG.ORG_CLASS,
ORG.NODE_URL,
ORG.IS_LAST_NODE,
ORG.BRANCH_INDEX,
ORG.LOGO,
ORG.DESC_INFO
FROM (SELECT SUB.*, ROWNUM AS RN FROM V_ORG_TREE_SUB SUB) ORG
LEFT JOIN (SELECT SUB.*, ROWNUM + 1 AS RN_PREV FROM V_ORG_TREE_SUB SUB) RN_PREV ON ORG.RN =
RN_PREV.RN_PREV
LEFT JOIN (SELECT SUB.*, ROWNUM - 1 AS RN_NEXT FROM V_ORG_TREE_SUB SUB) RN_NEXT ON ORG.RN =
RN_NEXT.RN_NEXT;



[b][u]3.视图数据挂接到jsp中[/u][/b]

<ul id="org" style="display: none">
<li>
<!-- 以下内容由SQL脚本直接查询 start-->
<c:forEach items="${orgTrees}" var="org">
${org}
</c:forEach>
<!-- 以上内容由SQL脚本直接查询 end-->

<!-- 匹配根节点的结束 -->
</ul>

</li>
</ul>


[color=darkred]------------------------------感情丰富的感谢线-----------------------------[/color]
Trees in Oracle SQL [url]http://philip.greenspun.com/sql/trees.html[/url]
jQuery Org Chart demo (注意:jOrgChart效果IE8+支持)[url]http://www.capricasoftware.co.uk/jquery/orgchart/orgchart.html[/url]
jQuery Org Chart Code [url]https://github.com/Tquila/jOrgChart[/url]

[color=gray]-------------后续报告--------------[/color]
jQuery Org Chart 已然修改了IE7的UBG [url]http://th3silverlining.com/2011/12/01/jquery-org-chart-a-plugin-for-visualising-data-in-a-tree-like-structure/[/url]
下载移步这里:[url]https://github.com/wesnolte/jOrgChart/downloads[/url]
相关标签: oracle org tree