Lösungen für das 3. DB

Lösungen für das 3. DB-Praktikum
Aufgabe A : PL/SQL BLOCK und Cursor-Programmierung
1.
Erstellen Sie ein PL/SQL Block, welcher die Anzahl von Studierenden anzeigt.
2.
Erstellen Sie ein PL/SQL Programm mit Hilfe eines Cursor-Konzepts, welches die Attribute
Matrikelnummer und Studiendauer von Studenten anzeigt.
create table studenten
(
Matrikelnummer varchar(10),
Studiendauer number(2),
primary key (Matrikelnummer)
);
commit;
insert into studenten values ('MD-891372', 5);
insert into studenten values ('HRO-912291', 3);
insert into studenten values ('KK-888888', 3);
commit;
----------------------------------------------------Lösung zu Aufgabe A.1
set serveroutput on;
DECLARE
anzahl number(9);
BEGIN
select count(*)into anzahl from studenten;
dbms_output.put_line('Anzahl von Studenten: ' || chr('9')|| anzahl);
--Bemerkung chr(9) ist Tabulator
END;
/
Lösung zu Aufgabe A.2 (Einfache Lösung)
DECLARE
CURSOR c_student IS
SELECT matrikelnummer, studiendauer FROM studenten;
S_MatrikelNr studenten.matrikelnummer%TYPE;
S_StudDauer studenten.studiendauer%TYPE;
BEGIN
OPEN c_student;
LOOP
FETCH c_student INTO S_MatrikelNr, S_StudDauer;
EXIT WHEN c_student%notfound;
dbms_output.PUT_LINE(S_MatrikelNr|| chr('9')||S_StudDauer);
END LOOP;
CLOSE c_student;
END;
1
=========================================================================
Aufgabe B: Trigger
1. Erstellen Sie einen Trigger, der beim Einfügen einer Datensatz in die Tabelle „STUDENT“
eine Meldung bringt, wenn der Studierende die Regelzeit (Studiendauer>8) überschreitet
und nur diese Studenten sollen in eine Tabelle „Regelzeit_Uberschritten“ eingefügt werden.
Nach Einfügen eines Datensatz in die Tabelle „STUDENT soll im Falle eines
Überschreitung die Meldung erscheinen: „Dieser Student hat die Regelzeit überschritten“).
2.
Erweitern Sie den Trigger so, das alle Fälle (insert und update) berücksichtigt
drop table studenten;
drop table Regelzeit_Tabelle;
CREATE TABLE studenten
(
Matrikelnummer varchar(10),
Studiendauer number(2),
primary key (Matrikelnummer)
);
CREATE TABLE Regelzeit_Tabelle
(
Matrikelnummer varchar(10),
Studiendauer number(2),
primary key (Matrikelnummer)
);
Lösung zu Aufgabe B.1
---------------------------------------CREATE OR REPLACE TRIGGER Stud_Regelzeit
AFTER INSERT OR UPDATE ON studenten
FOR EACH ROW
WHEN (NEW.Studiendauer > 8) --Bei Bedingungen WHEN- Anweisung anstatt IF in Triggern
BEGIN
INSERT INTO Regelzeit_Tabelle VALUES(:NEW.matrikelnummer,:NEW.studiendauer);
dbms_output.put_line('Der Student hat die Regelzeit überschritten');
END Stud_Regelzeit;
/
Testen:
------------------------------------------set serveroutput on;
insert into studenten values ('LL-5555555',7);
insert into studenten values ('LL-7777777',9);
(Beim 2. Insert wurde ein Datensatz in die Tabelle Regelzeit_Tabelle eingefügt,
weil die Studiendauer 8 semester überschritten hat.)
-----------------------------------------------Lösung zu Aufgabe B.2
2
drop trigger Stud_Regelzeit;
CREATE OR REPLACE
TRIGGER StudentenRegelzeit
AFTER INSERT OR UPDATE ON Studenten
FOR EACH ROW
WHEN (NEW.Studiendauer > 8)
BEGIN
IF INSERTING THEN
INSERT INTO Regelzeit_Tabelle VALUES(:NEW.Matrikelnummer,:NEW.Studiendauer);
dbms_output.put_line('Regelstudienzeit beim einfügen überschritten!');
ELSE IF UPDATING THEN
UPDATE Regelzeit_Tabelle SET Studiendauer=:new.Studiendauer WHERE
Matrikelnummer=:new.Matrikelnummer;
dbms_output.put_line('Regelstudienzeit beim ändern überschritten!');
END IF;
END IF;
END;
------------------------------------Testen
sql> set serveroutput on;
TEST 1:
insert into studenten values ('Müller',11);
-- nach insert sollte die Meldung "Der Student hat die Regelzeit überschrittenI_U' ersdcheinen;
TEST 2:
insert into studenten values ('99999',5);
update studenten
SET Studiendauer=9
WHERE matrikelnummer='99999';
=========================================================================
Aufgabe C: Stored Procedur (Siehe Anlage 3)
1. Erstellen Sie eine Prozedur, welche die Anzahl von Vorlesungen angibt.
2. Erstellen Sie eine Prozedur mit zwei Parametern (MATRIKELNUMMER UND
STUDIENDAUER), um Daten in die Tabelle Studenten einzufügen.
3. Gegeben ist die Tabelle F_PRODUKT mit Attributen (Siehe Anlage 2);Erstellen Sie eine
Stored Procedur, welche den Lager_Bestand (Produkt_am_Lager) reduziert, wenn ein
Produkt verkauf wurde.
Lösung zu Aufgabe C.1
create or replace PROCEDURE AnzVorlesung AS
BEGIN
DECLARE
anzahl number(9);
BEGIN
select count(*)into anzahl from Vorlesungen;
dbms_output.put_line('Anzahl von Vorlesungen: ' || anzahl);
END;
END AnzVorlesung;
3
/
Aufruf:
execute AnzVorlesung
----------------------------------------------Lösung zu Aufgabe C.2
CREATE OR REPLACE procedure New_Student (I_matrikelnr varchar2,I_sdauer number) is
BEGIN
IF I_sdauer < 8 THEN
insert into studenten(matrikelnummer,studiendauer)
values (I_matrikelnr,I_sdauer);
commit;
dbms_output.put_line('Ein neuer Student wurde eintragen:Proz_New Student');
ELSE
INSERT INTO Regelzeit_Tabelle (matrikelnummer,studiendauer) VALUES
(I_matrikelnr,I_sdauer);
dbms_output.put_line('Der Student hat die Regelzeit überschrittenProz_New Student');
END IF;
END;
Procedur-Aufruf
Execute New_Student ('12345', 7);
Procedur-Aufruf
Execute New_Student ('123456', 9);
-----------------------------------------------Lösung zu Aufgabe C.3
drop table F_Produkt;
create table F_Produkt
(Produkt_Nr number(5),
Produkt_Name varchar2(30),
Produkt_am_Lager number(3));
commit;
insert into F_Produkt (Produkt_Nr, Produkt_Name, Produkt_am_Lager) values (1,'NOTEBOOK',3);
insert into F_Produkt (Produkt_Nr, Produkt_Name, Produkt_am_Lager) values
(2,'DSL_MODEM',1);
insert into F_Produkt (Produkt_Nr, Produkt_Name, Produkt_am_Lager) values
(3,'ISDN_TELEFON',0);
commit;
/*
PROZEDUR ERSTELLEN
------------------------------------create or replace procedure produkt_verk (i_Produkt_Nr IN number, i_verkauft IN number) is
begin
declare
4
Lager_Stand F_Produkt.Produkt_am_Lager%type;
cursor c_produkt(i_produkt_nr IN F_Produkt.Produkt_Nr%type) is
select Produkt_am_Lager
from F_Produkt
where Produkt_Nr = i_produkt_nr;
begin
open c_produkt(i_Produkt_Nr);
fetch c_produkt into Lager_Stand;
if (i_produkt_nr is NOT NULL) and (Lager_Stand < i_verkauft) then
-- if L ager_Stand < i_verkauft then
dbms_output.put_line('Nicht genug am Lager');
else
update F_Produkt
set Produkt_am_Lager = Produkt_am_Lager - i_verkauft
where Produkt_Nr = i_Produkt_Nr and Produkt_am_Lager > i_verkauft ;
commit;
dbms_output.put_line('ProduktMenge geändert');
end if;
end;
end;
-- ENDE PROZDERUR ERSTELLEN
----------------
5