Oracle SQL中能否创建类列表变量并判断值是否在列表中？

问题场景 #

我需要基于数据集创建多个计算字段，统一逻辑为：

1. 检查room_name是否属于某个指定列表；
2. 若是，将usable_sq_ft除以对应任务的生产率；
3. 若否，返回null。

比如目前办公室吸尘的计算逻辑是这样写的：

CASE WHEN (room_name = 'OFFICE' OR room_name = 'CUBICLE' OR room_name = 'RECEPTION')
    THEN usable_sq_ft / 52
    ELSE null
END Vacuum

但办公室类房间名称众多，需计算的任务也很多，不想每个任务都重复编写一堆OR的布尔判断。在Python里可以简洁实现：

offices = ['office', 'cubicle', 'reception']
entrances = ['lobby', 'foyer', 'vestibule']
exercise = ['gym', 'weight_room']
if room_name in offices:
    <calculation>
else:
    null

想知道在Oracle SQL里能不能类似地定义列表，然后判断room_name是否在列表中？

解决方案 #

方法1：用IN操作符简化判断

Oracle SQL原生支持IN操作符，直接把列表值放在括号里，就能替代多个OR，写法更简洁：

CASE WHEN room_name IN ('OFFICE', 'CUBICLE', 'RECEPTION')
    THEN usable_sq_ft / 52
    ELSE NULL
END Vacuum

方法2：定义可复用的列表（适合多计算字段共享分类）

如果同一个房间分类要在多个计算中重复使用，可以用以下两种方式：

方式A：公共表表达式（CTE）定义分类

一次性在CTE中定义所有房间分类，后续查询直接关联使用：

WITH room_categories AS (
    SELECT 'OFFICE' AS room_name, 'OFFICES' AS category FROM DUAL
    UNION ALL SELECT 'CUBICLE', 'OFFICES' FROM DUAL
    UNION ALL SELECT 'RECEPTION', 'OFFICES' FROM DUAL
    UNION ALL SELECT 'LOBBY', 'ENTRANCES' FROM DUAL
    UNION ALL SELECT 'FOYER', 'ENTRANCES' FROM DUAL
    UNION ALL SELECT 'VESTIBULE', 'ENTRANCES' FROM DUAL
    UNION ALL SELECT 'GYM', 'EXERCISE' FROM DUAL
    UNION ALL SELECT 'WEIGHT_ROOM', 'EXERCISE' FROM DUAL
)
SELECT
    t.room_name,
    t.usable_sq_ft,
    -- 办公室吸尘计算
    CASE WHEN rc.category = 'OFFICES' THEN t.usable_sq_ft / 52 ELSE NULL END Vacuum,
    -- 入口区域拖地计算
    CASE WHEN rc.category = 'ENTRANCES' THEN t.usable_sq_ft / 30 ELSE NULL END Mop,
    -- 健身区器材清洁计算
    CASE WHEN rc.category = 'EXERCISE' THEN t.usable_sq_ft / 40 ELSE NULL END Clean_Equipment
FROM your_table t
LEFT JOIN room_categories rc ON t.room_name = rc.room_name;

方式B：使用SYS.ODCIVARCHAR2LIST集合（Oracle 12c+）

Oracle 12c及以上版本支持用SYS.ODCIVARCHAR2LIST定义字符串列表，配合MEMBER OF操作符判断归属：

-- 直接在查询中内联使用列表
SELECT
    room_name,
    usable_sq_ft,
    CASE WHEN room_name MEMBER OF SYS.ODCIVARCHAR2LIST('OFFICE', 'CUBICLE', 'RECEPTION')
         THEN usable_sq_ft / 52
         ELSE NULL
    END Vacuum
FROM your_table;

如果需要复用列表，也可以用变量定义：

-- 定义全局列表变量
DEFINE offices = SYS.ODCIVARCHAR2LIST('OFFICE', 'CUBICLE', 'RECEPTION');
DEFINE entrances = SYS.ODCIVARCHAR2LIST('LOBBY', 'FOYER', 'VESTIBULE');

-- 在查询中引用变量
SELECT
    room_name,
    usable_sq_ft,
    CASE WHEN room_name MEMBER OF &offices THEN usable_sq_ft / 52 ELSE NULL END Vacuum,
    CASE WHEN room_name MEMBER OF &entrances THEN usable_sq_ft / 30 ELSE NULL END Mop
FROM your_table;

方法3：创建持久化分类表（适合长期固定的分类规则）

如果房间分类和对应生产率是长期固定的，建议创建专门的分类表，后续维护和查询都更方便：

-- 创建分类表
CREATE TABLE room_categories (
    room_name VARCHAR2(50) PRIMARY KEY,
    category VARCHAR2(50) NOT NULL,
    productivity NUMBER NOT NULL -- 直接存储对应任务的生产率
);

-- 插入分类数据
INSERT INTO room_categories VALUES ('OFFICE', 'OFFICES', 52);
INSERT INTO room_categories VALUES ('CUBICLE', 'OFFICES', 52);
INSERT INTO room_categories VALUES ('RECEPTION', 'OFFICES', 52);
INSERT INTO room_categories VALUES ('LOBBY', 'ENTRANCES', 30);
INSERT INTO room_categories VALUES ('FOYER', 'ENTRANCES', 30);
INSERT INTO room_categories VALUES ('VESTIBULE', 'ENTRANCES', 30);
INSERT INTO room_categories VALUES ('GYM', 'EXERCISE', 40);
INSERT INTO room_categories VALUES ('WEIGHT_ROOM', 'EXERCISE', 40);

-- 查询时关联分类表计算
SELECT
    t.room_name,
    t.usable_sq_ft,
    CASE WHEN rc.category = 'OFFICES' THEN t.usable_sq_ft / rc.productivity ELSE NULL END Vacuum,
    CASE WHEN rc.category = 'ENTRANCES' THEN t.usable_sq_ft / rc.productivity ELSE NULL END Mop,
    CASE WHEN rc.category = 'EXERCISE' THEN t.usable_sq_ft / rc.productivity ELSE NULL END Clean_Equipment
FROM your_table t
LEFT JOIN room_categories rc ON t.room_name = rc.room_name;

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