Optionale Abfrageparameter in SQL mittels NVL() realisieren
Bei Abfrage auf eine Tabelle mit beliebigen Kombinationen von Einschränkungen z.B. aus einem Frontend heraus, können sehr schnell hunderte Kombinationen von Abfragevarianten entstehen. Gerade im Bezug auf die Abfrage auf NULL-Werte, werden die SQL-Statements sehr schnell unübersichltich. Mit dem folgenden SQL-Konstrukt – realisiert mittels der SQL Function NVL() – könnt ihr sozusagen „optionale“ Parameterwerte definieren. Das Bedeutet, dass die Parameter die mit Werten belegt werden in dem SQL-Statement berücksichtigt werden, alle anderen Columns werden in der WHERE Bedingung ignoriert.´
Demo – NVL()
Eine kleine Demo veranschaulicht den Mehrwert dieser Abfragevariante mittels NVL.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- Tabelle anlegen CREATE TABLE TMP_PERSON (ID NUMBER ,VORNAME VARCHAR2(200) ,NACHNAME VARCHAR2(200) ,GEBURTSNAME VARCHAR2(200) ); -- Testdaten einspielen Insert into TMP_PERSON (ID,VORNAME,NACHNAME,GEBURTSNAME) values (1,'Karsten','Besserdich',null); Insert into TMP_PERSON (ID,VORNAME,NACHNAME,GEBURTSNAME) values (2,'Tobias','Redmann',null); Insert into TMP_PERSON (ID,VORNAME,NACHNAME,GEBURTSNAME) values (3,null,'KeinVorname',null); Insert into TMP_PERSON (ID,VORNAME,NACHNAME,GEBURTSNAME) values (4,'Thomas','Müller','Geburtsname'); commit; |
Die folgende Abfrage absetzen und mal ein paar Kombinationen durchspielen, z.B. Kombination Nachname + Vorname oder nur der Nachname usw.
1 2 3 4 5 6 |
Select * from TMP_PERSON t WHERE 1=1 AND NVL(T.VORNAME,'XXX') = NVL(:para_vorname,NVL(T.VORNAME,'XXX')) -- optional AND NVL(T.NACHNAME,'XXX') = NVL(:para_nachname,NVL(NACHNAME,'XXX')) -- optional AND NVL(T.GEBURTSNAME,'XXX') = NVL(:para_geburtsname,NVL(GEBURTSNAME,'XXX')) -- optional ; |
Eine explizite Abfrage auf NULL-Werte ist mit der obigen Variante nicht möglich.
Hier solltet ihr das folgende Konstrukt in der WHERE-Bedingungen für die entsprechenden Parameter wählen.
1 2 3 4 5 6 |
Select * from TMP_PERSON t WHERE 1=1 AND NVL(T.VORNAME,'XXX') = NVL(:para_vorname,NVL(T.VORNAME,'XXX')) -- optional AND NVL(T.NACHNAME,'XXX') = NVL(:para_nachname,'XXX') -- Pflichtparameter - hier explizites Abfragen auf NULL-Werte möglich AND NVL(T.GEBURTSNAME,'XXX') = NVL(:para_geburtsname,NVL(GEBURTSNAME,'XXX')) ; |
Das Thema Performance habe ich in diesem Post nicht betrachtet. Lösungsansätze wären Function-Based Indizies oder der Einsatz von virtuellen Columns (+ Indizierung).
Habt ihr andere Lösungsansätze?
SCHREIBEN SIE EINEN KOMMENTAR