Erweiterung der WITH Klausel

29.
Juli
2013
Veröffentlicht von: Marco Patzwahl

Oracle hat in der Version 12c die WITH Klausel erweitert, so dass nun auch Funktionen oder Prozeduren verwendet werden können.
 

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

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
        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 Opens internal link in current windowOracle 12c Neuerungen Kurs. Wir freuen uns auf Sie!

SQL

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.