Optionale Abfrageparameter in SQL mittels NVL() realisieren

von Tobias Redmann

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.

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

-- 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.

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
;

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.

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'))
;

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

Sie sind hier: