关于根据[节点]、[父节点]和[节点级别]生成html标记的树型脚本
0.建表语句无私奉上
-- Create tablecreate 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 );
CREATE OR REPLACE VIEW V_ORG_TREE_SUB ASSELECT 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';CREATE OR REPLACE VIEW V_ORG_TREE ASSELECT 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;
<ul id="org" style="display: none"><li><!-- 以下内容由SQL脚本直接查询 start--><c:forEach items="${orgTrees}" var="org">${org}</c:forEach><!-- 以上内容由SQL脚本直接查询 end--><!-- 匹配根节点的结束 --></ul></li></ul>