如何在Oracle SELECT查询中实现存储过程的分支表查询逻辑

你可以通过WITH子句+UNION ALL的组合实现这个分支逻辑，核心思路是先计算学生表的计数，再根据计数条件过滤对应的部门表数据，具体代码如下：

WITH student_count AS (
    SELECT COUNT(EMP_ID) AS cnt FROM STUDENT
)
SELECT d1.*
FROM DEPARTMENT1 d1, student_count sc
WHERE sc.cnt > 0
UNION ALL
SELECT d2.*
FROM DEPARTMENT2 d2, student_count sc
WHERE sc.cnt = 0;

关键说明： #

• student_count 子句先一次性计算STUDENT表中EMP_ID的总数，避免重复查询
• 两个部门表的查询通过UNION ALL合并，只有符合计数条件的分支会返回数据，另一分支因条件不满足无结果
• 必须保证DEPARTMENT1和DEPARTMENT2的列结构（列名、数据类型、数量）完全一致，否则会报错。如果结构不一致，需要手动指定列名并对齐类型，示例：

WITH student_count AS (
    SELECT COUNT(EMP_ID) AS cnt FROM STUDENT
)
SELECT d1.dept_id, d1.dept_name, d1.create_date
FROM DEPARTMENT1 d1, student_count sc
WHERE sc.cnt > 0
UNION ALL
SELECT d2.dept_id, d2.dept_name, d2.create_date
FROM DEPARTMENT2 d2, student_count sc
WHERE sc.cnt = 0;

另一种实现方式（优先级过滤）： #

如果更倾向于用CASE判断，也可以给两个部门表的结果加优先级标记，再根据计数筛选对应优先级的数据：

SELECT *
FROM (
    SELECT d1.*, 1 AS query_priority FROM DEPARTMENT1 d1
    UNION ALL
    SELECT d2.*, 2 AS query_priority FROM DEPARTMENT2 d2
)
WHERE query_priority = CASE 
    WHEN (SELECT COUNT(EMP_ID) FROM STUDENT) > 0 THEN 1 
    ELSE 2 
END;

注意：这种方式会先扫描两个部门表的所有数据再过滤，数据量大时性能不如第一种方案，优先推荐WITH子句的写法。

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