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.