Oracle APEX中基于表数据查找两站点间所有路径的实现方案

表结构设计 #

1. 站点信息表（SITES） #

用于存储所有站点的基础信息，确保站点数据的唯一性：

CREATE TABLE SITES (
    SITE_ID VARCHAR2(10) PRIMARY KEY,
    SITE_NAME VARCHAR2(50) NOT NULL
);

-- 插入示例数据
INSERT INTO SITES (SITE_ID, SITE_NAME) VALUES ('A', '站点A');
INSERT INTO SITES (SITE_ID, SITE_NAME) VALUES ('B', '站点B');
INSERT INTO SITES (SITE_ID, SITE_NAME) VALUES ('C', '站点C');
INSERT INTO SITES (SITE_ID, SITE_NAME) VALUES ('D', '站点D');
INSERT INTO SITES (SITE_ID, SITE_NAME) VALUES ('E', '站点E');
INSERT INTO SITES (SITE_ID, SITE_NAME) VALUES ('F', '站点F');
INSERT INTO SITES (SITE_ID, SITE_NAME) VALUES ('G', '站点G');
INSERT INTO SITES (SITE_ID, SITE_NAME) VALUES ('H', '站点H');
INSERT INTO SITES (SITE_ID, SITE_NAME) VALUES ('J', '站点J');
COMMIT;

2. 站点连接关系表（SITE_CONNECTIONS） #

存储站点间的连接关系（为避免数据冗余，仅存储单向关系，查询时自动覆盖双向路径）：

CREATE TABLE SITE_CONNECTIONS (
    CONNECTION_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    FROM_SITE VARCHAR2(10) NOT NULL,
    TO_SITE VARCHAR2(10) NOT NULL,
    CONSTRAINT FK_CONN_FROM_SITE FOREIGN KEY (FROM_SITE) REFERENCES SITES(SITE_ID),
    CONSTRAINT FK_CONN_TO_SITE FOREIGN KEY (TO_SITE) REFERENCES SITES(SITE_ID),
    CONSTRAINT CHK_NO_SELF_CONN CHECK (FROM_SITE != TO_SITE)
);

-- 插入示例连接数据
INSERT INTO SITE_CONNECTIONS (FROM_SITE, TO_SITE) VALUES ('A', 'B');
INSERT INTO SITE_CONNECTIONS (FROM_SITE, TO_SITE) VALUES ('A', 'C');
INSERT INTO SITE_CONNECTIONS (FROM_SITE, TO_SITE) VALUES ('B', 'E');
INSERT INTO SITE_CONNECTIONS (FROM_SITE, TO_SITE) VALUES ('C', 'D');
INSERT INTO SITE_CONNECTIONS (FROM_SITE, TO_SITE) VALUES ('D', 'G');
INSERT INTO SITE_CONNECTIONS (FROM_SITE, TO_SITE) VALUES ('D', 'F');
INSERT INTO SITE_CONNECTIONS (FROM_SITE, TO_SITE) VALUES ('F', 'E');
INSERT INTO SITE_CONNECTIONS (FROM_SITE, TO_SITE) VALUES ('E', 'J');
INSERT INTO SITE_CONNECTIONS (FROM_SITE, TO_SITE) VALUES ('E', 'H');
COMMIT;

路径查询实现方法 #

1. 递归CTE查询所有可行路径 #

利用Oracle递归公共表表达式（CTE）遍历无循环路径，支持任意起点和终点的查询：

WITH site_paths AS (
    -- 锚点：从起点出发的初始路径
    SELECT 
        FROM_SITE AS current_site,
        TO_SITE AS next_site,
        FROM_SITE || ' → ' || TO_SITE AS path,
        ',' || FROM_SITE || ',' || TO_SITE || ',' AS visited_sites,
        1 AS path_length
    FROM SITE_CONNECTIONS
    WHERE FROM_SITE = '&START_SITE' -- 替换为目标起点，或APEX页面项
    
    UNION ALL
    
    -- 递归：继续遍历未访问的站点
    SELECT 
        sc.TO_SITE AS current_site,
        sc2.TO_SITE AS next_site,
        sp.path || ' → ' || sc2.TO_SITE AS path,
        sp.visited_sites || sc2.TO_SITE || ',' AS visited_sites,
        sp.path_length + 1 AS path_length
    FROM site_paths sp
    JOIN SITE_CONNECTIONS sc ON sp.next_site = sc.FROM_SITE
    JOIN SITE_CONNECTIONS sc2 ON sc.TO_SITE = sc2.FROM_SITE
    WHERE sp.visited_sites NOT LIKE '%,' || sc2.TO_SITE || ',%' -- 避免循环路径
)
-- 筛选到达终点的路径并去重
SELECT DISTINCT path, path_length
FROM site_paths
WHERE next_site = '&END_SITE' -- 替换为目标终点，或APEX页面项
ORDER BY path_length, path;

2. Oracle APEX集成实现 #

步骤1：创建页面选择项

在APEX页面添加两个选择列表：

• P1_START_SITE：数据源为SELECT SITE_ID, SITE_NAME FROM SITES，用于选择起点
• P1_END_SITE：数据源同上，用于选择终点

步骤2：创建交互式报表

添加交互式报表，使用绑定变量关联页面项：

WITH site_paths AS (
    SELECT 
        FROM_SITE AS current_site,
        TO_SITE AS next_site,
        FROM_SITE || ' → ' || TO_SITE AS path,
        ',' || FROM_SITE || ',' || TO_SITE || ',' AS visited_sites,
        1 AS path_length
    FROM SITE_CONNECTIONS
    WHERE FROM_SITE = :P1_START_SITE
    
    UNION ALL
    
    SELECT 
        sc.TO_SITE AS current_site,
        sc2.TO_SITE AS next_site,
        sp.path || ' → ' || sc2.TO_SITE AS path,
        sp.visited_sites || sc2.TO_SITE || ',' AS visited_sites,
        sp.path_length + 1 AS path_length
    FROM site_paths sp
    JOIN SITE_CONNECTIONS sc ON sp.next_site = sc.FROM_SITE
    JOIN SITE_CONNECTIONS sc2 ON sc.TO_SITE = sc2.FROM_SITE
    WHERE sp.visited_sites NOT LIKE '%,' || sc2.TO_SITE || ',%'
)
SELECT DISTINCT path AS "可行路径", path_length AS "路径长度"
FROM site_paths
WHERE next_site = :P1_END_SITE
ORDER BY path_length, path;

步骤3：添加查询触发按钮

添加按钮，设置动作为"刷新"，目标选择交互式报表区域，实现用户选择起点终点后一键查询路径。

注意事项 #

• 循环避免：通过visited_sites字符串记录已访问站点，防止路径陷入循环（如A→B→A）
• 性能优化：若站点数量较多，可添加path_length最大值限制（如WHERE path_length <= 10），避免递归过深影响性能
• 双向连接处理：本方案通过递归关联实现双向路径查询，若需更高效率，可直接在连接表中存储双向记录（如同时插入A→B和B→A）

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