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

0.建表语句无私奉上

-- 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
  );

1.初步格式化组织机构树形结构

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';

2.加工完毕树形结构代码

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;

3.视图数据挂接到jsp中

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

			</li>
		</ul>

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

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