UEB Übungen zum Seminar 4053

UEB
Übungen
zum Seminar 4053
1.1
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
1.10
1.11
1.12
1.13
Aufgaben zu Kapitel 1 ....................................................................... 2
Aufgaben zu Kapitel 2 ....................................................................... 3
Aufgaben zu Kapitel 3 ....................................................................... 6
Aufgaben zu Kapitel 4 ....................................................................... 8
Aufgaben zu Kapitel 5 ....................................................................... 9
Aufgaben zu Kapitel 6 ..................................................................... 10
Aufgaben zu Kapitel 7 ..................................................................... 11
Aufgaben zu Kapitel 8 ..................................................................... 12
Aufgaben zu Kapitel 9 ..................................................................... 14
Aufgabe zu Kapitel 10 ..................................................................... 15
Aufgaben zu Kapitel 11 ................................................................... 16
Aufgaben zu Kapitel 13 ................................................................... 18
Zusatzaufgaben............................................................................... 19
1.2.066 / 4053
UEB-1
Übungen
UEB
1
Übungen
1.1
Aufgaben zu Kapitel 1
1. Welcher Teil ist bei einem PL/SQL-Block obligatorisch, und welche
Teile kann ein Block noch enthalten?
2. Welches Recht brauchen Sie
a) um eine Funktion anlegen zu können?
b) um eine Funktion im eigenen Schema ausführen zu können?
c) um eine Funktion in einem fremden Schema ausführen zu können?
3. Ist dieser Code korrekt?
BEGIN
var_a
/*
var_b
*/
BEGIN
date;
number; /* Wird für die Gehälter benutzt */
NULL;
END;
/
4. Wie können Sie sicherstellen, dass Ihre PL/SQL-Programme so
schnell wie möglich laufen?
1.2.066 / 4053
UEB-2
Übungen
1.2
UEB
Aufgaben zu Kapitel 2
1. Deklarieren Sie eine Variable Provision als eine siebenstellige Zahl
mit 2 Nachkommastellen.
2. Deklarieren Sie eine Variable my_aname, welche die Spalte dname
der Tabelle dept des Benutzers SCOTT aufnehmen kann.
3. Welche zwei Vorteile bietet eine Variablendeklaration als %TYPE?
4. Welche Variablenbezeichner sind falsch?
var_a
_var_a
1_test
p_insert
insert
var-a
"UPdate"
number;
number;
varchar2(10);
varchar2(10);
varchar2(10);
number(9,-2);
varchar2(10);
5. Geben Sie jeweils den Wert der Variablen i (Lieblingsvariable vieler
Programmierer) an den Stellen 1,2,3 an.
DECLARE
i NUMBER;
j NUMBER;
BEGIN
i := 10;
BEGIN
j := 7;
Å 1
END;
DECLARE
i NUMBER :=17;
BEGIN
i := i + 2; Å 2
END;
i:= i + j;
Å 3
END;
/
1.2.066 / 4053
UEB-3
Übungen
UEB
6. Welche Blöcke lassen sich kompilieren/ausführen?
a)
declare
gebtag date;
GebTag date;
begin
null;
end;
/
b)
declare
gebtag date:=sysdate;
GebTag date:=sysdate+1;
begin
null;
end;
/
c) .
declare
gebtag date:=sysdate;
GebTag date:=sysdate+1;
begin
gebtag:=gebtag+1;
end;
/
1.2.066 / 4053
UEB-4
Übungen
UEB
7. Finden Sie den/die Fehler:
declare
p_var
p_var_2
p_var_3
sysdate
nvarchar2(20);
varchar2(20);
varchar
date;
begin
p_var := 10000;
p_var_2 := 'Das ist ein Test';
dbms_output.put_line(p_var_2);
p_var_2 := p_var_2 ||' vom '|| sysdate;
dbms_output.put_line(p_var_2);
end;
/
1.2.066 / 4053
UEB-5
Übungen
1.3
UEB
Aufgaben zu Kapitel 3
1. Schreiben Sie einen PL/SQL Block, der aus der Datenbank den Namen, das Gehalt und den Arbeitsort vom Mitarbeiter mit der Nummer
7839 auf den Bildschirm ausgibt.
2. Schreiben Sie einen Block, der drei Mitarbeiter in die EMP-Tabelle
einträgt.
Die Mitarbeiter sollen in der Abteilung arbeiten, die sich in New York
befindet. Kann man nach Beendigung des Blocks noch einen Rollback durchführen?
3. Schreiben Sie einen Block, der das Gehalt aller Mitarbeiter in der Abteilung 20 erhöht, lesen Sie die Anzahl der geänderten Datensätze in
eine Variable ein und lassen Sie sich diese am Bildschirm mit einer
geeigneten Meldung ausgeben.
4. Welche der folgenden Skripte sind funktionstüchtig. Testen Sie es
aus!
BEGIN
BEGIN
GOTO test
END;
<<test>>
NULL;
END;
/
BEGIN
GOTO test;
BEGIN
<<test>>
NULL;
END;
END;
/
1.2.066 / 4053
UEB-6
Übungen
UEB
BEGIN
BEGIN
GOTO test;
END;
BEGIN
<<test>>
NULL;
END;
END;.
/
1.2.066 / 4053
UEB-7
Übungen
1.4
UEB
Aufgaben zu Kapitel 4
1. Welche Anweisungen nach den IF-Abfragen werden im folgenden
Programm abgearbeitet?
a :=
b :=
c :=
IF b
1;
NULL;
NULL;
IN (NULL,10,20) THEN
....
/* Anweisung A */
END IF;
IF a IN (NULL,1,2) THEN
....
/* Anweisung B */
END IF;
IF a NOT IN (NULL,10,20) THEN
....
/* Anweisung C */
END IF;
2. Schreiben Sie einen anonymen Block, der einen Mitarbeiter auf sein
Gehalt prüft.
Wenn sein Gehalt <1500 ist, soll die Ausgabe "Kleines Gehalt", zwischen 1500 und 3500 die Ausgabe" Mittleres Gehalt" und bei mehr
als 3500 die Ausgabe "Großverdiener" erscheinen.
Der Mitarbeiter wird über seine Mitarbeiternummer dem Programm
als Textersetzungsvariable übergeben (Zur Erinnerung: Dies geschieht durch Verwendung eines “&“ vor dem Variablennamen, z.B.:
empno = &nummer;)
3. Schreiben Sie eine Schleife, die alle ganzzahligen, geraden Werte
von -10 bis 10 ausgibt. Lösen Sie diese Aufgabe mit allen drei
Schleifen.
4. Schreiben Sie eine Schleife, die beginnend bei der kleinsten Mitarbeiternummer der Tabelle emp die Mitarbeiternummern hochzählt
und das zugehörige Gehalt jeweils um 10 % erhöht. Sobald 7 Datensätze geändert wurden, soll abgebrochen werden.
1.2.066 / 4053
UEB-8
Übungen
1.5
UEB
Aufgaben zu Kapitel 5
1. Wie ist es möglich, einer Variablen den Datentyp einer ganzen Zeile
einer Datenbanktabelle zuzuweisen? Geben Sie ein Beispiel!
2. Lesen Sie eine Zeile aus der EMP-Tabelle in einen Record ein. Können auch mehrere Zeilen in einem Record gespeichert werden? Geben Sie nun zwei beliebige Spaltenwerte aus dem Record aus.
3. Weisen Sie der Spalte deptno in der Record-Variablen aus der vorhergehenden Aufgabe den Wert 20 zu.
4. Erstellen Sie zunächst eine Tabelle ‘hilf‘ mit dem Befehl
CREATE TABLE hilf AS SELECT * FROM emp;
Hängen Sie nun eine Spalte an mit dem Befehl
ALTER TABLE hilf ADD (nr number);
Nun tragen Sie in die neue Spalte eine fortlaufende (!) Nummer ein.
Schreiben Sie nun anhand dieser Tabelle einen PL/SQL-Block, in
dem die Namen und Gehälter aller Mitarbeiter in eine PL/SQLTabelle eingelesen werden.
1.2.066 / 4053
UEB-9
Übungen
1.6
UEB
Aufgaben zu Kapitel 6
1. Erstellen Sie einen Cursor, der jeden Mitarbeiter der emp-Tabelle mit
Namen, Gehalt und Abteilungsnummer ausgibt.
2. Erstellen Sie nun ein Programm, das alle vorhandenen Abteilungen
aus der dept-Tabelle ausliest und Mitarbeiter aus diesen Abteilungen mit Namen, Abteilungsnummer und Abteilungsort ausgibt. Es
sollen keine Joins oder Subqueries benutzt werden.
3. Verwenden Sie innerhalb eines PL/SQL-Blockes einen Cursor, um
die 5 bestbezahlten Mitarbeiter in die erst anzulegende Tabelle erg
einzufügen. Die Tabelle erg hat folgenden Aufbau:
Name
Type
------------------ -----------GEHALT
NUMBER(7,2)
MITARBEITERNR
NUMBER(4)
MITARBEITERNAME
CHAR(12)
Außerdem sollen die Namen dieser Mitarbeiter in der Tabelle emp mit
einem * markiert werden.
(Anmerkung: Entfernen Sie bitte hinterher den Stern wieder mit ROLLBACK)
1.2.066 / 4053
UEB-10
Übungen
1.7
UEB
Aufgaben zu Kapitel 7
1. Schreiben Sie einen Anonymen Block, der einen Gehaltswert eines
Mitarbeiters aus der emp-Tabelle ausliest. Die Mitarbeiternummer soll
dabei als Austauschvariable übergeben werden. Wenn die Nummer
nicht existiert, soll eine entsprechende Fehlermeldung ausgegeben
werden.
2. Schreiben Sie einen Anonymen Block, der Werte für empno, deptno, ename und sal in die Tabelle emp einfügt, die über Austauschvariablen eingelesen werden. Ist das angegebene Gehalt > 5000, so
soll die Anweisung automatisch zurückgerollt und eine entsprechende Fehlermeldung ausgegeben werden.
3. Ergänzen Sie Aufgabe 2 dahingehend, dass im Fehlerbehandlungsteil auch auf die Eingabe einer unzulässigen deptno reagiert wird.
Hinweis: Ermitteln Sie die passende Fehlernummer, indem Sie versuchen, einen
Eintrag mit einer falschen deptno zu machen.
1.2.066 / 4053
UEB-11
Übungen
1.8
UEB
Aufgaben zu Kapitel 8
1. Lösen Sie die Aufgabe 3 (inclusive 2) aus dem vorhergehenden Kapitel nun mit Hilfe einer Stored Procedure, an die die entsprechenden
Werte als Parameter übergeben werden.
2. Erzeugen Sie in der Datenbank folgende Tabellen:
Tabelle Verkauf:
Spalte
Type
------------------ -------NACHRICHT
CHAR(45)
DATUM
DATE
Tabelle Inventar:
Spalte
------------------PROD_NR
PRODUKT
MENGE
Type
-------NUMBER(5)
CHAR(15)
NUMBER(5)
Die Tabelle Verkauf ist leer, in die Tabelle Inventar fügen Sie
folgende Daten ein:
PROD_NR
---------------1234
8159
2741
PRODUKT
MENGE
----------------------- ----------Rollschuh
3
Fußball
4
Barbie Puppe
2
Schreiben Sie eine Prozedur, die den Verkauf eines Rollschuhs in beiden Tabellen protokolliert. Bei Aufruf des Programms soll die Menge an
Rollschuhen in der Tabelle Inventar um 1 vermindert werden, solange noch mindestens 1 Rollschuhpaar vorhanden ist. Der Verkauf eines
Rollschuhpaares soll in der Tabelle Verkauf registriert werden. Falls
keine Rollschuhe mehr vorhanden sind, soll dies ebenfalls in der Tabelle Verkauf vermerkt werden.
1.2.066 / 4053
UEB-12
Übungen
UEB
3. Erstellen Sie eine PL/SQL-Prozedur, die das Einkommen (Gehalt +
Provision) aller Mitarbeiter der Abteilung 30 ermittelt. Des Weiteren
soll ermittelt werden, wie viele Mitarbeiter mehr als 2000,- Euro verdienen, und bei wie vielen Mitarbeitern die Provision höher als das
Gehalt ist.
4. Schreiben Sie eine Prozedur, die das Gehalt aller Mitarbeiter um x%
erhöht, wenn der Mitarbeiter weniger verdient, als der Durchschnitt in
seiner Abteilung. x wird als Eingabeparameter übergeben.
5. Ihr Chef gibt Ihnen die Aufgabe, einige Informationen aus der Mitarbeitertabelle im Intranet darzustellen.
Im ersten Schritt sollen jedoch die Ausgaben zu Testzwecken im
SQL*Plus-Fenster erfolgen.
Schreiben Sie dafür eine Prozedur mit einem Cursor, der nach Eingabe einer gewünschten Abteilung alle Mitarbeiter (empno, ename,
deptno) der jeweiligen Abteilung ausgibt.
1.2.066 / 4053
UEB-13
Übungen
1.9
UEB
Aufgaben zu Kapitel 9
1. Schreiben Sie eine Funktion, die aus einem eingegebenen String
alle
Ä durch Ae bzw. ä durch ae,
Ö durch Oe bzw. ö durch oe,
Ü durch Ue bzw. ü durch ue
ersetzt.
2. Schreiben Sie eine Funktion, die das Gesamtgehalt (sal und comm)
eines Mitarbeiters zurückgibt, wenn er in der Abteilung Nr. 10 arbeitet, ansonsten soll -1 zurückgegeben werden.
3. Schreiben Sie sich eine eigene String-Manipulations-Funktion. Diese
Funktion soll vom Anfang oder vom Ende eines Strings eine bestimmte Anzahl von Zeichen wegschneiden
Die Funktion hat drei Parameter (String, Anzahl der Zeichen, Vom
Anfang an (1), oder vom Ende (-1))
Ein Aufruf von
SELECT my_cutter('Das ist Text x',1,-1) FROM dual;
soll folgende Ausgabe haben:
--------------------Das ist Text
1.2.066 / 4053
UEB-14
Übungen
1.10
UEB
Aufgabe zu Kapitel 10
Schreiben Sie ein Package emp_pack, das zwei Prozeduren und eine
Funktion enthält:
– Prozedur 1 fügt eine Zeile in die Tabelle emp ein (Spalten empno,
ename, sal, hiredate und deptno); die Werte müssen als Parameter übergeben werden.
– Prozedur 2 löscht einen Datensatz aus Tabelle emp; die empno wird
als Parameter übergeben
– Die Funktion überprüft eine Abteilungsnummer auf ihre Gültigkeit; sie
wird von Prozedur 1 aufgerufen und soll nicht öffentlich sein. Falls
die deptno ungültig ist, soll stattdessen NULL eingetragen werden.
Testen Sie nun das Package.
1.2.066 / 4053
UEB-15
Übungen
1.11
UEB
Aufgaben zu Kapitel 11
1. Definieren Sie einen Trigger auf die Tabelle emp, der keine Gehälter
unter 1000 bzw. über 6000 zulässt.
2. Definieren Sie einen Trigger, der allen Benutzern den Zugriff auf die
Tabelle dept (DELETE, INSERT, UPDATE) nur zwischen 08:00 und
17:00 Uhr erlaubt.
3. Erstellen Sie einen Trigger, der in der Tabelle Protokoll folgende
Einträge bei einem Zugriff eines Benutzers auf die Mitarbeitertabelle
protokolliert: Benutzernamen, verwendetes Statement und Datum.
Die Tabelle Protokoll ist in folgender Form zu erzeugen:
Benutzer VARCHAR2(30),
Statement VARCHAR2(10),
Datum DATE;
4. Legen Sie sich eine Kopie der Tabelle emp und dept an mit:
create table emp_copy as select * from emp;
create table dept_copy as select * from dept;
Schreiben Sie einen Trigger, der bei Update auf die Abteilungsnummer in der dept_copy-Tabelle die Mitarbeiter in der emp_copyTabelle auf den gleichen neuen Wert setzt.
Fügen Sie nun eine Spalte ysal zur Tabelle emp_copy hinzu. Wenn
ein Mitarbeiter einen Eintrag in die sal-Spalte bekommt, soll das
Jahresgehalt entsprechend mitgepflegt werden.
1.2.066 / 4053
UEB-16
Übungen
UEB
5. Erledigen Sie folgende Aufgaben:
a) Schreiben Sie einen Trigger der alle veränderten Zeilen der Tabelle mitdokumentiert.
So sollen INSERT, UPDATE und DELETE in einer separaten Tabelle aufgezeichnet werden (emp_hist)
emp_hist soll über die gleichen Spalten verfügen wie die Tabelle
emp plus zusätzlich folgende Spalten:
change_date (Änderungsdatum)
change_nr (eindeutige Änderungsnummer aus Sequenz
generiert)
change_user (Benutzer der die Änderung durchgeführt hat)
change_op (Welche DML-Operation wurde durchgeführt:
INS, UPD, DEL)
Bei einem UPDATE/DELETE soll die gesamte alte Zeile aufgezeichnet werden:
Beispiel:
insert into emp (empno,ename,job,sal,comm,deptno)
values (9000,'HUBER','SALESMAN',3000,2000,20);
update emp set sal=4000 where empno=9000;
delete from emp where empno=9000;
In der Tabelle emp_hist sollen nun folgende Einträge stehen:
empno
ename
job
sal
comm
dept
no
9000
change_date
change_nr
<akt Datum>
2
SCOTT
INS
change_user
change_op
9000
HUBER
SALESMAN 3000
2000
20
<akt_datum>
3
SCOTT
UPD
9000
HUBER
SALESMAN 4000
2000
20
<akt_datum>
4
SCOTT
DEL
b) (*) Nun soll eine Routine geschrieben werden, die die DML- Operation auch nach einem COMMIT noch rückgängig machen soll.
Bei Eingabe der change_nr wird diese Operation zurückgenommen (INSERT wird zu DELETE, DELETE zu INSERT und der UPDATE hat wieder die alten Werte).
1.2.066 / 4053
UEB-17
Übungen
1.12
UEB
Aufgaben zu Kapitel 13
Übungen zu HTML-Prozeduren:
1. Erweitern Sie das Beispiel der Tabellendarstellung (siehe TheorieTeil) um folgende Punkte:
a) Es soll eine Auswahlliste aller verfügbaren Tabellen des Benutzers
(all_tables) in einer SELECT-Liste dargestellt werden
Tipp: Bei fehlerhaften HTML-Seiten sehen sich den Quell-Code
der Seite an (Iexplorer: rechte Maustaste, Quelltext anzeigen)
b) Nun soll der letzte benutzte Tabellenname als Default-Eintrag in
der Select Liste erscheinen.
Übungen zu DBMS_LOB
2. Erstellen Sie eine Prozedur, die das Alertfile aus der Datenbank ausliest. Der Name dieser Datei lautet orclALRT.log. Den Pfad erhalten Sie, wenn Sie in der View v$parameter den Wert (value) für
den Parameter background_dump_dest (Spalte: name) suchen.
Eine Auswertung des Pfades soll dynamisch erfolgen!
Informationen aus dieser Datei sollen in eine Tabelle geschrieben
werden:
CREATE TABLE msta_alertfile (line NUMBER, prio NUMBER, datum DATE, text VARCHAR2(4000));
Vergeben Sie an den Entwickler die notwendigen Rechte (Tabelle,
Directory)
GRANT CREATE ANY DIRECTORY TO user;
GRANT SELECT ANY TABLE TO user;
3. Übung zu UTL_FILE:
Scheiben Sie eine Prozedur, die die Emp-Tabelle in eine Datei sichert.
1.2.066 / 4053
UEB-18
Übungen
1.13
UEB
Zusatzaufgaben
1. Übung
Schreiben Sie einen anonymen Block, der einen Mitarbeiter auf sein
Gehalt prüft. Wenn sein Gehalt <1500 ist, soll die Ausgabe "Kleines
Gehalt", zwischen 1500 und 3500 die Ausgabe "Mittleres Gehalt" und
bei mehr als 3500 die Ausgabe "Großverdiener" erscheinen. Der Mitarbeiter wird über seine Mitarbeiternummer dem Programm als Textersetzungsvariable übergeben.
2. Übung
Berechnen Sie die Umstellung auf den Gregorianischen Kalender.
Tipp 1: Bei der Umstellung auf diesen Kalender wurden einige Tage
übersprungen und sind auch in der Datenbank nicht vorhanden.
Beispiel: Wenn der Kalender am 12.8. umgestellt wurde, wären der
13.8. und 14.8. nicht verfügbar.
Wird der Datenbank einer dieser beiden Tage übergeben, bekommen
Sie z.B. den 20.8. zurück.
Tipp 2: Die Umstellung erfolgte zwischen dem 2.01.1500 und dem
01.01.1600.
Tipp 3: Die Umstellung erfolgte zwischen dem 3ten und 28ten eines
Monats.
3. Übung
Schreiben Sie die drei Mitarbeiter mit dem Beruf Manager in eine vorher
anzulegende Tabelle (top_verdiener mit name und gehalt). Die
Werte der Spalten sollen zuerst in einem PL/SQL Array gespeichert
werden, bevor sie in die Tabelle geschrieben werden.
Anmerkung: Es gibt nur einen Manager je Abteilung (10,20,30).
1.2.066 / 4053
UEB-19
Übungen
UEB
4. Übung
Schreiben Sie einen anonymen Block, der alle Mitarbeiter mit Namen
und Gehalt in ein PL/SQL Array einliest und dann wieder ausgibt.
Tipp: Holen Sie die kleinste Mitarbeiternummer und dann immer die
nächsthöhere (vergebene) Nummer.
5. Übung
Verwenden Sie innerhalb eines PL/SQL-Blockes einen Cursor, um die 5
bestbezahltesten Mitarbeiter in die erst anzulegende Tabelle erg einzufügen. Die Tabelle erg hat folgenden Aufbau:
Name
Typ
---------------------- -----------GEHALT
NUMBER(7,2)
MITARBEITERNR
NUMBER(4)
MITARBEITERNAME
VARCHAR2(12)
Außerdem sollen die Namen dieser Mitarbeiter in der Tabelle emp mit
einem * markiert werden.
(Anmerkung: Entfernen Sie bitte hinterher den Stern wieder mit ROLLBACK)
1.2.066 / 4053
UEB-20
Übungen
UEB
6. Übung
Ihr DBA bittet Sie um Mithilfe. Er möchte alle Tabellen analysieren, die
bisher noch nicht analysiert worden sind.
Um die Belastung des Rechners in Grenzen zu halten, schlägt er folgendes Vorgehen vor: Es sollen nur Tabellen des Benutzers SCOTT
und SYSTEM analysiert werden.
Wenn die Tabelle mehr als 100 Blöcke besitzt, soll eine 5% Stichprobe
durchgeführt werden, zwischen 16 und 100 Blöcken sollen 20% der Tabelle analysiert werden, ansonsten die komplette Tabelle.
Sie können zum Testen noch ein paar Tabellen anlegen. z. B.:
CREATE TABLE small_emp AS
SELECT e.* FROM scott.emp e,scott.emp, scott.emp;
CREATE TABLE big_emp AS
SELECT e.*
scott.emp;
FROM
scott.emp
e,scott.emp,
scott.emp,
Tipp: Sie können z. B. die Tabellen dba_segments, dba_tables oder
dba_extents verwenden. Schauen Sie sich zuerst die Syntax des
ANALYZE TABLE und dbms_ddl Befehls an.
1.2.066 / 4053
UEB-21