Dynamische SQL's mit Variablen

Foxamon

Benutzer
Beiträge
10
Hallo Community,

ich habe ein Problem, und benötige dringend eine Lösung.

Wir verwenden mit ca. 10 Kollegen eine Tabelle die ca. 100 Spalten hat. In dieser Tabelle befindet sich eine gewisse Anzahl von Datensätzen (ca. 23. Millionen), die über eine ID eindeutig identifiziert werden können.

Nun ist es nötig, dass wir mit verschiedenen PL/SQL Skripten in einer entsprechenden Spalte ein Update durchführen. Die dahinter liegende Logik (welche Datensätze sollen aktualisiert werden und mit welchem Werte) ist dabei von Skript zu Skript unterschiedlich.

Um Fehler zu vermeiden, möchte ich nun eine Standardvorlage als PL/SQL Skript entwickeln, die wir gemeinsam verwenden. Diese Vorlage hat im Kopf (DECLARE) einen Konfigurationsteil (hier gibt man z.B. eine Konstante an, die den Spaltennamen beschreibt in die geschrieben werden soll). Im BEGIN-Teil kommt dann ein MERGE-Befehl, dessen Source-SQL vom jeweiligen Entwickler mit seinem Speziellen SQL befüllt wird. Dieser SQL muss nun zwei Werte liefern, zum einen die eindeutige ID des zu aktualisierenden Datensatzes und den Wert, der in die gewünschte Spalte (Konstante im Konfigurationsteil) geschrieben werden soll.

Hier mal eine abgespeckte Version meines Vorlagenskriptes:

Code:
DECLARE
   
    -- Konfiguration (Konstanten festlegen)
    CS_SPALTE   CONSTANT VARCHAR2(7) := 'MEINESPALTE_5';
   
BEGIN
   
    MERGE INTO MEINSCHEMA.MEINEZIELTABELLE D
   
    USING
    (
        ----------------------------------------------------------------
        SELECT ID,
               NEUER_WERT
        FROM   ANDERE_TABELLE
        ----------------------------------------------------------------
    ) S
   
    ON  (
            D.ID        = S.ID
        )
   
    WHEN MATCHED THEN UPDATE SET D.MEINESPALTE_5 = S.KPI
    ;
   
    COMMIT;
   
END;
/


Der SQL zwischen den gestrichelten Linien ist genau der, der vom jeweiligen Programmierer eingesetzt werden soll. In diesem Beispiel hier ist er sehr schlicht gehalten, kann aber in der Realität durchaus sehr komplex (mehrere hundert Zeilen) lang werden.

Das Kernproblem ist nun, dass die Zielspalte (im Code-Beispiel hier "MEINESPALTE_5") ganz unten im Code noch einmal aufgerufen wird:

Code:
WHEN MATCHED THEN UPDATE SET D.MEINESPALTE_5 = S.KPI

Es ist mir jetzt nicht ohne weiteres möglich, hier die oben festgelegte Konstante "CS_SPALTE" zu verwenden um die Zielspalte zu definieren.

Dadurch besteht jedoch das Risiko, dass ein Entwickler es einfach mal vergisst, diese Zeile am Ende des Skriptes anzupassen, und somit ggf. die Berechnungsergebnisse eines anderen Kollegen (in einer anderen Spalte) zu zerschießen. (Leider kommt es bei uns öfters vor, dass man sich andere Skripte von Kollegen oder von sich selbst als Vorlage nimmt, und diese dann für seine Zwecke entsprechend anpasst).

Um dieses Risiko zu minimieren, würde ich gern im Konfigurationsbereich oben festlegen, welche Spalte in der Zieltabelle dieses Skript beschreiben darf. Die manuelle Anpassung in der unteren Zeile des Merges würde ich mir gern sparen.

Aufgrund der Komplexität des jeweiligen Berechnungs-SQL's, der zwischen den beiden gestrichelten Linien in dieser Vorlage eingesetzt werden kann (hier werden z.B. häufig unter anderem weitere Variablen und Konstanten verwendet), scheidet eine Ausführung durch einen EXECUTE IMMEDIATE leider aus. Das Syntax-Highlightning wird dadurch außerdem komplett ausgehebelt, was der Übersichtlichkeit nicht hilft.

Hat hier ein schlauer Kopf vielleicht eine Lösung für mein Problem? Ich bin auch gern offen für andere Ansätze...
 
Werbung:
Also erstmal hast du natürlich Angriffspunkte an denen dein Script bei unsachgemäßer Anwendung jederzeit Schaden anrichten kann aber es klingt als sei dir das bewusst.

Du schreibst im Titel ja schon von dynamischem SQL, das sollte eigentlich funktionieren. Statt
Code:
WHEN MATCHED THEN UPDATE SET D.MEINESPALTE_5 = S.KPI
verwendest du
Code:
EXEC('WHEN MATCHED THEN UPDATE SET D.' + @CS_SPALTE + ' = S.KPI')
 
Code:
CREATE OR REPLACE FUNCTION GETMERGE(P_TARGETCOLUMN VARCHAR2) RETURN VARCHAR2 IS
  L_STMT VARCHAR2(2000);
BEGIN
  L_STMT:='MERGE INTO irgendeinetabelle '||CHR(10)||' USING ( /*TODO Skript einsetzen */) s'||CHR(10)||
          'ON ( d.id=s.id)'||CHR(10)||' WHEN MATCHED THEN UPDATE '||P_TARGETCOLUMN||'=s.kpi;'||CHR(10)||'COMMIT;';
  RETURN L_STMT;
END;
/

Und dann
Code:
select getMerge('testcol') from dual

Nach Belieben zu erweitern.
 
hey, vielen Dank für die schnelle Rückmeldung. Ja, dieses hohe Risiko, ist mir durchaus bewusst, leider gibt es derzeit bei uns keine andere Lösung. Gerade deshalb ist es mir wichtig, nur einmal am Anfang des Skriptes die Zielspalte definieren zu können, so dass man sie wenigstens gleich im Blick hat, wenn man das Skript öffnet.

Der erste Vorschlag gefällt mir sehr gut, wirf jedoch noch einen Fehler bei mir:

Hier mal ein Screenshot:

upload_2017-6-8_13-54-58.png
(Zeile 49 ist hier sichtbar, Zeile 18 ist der Begin des MERGE's)

Mache ich etwas falsch oder ist hier noch ein Fehler drin?

Der Zweite vorschlag würde zwar funktionieren, aber wie erwähnt, wenn ich als "TODO" hier einen 900 Zeilen langen SQL einfüge, und dieser kein Syntax-Highlightning mehr hat, da der gesamte SQL als Zeichenkette betrachtet wird, hilft der Übersichtlichkeit des Skriptes leider nicht. Zusätzlich werden innerhalb dieses SQL's leider zahlreiche Variablen verwendet, was eine erhebliche manuelle Anpassung erfordert, wenn ich dies in eine Zeichenkette umwandeln will.
 
Sry ich verwende MSSQL, bin mit Oracle nicht so vertraut. Das @ kannst du aus meinem Beispiel streichen, da muss einfach nur die Variable stehen die den Spaltennamen enthält. Tausch mal auch EXEC gegen EXECUTE.
 
Mache ich etwas falsch oder ist hier noch ein Fehler drin?

EXEC gibts nicht in Oracle, und ich bin mir auch nicht sicher, was mit dieser Syntax beabsichtigt werden soll.

Wieso hat das Auswirkungen auf Syntax Highlightning ?
Die Funktion generiert dir ein Statement. Dann setzt der Entwickler sein Versatzstück ein und führt es aus.
Wenn das nicht passt, hab ich die Aufgabenstellung nicht verstanden.
 
Also mit EXECUTE scheint es nicht zu funktionieren. Ich erhalte weiterhin die gleiche Fehlermeldung. Soweit ich das von Oracle her kenne, kann man mit EXECUTE IMMEDIATE nur einen gesamten SQL ausführen, aber das was wir hier versuchen ist ja nur einen Teil (quasi die WHEN-Bedingung) auszuführen. Mit MSSQL kenne ich mich leider überhaupt nicht aus, um hier einen Vergleich machen zu können.

Was die Funktion betrifft, da gibt es zwei Dinge die mich stören. Ich habe bisher noch nicht erwähnt, dass wir auf der Datenbank keine Berechtigung haben, um selbst eine Funktion zu installieren. Wir führen bei uns lediglich BEGIN-DECLARE-END Skripte aus.

Aber, theoretisch, wenn wir das Problem über eine Funktion wirklich gelöst kriegen, kann ich den Datenbankadministrator natürlich darum bitten, uns diese Hilfs-Funktion einzubauen.

Was die Auswirkungen auf das Syntax-Highlightning betrifft, damit meine ich, dass der individuelle Code des jeweiligen Entwicklers, der ja seinen SQL hier an der Stelle "/*TODO Skript einsetzen */" einsetzen soll, innerhalb des Skriptes als Zeichenkette betrachtet wird, die in "L_STMT" gespeichert wird. Dadurch zeigt der Editor (TOAD oder SQL Developer) kein Syntax Highlightning mehr an. Funktionieren würde das theoretisch, ist aber wahnsinnig aufwendig, wenn man seinen Code auf diese Weise innerhalb der Funktion einfügt:

1. werden innerhalb des SQL's der hier eingesetzt werden soll, verschiedene Variablen aus PL/SQL verwendet. Diese muss ich dann immer manuell innerhalb des SQL's verketten.

Aus einem "...WHERE zeitstempel = t_meinDatum AND ..." müsste man z.B. ein "WHERE zeitstempel = ' + t_meinDatum + ' AND" machen. Wie erwähnt, bei einem bis zu 900 Zeilen langem SQL, ist das etwas umständlich alle Variablen auf diese Art zu verketten. Wenn man den SQL später aus dem Skript wieder mal rausziehen und weiterentwickeln möchte, muss man ja wieder alles "umtransformieren" und die Verkettungen aufheben.

2. Durch das Fehlende Syntax Highlightning verliert die Funktion oder das Skript an Übersichtlichkeit. Durch die unter 1. genannte Verkettung von Variablen, die dann zusätzlich nötig sein wird, leidet die Übersichtlichkeit dann erneut.

Ich hoffe wir reden jetzt nicht aneinander vorbei?
 
innerhalb des Skriptes als Zeichenkette betrachtet wird, die in "L_STMT" gespeichert wird.

Nein, das war nicht beabsichtigt. Die Funktion liefert dir lediglich ein Template, das sicherstellt, dass die übergebene Spalte ins UPDATE genommen wird.
Damit ist die Arbeit der Funktion auch schon beendet und der Entwickler nimmt das Statement in einen Editor seiner Wahl und ersetzt dort das TODO mit seinem Skript.

Ich zitiere:
Dadurch besteht jedoch das Risiko, dass ein Entwickler es einfach mal vergisst, diese Zeile am Ende des Skriptes anzupassen, und somit ggf. die Berechnungsergebnisse eines anderen Kollegen (in einer anderen Spalte) zu zerschießen.

Das kann durch dieses Template vermieden werden, sofern nicht doch wieder ein bereits vorhandenen Skript verwendet wird ;)

Es geht auch ohne Funktion:
Code:
declare
  l_stmt VARCHAR2(1000);
  l_targetcolumn varchar2(30):='eine_spalte';
BEGIN
  l_stmt:='MERGE INTO irgendeinetabelle '||CHR(10)||' USING ( /*TODO Skript einsetzen */) s'||CHR(10)||
          'ON ( d.id=s.id)'||CHR(10)||' WHEN MATCHED THEN UPDATE '||l_targetcolumn||'=s.kpi;'||CHR(10)||'COMMIT;';
  dbms_output.put_line(l_stmt);
END;
/

Im Toad/SQL Dev /SQLPLus etc. muss zuvor der DBMS Output angeschaltet und die Variable l_targetcolumn auf den gewünschten Wert gesetzt werden.
 
Ja, das ganze würde theoretisch soweit funktionieren.

Aber wie gesagt, die Skripte werden von uns leider regelmäßig weiterentwickelt. Haben wir also 900 Zeilen Code, die ich hier als Entwickler einsetze, dann muss ich alle Variablen die innerhalb des Codes zusätzlich verwendet wurden immer wieder verketten. Und wenn ich später den Code wieder aus dem Skript kopieren möchte, um ihn weiter zu entwickeln, dann muss ich diese Verkettungen alle wieder auflösunen. Verstehst Du was ich meine? Das ist leider etwas umständlich und schwer zu pflegen...
 
ohje, das scheint garnicht so leicht zu erklären zu sein ^^

also wir haben diese eine tabelle, in der wir bestimmte spalten pflegen müssen. wir bekommen also eine anforderung "in spalte kpi_20 muss zukünftig der wert x berechnet werden" ...dann schreiben wir eine dafür individuelle skript-vorlage und berechnen für diese spalte den wert x. funktioniert alles, wird der job automatisiert und aktualisiert den wert x täglich.

leider müssen wir hier sehr flexibels ein. es kann also häufiger vorkommen, dass unser anforderer schreibt, dass der wert x in der spalte kpi_20 zukünftig eine komplett neue berechnungslogik erhält. wir müssen also regelmäßig anpassungen vornehmen.

und da kommen wir zu der skript-vorlage die ich derzeit versuche zu entwickeln: zum einen möchte ich meinem team und mir eine einfache pl/sql skriptvorlage basteln, die wir einfach nutzen können um eine spezielle spalte in der zieltabelle pflegen zu können. auf der anderen seite müssen wir hier natürlich flexibel sein, was den selbst-entwickelten code angeht (bei dir liebevoll mit dem platzhalter "/*TODO Skript einsetzen */" beschrieben.

dieser code-abschnitt ist aber quasi das herz-stück des gesamten skriptes was wir hinterher regelmäßig ausführen müssen. er liefert plump gesagt erstmal nur zwei werte: die zu aktualisierende ID des jeweiligen datensatzes und dessen wert, den ich in eine speziell vorgegebene spalte schreiben möchte. und dieser code-abschnitt ist meistens relativ komplex. es werden spezielle variablen innerhalb des skriptes generiert und dort verwendet, und er kann unter umständen auch sehr lang werden (je nach komplexität).

würde ich den von dir vorgeschlagenen weg gehen, wäre es immer wieder nötig, den fertigen code anzupassen. es müssten hochkommas maskiert werden, und variablen die innerhalb des codes verwendet werden müssten entsprechend verkettet werden. wie erwähnt, aus einem

Code:
WHERE datumsspalte = t_meinDatum AND textspalte = 'Test' AND...

müsste man ein
Code:
WHERE datumsspalte = ' + t_meinDatum + ' AND testspalte = ''Test'' AND...

machen, usw.

das ist wahnsinnig umständlich, wenn ich einen Code mit beispielsweise 900 Zeilen so anpassen müsste. Die Arbeit wäre es mir ja wert, wenn ich nicht wüsste, dass in den nächsten Monaten oder Wochen eine neue Anforderung hereinschneien könnte, die es nötig macht, das ganze Skript noch einmal zu überarbeiten. Das macht es dann wahnsinnig unübersichtlich und sehr schwer zu pflegen.

Und wie erwähnt, das Syntaxhighlightning funktioniert auch nicht mehr (schließlich kann der Editor bei dieser Methode SQL nicht mehr von einer normalen Zeichenkette unterscheiden), wenn ich es auf diesem Wege löse, was die Lesbarkeit zusätzlich erschwert.

So sieht es aus...
upload_2017-6-8_16-26-46.png

Und so sollte es aussehen...
upload_2017-6-8_16-27-46.png

Ich habe mir erhofft, dass es eine elegantere Lösung für dieses Problem gäbe, aber ich fürchte, von dem Gedanken muss ich mich wohl verabschieden.
 
Ich kapiert was du meinst, aber genau das macht die Funktion ja.
Du rufst die Funktion auf (ohne das TODO anzupassen) und setzt im DECLARE nur den gewünschten Spaltennamen.

Jetzt bekommst du genau den MERGE den du haben willst mit dem TODO Kommentar und dann ersetzt ihr den Kommentar mit euren Code.
Im PLSQL Code wird nichts angepasst (bis auf den Tabellennamen aber auch der nur einmalig).
 
Also

1) Du hast recht, mit meiner Variante wird nur ein Teil des Codes ausgeführt, das habe ich übersehen. Das liegt auch daran das mir Oracle nicht so im Blut liegt, daher überlasse ich gerne drdimitri das Feld.
2) Grundsätzlich ist EXECUTE() der richtige Weg, du musst nur mehr Code damit ausführen. Auch wenn die Lösung eine Funktion wird, wird das glaube ich nicht verzichtbar sein.
3) Ich glaube dimitri versucht zu sagen das du sowohl einzelne Parameter per Variable an die Funktion übergeben kannst als auch den gesammten "Teil-Code" (deine 900 Zeilen) in einer Zeichenkette (gerne auch mit Zeilenumbrüchen zur Lesbarkeit) und diesen dann mit dem Rest verknüpfst und ausführst.
 
Guten Morgen Jungs,

sorry, aber ich hatte gestern abend noch einen Termin.

Sorry, aber irgendwie habe ich das Gefühl ich stehe gerade auf dem Schlauch.

Ich habe jetzt mal dein zweites Beispiel (ohne Funktion) angepasst, und ausprobiert. Das Folgende kommt jetzt raus:

Code:
SET SERVEROUTPUT ON

DECLARE

  l_stmt VARCHAR2(1000);
  l_targetcolumn varchar2(30) := 'KPII_69';
  t_Datum DATE := TO_DATE('01.01.2017', 'DD.MM.YYYY');
BEGIN

  l_stmt := 'MERGE INTO ZIELTABELLE D '||CHR(10)||' USING
  (
    /*TODO Skript einsetzen */
            SELECT ID,
                   KPI
            FROM   QUELLTABELLE
            WHERE  LADEDATUM = t_Datum
  ) S'||CHR(10)||
          'ON ( D.ID = S.ID)'||CHR(10)||' WHEN MATCHED THEN UPDATE '||l_targetcolumn||'=S.KPI;'||CHR(10)||'COMMIT;';
        
  dbms_output.put_line(l_stmt);
END;
/

Was jetzt passiert, ist, dass er mir lediglich den gesamten (neuen) Merge im Ausgabefenster ausgibt. Und die WHERE-Bedingung die auf t_Datum filtert, wird nicht ergänzt. D.h. hier steht jetzt einfach nur t_Datum. Wenn ich den Code so wie er ausgegeben wurde jetzt ausführe, meckert er, dass t_Datum nicht definiert ist.

Ich könnte also auch nicht einfach EXECUTE IMMEDIATELY(l_stmt) machen, weil die Variable nicht vorhanden ist. Folgendes würde dann vmtl funktionieren:

Code:
SET SERVEROUTPUT ON

DECLARE

  l_stmt VARCHAR2(1000);
  l_targetcolumn varchar2(30) := 'KPII_69';
  t_Datum DATE := TO_DATE('01.01.2017', 'DD.MM.YYYY');
BEGIN

  l_stmt := 'MERGE INTO ZIELTABELLE D '||CHR(10)||' USING
  (
    /*TODO Skript einsetzen */
            SELECT ID,
                   KPI
            FROM   QUELLTABELLE
            WHERE  LADEDATUM = TO_DATE(''' || TO_DATE(t_Datum, 'DD.MM.YYYY') || ''', ''DD.MM.YYYY'')
  ) S'||CHR(10)||
          'ON ( D.ID = S.ID)'||CHR(10)||' WHEN MATCHED THEN UPDATE '||l_targetcolumn||'=S.KPI;'||CHR(10)||'COMMIT;';
        
  dbms_output.put_line(l_stmt);
END;
/

Ich müsste also innerhalb der Zeichenkette, das Datum auf eine wahnsinnig komplizierte Art verketten, damit hier der richtige Output raus kommt. Und in diesem Beispiel hier, gibt es nur diese eine Variable. In unseren SQL's können das schnell mal um die 30 Positionen sein, an denen wir diese einsetzen müssen.

Und dazu kommt noch, wie bereits erwähnt, dass das Syntax-Highlightning für den gesamten Code im SQL Developer (und auch im TOAD), einfach nur einfarbig ist, da der Code hier als Zeichenkette betrachtet wird.
 
Werbung:
Jetzt bekommst du genau den MERGE den du haben willst mit dem TODO Kommentar und dann ersetzt ihr den Kommentar mit euren Code.
Im PLSQL Code wird nichts angepasst (bis auf den Tabellennamen aber auch der nur einmalig).

Bevor du jetzt wieder mit Syntax Highlightning beginnst, ließt dir den Text nochmal durch, dann nochmal und dann vergleichst ihn mit dem was du grade gemacht hast.

Die Funktion liefert dir ein Template, welches nur die Updatebedingung anpasst. Nicht mehr und nicht weniger. Genau das, was dir im Ursprungspost so wichtig war, damit das nicht versehentlich von einem anderen Skript übernommen und nicht angepasst wird.

Es liefert nicht dein komplettes 900 Zeilen SQL, das ergänzt du anschließend.
 
Zurück
Oben