Als PDF Downloaden!

Tipps & Tricks: Erweiterung der WITH Klausel
Bereich:
SQL
Erstellung:
07/2013 MP
Versionsinfo:
12.1
Letzte Überarbeitung:
07/2013 MP
Erweiterung der WITH Klausel
Oracle hat in der Version 12c die WITH Klausel erweitert, so dass nun auch Funktionen oder Prozeduren
verwendet werden können.
Einschränkungen: Eine Prozedur darf nur innerhalb der Funktion aufgerufen werden.
SQL Developer Version 3.2.20 und SQL Plus Version 11.2.0.x verstehen die Syntax nicht und erzeugen einen
Fehler:
ORA-06550: Zeile 8, Spalte 2:
PLS-00103: Fand das Symbol "BEGIN"
Nur SQL*Plus 12.1.0 beherrscht (derzeit) das Feature.
Vorteile der Funktion in der WITH Klausel:
Sie brauchen kein CREATE PROCEDURE Recht
Teilweise schnellere Verarbeitung
Schauen wir uns Beispiel 1 an (Prozedur und Funktion stehen auf der gleichen Ebene):
Hier wird die Ausgabe auf die Console (Modus TERMINAL) oder den Webserver (Modus HTML) umgelenkt.
WITH
PROCEDURE p(text IN varchar2) IS
p_mode VARCHAR2(20):='TERMINAL'; -- Modi 'TERMINAL' oder 'HTML'
BEGIN
IF p_mode='TERMINAL' THEN
DBMS_OUTPUT.put_line(text);
ELSIF p_mode='HTML' THEN
htp.p(text||'<BR>');
END IF;
END;
FUNCTION f(id IN NUMBER) RETURN NUMBER IS
BEGIN
p(to_char(id));
RETURN id;
END;
SELECT f(object_id) FROM all_objects
WHERE rownum <4
/
Ausgabe:
F(OBJECT_ID)
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 1 von 4
-----------49
3
28
49
3
28
Beispiel 2: Prozedur ist in die Funktion im Deklarationsteil eingebettet
WITH
FUNCTION f(id IN NUMBER) RETURN NUMBER IS
PROCEDURE p(text IN varchar2) IS
p_mode VARCHAR2(20):='TERMINAL'; -- Modi 'TERMINAL' oder 'HTML'
BEGIN
IF p_mode='TERMINAL' THEN
DBMS_OUTPUT.put_line(text);
ELSIF p_mode='HTML' THEN
htp.p(text||'<BR>');
END IF;
END;
BEGIN
p(to_char(id));
RETURN id;
END;
SELECT f(object_id) FROM all_objects
WHERE rownum <4
/
Im dritten Bespiel schauen wir uns eine Datumsumwandlungsfunktion an, die etwas toleranter ist als die von
Oracle.
Sie können ein beliebiges Trennzeichen aus der Menge /.,\-;#+*= zwischen Tag / Monat und Jahr verwenden. Der
Monatsname kann in Deutsch/Englisch als drei Buchstaben geschrieben werden. Zweistelliges Jahr wird
automatisch (wie die RR Funktion) auf vierstellig erweitert. Sie können das Beispiel natürlich beliebig erweitern,
um z. B. auch die Uhrzeit zu erfassen. Stellen Sie zuerst das gewünschte Ausgabedatumsformat ein:
ALTER SESSION SET nls_date_format='DD.MM.YYYY';
Wir erstellen uns eine Demo (Staging) Tabelle, in der das Datum in einer Varchar2 Spalte steht und in ein Date
Format gewandelt werden soll:
CREATE TABLE scott.dwh_datum_tab (datum VARCHAR2(30));
INSERT INTO scott.dwh_datum_tab VALUES('01.01.01');
INSERT INTO scott.dwh_datum_tab VALUES('01-Mai.99');
INSERT INTO scott.dwh_datum_tab VALUES('30/DeC.2001');
INSERT INTO scott.dwh_datum_tab VALUES('02-jan:1932');
COMMIT;
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 2 von 4
Nun beginnt unsere Funktion in der WITH Klausel:
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,
'/.,\-;#+*=','..........'); -- versch. Trennzeichen in . wandeln
Format zerlegen in drei Teile
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 (Sie können weitere Sprachen ergänzen)
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
/*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
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 3 von 4
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(datum) as datum FROM scott.dwh_datum_tab
/
Ausgabe:
DATUM
---------01.01.2001
01.05.1999
30.12.2001
02.01.1932
Zum Schluss sollte nicht unerwähnt bleiben, dass die neue Funktion auch gefährlich ist. Ein Benutzer mit
CREATE VIEW aber ohne CREATE PROCEDURE Recht kann folgende View erstellen:
CREATE OR REPLACE VIEW dual as
WITH
FUNCTION f(id IN NUMBER) RETURN NUMBER IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'drop table scott.wichtig';
RETURN 'X';
END;
SELECT f(1) dummy FROM sys.dual
/
Raten Sie mal, was passiert, wenn jemand den folgenden Befehl im Schema der View absetzt:
SELECT * FROM dual;
Richtig, ihre "Wichtig" Tabelle liegt jetzt im Mülleimer ...
Dies ist nur ein kleiner Ausschnitt der Möglichkeiten, die sich mit der WITH Funktion realisieren lassen. Und wie
immer gilt:
Darf´s noch ein bisschen mehr an Informationen zu Oracle 12c sein? Dann besuchen Sie doch unseren Oracle
12c Neuerungen Kurs. Wir freuen uns auf Sie!
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 4 von 4