Als PDF Downloaden!

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;

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';

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.

Suche