Oracle APEX基于Oracle表实现多级动态菜单技术求助

1. 菜单表结构 #

设计支持无限层级嵌套的表，包含核心字段用于菜单管理与排序：

CREATE TABLE APP_MENUS (
    MENU_ID          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    PARENT_MENU_ID   NUMBER REFERENCES APP_MENUS(MENU_ID), -- 父菜单ID，顶级菜单为NULL
    MENU_NAME        VARCHAR2(100) NOT NULL, -- 菜单显示名称
    MENU_ORDER       NUMBER NOT NULL, -- 同层级菜单的排序序号
    TARGET_URL       VARCHAR2(200), -- APEX页面路径或外部URL，格式如 'f?p=&APP_ID.:1:&APP_SESSION.'
    IS_VISIBLE       CHAR(1) DEFAULT 'Y' CHECK (IS_VISIBLE IN ('Y', 'N')) -- 控制菜单是否显示
);

2. 示例数据 #

插入包含多级嵌套的菜单数据，注意同层级菜单通过MENU_ORDER指定显示顺序：

-- 顶级菜单
INSERT INTO APP_MENUS (PARENT_MENU_ID, MENU_NAME, MENU_ORDER, TARGET_URL)
VALUES (NULL, '系统管理', 1, 'f?p=&APP_ID.:100:&APP_SESSION.');
INSERT INTO APP_MENUS (PARENT_MENU_ID, MENU_NAME, MENU_ORDER, TARGET_URL)
VALUES (NULL, '业务模块', 2, 'f?p=&APP_ID.:200:&APP_SESSION.');
INSERT INTO APP_MENUS (PARENT_MENU_ID, MENU_NAME, MENU_ORDER, TARGET_URL)
VALUES (NULL, '报表中心', 3, 'f?p=&APP_ID.:300:&APP_SESSION.');

-- 系统管理子菜单
INSERT INTO APP_MENUS (PARENT_MENU_ID, MENU_NAME, MENU_ORDER, TARGET_URL)
VALUES (1, '用户管理', 1, 'f?p=&APP_ID.:101:&APP_SESSION.');
INSERT INTO APP_MENUS (PARENT_MENU_ID, MENU_NAME, MENU_ORDER, TARGET_URL)
VALUES (1, '角色权限', 2, 'f?p=&APP_ID.:102:&APP_SESSION.');

-- 角色权限子菜单
INSERT INTO APP_MENUS (PARENT_MENU_ID, MENU_NAME, MENU_ORDER, TARGET_URL)
VALUES (5, '角色配置', 1, 'f?p=&APP_ID.:103:&APP_SESSION.');
INSERT INTO APP_MENUS (PARENT_MENU_ID, MENU_NAME, MENU_ORDER, TARGET_URL)
VALUES (5, '权限分配', 2, 'f?p=&APP_ID.:104:&APP_SESSION.');

-- 业务模块子菜单
INSERT INTO APP_MENUS (PARENT_MENU_ID, MENU_NAME, MENU_ORDER, TARGET_URL)
VALUES (2, '客户管理', 1, 'f?p=&APP_ID.:201:&APP_SESSION.');
INSERT INTO APP_MENUS (PARENT_MENU_ID, MENU_NAME, MENU_ORDER, TARGET_URL)
VALUES (2, '订单管理', 2, 'f?p=&APP_ID.:202:&APP_SESSION.');

COMMIT;

3. 正确的层级查询SQL #

使用Oracle递归查询语法，通过ORDER SIBLINGS BY确保同层级菜单按指定顺序排列，解决顺序异常问题：

SELECT
    MENU_ID,
    PARENT_MENU_ID,
    MENU_NAME,
    TARGET_URL,
    IS_VISIBLE,
    LEVEL AS MENU_LEVEL, -- 菜单层级深度
    SYS_CONNECT_BY_PATH(MENU_ORDER, '/') AS SORT_PATH -- 用于排序的层级路径
FROM APP_MENUS
WHERE IS_VISIBLE = 'Y'
START WITH PARENT_MENU_ID IS NULL -- 从顶级菜单开始递归
CONNECT BY PRIOR MENU_ID = PARENT_MENU_ID -- 关联父菜单与子菜单
ORDER SIBLINGS BY MENU_ORDER; -- 核心：保证同层级菜单按MENU_ORDER排序

关键说明 #

• START WITH指定递归起点为顶级菜单（父ID为空）
• CONNECT BY定义递归关联规则，实现无限层级嵌套
• ORDER SIBLINGS BY是解决顺序异常的核心，确保同一父菜单下的子菜单严格按照MENU_ORDER值排序
• 查询结果可直接作为Oracle APEX导航菜单的数据源，配合APEX菜单组件配置层级显示即可实现需求

内容的提问来源于stack exchange，提问作者Arghya