CREATE OR REPLACE FUNCTION fn_ln_LOCATION ( pk_LOCATION_ID NUMBER ) RETURN varchar2 IS BEGIN DECLARE dummy_pk NUMBER(3) ; /* Dummy fuer gefundene foreign keys. Typ wie pk */ linkedtable char(80) ; /* Name der Tabelle, aus der ein Eintrag 'LOCATION' referenziert */ /* Cursoren fuer jede Tabelle, die foreignkeys auf Table LOCATION hat*/ CURSOR c_DEPARTMENT_9 IS SELECT DEPARTMENT_ID FROM DEPARTMENT WHERE DEPARTMENT.LOCATION_ID = pk_LOCATION_ID ; BEGIN OPEN c_DEPARTMENT_9 ; FETCH c_DEPARTMENT_9 INTO dummy_pk ; IF c_DEPARTMENT_9%NOTFOUND THEN linkedtable := NULL ; ELSE linkedtable := 'DEPARTMENT:DEPARTMENT_ID='||dummy_pk ; END IF ; CLOSE c_DEPARTMENT_9 ; RETURN linkedtable ; END ; END ; / show errors ; CREATE OR REPLACE FUNCTION fn_ln_ADDRESS ( pk_ADDRESS_ID NUMBER ) RETURN varchar2 IS BEGIN DECLARE dummy_pk NUMBER(6) ; /* Dummy fuer gefundene foreign keys. Typ wie pk */ linkedtable char(80) ; /* Name der Tabelle, aus der ein Eintrag 'ADDRESS' referenziert */ /* Cursoren fuer jede Tabelle, die foreignkeys auf Table ADDRESS hat*/ CURSOR c_DEPARTMENT_10 IS SELECT DEPARTMENT_ID FROM DEPARTMENT WHERE DEPARTMENT.ADDRESS_ID = pk_ADDRESS_ID ; CURSOR c_CUSTOMER_36 IS SELECT CUSTOMER_ID FROM CUSTOMER WHERE CUSTOMER.ADDRESS_ID = pk_ADDRESS_ID ; BEGIN OPEN c_DEPARTMENT_10 ; FETCH c_DEPARTMENT_10 INTO dummy_pk ; IF c_DEPARTMENT_10%NOTFOUND THEN OPEN c_CUSTOMER_36 ; FETCH c_CUSTOMER_36 INTO dummy_pk ; IF c_CUSTOMER_36%NOTFOUND THEN linkedtable := NULL ; ELSE linkedtable := 'CUSTOMER:CUSTOMER_ID='||dummy_pk ; END IF ; CLOSE c_CUSTOMER_36 ; ELSE linkedtable := 'DEPARTMENT:DEPARTMENT_ID='||dummy_pk ; END IF ; CLOSE c_DEPARTMENT_10 ; RETURN linkedtable ; END ; END ; / show errors ; CREATE OR REPLACE FUNCTION fn_ln_DEPARTMENT ( pk_DEPARTMENT_ID NUMBER ) RETURN varchar2 IS BEGIN DECLARE dummy_pk NUMBER(2) ; /* Dummy fuer gefundene foreign keys. Typ wie pk */ linkedtable char(80) ; /* Name der Tabelle, aus der ein Eintrag 'DEPARTMENT' referenziert */ /* Cursoren fuer jede Tabelle, die foreignkeys auf Table DEPARTMENT hat*/ CURSOR c_EMPLOYEE_22 IS SELECT EMPLOYEE_ID FROM EMPLOYEE WHERE EMPLOYEE.DEPARTMENT_ID = pk_DEPARTMENT_ID ; BEGIN OPEN c_EMPLOYEE_22 ; FETCH c_EMPLOYEE_22 INTO dummy_pk ; IF c_EMPLOYEE_22%NOTFOUND THEN linkedtable := NULL ; ELSE linkedtable := 'EMPLOYEE:EMPLOYEE_ID='||dummy_pk ; END IF ; CLOSE c_EMPLOYEE_22 ; RETURN linkedtable ; END ; END ; / show errors ; CREATE OR REPLACE PROCEDURE pi_DEPTS ( p_REGIONAL_GROUP IN OUT VARCHAR2, p_DEPARTMENT_NAME IN OUT VARCHAR2, p_street IN OUT VARCHAR2, p_CITY IN OUT VARCHAR2, p_STATE IN OUT VARCHAR2, p_ZIP_CODE IN OUT VARCHAR2 ) AS BEGIN DECLARE /* Fuer jeden Inputparameter 'p_*' eine Variable 'prm_*' anlegen */ prm_REGIONAL_GROUP VARCHAR2(20) ; prm_DEPARTMENT_NAME VARCHAR2(14) ; prm_street VARCHAR2(40) ; prm_CITY VARCHAR2(30) ; prm_STATE VARCHAR2(2) ; prm_ZIP_CODE VARCHAR2(9) ; /* Variable zur Speicherung der Record-Primaerkeys */ pk_LOCATION NUMBER(3) ; pk_DEPARTMENT NUMBER(2) ; pk_ADDRESS NUMBER(6) ; /* Cursoren zur Suche aller Einzeldatensaetze*/ /* Suche nach aktuellem record im Tablealias 'LOCATION */ CURSOR c_LOCATION IS /*Fall 3*/ SELECT LOCATION_ID FROM LOCATION WHERE (REGIONAL_GROUP = prm_REGIONAL_GROUP) ; /* Suche nach aktuellem record im Tablealias 'DEPARTMENT */ CURSOR c_DEPARTMENT IS /*Fall 3*/ SELECT DEPARTMENT_ID FROM DEPARTMENT WHERE (LOCATION_ID = pk_LOCATION) AND (NAME = prm_DEPARTMENT_NAME) ; /* Suche nach aktuellem record im Tablealias 'ADDRESS */ CURSOR c_ADDRESS IS /*Fall 1*/ SELECT ADDRESS_ID FROM ADDRESS WHERE ( ADDRESS_ID = pk_ADDRESS ) /* PK localized by detail */ ; var_tmp_fk NUMBER(38) ; /* speichert einen temporaeren Foreignkey */ BEGIN /* Jeden Inputparameter 'p_*' in die Variable 'prm_*' kopieren */ prm_REGIONAL_GROUP := p_REGIONAL_GROUP ; prm_DEPARTMENT_NAME := p_DEPARTMENT_NAME ; prm_street := p_street ; prm_CITY := p_CITY ; prm_STATE := p_STATE ; prm_ZIP_CODE := p_ZIP_CODE ; /* Ggf. Defaults/Values fuer Inputparameter setzen */ IF prm_REGIONAL_GROUP IS NULL THEN raise_application_error(-20004, 'VIRTAB "DEPTS": Bedingung "REGIONAL_GROUP IS NOT NULL" nicht erfuellt.'); END IF ; IF prm_DEPARTMENT_NAME IS NULL THEN raise_application_error(-20004, 'VIRTAB "DEPTS": Bedingung "DEPARTMENT_NAME IS NOT NULL" nicht erfuellt.'); END IF ; OPEN c_LOCATION ; FETCH c_LOCATION INTO pk_LOCATION ; IF c_LOCATION%NOTFOUND THEN SELECT seq_LOCATION_ID.NEXTVAL INTO pk_LOCATION FROM DUAL ; INSERT INTO LOCATION (LOCATION_ID, REGIONAL_GROUP) VALUES (pk_LOCATION, prm_REGIONAL_GROUP) ; ELSE FETCH c_LOCATION INTO pk_LOCATION ; IF c_LOCATION%FOUND THEN raise_application_error(-20003, 'VIRTAB "DEPTS", Table "LOCATION": Auswahl ist mehrdeutig. (' || ' prm_REGIONAL_GROUP=' || prm_REGIONAL_GROUP || ')'); END IF ; END IF ; CLOSE c_LOCATION ; OPEN c_DEPARTMENT ; FETCH c_DEPARTMENT INTO pk_DEPARTMENT ; IF c_DEPARTMENT%NOTFOUND THEN /* Master 'ADDRESS' wird vor Detail 'DEPARTMENT' angelegt */ OPEN c_ADDRESS ; FETCH c_ADDRESS INTO pk_ADDRESS ; IF c_ADDRESS%NOTFOUND THEN SELECT seq_ADDRESS_ID.NEXTVAL INTO pk_ADDRESS FROM DUAL ; INSERT INTO ADDRESS (ADDRESS_ID, STREET, CITY, STATE, ZIP_CODE) VALUES (pk_ADDRESS, prm_street, prm_CITY, prm_STATE, prm_ZIP_CODE) ; ELSE FETCH c_ADDRESS INTO pk_ADDRESS ; IF c_ADDRESS%FOUND THEN raise_application_error(-20003, 'VIRTAB "DEPTS", Table "ADDRESS": Auswahl ist mehrdeutig. (' || ' pk_ADDRESS=' || pk_ADDRESS || ')'); END IF ; END IF ; CLOSE c_ADDRESS ; /* Master 'ADDRESS' wurde angelegt, weiter mit Detail 'DEPARTMENT' */ SELECT seq_DEPARTMENT_ID.NEXTVAL INTO pk_DEPARTMENT FROM DUAL ; INSERT INTO DEPARTMENT (DEPARTMENT_ID, LOCATION_ID, ADDRESS_ID, NAME) VALUES (pk_DEPARTMENT, pk_LOCATION, pk_ADDRESS, prm_DEPARTMENT_NAME) ; /* 'ADDRESS' wird nur ueber den FK 'DEPARTMENT.ADDRESS_ID' lokalisiert */ BEGIN SELECT ADDRESS_ID INTO pk_ADDRESS FROM (SELECT ADDRESS.ADDRESS_ID,DEPARTMENT.DEPARTMENT_ID FROM ADDRESS,DEPARTMENT WHERE ADDRESS.ADDRESS_ID=DEPARTMENT.ADDRESS_ID) WHERE DEPARTMENT_ID = pk_DEPARTMENT ; EXCEPTION WHEN NO_DATA_FOUND THEN pk_ADDRESS := NULL ; END; ELSE /* 'ADDRESS' wird nur ueber den FK 'DEPARTMENT.ADDRESS_ID' lokalisiert */ BEGIN SELECT ADDRESS_ID INTO pk_ADDRESS FROM (SELECT ADDRESS.ADDRESS_ID,DEPARTMENT.DEPARTMENT_ID FROM ADDRESS,DEPARTMENT WHERE ADDRESS.ADDRESS_ID=DEPARTMENT.ADDRESS_ID) WHERE DEPARTMENT_ID = pk_DEPARTMENT ; EXCEPTION WHEN NO_DATA_FOUND THEN pk_ADDRESS := NULL ; END; FETCH c_DEPARTMENT INTO pk_DEPARTMENT ; IF c_DEPARTMENT%FOUND THEN raise_application_error(-20003, 'VIRTAB "DEPTS", Table "DEPARTMENT": Auswahl ist mehrdeutig. (' || ' pk_LOCATION=' || pk_LOCATION || ' prm_DEPARTMENT_NAME=' || prm_DEPARTMENT_NAME || ')'); END IF ; /* Ueberpruefung/Update des vorhandenen Foreignkeys "DEPARTMENT.LOCATION_ID" */ SELECT LOCATION_ID INTO var_tmp_fk FROM DEPARTMENT WHERE DEPARTMENT_ID = pk_DEPARTMENT ; IF var_tmp_fk IS NULL THEN UPDATE DEPARTMENT SET LOCATION_ID = (SELECT LOCATION_ID FROM LOCATION WHERE LOCATION_ID = pk_LOCATION) WHERE DEPARTMENT_ID = pk_DEPARTMENT; END IF ; IF (var_tmp_fk IS NULL) OR (var_tmp_fk = pk_LOCATION) THEN NULL; ELSE raise_application_error(-20010, 'VIRTAB "DEPTS", Table "DEPARTMENT": Fremdschluessel "LOCATION_ID" (= ' || var_tmp_fk || ') von vorhandenem Record zeigt nicht auf Masterrecord in Table "LOCATION".'); END IF ; /* Ueberpruefung/Update des vorhandenen Foreignkeys "DEPARTMENT.ADDRESS_ID" */ SELECT ADDRESS_ID INTO var_tmp_fk FROM DEPARTMENT WHERE DEPARTMENT_ID = pk_DEPARTMENT ; IF var_tmp_fk IS NULL THEN UPDATE DEPARTMENT SET ADDRESS_ID = (SELECT ADDRESS_ID FROM ADDRESS WHERE ADDRESS_ID = pk_ADDRESS) WHERE DEPARTMENT_ID = pk_DEPARTMENT; END IF ; IF (var_tmp_fk IS NULL) OR (var_tmp_fk = pk_ADDRESS) THEN NULL; ELSE raise_application_error(-20010, 'VIRTAB "DEPTS", Table "DEPARTMENT": Fremdschluessel "ADDRESS_ID" (= ' || var_tmp_fk || ') von vorhandenem Record zeigt nicht auf Masterrecord in Table "ADDRESS".'); END IF ; END IF ; CLOSE c_DEPARTMENT ; IF pk_ADDRESS IS NULL THEN /* Table immer noch nicht lokalisiert? */ OPEN c_ADDRESS ; FETCH c_ADDRESS INTO pk_ADDRESS ; IF c_ADDRESS%NOTFOUND THEN SELECT seq_ADDRESS_ID.NEXTVAL INTO pk_ADDRESS FROM DUAL ; INSERT INTO ADDRESS (ADDRESS_ID, STREET, CITY, STATE, ZIP_CODE) VALUES (pk_ADDRESS, prm_street, prm_CITY, prm_STATE, prm_ZIP_CODE) ; ELSE FETCH c_ADDRESS INTO pk_ADDRESS ; IF c_ADDRESS%FOUND THEN raise_application_error(-20003, 'VIRTAB "DEPTS", Table "ADDRESS": Auswahl ist mehrdeutig. (' || ' pk_ADDRESS=' || pk_ADDRESS || ')'); END IF ; END IF ; CLOSE c_ADDRESS ; END IF ; /* Test auf Table-Lokalisierung */ /* 'prm_*'-Variable in die Aufrufparameter 'p_*' zurueckkopieren */ p_REGIONAL_GROUP := prm_REGIONAL_GROUP ; p_DEPARTMENT_NAME := prm_DEPARTMENT_NAME ; p_street := prm_street ; p_CITY := prm_CITY ; p_STATE := prm_STATE ; p_ZIP_CODE := prm_ZIP_CODE ; END ; END ; / show errors ; CREATE OR REPLACE PROCEDURE pu_DEPTS ( p_REGIONAL_GROUP IN OUT VARCHAR2, p_DEPARTMENT_NAME IN OUT VARCHAR2, p_street IN OUT VARCHAR2, p_CITY IN OUT VARCHAR2, p_STATE IN OUT VARCHAR2, p_ZIP_CODE IN OUT VARCHAR2 ) AS BEGIN DECLARE /* Fuer jeden Inputparameter 'p_*' eine Variable 'prm_*' anlegen */ prm_REGIONAL_GROUP VARCHAR2(20) ; prm_DEPARTMENT_NAME VARCHAR2(14) ; prm_street VARCHAR2(40) ; prm_CITY VARCHAR2(30) ; prm_STATE VARCHAR2(2) ; prm_ZIP_CODE VARCHAR2(9) ; /* Variable zur Speicherung der Record-Primaerkeys */ pk_LOCATION NUMBER(3) ; pk_DEPARTMENT NUMBER(2) ; pk_ADDRESS NUMBER(6) ; /* Cursoren zur Suche aller Einzeldatensaetze*/ /* Suche nach aktuellem record im Tablealias 'LOCATION */ CURSOR c_LOCATION IS /*Fall 3*/ SELECT LOCATION_ID FROM LOCATION WHERE (REGIONAL_GROUP = prm_REGIONAL_GROUP) ; /* Suche nach aktuellem record im Tablealias 'DEPARTMENT */ CURSOR c_DEPARTMENT IS /*Fall 3*/ SELECT DEPARTMENT_ID FROM DEPARTMENT WHERE (LOCATION_ID = pk_LOCATION) AND (NAME = prm_DEPARTMENT_NAME) ; /* Suche nach aktuellem record im Tablealias 'ADDRESS */ CURSOR c_ADDRESS IS /*Fall 1*/ SELECT ADDRESS_ID FROM ADDRESS WHERE ( ADDRESS_ID = pk_ADDRESS ) /* PK localized by detail */ ; var_tmp_fk NUMBER(38) ; /* speichert einen temporaeren Foreignkey */ BEGIN /* Jeden Inputparameter 'p_*' in die Variable 'prm_*' kopieren */ prm_REGIONAL_GROUP := p_REGIONAL_GROUP ; prm_DEPARTMENT_NAME := p_DEPARTMENT_NAME ; prm_street := p_street ; prm_CITY := p_CITY ; prm_STATE := p_STATE ; prm_ZIP_CODE := p_ZIP_CODE ; /* Ggf. Defaults/Values fuer Inputparameter setzen */ IF prm_REGIONAL_GROUP IS NULL THEN raise_application_error(-20004, 'VIRTAB "DEPTS": Bedingung "REGIONAL_GROUP IS NOT NULL" nicht erfuellt.'); END IF ; IF prm_DEPARTMENT_NAME IS NULL THEN raise_application_error(-20004, 'VIRTAB "DEPTS": Bedingung "DEPARTMENT_NAME IS NOT NULL" nicht erfuellt.'); END IF ; OPEN c_LOCATION ; FETCH c_LOCATION INTO pk_LOCATION ; IF c_LOCATION%NOTFOUND THEN raise_application_error(-20002, 'VIRTAB "DEPTS", Table "LOCATION": Eintrag nicht vorhanden. (' || ' prm_REGIONAL_GROUP=' || prm_REGIONAL_GROUP || ')'); END IF ; FETCH c_LOCATION INTO pk_LOCATION ; IF c_LOCATION%FOUND THEN raise_application_error(-20003, 'VIRTAB "DEPTS", Table "LOCATION": Auswahl ist mehrdeutig. (' || ' prm_REGIONAL_GROUP=' || prm_REGIONAL_GROUP || ')'); END IF ; CLOSE c_LOCATION ; OPEN c_DEPARTMENT ; FETCH c_DEPARTMENT INTO pk_DEPARTMENT ; IF c_DEPARTMENT%NOTFOUND THEN raise_application_error(-20002, 'VIRTAB "DEPTS", Table "DEPARTMENT": Eintrag nicht vorhanden. (' || ' pk_LOCATION=' || pk_LOCATION || ' prm_DEPARTMENT_NAME=' || prm_DEPARTMENT_NAME || ')'); END IF ; FETCH c_DEPARTMENT INTO pk_DEPARTMENT ; IF c_DEPARTMENT%FOUND THEN raise_application_error(-20003, 'VIRTAB "DEPTS", Table "DEPARTMENT": Auswahl ist mehrdeutig. (' || ' pk_LOCATION=' || pk_LOCATION || ' prm_DEPARTMENT_NAME=' || prm_DEPARTMENT_NAME || ')'); END IF ; CLOSE c_DEPARTMENT ; /* 'ADDRESS' wird nur ueber den FK 'DEPARTMENT.ADDRESS_ID' lokalisiert */ BEGIN SELECT ADDRESS_ID INTO pk_ADDRESS FROM (SELECT ADDRESS.ADDRESS_ID,DEPARTMENT.DEPARTMENT_ID FROM ADDRESS,DEPARTMENT WHERE ADDRESS.ADDRESS_ID=DEPARTMENT.ADDRESS_ID) WHERE DEPARTMENT_ID = pk_DEPARTMENT ; EXCEPTION WHEN NO_DATA_FOUND THEN pk_ADDRESS := NULL ; END; OPEN c_ADDRESS ; FETCH c_ADDRESS INTO pk_ADDRESS ; IF c_ADDRESS%NOTFOUND THEN raise_application_error(-20002, 'VIRTAB "DEPTS", Table "ADDRESS": Eintrag nicht vorhanden. (' || ' pk_ADDRESS=' || pk_ADDRESS || ')'); END IF ; FETCH c_ADDRESS INTO pk_ADDRESS ; IF c_ADDRESS%FOUND THEN raise_application_error(-20003, 'VIRTAB "DEPTS", Table "ADDRESS": Auswahl ist mehrdeutig. (' || ' pk_ADDRESS=' || pk_ADDRESS || ')'); END IF ; CLOSE c_ADDRESS ; IF pk_LOCATION IS NOT NULL THEN /* OPTIONAL-Logik */ UPDATE LOCATION SET REGIONAL_GROUP = prm_REGIONAL_GROUP WHERE LOCATION_ID = pk_LOCATION ; END IF; IF pk_DEPARTMENT IS NOT NULL THEN /* OPTIONAL-Logik */ UPDATE DEPARTMENT SET LOCATION_ID = pk_LOCATION, ADDRESS_ID = pk_ADDRESS, NAME = prm_DEPARTMENT_NAME WHERE DEPARTMENT_ID = pk_DEPARTMENT ; END IF; IF pk_ADDRESS IS NOT NULL THEN /* OPTIONAL-Logik */ UPDATE ADDRESS SET STREET = prm_street, CITY = prm_CITY, STATE = prm_STATE, ZIP_CODE = prm_ZIP_CODE WHERE ADDRESS_ID = pk_ADDRESS ; END IF; /* 'prm_*'-Variable in die Aufrufparameter 'p_*' zurueckkopieren */ p_REGIONAL_GROUP := prm_REGIONAL_GROUP ; p_DEPARTMENT_NAME := prm_DEPARTMENT_NAME ; p_street := prm_street ; p_CITY := prm_CITY ; p_STATE := prm_STATE ; p_ZIP_CODE := prm_ZIP_CODE ; END ; END ; / show errors ; CREATE OR REPLACE PROCEDURE pd_DEPTS ( p_REGIONAL_GROUP IN OUT VARCHAR2, p_DEPARTMENT_NAME IN OUT VARCHAR2, p_street IN OUT VARCHAR2, p_CITY IN OUT VARCHAR2, p_STATE IN OUT VARCHAR2, p_ZIP_CODE IN OUT VARCHAR2 ) AS BEGIN DECLARE linkedtable CHAR(80) ; /* fuer Delete-Fehlermeldungen */ /* Fuer jeden Inputparameter 'p_*' eine Variable 'prm_*' anlegen */ prm_REGIONAL_GROUP VARCHAR2(20) ; prm_DEPARTMENT_NAME VARCHAR2(14) ; prm_street VARCHAR2(40) ; prm_CITY VARCHAR2(30) ; prm_STATE VARCHAR2(2) ; prm_ZIP_CODE VARCHAR2(9) ; /* Variable zur Speicherung der Record-Primaerkeys */ pk_LOCATION NUMBER(3) ; pk_DEPARTMENT NUMBER(2) ; pk_ADDRESS NUMBER(6) ; /* Cursoren zur Suche aller Einzeldatensaetze*/ /* Suche nach aktuellem record im Tablealias 'LOCATION */ CURSOR c_LOCATION IS /*Fall 3*/ SELECT LOCATION_ID FROM LOCATION WHERE (REGIONAL_GROUP = prm_REGIONAL_GROUP) ; /* Suche nach aktuellem record im Tablealias 'DEPARTMENT */ CURSOR c_DEPARTMENT IS /*Fall 3*/ SELECT DEPARTMENT_ID FROM DEPARTMENT WHERE (LOCATION_ID = pk_LOCATION) AND (NAME = prm_DEPARTMENT_NAME) ; /* Suche nach aktuellem record im Tablealias 'ADDRESS */ CURSOR c_ADDRESS IS /*Fall 1*/ SELECT ADDRESS_ID FROM ADDRESS WHERE ( ADDRESS_ID = pk_ADDRESS ) /* PK localized by detail */ ; /* Flags: konnte der Eintrag eines Tables geloescht werden? */ del_LOCATION NUMBER(1) ; del_DEPARTMENT NUMBER(1) ; del_ADDRESS NUMBER(1) ; BEGIN /* Jeden Inputparameter 'p_*' in die Variable 'prm_*' kopieren */ prm_REGIONAL_GROUP := p_REGIONAL_GROUP ; prm_DEPARTMENT_NAME := p_DEPARTMENT_NAME ; prm_street := p_street ; prm_CITY := p_CITY ; prm_STATE := p_STATE ; prm_ZIP_CODE := p_ZIP_CODE ; /* Ggf. Defaults/Values fuer Inputparameter setzen */ IF prm_REGIONAL_GROUP IS NULL THEN raise_application_error(-20004, 'VIRTAB "DEPTS": Bedingung "REGIONAL_GROUP IS NOT NULL" nicht erfuellt.'); END IF ; IF prm_DEPARTMENT_NAME IS NULL THEN raise_application_error(-20004, 'VIRTAB "DEPTS": Bedingung "DEPARTMENT_NAME IS NOT NULL" nicht erfuellt.'); END IF ; OPEN c_LOCATION ; FETCH c_LOCATION INTO pk_LOCATION ; IF c_LOCATION%NOTFOUND THEN raise_application_error(-20002, 'VIRTAB "DEPTS", Table "LOCATION": Eintrag nicht vorhanden. (' || ' prm_REGIONAL_GROUP=' || prm_REGIONAL_GROUP || ')'); END IF ; FETCH c_LOCATION INTO pk_LOCATION ; IF c_LOCATION%FOUND THEN raise_application_error(-20003, 'VIRTAB "DEPTS", Table "LOCATION": Auswahl ist mehrdeutig. (' || ' prm_REGIONAL_GROUP=' || prm_REGIONAL_GROUP || ')'); END IF ; CLOSE c_LOCATION ; OPEN c_DEPARTMENT ; FETCH c_DEPARTMENT INTO pk_DEPARTMENT ; IF c_DEPARTMENT%NOTFOUND THEN raise_application_error(-20002, 'VIRTAB "DEPTS", Table "DEPARTMENT": Eintrag nicht vorhanden. (' || ' pk_LOCATION=' || pk_LOCATION || ' prm_DEPARTMENT_NAME=' || prm_DEPARTMENT_NAME || ')'); END IF ; FETCH c_DEPARTMENT INTO pk_DEPARTMENT ; IF c_DEPARTMENT%FOUND THEN raise_application_error(-20003, 'VIRTAB "DEPTS", Table "DEPARTMENT": Auswahl ist mehrdeutig. (' || ' pk_LOCATION=' || pk_LOCATION || ' prm_DEPARTMENT_NAME=' || prm_DEPARTMENT_NAME || ')'); END IF ; CLOSE c_DEPARTMENT ; /* 'ADDRESS' wird nur ueber den FK 'DEPARTMENT.ADDRESS_ID' lokalisiert */ BEGIN SELECT ADDRESS_ID INTO pk_ADDRESS FROM (SELECT ADDRESS.ADDRESS_ID,DEPARTMENT.DEPARTMENT_ID FROM ADDRESS,DEPARTMENT WHERE ADDRESS.ADDRESS_ID=DEPARTMENT.ADDRESS_ID) WHERE DEPARTMENT_ID = pk_DEPARTMENT ; EXCEPTION WHEN NO_DATA_FOUND THEN pk_ADDRESS := NULL ; END; OPEN c_ADDRESS ; FETCH c_ADDRESS INTO pk_ADDRESS ; IF c_ADDRESS%NOTFOUND THEN raise_application_error(-20002, 'VIRTAB "DEPTS", Table "ADDRESS": Eintrag nicht vorhanden. (' || ' pk_ADDRESS=' || pk_ADDRESS || ')'); END IF ; FETCH c_ADDRESS INTO pk_ADDRESS ; IF c_ADDRESS%FOUND THEN raise_application_error(-20003, 'VIRTAB "DEPTS", Table "ADDRESS": Auswahl ist mehrdeutig. (' || ' pk_ADDRESS=' || pk_ADDRESS || ')'); END IF ; CLOSE c_ADDRESS ; /* Loeschen des Eintrags aus Table 'DEPARTMENT' */ IF pk_DEPARTMENT IS NOT NULL THEN /* OPTIONAL-Logik */ del_DEPARTMENT := 0 ; linkedtable := fn_ln_DEPARTMENT(pk_DEPARTMENT) ; if linkedtable IS NULL THEN /* Keine Referenz mehr: loeschen */ DELETE FROM DEPARTMENT WHERE DEPARTMENT_ID = pk_DEPARTMENT ; OPEN c_DEPARTMENT ; FETCH c_DEPARTMENT INTO pk_DEPARTMENT ; IF c_DEPARTMENT%FOUND THEN raise_application_error(-20008, 'VIRTAB "DEPTS", Table "DEPARTMENT": DB-Error: Eintrag nicht geloescht.'); ELSE del_DEPARTMENT := 1 ; END IF ; CLOSE c_DEPARTMENT ; END IF ; IF del_DEPARTMENT = 0 THEN raise_application_error(-20006, 'VIRTAB "DEPTS", Table "DEPARTMENT": Eintrag in Blattabelle nicht geloescht (Link auf Table ' || linkedtable || ').'); END IF ; END IF ; /* Loeschen des Eintrags aus Table 'ADDRESS' */ IF pk_ADDRESS IS NOT NULL THEN /* OPTIONAL-Logik */ del_ADDRESS := 0 ; linkedtable := fn_ln_ADDRESS(pk_ADDRESS) ; if linkedtable IS NULL THEN /* Keine Referenz mehr: loeschen */ DELETE FROM ADDRESS WHERE ADDRESS_ID = pk_ADDRESS ; OPEN c_ADDRESS ; FETCH c_ADDRESS INTO pk_ADDRESS ; IF c_ADDRESS%FOUND THEN raise_application_error(-20008, 'VIRTAB "DEPTS", Table "ADDRESS": DB-Error: Eintrag nicht geloescht.'); ELSE del_ADDRESS := 1 ; END IF ; CLOSE c_ADDRESS ; END IF ; END IF ; /* Loeschen des Eintrags aus Table 'LOCATION' */ IF pk_LOCATION IS NOT NULL THEN /* OPTIONAL-Logik */ del_LOCATION := 0 ; linkedtable := fn_ln_LOCATION(pk_LOCATION) ; if linkedtable IS NULL THEN /* Keine Referenz mehr: loeschen */ DELETE FROM LOCATION WHERE LOCATION_ID = pk_LOCATION ; OPEN c_LOCATION ; FETCH c_LOCATION INTO pk_LOCATION ; IF c_LOCATION%FOUND THEN raise_application_error(-20008, 'VIRTAB "DEPTS", Table "LOCATION": DB-Error: Eintrag nicht geloescht.'); ELSE del_LOCATION := 1 ; END IF ; CLOSE c_LOCATION ; END IF ; END IF ; END ; END ; / show errors ;