-- re-create the needed tables to have a clean set of dataDROP TABLE personen_tw04n133;CREATE TABLE personen_tw04n133 AS SELECT * FROM personen;DROP TABLE personal_tw04n133;CREATE TABLE personal_tw04n133 AS SELECT * FROM personal;DROP TABLE studenten_tw04n133;CREATE TABLE studenten_tw04n133 AS SELECT * FROM studenten;DROP TABLE studiengangleiter_tw04n133;CREATE TABLE studiengangsleiter_tw04n133 AS SELECT * FROM studiengangsleiter;DROP TABLE unterricht_tw04n133;CREATE TABLE unterricht_tw04n133 AS SELECT * FROM unterricht;DROP TABLE fachbereich_tw04n133;CREATE TABLE fachbereich_tw04n133 AS SELECT fachbereich_id, personen_id, kurzbezeichnung FROM fachbereich;DROP TABLE noten_tw04n133;CREATE TABLE noten_tw04n133 AS SELECT * FROM noten;DROP TABLE noten_new_tw04n133;CREATE TABLE noten_new_tw04n133 AS SELECT * FROM noten WHERE noten_id IS NULL;-- creating package with all testfunctionsCREATE OR REPLACE PACKAGE test_tw04n133 AS PROCEDURE testSst_tw04n133 (oldID NUMBER, newID NUMBER); PROCEDURE testRemovePerson_tw04n133 (personenID NUMBER); PROCEDURE print_tw04n133 (personenID NUMBER); PROCEDURE testRemoveDouble_tw04n133 (unterrichtID NUMBER, fachID NUMBER, personenID NUMBER); PROCEDURE testAutoIncrement_tw04n133 (unterrichtID NUMBER, personenID NUMBER);END test_tw04n133;CREATE OR REPLACE PACKAGE BODY test_tw04n133 AS -- creating the a test procedure which is used for easy testing of the set teacher -- status trigger PROCEDURE testSst_tw04n133 (oldID NUMBER, newID NUMBER) IS currentStatus NUMBER; CURSOR getUnterrichtID IS SELECT u.unterricht_id FROM unterricht_tw04n133 u WHERE u.personen_id = oldID; result getUnterrichtID%ROWTYPE; BEGIN FOR result IN getUnterrichtID LOOP BEGIN SELECT p.status INTO currentStatus FROM personal_tw04n133 p WHERE p.personen_id = oldID; DBMS_OUTPUT.PUT_LINE('status of personen with personen_id = ' || oldID || ' is: ' || currentStatus); DBMS_OUTPUT.PUT_LINE('changing all entries with personen_id = ' || oldID || ' to personen_id = ' || newID); UPDATE unterricht_tw04n133 SET personen_id = newID WHERE unterricht_id = result.unterricht_id; SELECT p.status INTO currentStatus FROM personal_tw04n133 p WHERE p.personen_id = oldID; DBMS_OUTPUT.PUT_LINE('Status of person ' || oldID || ' changed to ' || currentStatus); END; END LOOP; END testSst_tw04n133; -- creating the a test procedure which is used for easy testing of the -- remove person trigger PROCEDURE testRemovePerson_tw04n133 (personenID NUMBER) IS BEGIN print_tw04n133 (personenID); DBMS_OUTPUT.PUT_LINE('deleting person with personen_id = ' || personenID || ' in table personen!'); print_tw04n133 (personenID); END testRemovePerson_tw04n133; -- print if a personen is in tables PROCEDURE print_tw04n133 (personenID NUMBER) IS isIn NUMBER; countEntries NUMBER; BEGIN countEntries := 0; DELETE FROM personen_tw04n133 WHERE personen_id = personenID; SELECT count(*) INTO isIn FROM personen_tw04n133 p WHERE p.personen_id = personenID; countEntries := countEntries + isIn; IF isIn > 0 THEN DBMS_OUTPUT.PUT_LINE('person with personen_id = ' || personenID || ' is in table personen!'); END IF; SELECT count(*) INTO isIn FROM personal_tw04n133 p WHERE p.personen_id = personenID; countEntries := countEntries + isIn; IF isIn > 0 THEN DBMS_OUTPUT.PUT_LINE('person with personen_id = ' || personenID || ' is in table personal!'); END IF; SELECT count(*) INTO isIn FROM studenten_tw04n133 s WHERE s.personen_id = personenID; countEntries := countEntries + isIn; IF isIn > 0 THEN DBMS_OUTPUT.PUT_LINE('person with personen_id = ' || personenID || ' is in table studenten!'); END IF; SELECT count(*) INTO isIn FROM noten_tw04n133 n WHERE n.personen_id = personenID; countEntries := countEntries + isIn; IF isIn > 0 THEN DBMS_OUTPUT.PUT_LINE('person with personen_id = ' || personenID || ' is in table noten!'); END IF; SELECT count(*) INTO isIn FROM studiengangsleiter_tw04n133 s WHERE s.personen_id = personenID; countEntries := countEntries + isIn; IF isIn > 0 THEN DBMS_OUTPUT.PUT_LINE('person with personen_id = ' || personenID || ' is in table studiengangsleiter!'); END IF; SELECT count(*) INTO isIn FROM unterricht_tw04n133 u WHERE u.personen_id = personenID; countEntries := countEntries + isIn; IF isIn > 0 THEN DBMS_OUTPUT.PUT_LINE('person with personen_id = ' || personenID || ' is in table unterricht!'); END IF; SELECT count(*) INTO isIn FROM fachbereich_tw04n133 f WHERE f.personen_id = personenID; countEntries := countEntries + isIn; IF isIn > 0 THEN DBMS_OUTPUT.PUT_LINE('person with personen_id = ' || personenID || ' is in table fachbereich!'); END IF; DBMS_OUTPUT.PUT_LINE('person with personen_id = ' || personenID || ' has ' || countEntries || ' entries!'); END print_tw04n133; -- creating the a test procedure which is used for easy testing the trigger for -- removing a doubled entry in the table unterricht PROCEDURE testRemoveDouble_tw04n133 (unterrichtID NUMBER, fachID NUMBER, personenID NUMBER) IS BEGIN -- assuming that the unterricht_id not exists and the fachID and personenID exists INSERT INTO unterricht_tw04n133 (unterricht_id, fach_id, personen_id) VALUES (unterrichtID, fachID, personenID); END testRemoveDouble_tw04n133; -- creating the a test procedure which is used for easy testing of the -- AutoIncrement on table noten PROCEDURE testAutoIncrement_tw04n133 (unterrichtID NUMBER, personenID NUMBER) IS BEGIN -- assuming that the id's used for the subject and person are valid INSERT INTO noten_tw04n133 (unterricht_id, personen_id) VALUES (unterrichtID, personenID); END testAutoIncrement_tw04n133;END test_tw04n133;BEGIN -- test beispiel01 test_tw04n133.testSst_tw04n133 (6,1); -- test beispiel02 testRemovePerson_tw04n133 (2); testRemovePerson_tw04n133 (100); -- test beispiel03 testRemoveDouble (100, 2, 4); testRemoveDouble (101, 3, 4); -- test beispiel04 testAutoIncrement_tw04n133(1, 1); testAutoIncrement_tw04n133(1, 2); testAutoIncrement_tw04n133(1, 3);END;