Skip to Main Content
 
Titelbild Muniqsoft Training

Search Results

Oracle Tipp Text

Die WITH-Klausel

Bereich:SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 10.06.2024

Keywords:WITH-Klausel

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


oder wollten Sie immer schon mal wissen, was der Parser aus Ihrem SQL Statement macht, bevor er loslegt?

WITH FUNCTION 
get_sql (p_sql IN CLOB) RETURN CLOB
IS
p_sql_out CLOB;
BEGIN
 DBMS_UTILITY.EXPAND_SQL_TEXT (p_sql, p_sql_out);
 RETURN p_sql_out;
END;
select get_sql('select * from dual') from dual;
/

oder interessieren Sie sich für Fehlertexte (Hier werden alle zwischen ORA-0100 und ORA-0200 angezeigt) in der Oracle Datenbank:

WITH FUNCTION get_err_text(id IN NUMBER) RETURN VARCHAR2 IS
BEGIN
return (sqlerrm(id));
END;
err_msg as (SELECT rownum as err_id FROM dual CONNECT BY LEVEL<100000)
SELECT get_err_text(-1*err_id) FROM err_msg
WHERE err_id between 100 and 200;

oder möchten Sie den Längen und Breitengrad in den Oracle Spatial Dytentyp konvertieren?

WITH FUNCTION to_sdo ( lon IN NUMBER,lat IN NUMBER)
RETURN SDO_GEOMETRY
DETERMINISTIC IS
 geom SDO_GEOMETRY;
BEGIN
 IF (lon BETWEEN -180 AND 180) AND (lat BETWEEN -90 AND 90) THEN 
 RETURN SDO_GEOMETRY( 2001 -- 2-dimensionaler Punkt  
 , 4326 -- SRID
 , SDO_POINT_TYPE( lon -- x-Koordinate
 , lat -- y-Koordinate
 , NULL )  -- z-Koordinate
 , NULL -- SDO_ELEM_INFO_ARRAY
 , NULL );-- SDO_ORDINATE_ARRAY         
 ELSE RETURN NULL;
 END IF;
END;
SELECT to_sdo(48.16989342,11.55152705 ) AS geom – Unterhaching/München
FROM dual;

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.



Weitere Interessente Artikel zum Thema:


Empfohlene Schulungen zum Thema:
2026.02.18