SQL:搜索具有给定值的列列表(在一行中)

Question

我有一张 table ，上面有 许多 列。有没有办法进行查询来回答以下问题:“对于特定的 _id(主键)，该行中哪些字段的值为 10”？

编辑:

说明:该表已正确设置。我正在做的查询是一些手动查询，因为我追踪了一些不正确的数据。该表已经过优化，对于代表 的自动查询速度最快。浩瀚大多数查询运行。 (并且有超过 9500 万行，每一点优化都很重要)

我意识到我的问题是要求做一些 SQL 不打算做的事情。我只是希望有一些技巧可以得到我想要的东西。

为后代编辑:

在我们的系统中，我们有许多不同的用户帐户。一个帐户是我们用于所有只读查询的帐户(这是我大部分时间使用的帐户)。它不拥有相关表格，因此当我根据自己的情况调整答案时，我必须进行以下更改:
USER_TAB_COLUMNS不得不变成ALL_TAB_COLUMNS我不得不添加 OWNER = '[OWNER]'到查询。

Answer 1

这不是正常的数据库功能。但是，您并不是第一个提出此要求或类似要求的人。

解决方案需要两件事。第一个是数据字典； Oracle 数据库不支持反射，但它确实带有一组 View ，这些 View 为我们提供了有关数据库对象的元数据。在这种情况下，我们需要 user_tab_columns ，这将为我们提供给定表的列。第二件事是动态SQL；这是在运行时组装 SQL 查询然后执行它的能力。有几种方法可以做到这一点，但通常引用游标就足够了。

以下代码是概念证明。它需要四个参数:

要搜索的表名

该表的主键的名称
栏目

你想要的主键值
限制由

您要搜索的值。

它是粗略的'n'ready，所以您可能需要对其进行编辑以整理输出或使程序更灵活。

create or replace procedure search_cols
  (tname in user_tables.table_name%type
   , pk_col in user_tab_columns.column_name%type
   , pk in number
   , val in number )
is
    firstcol boolean := true;
    stmt varchar2(32767);
    result varchar2(32767);
    rc sys_refcursor;
begin
    stmt := 'select ';
    << projection >>
    for lrec in ( select column_name from user_tab_columns
                  where table_name = tname
                  and column_name != pk_col
                  and data_type = 'NUMBER'
                  order by column_id )
    loop
        if not firstcol then
            stmt := stmt || chr(10) || '||'',''||';
        else
            firstcol := false;
        end if;
        stmt := stmt || ' case when '|| lrec.column_name||' = '|| val ||
                           ' then '''|| lrec.column_name || ''' else null end';
    end loop projection;
    stmt := stmt || chr(10)|| ' from '||tname||' where '|| pk_col || ' = '|| pk;
    --  dbms_output.put_line(stmt);
    open rc for stmt;
    fetch rc into result;
    close rc;
    dbms_output.put_line(tname || '::' || val || ' found in '||result);
end search_cols;
/

如您所见，动态 SQL 很难阅读。调试起来比较困难 :) 因此，最好有一种方法来显示最终语句。

无论如何，这是结果:

SQL> set serveroutput on size unlimited
SQL> exec search_cols('T23', 'ID', 111, 10)
T23::10 found in ,COL_B,COL_C,

PL/SQL procedure successfully completed.

SQL> exec search_cols('T23', 'ID', 222, 10)
T23::10 found in COL_A,,,

PL/SQL procedure successfully completed.

SQL>