Als PDF Downloaden!

Tipps & Tricks: Februar 2002
Bereich:
SQL, PL/SQL
Erstellung:
02/2002 HA
Versionsinfo:
9.2.0.8, 10.2.0.4, 11.1.0.6
Letzte Überarbeitung:
07/2009 MM
Updateable Views
Sie möchten den direkten Zugriff auf Basistabellen unterbinden und stattdessen Ihren Mitarbeitern Views zur
Verfügung stellen, wissen aber nicht, wie Sie trotzdem DML-Befehle ermöglichen können?
Absolut problemlos geht das bei Views, die folgende Voraussetzungen erfüllen
nur eine Tabelle in der FROM-Klausel
keine Funktionen (egal, ob Single Row- oder Gruppenfunktion)
keine SET-Operatoren (UNION [ALL], MINUS, INTERSECT)
kein DISTINCT, ROWNUM
keine GROUP BY, START WITH oder CONNECT BY Klausel
keine Unterabfrage in der SELECT-Liste
keine WITH READ ONLY-Option
Solange die View auch alle Spalten der Basistabelle enthält, die als NOT NULL definiert wurden, kann sie exakt in
gleicher Weise behandelt werden wie eine Tabelle. Alle Änderungen werden an der zugrundeliegenden Tabelle
durchgeführt, nicht in der View enthaltene Spalten sind NULL. Wurde die CHECK-Option mit angegeben, so darf
der DML-Befehl dieser nicht widersprechen.
Die angegebenen Beispiele beziehen sich auf die Tabelle EMP des Demo-Users SCOTT.
Beispiel:
CREATE OR REPLACE VIEW emp_view AS
SELECT empno, ename, job, hiredate, sal, deptno
FROM emp WHERE deptno = 10;
INSERT INTO emp_view
VALUES (4711, 'Kilroy', 'CLERK', SYSDATE, 3000, 10);
UPDATE emp_view SET sal = 3300 WHERE empno = 4711;
DELETE FROM emp_view WHERE empno = 4711;
Sollten in der View Ausdrücke enthalten sein, so dürfen diese in der DML-Anweisung nicht referenziert werden.
Beispiel:
CREATE OR REPLACE VIEW emp_view AS
SELECT empno, ename, job, hiredate, sal, sal*12 j_gehalt, deptno
FROM emp WHERE deptno = 10;
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 1 von 3
INSERT INTO emp_view (empno, ename, job, hiredate, sal, deptno)
VALUES( 4711, 'Kilroy', 'CLERK', SYSDATE, 3000, 10);
Anmerkung:
Views, die in ihrer FROM-Klausel mehr als eine Tabelle referenzieren (sog. Join Views), sind prinzipiell
DML-fähig, allerdings nur unter sehr eingeschränkten Bedingungen, und immer nur auf eine der Basistabellen.
Näheres dazu finden Sie in der Oracle-Dokumentation.
Wenn Ihre View den Anforderungen nicht entspricht, können Sie sie trotzdem DML-fähig machen, indem Sie
entsprechende INSTEAD OF-Trigger implementieren, in denen Sie beispielsweise fehlende Werte vorgeben (z.B.
beim INSERT auf Views, die nicht alle NOT NULL-Spalten enthalten) oder Funktionen wieder auflösen.
Beispiel:
CREATE VIEW emp_view AS
SELECT ename, job, hiredate, deptno
DECODE(empno, 7839, NULL, sal) sal --Gehalt des Präsidenten ausblenden
FROM emp;
CREATE SEQUENCE emp_seq START WITH 7935;
CREATE OR REPLACE TRIGGER emp_view_ins
INSTEAD OF INSERT ON emp_view
FOR EACH ROW
BEGIN
INSERT INTO emp (empno, ename, job, hiredate, sal, deptno)
VALUES (emp_seq.NEXTVAL, :NEW.ename,
:NEW.job, :NEW.hiredate, :NEW.sal, :NEW.deptno);
END;
/
CREATE OR REPLACE TRIGGER emp_view_upd
INSTEAD OF UPDATE ON emp_view
FOR EACH ROW
BEGIN
IF :OLD.job = 'PRESIDENT' THEN
RAISE_APPLICATION_ERROR(-20111,'Datensatz darf nicht geändert werden');
ELSE
UPDATE emp SET ename =:NEW.ename,
job =:NEW.job ,
hiredate=:NEW.hiredate,
sal=:NEW.sal,
deptno=:NEW.deptno
WHERE ename = :old.ename;
END IF;
END;
/
UPDATE emp_view SET sal=1000
WHERE ename='SMITH';
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 2 von 3
Natürlich würde man in der Praxis eine passendere WHERE-Bedingung benötigen als im o.a. Beispieltrigger
EMP_VIEW_UPD. Die Sequenz startet um eins höher als die höchste EMPNO in EMP.
Analog müsste noch ein INSTEAD OF DELETE-Trigger implementiert werden.
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 3 von 3