SQL:搜索具有给定值的列的列表(在一行中) [英] SQL: Search a list of columns with a given value (within a row)

问题描述

我有一个带有许多列的表.有没有一种方法可以回答以下问题:对于特定的_id(主键)，该行中的哪些字段的值为10"?

I have a table with many columns. Is there a way to make a query that answers the question: "For a particular _id (the primary key), what field(s) in this row have a value of 10"?

说明:表格已正确设置.我正在执行的查询是一些手动查询，因为我跟踪了一些不正确的数据.该表已经过优化，对于表示大多数查询运行的 浩大 的自动查询来说，该表是最快的. (对于超过9500万行的数据，优化的每一点都很重要)

Clarification: The table is set up properly. The querying I am doing is some manual querying as I track down some incorrect data. The table has been optimized to be fastest for the automated queries that represent the vast majorities of queries run. (And with more than 95 million rows, every bit of optimization there is important)

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

I realize that my question is asking to do something SQL wasn't intended to do. I'm just hoping that there is some trick to get what I want.

后代

在我们的系统中，我们有许多不同的用户帐户.一个帐户是我们用于所有只读查询的帐户(这是我大多数时候使用的帐户).它不拥有所涉及的表，因此当我根据自己的情况调整答案时，我必须进行以下更改:

In our system, we have many different user accounts. One account is the one we use for all read-only queries (this is the one I use most of the time). It does not own the tables in question, so when I was adapting the answer to my situation, I had to make the following change:

USER_TAB_COLUMNS必须成为ALL_TAB_COLUMNS，我必须在查询中添加OWNER = '[OWNER]'.

USER_TAB_COLUMNS had to become ALL_TAB_COLUMNS and I had to add OWNER = '[OWNER]' to the query.

推荐答案

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

This is not a normal piece of database functionality. However you are not the first person who has asked for this, or something like it.

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

The solution requires two things. The first is the data dictionary; the Oracle database doesn't support Reflection but it does come with a set of views which give us metadata about our database objects. In this case we need user_tab_columns, which will give us the columns for a given table. The second thing is dynamic SQL; this is the ability to assemble a SQL query at runtime and then execute it. There are a couple of ways of do ing this, but usually ref cursors are sufficient.

以下代码是概念证明.它包含四个参数:

The following code is a proof of concept. It takes four parameters:

1. 您要搜索的表的名称
2. 该表的主键名称 列
3. 您想要的主键值 限制
4. 您要搜索的值.

1. the name of table you want to search
2. the name of that table's primary key column
3. the primary key value you want to restrict by
4. the value you want to search for.

它还没准备好，所以您可能需要对其进行编辑以整理输出或使程序更灵活.

It is rough'n'ready so you may need to edit it to tidy the output or to make the program more flexible.

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很难阅读.调试起来比较困难:)因此，最好有一种方法来显示最终声明.

As you can see, dynamic SQL is hard to read. It is harder to debug :) So it is a good idea to have a means to show the final statement.

无论如何，这是结果:

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>

这篇关于SQL:搜索具有给定值的列的列表(在一行中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！