Auswahl  

 

Oracle
SQL
18.1:12.1, 12.2
19.03.18 (MP)
18.12.18 (MP)
WITH-Klausel

Body

Die WITH-Klausel ist ja eines der bestgehütesten Geheimnisse der Sprache SQL. In unseren Schulungen ernten wir immer wieder Erstaunen, wenn wir die Funktion vorstellen. Dabei ist sie schon seit einigen Oracle Versionen verfügbar.

Richtig spannend wurde es jedoch erst ab Version 12.x, doch dazu später mehr.

Schauen wir uns erst einmal die einfachste WITH-Klausel an:

WITH s as (SELECT 'SCOTT' as myuser FROM dual)
SELECT * FROM s;


Die WITH-Klausel ermöglicht uns, eine Ergebnismenge vorab zu aggregieren, um sie dann evtl. sogar mehrmals in einem SELECT wieder einzusetzen.

Hier wird die Query unter dem Namen "S" in Form einer Tabelle zur Verfügung gestellt. Nur hat die Ergebnismenge im ersten Fall halt nur eine Spalte und eine Zeile.

Ein Beispiel mit der EMP Tabelle darf natürlich nicht fehlen:

WITH s as (SELECT * FROM scott.emp)
SELECT * FROM s;


Ab Version 12.x können nun auch Proceduren und Funktionen in der WITH-Klausel verwendet werden. Erstaunlicherweise muss man dazu noch nicht mal ein "CREATE PROCEDURE" Recht besitzen. Ein Schelm, wer böses damit anstellt.

Manchmal möchte man auch einen Aufruf in SQL starten, der eigentlich nur in PL/SQL funktioniert,das ist mit der WITH-Klausel kein Problem.

Beispiel: Sie haben eine Fehlernummer von Oracle erhalten, aber nicht den Text. Der Versuch in SQL:

SELECT sqlerrm(-1*abs(error_id)) FROM dual;
ORA-00904: "SQLERRM": ungültige ID


Mit der WITH-Klausel:

WITH
    FUNCTION errm(error_id IN NUMBER) RETURN varchar2 IS         
    BEGIN
        return sqlerrm(-1*abs(error_id));
    END;
SELECT errm(1017) from dual;


Oder mal eine komfortablere Datumskonvertierungsfunktion ...

WITH FUNCTION       to_date2(p_date IN VARCHAR2 ) RETURN DATE IS     
    v_month VARCHAR2(30);
    V_DAY   VARCHAR2(30);
    v_year  VARCHAR2(30);
    v_dummy VARCHAR2(30);
    BEGIN
    V_DUMMY:=TRANSLATE(P_DATE,'/.,\-;#+*=','..........'); -- Trennzeichen in . wandeln
    V_DAY:=REGEXP_REPLACE(V_DUMMY,'([[:digit:]]{1,2}).([[:alnum:]]{1,12}).([[:digit:]]{1,4})','\1');
    v_month:=REGEXP_REPLACE(V_DUMMY,'([[:digit:]]{1,2}).([[:alnum:]]{1,12}).([[:digit:]]{1,4})','\2');
    V_YEAR:=REGEXP_REPLACE(V_DUMMY,'([[:digit:]]{1,2}).([[:alnum:]]{1,12}).([[:digit:]]{1,4})','\3');
    
    -- Monatsnamen ersetzen durch Zahlen
    IF UPPER(v_month) IN ('JAN')        THEN v_month:='01'; END IF;
    IF UPPER(v_month) IN ('FEB')        THEN v_month:='02'; END IF;
    IF UPPER(v_month) IN ('MAR','MÄR')  THEN v_month:='03'; END IF;
    IF UPPER(v_month) IN ('APR')        THEN v_month:='04'; END IF;
    IF UPPER(v_month) IN ('MAI','MAY')  THEN v_month:='05'; END IF;
    IF UPPER(v_month) IN ('JUN')        THEN v_month:='06'; END IF;
    IF UPPER(v_month) IN ('JUL')        THEN v_month:='07'; END IF;
    IF UPPER(v_month) IN ('AUG')        THEN v_month:='08'; END IF;
    IF UPPER(v_month) IN ('SEP')        THEN v_month:='09'; END IF;
    IF UPPER(v_month) IN ('OKT','OCT')  THEN v_month:='10'; END IF;
    IF UPPER(v_month) IN ('NOV','NOV')  THEN v_month:='11'; END IF;
    IF UPPER(v_month) IN ('DEZ','DEC')  THEN v_month:='12'; END IF;
    --DBMS_OUTPUT.PUT_LINE('#'||v_month); return sysdate;
    -- Prüfen ob das Jahr im aktuellen Jahrhundert (bis 2050) oder im letzten Jahrhundert (ab 1951) liegt
    IF TO_NUMBER(V_YEAR)<=50 THEN
      V_YEAR:=SUBSTR(TO_CHAR(SYSDATE,'YYYY'),1,2)||V_YEAR;
    ELSE
      IF not length(v_year)=4 THEN -- Nur wenn Jahr nicht vierstellig ist, wird ergänzt
        V_YEAR:=TO_NUMBER(SUBSTR(TO_CHAR(SYSDATE,'YYYY'),1,2)-1)||V_YEAR;
      END IF;
    END IF;
    RETURN TO_DATE(v_day||'.'||v_month||'.'||v_year,'DD.MM.YYYY');
    END;
SELECT to_date2('01.JAN.2000') as datum FROM dual
/


Das geht auch mit Datumswerten wie: '01.01.01', '01-Mai.99', '30/DeC.2001', '02-jan:1932'. Das versuchen Sie mal mit der to_date Funktion ...

Auch mehrere WITH-Klauseln sind möglich:

WITH
a as ( SELECT 1 as col FROM dual),
b as ( SELECT 2 as col FROM dual)
SELECT a.col,b.col
  FROM a, b;


Sie müssen sie nur mit einem Komma trennen.

Zum Schluss noch ein etwas anspruchsvolleres Beispiel, das wir gerne in den Kursen zeigen:
Es soll ein Skript angelegt werden, das einen Benutzer mit all seinen zugewiesenen Rechten anzeigt.

WITH U AS (SELECT 'SCOTT' AS NAME FROM DUAL)(
SELECT TO_CHAR(DBMS_METADATA.GET_DDL('USER',U.NAME)) FROM U,DUAL
UNION ALL
SELECT CASE WHEN cnt>0 then to_char(DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',u.name)) end
  FROM u,(select count(*) as cnt from u,all_tab_privs where grantee=u.name)
UNION ALL
SELECT CASE WHEN cnt>0 then to_char(DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',u.name)) end
  FROM u,(select count(*) as cnt from u,dba_sys_privs where grantee=u.name)
UNION ALL
SELECT  CASE WHEN cnt>0 then to_char(DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',u.name)) end
  FROM u,(SELECT count(*) as cnt  FROM u,dba_role_privs where grantee=u.name)
UNION ALL
SELECT to_char(dbms_metadata.get_ddl('SYNONYM',synonym_name,owner))
  FROM u,dba_synonyms where table_owner=u.name);


Der Vorteil hier ist, dass man den Schemanamen in der WITH-Klausel, quasi wie eine Variable definieren kann, die man dann an mehreren Stellen weiter unten im SQL Statement wieder verwenden kann.

Nur kann es passieren, dass einer der Teil-Selects keine Ergebnismenge zurückbringt. Dieser ist dann so nett, mit dem Fehler ORA-31608 abzustürzen. Damit stürzt dann aber auch gleich das komplette SQL-Statement ab. Deswegen haben wir oben mit der COUNT Funktion geprüft, ob überhaupt Rechte vorhanden sind.

Ab 12c können wir aber auch das schöner lösen:

WITH
  FUNCTION get_ddl(ddl_typ in VARCHAR2, uname IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
   DBMS_METADATA.SET_TRANSFORM_PARAM(
   DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
   IF ddl_typ like '%GRANT' THEN
    RETURN to_char(DBMS_METADATA.GET_GRANTED_DDL(ddl_typ,uname));
   ELSE
    RETURN to_char(DBMS_METADATA.GET_DDL(ddl_typ,uname));
  END IF;
    EXCEPTION WHEN OTHERS THEN
      IF sqlcode=-31608 THEN RETURN NULL; ELSE RAISE; END IF;
  END;
  u AS (SELECT 'SCOTT'  as name FROM dual)
SELECT get_ddl('USER',u.name) from u
UNION ALL
SELECT get_ddl('OBJECT_GRANT',u.name) from u
UNION ALL
SELECT get_ddl('SYSTEM_GRANT',u.name) from u
UNION ALL
SELECT get_ddl('ROLE_GRANT',u.name) from u

oder wollten Sie immer schon mal viele DDL befehle in einem Rutsch ausführen?
Achtung die folgende Funktion löscht sofort alle invaliden Synonyme der Benutzer MDSYS und CTXSYS:

WITH  FUNCTION   eval (cmd IN VARCHAR2)
RETURN VARCHAR2
IS
PRAGMA AUTONOMOUS_TRANSACTION; -- Damit ist die Funktion eigenständig und beeinflusst niemanden :-)
BEGIN
EXECUTE IMMEDIATE cmd; -- Befehl ausführen
RETURN cmd||'; --OK'; -- Befehl erfolgreich ausgeführt
EXCEPTION WHEN OTHERS THEN
 RETURN cmd||' --'||sqlerrm;
END;
select eval('
DROP PUBLIC SYNONYM "'||object_name||'"') from dba_objects
where status<>'VALID'
and object_type='SYNONYM'
and owner IN ('MDSYS','CTXSYS');

;


Na, auf den Geschmack gekommen? Weitere Beispiele erhalten Sie z.B. in unserem PL/SQL Kurs. Besuchen Sie uns doch mal im schönen Unterhaching bei München.

Besuchen Sie uns doch bei einer unsere über 40 Oracle Schulungen in München - Unterhaching.