Dynamisch formulierte Anweisungen
17 Dynamisch formulierte Anweisungen
17.2 Vorbereitung und Ausführung einer SQL-Anweisung
17.3 Dynamisch formulierte
SELECT-Anweisungen in ESQL/C
17.4 Dynamisch formulierte SQL-Anweisungen
und Optimierung
In diesem Kapitel
werden eingebettete SQL-Anweisungen
dargestellt, die
die Formulierung von
Anweisungen zur
Laufzeit ermöglichen. Im ersten
Teil des Kapitels werden
die
eingebetteten SQL-Anweisungen PREPARE,
EXECUTE und
EXECUTE IMMEDIATE erläutert. Anschließend wird die dynamisch
formulierte SELECT-Anweisung erklärt. Am
Ende des Kapitels
wird
gezeigt, wie die
Formulierung der Anweisungen
zur
Laufzeit die Effizienz eines
Programms erhöhen kann.
Alle im vorherigen Kapitel
erläuterten SQL-Anweisungen werden
explizit in ein Programm eingebettet und sind damit zur
Übersetzungszeit bekannt.
Mit diesen Anweisungen
ist es
nicht möglich, solche Anwendungen
zu programmieren, bei
denen z.B. eine ganze
Anweisung am Bildschirm vom Benutzer
eingegeben und dann
vom System analysiert und ausgeführt
wird. Solche Anwendungen können nur mit Hilfe von
speziellen
SQL-Anweisungen programmiert werden, die eine
dynamische
Formulierung der Anweisungen zur
Laufzeit erlauben.
Grundsätzlich existieren zwei Fälle,
bei denen die Verwendung
solcher speziellen SQL-Anweisungen
notwendig ist:
- dem Programm ist
zur Übersetzungszeit nicht
bekannt,
welche Anweisung ausgeführt
werden soll;
- die Anzahl und der Typ der
Datenwerte, die eine Anweisung
dem Programm zurückgibt, sind
unbekannt.
Diese beiden Fälle sind grundverschieden und werden
deswegen
auch unterschiedlich behandelt.
Falls eine Anweisung
dem
Programm zur Übersetzungszeit
nicht bekannt ist, muß diese
Anweisung zuerst vorübersetzt und
dann ausgeführt werden. Die
so
bearbeiteten, dynamisch
formulierten Anweisungen liefern
dem Programm nur die Rückgabewerte,
die in der SQLCA-Struktur
gespeichert werden, im Unterschied z.B. zur SELECT-Anweisung
mit der INTO-Klausel, die auch
Datenwerte liefert.
Im
zweiten Fall, in dem
Anzahl und Typ
der Datenwerte
nicht bekannt ist, muß dem
Programm die Information darüber
mitgeteilt werden. Das typische
Beispiel dafür ist
die
SELECT-Anweisung mit INTO-Klausel. Das Programm, das eine
solche Anweisung dynamisch bearbeitet,
muß wissen, wieviele
Datenwerte jeder Reihe ausgewählt
werden, damit es
die
entsprechende Anzahl der
Host-Variablen zur Verfügung stellen
kann. Zusätzlich dazu muß auch der
Datentyp und die Länge der
Datenwerte dem Programm bekannt sein,
damit die Eigenschaften
der Host-Variablen definiert werden
können.
Alle
SQL-Anweisungen, die notwendig
sind, die beiden obigen
Fälle zu behandeln, werden in
den folgenden Abschnitten
erörtert.
Wie
wir schon erwähnt haben, muß eine
Anweisung, die dem
Programm zur Übersetzungszeit nicht bekannt ist, vorbereitet
und ausgeführt werden. Das geschieht
mit den Anweisungen
PREPARE und
EXECUTE .
Mit der PREPARE-Anweisung wird eine
Anweisung vorübersetzt
und
damit für die
spätere Ausführung vorbereitet. Die
PREPARE-Anweisung hat folgende Form
PREPARE obj_name FROM quell_string
quell_string ist entweder eine
Zeichenkette oder eine
Host-Variable, die eine Anweisung enthält. Ist quell_string
eine
Host-Variable, muß sie als alphanumerischer Datentyp
definiert werden.
obj_name definiert eine
SQL-Variable, die die in quell_string
enthaltene und vorübersetzte
Anweisung aufnimmt.
Die
in quell_string gespeicherte Anweisung darf die
Schlüsselwörter "EXEC SQL" und "END-EXEC." nicht enthalten.
Genauso darf sie keine
Host-Variablen enthalten. Anstelle
jeder Host-Variablen muß das
Zeichen "?" als
Platzhalter
benutzt werden. Bei der Ausführung wird jedes dieser Zeichen
durch den entsprechenden Wert
ersetzt.
Durch die EXECUTE-Anweisung wird die
mit PREPARE vorbereitete
Anweisung ausgeführt. Die EXECUTE-Anweisung hat folgende
Form:
EXECUTE obj_name [USING param_liste]
obj_name kennzeichnet die SQL-Variable, die zuvor in
der
PREPARE-Anweisung angegeben
wurde. Sie beinhaltet die
vorübersetzte Anweisung, die
anschließend ausgeführt wird.
param_liste kennzeichnet eine Liste von Host-Variablen. Wie
wir schon bei der PREPARE-Anweisung
erwähnt haben, dürfen
die dynamisch formulierten Anweisungen keine Host-Variablen
beinhalten. Statt dieser
enthalten die Anweisungen
einen
Platzhalter. Jede Host-Variable in param_liste
ersetzt den
entsprechenden Platzhalter innerhalb der PREPARE-Anweisung.
Anzahl und Datentyp der Host-Variablen müssen schon zur
Übersetzungszeit bekannt sein.
Die folgenden beiden Beispiele zeigen ein COBOL-
bzw. ein
C-Programm, in denen zuerst
eine Host-Variable PSTRING
definiert wird. In PSTRING
ist die Anweisung gespeichert,
die vom Anwender am Bildschirm
eingegeben wurde. Mit
der
PREPARE-Anweisung wird die
dynamisch formulierte Anweisung
vorübersetzt und
der SQL-Variablen obj_1 übergeben.
Anschließend wird die
Anweisung mittels EXECUTE ausgeführt.
Die
EXECUTE-Anweisung in den
Beispielen 17.1 und
17.2
beinhaltet nicht die USING-Klausel. Deswegen ist es möglich,
nur solche Anweisungen am
Bildschirm zu formulieren, die
keine Platzhalter enthalten.
Beispiel
17.1
IDENTIFICATION DIVISION.
PROGRAM-ID. BSP1701.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION
END-EXEC.
77
PRNR PIC X(4) VALUE SPACES.
77
AUFGABE PIC X(15) VALUE SPACES.
77
MNR PIC S9(9) COMP.
77
PSTRING PIC X(80).
EXEC SQL END DECLARE SECTION
END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
PROCEDURE DIVISION.
A1.
PERFORM DBOPEN.
PERFORM A100-PREPARE.
PERFORM A200-EXECUTE.
PERFORM DBCLOSE
STOP RUN.
DBOPEN.
EXEC SQL
CONNECT BEISPIEL
END-EXEC.
IF SQLCODE OF SQLCA < 0 THEN
DISPLAY
"FEHLER BEIM DB-OEFFNEN"
STOP RUN.
A100-PREPARE.
DISPLAY "Geben Sie die SQL-Anweisung an".
ACCEPT PSTRING.
EXEC SQL
PREPARE obj_1 FROM :PSTRING
END-EXEC.
A200-EXECUTE.
EXEC SQL
EXECUTE obj_1
END-EXEC.
IF SQLCODE OF SQLCA < 0 THEN
DISPLAY "Fehler in
EXECUTE".
DBCLOSE.
EXEC SQL
DISCONNECT
END-EXEC.
Beispiel 17.2
/* Beispiel 17.02 */
EXEC SQL
INCLUDE SQLCA;
#include <stdio.h>
EXEC SQL BEGIN DECLARE SECTION;
char zkette[71];
int m_nr;
EXEC SQL END DECLARE SECTION;
main()
{
EXEC SQL
CONNECT beispiel;
if (sqlca.sqlcode < 0 )
{printf
("Fehler beim DB-Oeffnen\n");
printf ("Fehlernummer: %d\n", sqlca.sqlcode);
exit(sqlca.sqlcode);
}
printf("Geben Sie die SQL-Anweisung an\n");
gets(zkette);
EXEC SQL
PREPARE obj FROM :zkette;
if (sqlca.sqlcode < 0 )
{printf
("Fehler in PREPARE\n");
printf ("Fehlernummer: %d\n", sqlca.sqlcode);
exit (sqlca.sqlcode);
}
EXEC SQL
EXECUTE obj;
if (sqlca.sqlcode < 0 )
{printf
("Fehler in PREPARE\n");
printf ("Fehlernummer: %d\n", sqlca.sqlcode);
exit (sqlca.sqlcode);
}
EXEC SQL
DISCONNECT;
}
Das COBOL-Programm
im folgenden Beispiel zeigt eine
EXECUTEAnweisung
mit der INTO-Klausel. Das Beispiel danach zeigt
ein
entsprechendes C-Programm.
Beispiel
17.3
IDENTIFICATION DIVISION.
PROGRAM-ID. BSP1703.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION
END-EXEC.
77
ABTNR PIC X(4) VALUE SPACES.
77
MNR PIC S9(9) COMP.
77 ZKETTE PIC
X(80).
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
PROCEDURE DIVISION.
A1.
PERFORM
DBOPEN.
DISPLAY
"Geben Sie die SQL-Anweisung
an".
ACCEPT ZKETTE.
DISPLAY ZKETTE.
EXEC SQL
PREPARE OBJ FROM :ZKETTE
END-EXEC.
DISPLAY SQLCODE OF SQLCA.
PERFORM A100-EXECUTE UNTIL MNR = 0.
PERFORM DBCLOSE.
STOP RUN.
DBOPEN.
EXEC SQL
CONNECT BEISPIEL
END-EXEC.
IF SQLCODE OF SQLCA < 0 THEN
DISPLAY
"FEHLER BEIM DB-OEFFNEN"
STOP RUN.
A100-EXECUTE.
DISPLAY
"Geben Sie die Mitarbeiternummer an".
ACCEPT MNR.
EXEC SQL
EXECUTE OBJ USING :MNR
END-EXEC.
IF SQLCODE OF SQLCA < 0 THEN
DISPLAY "Fehler in
EXECUTE"
DISPLAY SQLCODE OF SQLCA.
DBCLOSE.
EXEC SQL
DISCONNECT
END-EXEC.
Beispiel 17.4
/* Beispiel 17.04 */
EXEC SQL
INCLUDE sqlca;
#include <stdio.h>
EXEC SQL BEGIN DECLARE SECTION;
char zkette[71];
int m_nr;
EXEC SQL END DECLARE SECTION;
main()
{
EXEC SQL
CONNECT beispiel;
if (sqlca.sqlcode < 0 )
{printf
("Fehler beim DB-Oeffnen\n");
printf ("Fehlernummer: %d\n", sqlca.sqlcode);
exit(sqlca.sqlcode);
}
printf("Geben Sie die SQL-Anweisung an\n");
gets(zkette);
EXEC SQL
PREPARE obj from :zkette;
if (sqlca.sqlcode < 0 )
{printf
("Fehler in PREPARE\n");
printf ("Fehlernummer: %d\n", sqlca.sqlcode);
exit (sqlca.sqlcode);
}
printf("Geben Sie die Mitarbeiternummer an:\n");
scanf("%d",
&m_nr);
EXEC SQL
EXECUTE obj using :m_nr;
if (sqlca.sqlcode < 0 )
{printf
("Fehler in PREPARE\n");
printf ("Fehlernummer: %d\n", sqlca.sqlcode);
exit (sqlca.sqlcode);
}
EXEC SQL
DISCONNECT;
}
Folgende SQL-Anweisungen können z.B.
in den Beispielen 17.3
und 17.4 vorbereitet und ausgeführt
werden:
UPDATE arbeiten SET pr_nr='p2' WHERE m_nr=?;
DELETE FROM arbeiten WHERE m_nr=?;
INGRES unterstützt eine weitere
eingebettete SQL-Anweisung
EXECUTE IMMEDIATE
obj_name,
mit der
eine Anweisung gleichzeitig vorbereitet und
ausgeführt werden kann. Damit
beinhaltet die EXECUTE
IMMEDIATE-Anweisung die
Funktionalität der beiden Anweisungen
PREPARE und
EXECUTE. Im Unterschied zu PREPARE
und
EXECUTE, mit denen eine
Anweisung einmal vorbereitet
und
mehrmals ausgeführt werden kann,
wird bei der
EXECUTE
IMMEDIATE-Anweisung die Vorbereitung
und die Ausführung immer
zusammen durchgeführt. Damit eignet sich EXECUTE IMMEDIATE
besonders dann, wenn eine
Anweisung nur einmal in einem
Programm ausgeführt werden soll.
Eine
dynamisch formulierte
SELECT-Anweisung mit der
INTOKlausel
kann nicht mit
PREPARE und EXECUTE
bzw. EXECUTE
IMMEDIATE ausgeführt werden. Das
Ergebnis einer solchen
Abfrage wird an das Progamm zurückgegeben und die
Datenwerte
den
Host-Variablen zugewiesen. Die
Anzahl und der Datentyp
der
ermittelten Datenwerte ist
erst zur Laufzeit bekannt.
Deswegen ist es nicht möglich,
Anzahl und Datentyp
der
Host-Variablen im
voraus im Programm
festzulegen. Diese
Information muß zur Laufzeit dem
Programm mitgeteilt werden.
Die
dynamisch formulierte
SELECT-Anweisung wird mit Hilfe
der DESCRIBE-Anweisung und einem
Cursor programmiert. Mit der
DESCRIBE-Anweisung wird die Liste der Spalten in der
SELECTAnweisung
untersucht sowie die Anzahl
und der Datentyp der
Spalten festgestellt. Deswegen muß im Programm Speicherplatz
zur Verfügung gestellt werden, in dem
dann die Datenwerte der
aktuellen Reihe gespeichert werden.
Die Aufgabe des Cursors
und
der mit ihm verbundenen
ausführbaren SQL-Anweisungen
OPEN, FETCH und CLOSE ist,
die Reihen der
Treffermenge
sequentiell zu lesen und dem Programm
zu übergeben.
Der
Speicherplatz, der im
Programm zur Speicherung
der
Datenwerte einer Reihe benutzt
wird, muß dynamisch
zur
Verfügung gestellt werden.
Das heißt: Mit
den Sprachen,
die über eine dynamische
Speicherverwaltung verfügen (wie
C
und Ada), ist
es möglich, die
dynamisch formulierte
SELECT-Anweisung mit der INTO-Klausel
zu programmieren.
Die DESCRIBE-Anweisung hat folgende
Form:
DESCRIBE obj INTO deskriptor_name;
obj kennzeichnet die in PREPARE vorübersetzte SQL-Anweisung.
deskriptor_name ist ein Zeiger, der auf eine Struktur zeigt,
in
der Anzahl und Datentyp aller mittels SELECT-Anweisung
ausgewählten Datenwerte gespeichert werden. Diese Struktur
heißt SQLDA ("SQL
Descriptor Area"), und sie muß
genauso
wie die SQLCA-Struktur mit der INCLUDE-Anweisung im Programm
eingefügt werden.
Jede SQLDA-Struktur hat u.a.
zwei Teile - ein Datenfeld, in
dem die Information über die
Anzahl aller Datenwerte der
Abfrage gespeichert wird sowie
einen Vektor, der für jeden
Datenwert den Typ und die Länge enthält.
Mit der DESCRIBE-Anweisung ist es
grundsätzlich möglich (aber
nicht unbedingt notwendig), jede
mit PREPARE vorübersetzte
SQL-Anweisung dynamisch zu
formulieren.
Die
Ersetzung der zur
Übersetzungszeit formulierten
SQL-Anweisungen durch
die entsprechenden dynamisch
formulierten SQL-Anweisungen kann die
Effizienz des Programms
erhöhen. Das typische Beispiel
ist der Fall,
in dem
ein Anwender interaktiv
Eingaben macht und
dadurch eine
SQL-Anweisung mehrmals in einer
Schleife ausgeführt werden
muß.
Mit Hilfe der PREPARE-Anweisung ist es oft möglich, die
Vorübersetzung der Anweisung
außerhalb der Schleife und damit
nur einmal auszuführen.
Die folgenden COBOL-Beispiele zeigen
zwei funktionell gleiche
Programmteile, in denen
eine UPDATE-Anweisung mit und
ohne
eine
PREPARE-Anweisung ausgeführt
wird. Im ersten Beispiel
ist diese Anweisung zur Übersetzungszeit und im zweiten erst
zur Ablaufzeit formuliert.
Beispiel 17.5
Mehrere Mitarbeiter der Firma
haben ihre Abteilungen
gewechselt. Ändern Sie die
Abteilungsnummer dieser
Mitarbeiter. Sowohl
die Mitarbeiter- als
auch die
Abteilungsnummer sollen am Bildschirm
eingegeben werden.
IDENTIFICATION
DIVISION.
PROGRAM-ID. BSP1705.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
77
ABTNR PIC X(4) VALUE SPACES.
77
MNR PIC S9(9) COMP.
EXEC SQL END DECLARE SECTION
END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
PROCEDURE DIVISION.
A1.
PERFORM
DBOPEN.
PERFORM A100-UPDATE UNTIL MNR = 0.
PERFORM DBCLOSE.
STOP RUN.
DBOPEN.
EXEC SQL
CONNECT BEISPIEL
END-EXEC.
IF SQLCODE OF SQLCA < 0 THEN
DISPLAY "FEHLER BEIM DB-OEFFNEN"
STOP RUN.
A100-UPDATE.
DISPLAY
"Geben Sie die Mitarbeiternummer an".
DISPLAY "Endekriterium ist 0".
ACCEPT MNR.
DISPLAY "Geben Sie die neue Abteilungsnummer
an".
ACCEPT ABTNR.
EXEC SQL
UPDATE mitarbeiter
SET abt_nr = :ABTNR
WHERE m_nr = :MNR
END-EXEC.
IF SQLCODE OF SQLCA < 0 THEN
DISPLAY "FEHLER BEI UPDATE".
DBCLOSE.
EXEC SQL
DISCONNECT
END-EXEC.
Beispiel 17.6
IDENTIFICATION
DIVISION.
PROGRAM-ID. BSP1706.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION
END-EXEC.
77
ABTNR PIC X(4) VALUE SPACES.
77
MNR PIC S9(9) COMP.
EXEC SQL END DECLARE SECTION
END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
PROCEDURE DIVISION.
A1.
PERFORM DBOPEN.
EXEC SQL
PREPARE obj FROM
"UPDATE mitarbeiter SET
abt_nr=? WHERE m_nr=?"
END-EXEC.
PERFORM A100-EXECUTE UNTIL MNR = 0.
PERFORM DBCLOSE.
STOP RUN.
DBOPEN.
EXEC SQL
CONNECT BEISPIEL
END-EXEC.
IF SQLCODE OF SQLCA < 0 THEN
DISPLAY "FEHLER BEIM DB-OEFFNEN"
STOP RUN.
A100-EXECUTE.
DISPLAY
"Geben Sie die Mitarbeiternummer an".
DISPLAY "Endekriterium ist 0".
ACCEPT MNR.
DISPLAY "Geben Sie die neue Abteilungsnummer
an".
ACCEPT ABTNR.
EXEC SQL
EXECUTE obj USING :ABTNR, :MNR
END-EXEC.
IF SQLCODE OF SQLCA < 0 THEN
DISPLAY "FEHLER BEI EXECUTE".
DBCLOSE.
EXEC SQL
DISCONNECT
END-EXEC.
In Beispiel
17.5 wird für
jeden Mitarbeiter, der
die
Abteilung gewechselt hat, einmal
der Abschnitt A100-UPDATE
und
damit auch die
UPDATE-Anweisung ausgeführt. Bei jeder
Wiederholung muß
die UPDATE-Anweisung neu
vorübersetzt
werden.
In
Beispiel 17.6 wird
die UPDATE-Anweisung mit
PREPARE
außerhalb des A100-EXECUTE-Abschnitts
vorbereitet und dadurch
nur
einmal vorübersetzt. Die Anzahl
der E/A-Operationen ist
in diesem Beispiel wesentlich
geringer als im vorherigen.
Die C-Programme in
Beispielen 17.7 und
17.8 entsprechen
jeweils den COBOL-Programmen in
Beispielen 17.5 bzw. 17.6.
Beispiel 17.7
/* Beispiel 17.07 */
EXEC SQL
INCLUDE sqlca;
#include <stdio.h>
EXEC SQL
BEGIN DECLARE SECTION;
char abt_nr[5};
int m_nr;
EXEC SQL END DECLARE SECTION;
main()
{
EXEC SQL
CONNECT beispiel;
if (sqlca.sqlcode < 0 )
{printf
("Fehler beim DB-Oeffnen\n");
printf ("Fehlernummer: %d\n", sqlca.sqlcode);
exit(sqlca.sqlcode);
}
m_nr = 1;
while (m_nr != 0)
{
printf("Geben Sie die Nummer des Mitarbeiters
an:\n");
printf("Endekriterium ist 0 \n");
scanf ("%d", &m_nr);
printf("Geben
Sie die neue Abteilungsnummer an\n");
scanf("%s",abt_nr);
EXEC
SQL
UPDATE
mitarbeiter
SET abt_nr = :abt_nr
WHERE m_nr = :m_nr;
if (sqlca.sqlcode < 0 )
{printf
("Fehler in UPDATE\n");
printf ("Fehlernummer: %d\n", sqlca.sqlcode);
exit (sqlca.sqlcode);
}
}
EXEC SQL
DISCONNECT;
}
Beispiel 17.8
/* Beispiel 17.08 */
EXEC SQL
INCLUDE sqlca;
#include <stdio.h>
EXEC SQL BEGIN DECLARE SECTION;
int m_nr;
char abt_nr[5];
EXEC SQL END DECLARE SECTION;
main()
{
EXEC SQL
CONNECT beispiel;
if (sqlca.sqlcode < 0 )
{printf
("Fehler beim DB-Oeffnen\n");
printf ("Fehlernummer: %d\n", sqlca.sqlcode);
exit(sqlca.sqlcode);
}
EXEC SQL
PREPARE obj from
'UPDATE mitarbeiter SET abt_nr=?
WHERE m_nr=?';
if (sqlca.sqlcode < 0 )
{printf
("Fehler in PREPARE\n");
printf ("Fehlernummer: %d\n", sqlca.sqlcode);
exit (sqlca.sqlcode);
}
m_nr = 1;
while (m_nr != 0)
{
printf("Geben Sie die Mitarbeiternummer
an:\n");
printf("Endekriterium ist 0 \n");
scanf("%d",
&m_nr);
printf("Geben
Sie die neue Abteilungsnummer an\n");
scanf("%s", abt_nr);
EXEC SQL
EXECUTE obj using :abt_nr, :m_nr;
if (sqlca.sqlcode < 0 )
{printf
("Fehler in EXECUTE\n");
printf ("Fehlernummer: %d\n", sqlca.sqlcode);
exit (sqlca.sqlcode);
}
}
EXEC SQL
DISCONNECT;
}
A.17.1 Bereiten Sie folgende Anweisungen:
a) UPDATE
mitarbeiter SET aufgabe = ? WHERE m_nr = ?
b) DELETE
FROM arbeiten WHERE m_name = ?
c) DELETE
FROM mitarbeiter WHERE m_name = ?
d) UPDATE
abteilung SET stadt = ? WHERE abt_nr = ?
mit der Programmiersprache Ihrer Wahl vor und führen
Sie
sie anschließend aus.