SQL: Suche in einer Liste von Spalten mit einem bestimmten Wert (innerhalb einer Zeile)

Question

Ich habe eine Tabelle mit viele Spalten. Gibt es eine Möglichkeit, eine Abfrage zu erstellen, die die Frage beantwortet: "Welche(s) Feld(er) in dieser Zeile haben für eine bestimmte _id (den Primärschlüssel) den Wert 10"?

EDIT :

Klärung: Die Tabelle ist ordnungsgemäß eingerichtet. Die Abfrage, die ich durchführe, ist eine manuelle Abfrage, da ich einige falsche Daten aufspüre. Die Tabelle wurde so optimiert, dass sie für die automatisierten Abfragen, die die . die Mehrheit der Abfragen durchgeführt. (Und bei mehr als 95 Millionen Zeilen ist jedes bisschen Optimierung wichtig)

Mir ist klar, dass meine Frage nach etwas fragt, was SQL nicht tun sollte. Ich hoffe nur, dass es einen Trick gibt, um zu bekommen, was ich will.

EDIT für die Nachwelt:

In unserem System haben wir viele verschiedene Benutzerkonten. Ein Konto verwenden wir für alle Abfragen mit Lesezugriff (das ist das Konto, das ich die meiste Zeit verwende). Ihm gehören die fraglichen Tabellen nicht. Als ich die Antwort an meine Situation anpasste, musste ich die folgende Änderung vornehmen:

USER_TAB_COLUMNS werden musste ALL_TAB_COLUMNS und ich musste hinzufügen OWNER = '[OWNER]' auf die Abfrage.

Answer 1

Dies ist kein normaler Teil der Datenbankfunktionalität. Sie sind jedoch nicht die erste Person, die dies oder etwas Ähnliches verlangt hat.

Die Lösung erfordert zwei Dinge. Das erste ist das Datenwörterbuch; die Oracle-Datenbank unterstützt Reflection nicht, aber sie verfügt über eine Reihe von Ansichten, die uns Metadaten über unsere Datenbankobjekte liefern. In diesem Fall benötigen wir user_tab_columns , die uns die Spalten einer bestimmten Tabelle liefert. Der zweite Punkt ist dynamisches SQL, d.h. die Möglichkeit, eine SQL-Abfrage zur Laufzeit zusammenzustellen und dann auszuführen. Es gibt mehrere Möglichkeiten, dies zu tun, aber in der Regel sind Ref-Cursor ausreichend.

Der folgende Code ist ein Beispiel für ein Konzept. Er benötigt vier Parameter:

1. den Namen der Tabelle, die Sie durchsuchen möchten
2. der Name des Primärschlüssels dieser Tabelle Spalte
3. den Primärschlüsselwert, den Sie einschränken wollen
4. den Wert, nach dem Sie suchen möchten.

Es handelt sich um eine Rohfassung, die Sie möglicherweise noch bearbeiten müssen, um die Ausgabe zu optimieren oder das Programm flexibler zu gestalten.

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;
/

Wie Sie sehen können, ist dynamisches SQL schwer zu lesen. Es ist schwieriger zu debuggen :) Daher ist es eine gute Idee, eine Möglichkeit zu haben, die letzte Anweisung anzuzeigen.

Wie auch immer, hier sind die Ergebnisse:

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>

Answer 2

Es scheint, dass Ihre Datenbank nicht richtig normalisiert ist. Das heißt, Sie können wahrscheinlich den UNPIVOT-Befehl innerhalb einer Unterabfrage verwenden, um das zu tun, was Sie versuchen.

Answer 3

Meine Lösung wäre die Verwendung von Wörterbuch-Tabellen (USER_TAB_COLUMNS), um dynamisch die Namen aller NUMBER-Spalten aus Ihrer Tabelle zu holen, und Dynamic SQL, denn hier sehe ich nicht, wie man es vermeiden könnte.

DECLARE
   CURSOR cur_columns IS
          select COLUMN_NAME from USER_TAB_COLUMNS 
          where TABLE_NAME='<MY_TABLE>' and DATA_TYPE='NUMBER';
   query_text VARCHAR2(1000);
   result_value NUMBER;
BEGIN
   -- Iterate through each NUMBER column of the table
   FOR rec_col IN cur_columns LOOP

       -- In my line of primary key <MY_ID>, check if the current column has
       --  the wanted value.
       query_text := 
         'SELECT count(1) FROM <MY_TABLE> WHERE <TABLE_ID> = <MY_ID> AND ' 
         || rec_col.COLUMN_NAME || ' = <MY_VALUE>'; -- < the "magic" is here

       EXECUTE IMMEDIATE query_text INTO result_value; 

       IF result_value > 0 THEN
          DBMS_OUTPUT.PUT_LINE('Got a match for column ' || 
                               rec_col.COLUMN_NAME || '.');
       END IF;

   END LOOP;
END;

Natürlich müssen Sie alle <Variablen > durch den von Ihnen gewählten Wert ersetzen.

Für manuelle Abfragen funktioniert es gut. Allerdings ist die Leistung wahrscheinlich schlecht, also lassen Sie es nicht mit großen Datensätzen so laufen.