Oracle 11g无创建函数权限时如何将外部值列表作为表参与内连接查询

可以实现，以下两种方案均不需要创建函数、存储过程、临时表的权限，仅用原生SELECT语法即可完成。

方案1：UNION ALL 构造虚拟表（最易实现，适合数据量较小场景） #

你提到的SQL Server VALUES 语法在Oracle 11g中没有直接对应的原生写法，可通过系统虚拟表DUAL加UNION ALL拼接实现完全等效的效果。

虚拟表构造示例： #

SELECT '1' EmpNo, 1 Dept FROM DUAL
UNION ALL
SELECT '2' EmpNo, 1 Dept FROM DUAL
UNION ALL
SELECT '3' EmpNo, 2 Dept FROM DUAL
UNION ALL
SELECT '4' EmpNo, 2 Dept FROM DUAL

完整关联查询代码： #

SELECT att.* FROM attendance att
INNER JOIN (
    SELECT '1' EmpNo, 1 Dept FROM DUAL
    UNION ALL
    SELECT '2' EmpNo, 1 Dept FROM DUAL
    UNION ALL
    SELECT '3' EmpNo, 2 Dept FROM DUAL
    UNION ALL
    SELECT '4' EmpNo, 2 Dept FROM DUAL
) emp
ON att.EmpNo = emp.EmpNo
WHERE emp.Dept = 1

你只需要在应用层将拿到的JSON数组循环拼接为上述UNION ALL结构即可，无需任何数据库额外权限。

方案2：XMLTABLE 拆分结构化字符串（适合数据量较大场景） #

当员工数据量较大时，拼接大量UNION ALL语句效率较低，可先在应用层将JSON数组解析为EmpNo:Dept格式的逗号分隔字符串，再通过XMLTABLE函数拆分为多行结果集。

虚拟表构造示例： #

SELECT 
    REGEXP_SUBSTR(column_value, '[^:]+', 1, 1) EmpNo,
    TO_NUMBER(REGEXP_SUBSTR(column_value, '[^:]+', 1, 2)) Dept
FROM XMLTABLE(('"' || REPLACE('1:1,2:1,3:2,4:2', ',', '","') || '"'))

上述代码中'1:1,2:1,3:2,4:2'就是你需要替换的动态拼接字符串。

完整关联查询代码： #

SELECT att.* FROM attendance att
INNER JOIN (
    SELECT 
        REGEXP_SUBSTR(column_value, '[^:]+', 1, 1) EmpNo,
        TO_NUMBER(REGEXP_SUBSTR(column_value, '[^:]+', 1, 2)) Dept
    FROM XMLTABLE(('"' || REPLACE('1:1,2:1,3:2,4:2', ',', '","') || '"'))
) emp
ON att.EmpNo = emp.EmpNo
WHERE emp.Dept = 1

补充说明 #

Oracle 11g未提供原生JSON解析能力，JSON解析动作需要在调用SQL的应用层完成，解析后按上述两种方案的格式传入SQL即可。

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