如何结合SELECT与ALL_TAB_COLUMNS实现Oracle动态列查询？

核心改进思路 #

针对你提出的三类问题，直接给出落地的解决实现，同时完善列值返回与异常处理逻辑：

1. 解决列数量受限问题 #

使用自定义嵌套表类型替代固定参数，支持任意数量的列名输入。

2. 解决JDBC结果集为空问题 #

改用SYS_REFCURSOR作为输出参数，让JDBC可以直接获取完整结果集，避免EXECUTE IMMEDIATE的结果无法传递的问题。

3. 适配任意类型主键参数 #

通过通用字符串类型结合Oracle隐式转换，兼容数值、日期等各类主键格式；也可扩展为ANYDATA类型实现更严格的类型适配。

4. 完善列存在性校验与错误反馈 #

批量校验输入列的合法性，抛出明确的无效列提示，同时处理无匹配主键记录的场景。

完整代码实现 #

第一步：创建全局嵌套表类型 #

CREATE OR REPLACE TYPE str_list IS TABLE OF VARCHAR2(30);
/

第二步：实现存储过程 #

CREATE OR REPLACE PROCEDURE get_column_values(
    p_table_name   IN  VARCHAR2,
    p_pk_name      IN  VARCHAR2,
    p_pk_value     IN  VARCHAR2, -- 通用类型适配各类主键
    p_column_names IN  str_list,
    p_result       OUT SYS_REFCURSOR
) AS
    v_valid_columns str_list := str_list();
    v_sql           VARCHAR2(32767);
BEGIN
    -- 批量校验输入列是否存在于目标表
    SELECT COLUMN_NAME
    BULK COLLECT INTO v_valid_columns
    FROM ALL_TAB_COLUMNS
    WHERE TABLE_NAME = UPPER(p_table_name)
      AND OWNER = USER -- 可根据需求修改为指定用户
      AND COLUMN_NAME IN (SELECT COLUMN_VALUE FROM TABLE(p_column_names));

    -- 检查是否存在无效列
    IF v_valid_columns.COUNT != p_column_names.COUNT THEN
        FOR i IN 1..p_column_names.COUNT LOOP
            IF p_column_names(i) NOT MEMBER OF v_valid_columns THEN
                RAISE_APPLICATION_ERROR(-20001, '无效列名：' || p_column_names(i));
            END IF;
        END LOOP;
    END IF;

    -- 动态构造SELECT语句
    v_sql := 'SELECT ';
    FOR i IN 1..v_valid_columns.COUNT LOOP
        v_sql := v_sql || '"' || v_valid_columns(i) || '"';
        v_sql := v_sql || CASE WHEN i < v_valid_columns.COUNT THEN ', ' ELSE '' END;
    END LOOP;
    v_sql := v_sql || ' FROM "' || UPPER(p_table_name) || '" WHERE "' || UPPER(p_pk_name) || '" = :1';

    -- 打开游标并绑定参数，返回列值结果集
    OPEN p_result FOR v_sql USING p_pk_value;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20002, '未找到匹配主键的记录');
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20003, '查询失败：' || SQLERRM);
END;
/

JDBC调用示例（伪代码） #

// 注册存储过程调用
CallableStatement cs = conn.prepareCall("{CALL get_column_values(?, ?, ?, ?, ?)}");

// 设置输入参数
cs.setString(1, "EMP"); // 目标表名
cs.setString(2, "EMPNO"); // 主键列名
cs.setString(3, "7369"); // 主键值（字符串格式适配各类类型）
// 传递列名列表
Array columnArray = conn.createArrayOf("STR_LIST", new String[]{"ENAME", "JOB", "SAL"});
cs.setArray(4, columnArray);

// 注册输出游标
cs.registerOutParameter(5, OracleTypes.CURSOR);

// 执行调用并获取结果
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(5);
while (rs.next()) {
    System.out.println("ENAME: " + rs.getString("ENAME"));
    System.out.println("JOB: " + rs.getString("JOB"));
    System.out.println("SAL: " + rs.getDouble("SAL"));
}

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