SQL

Das potentiell komplexeste Statement in SQL ist das SELECT-Statement. Dieses wiederverwendbar zu kapseln, z.B. in einer Methode einer Klasse, kann eine Herausforderung sein.

Hier ein Beispiel. Mit folgendem SELECT-Statement wollen wir aus dem Data Dictionary (Catalog) von PostgreSQL Informationen über die auf der Datenbank definierten Datenbank-Funktionen abfragen:

SELECT
    fnc.oid AS fnc_oid
    , usr.usename AS fnc_owner
    , nsp.nspname AS fnc_schema
    , fnc.proname AS fnc_name
    , pg_get_function_identity_arguments(pro.oid) AS fnc_arguments
    , pg_get_functiondef(fnc.oid) AS fnc_source
FROM
    pg_proc AS fnc
    JOIN pg_namespace AS nsp
        ON fnc.pronamespace = nsp.oid
    JOIN pg_user usr
        ON fnc.proowner = usr.usesysid

Dieses relativ kurze Statement besitzt eine beachtliche Komplexität. Es erstreckt sich über drei Tabellen, deren Aufbau und Verknüpfung alles andere als offensichtlich ist. Wir wollen es so kapseln, dass wir bei Abfragen keine Details über die interne Repräsentation des Data Dictionary wissen müssen. In einem ersten Schritt haben wir hierzu für die Kolumnen Aliasnamen vergeben, aus denen sich deren Bedeutung relativ klar ergibt:

fnc_oid

Objekt-Id der Funktion.

fnc_owner

Name des Owners der Funktion.

fnc_schema

Name des Schemas, in dem sich die Funktion befindet.

fnc_name

Name der Funktion.

fnc_arguments

Argumentliste der Funktion als kommaseparierte Aufzählung der Typen.

fnc_source

Quelltext der Funktion.

Das Problem ist jedoch, dass wir die Kolumnen-Aliase nicht in allen Klauseln des SELECT-Statements verwenden können, insbesondere nicht in der WHERE-Klausel, mit der wir die Ergebnismenge bestimmen.

Eine mögliche Lösung ist das Anlegen einer View, die diese Beschränkung nicht besitzt:

CREATE VIEW function_view AS
SELECT
    fnc.oid AS fnc_oid
    , usr.usename AS fnc_owner
    , nsp.nspname AS fnc_schema
    , fnc.proname AS fnc_name
    , pg_get_function_identity_arguments(pro.oid) AS fnc_arguments
    , pg_get_functiondef(fnc.oid) AS fnc_source
FROM
    pg_proc AS fnc
    JOIN pg_namespace AS nsp
        ON fnc.pronamespace = nsp.oid
    JOIN pg_user usr
        ON fnc.proowner = usr.usesysid

Damit schaffen wir allerdings eine öffentliche Schnittstelle, die wir nicht nach Belieben anlegen (Namenskonflikte) und ändern können (wir wissen nicht, wer die View sonst noch nutzt). Dies ist das Gegenteil einer Kapselung. Das Ziel einer programminternen Kapselung erreichen wir mit einer View nicht.

Eine mögliche Lösung bietet eine Inline-View. Bei einer Inline-View betten wir das SELECT-Statement in eine FROM-Klausel ein:

SELECT
    ...
FROM (
    SELECT
        fnc.oid AS fnc_oid
        , usr.usename AS fnc_owner
        , nsp.nspname AS fnc_schema
        , fnc.proname AS fnc_name
        , pg_get_function_identity_arguments(pro.oid) AS fnc_arguments
        , pg_get_functiondef(fnc.oid) AS fnc_source
    FROM
        pg_proc AS fnc
        JOIN pg_namespace AS nsp
            ON fnc.pronamespace = nsp.oid
        JOIN pg_user usr
            ON fnc.proowner = usr.usesysid
) AS function_view
...

Diese Lösung hat jedoch den Nachteil, dass sie nicht 100%ig portabel ist. Z.B. verlangt PostgreSQL einen Namen für die Inline-View (AS function_view), Oracle jedoch nicht, wobei ein Name bei Oracle zwar vereinbart werden kann, dann aber ohne AS.

Eine elegante Lösung bietet die Einbettung in eine WITH-Klausel:

WITH function_view AS (
    SELECT
        fnc.oid AS fnc_oid
        , usr.usename AS fnc_owner
        , nsp.nspname AS fnc_schema
        , fnc.proname AS fnc_name
        , pg_get_function_identity_arguments(pro.oid) AS fnc_arguments
        , pg_get_functiondef(fnc.oid) AS fnc_source
    FROM
        pg_proc AS fnc
        JOIN pg_namespace AS nsp
            ON fnc.pronamespace = nsp.oid
        JOIN pg_user usr
            ON fnc.proowner = usr.usesysid
)
SELECT
    ...
FROM
    function_view
...

Diese Lösung ist portabel und separiert die konkrete Abfrage (SELECT ... FROM function_view ...) klar vom gekapselten SELECT-Statement.

SELECT col1, ..., colN, COUNT(*)
FROM tab
GROUP BY col1, ..., colN
HAVING COUNT(*) > 1

col1, ..., colN sind die Kolumnen, über denen die Dubletten-Eigenschaft geprüft wird.

Oracle kennt die Pseudo-Kolumne ROWNUM, die die Datensätze einer Selektion von 1 an aufsteigend durchnummeriert. In MySQL existiert dieses Konzept nicht, es kann aber mittels einer Benutzer-definierten Variable simuliert werden.

Implementierung:

Einschränkung: Die Lösung ist unzureichend, wenn ein ORDER BY verwendet wird, da die Nummerierung vor der Sortierung stattfindet.

Unter Oracle wird ROWNUM auch benutzt, um die Ergebnismenge auf die die ersten n Datensätze zu begrenzen. Dafür hat MySQL ein besseres Konzept, die SELECT-Klausel LIMIT.