22. Juli 2009

Views und DML-Operationen (Updatable Views): Was geht - was geht nicht ...?

English title: Some notes on Oracle Views and DML operations

In diesem Blog-Posting möchte ich mich ein wenig über das Update-Verhalten von Views auslassen - hier bietet die Oracle-Datenbank einige Möglichkeiten an. Zunächst ist eine View in Oracle prizipiell updatefähig. Das kann man auch sehr einfach testen ...
In this blog posting I'd like to write a little bit about views and DML operations on them. The features the Oracle database offers, are not known widely. First of all: A view in the Oracle database is basically updatable. This can be easily tested ...
EMPNO ENAME      JOB         MGR HIREDATE   SAL  COMM DEPTNO
----- ---------- --------- ----- -------- ----- ----- ------
 4711 CZARSKI    CLERK         ?        ?    90     ?     20
Das INSERT-Kommando geht durch - das INSERT wird auf die Tabelle EMP übertragen. Wenn Ihr das verhindern möchtet, könnt Ihr dies mit der READ ONLY-Klausel erreichen ...
The SQL INSERT command is being executed successfully - the DML operation is passed to the underlying table EMP. If you don't like this you might add the READ ONLY clause ...
Eine DML-Operation auf einer solchen View führt dann zu einer Fehlermeldung.
Now you get an error message when you try to perform DML on this view.
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view
Die obige View selektiert alle Zeilen mit der DEPTNO = 20. Was passiert eigentlich, wenn man versucht, einen Eintrag mit der DEPTNO = 30 einzufügen ...?
The above view selects all rows having DEPTNO = 20. What about inserting a row with DEPTNO = 30 ...?
No rows selected.
Das DML ging ebenfalls durch - wobei das in diesem Falle ziemlicher Blödsinn ist, denn aus Sicht der Anwendung ist der INSERT im Nirwana verschwunden. Dies lässt sich mit der CHECK OPTION verhindern ...
This works - but it doesn't make much sense: The inserted row is not visible when selecting the view since it "violates" the WHERE clause. Such issues can be prevented with the CHECK OPTION clause.
Das führt wiederum zu einer Fehlermeldung ...
Now you -again- get an error message ...
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
So weit - so gut. Bis hierhin waren die Beispiele auch recht einfach. Doch wie sieht das aus, wenn die Views komplizierter werden ...?
So far - so good. Up to now the examples were quite simple. But what about some more complex views ...? Here's another example having also some aggregate functions.
Hier werdet Ihr eine Fehlermeldung bekommen - denn Oracle kann den INSERT in diese View nicht alleine ausführen. Man muss hier die Fälle der theoretisch updatefähigen und theoretisch nicht updatefähigen View unterscheiden. Das obige Beispiel ist auch theoretisch gar nicht updatefähig, da Aggregatsfunktionen verwendet werden. Wie soll die Datenbank auch die Gehaltssumme von 20000 auf mehrere Zeilen in der Tabelle EMP verteilen?
You'll get an error message when you try a DML operation on this view. To explain the behaviour it's useful to difference between theoretical updatable and theoretical not updatable views. This last example is not updatable at all - the database cannot know how to distribute the salary sum of (here: 20000) to individual employee rows.
Es gibt aber auch theoretisch updatefähige Views, auf die Oracle das DML dennoch nicht alleine ausführen kann. Das ist insbesondere der Fall, wenn komplexere Joins durchgeführt werden. Die Data Dictionary View USER|ALL|DBA_UPDATABLE_COLUMNS gibt euch Auskunft, ob Oracle DML-Anweisungen auf eine View verarbeiten kann ... zunächst prüfen wir dies für das Eingangsbeispiel ...
There are view which are theoretical updatable, but for which Oracle cannot handle DML operations. Typical cases are views with complex joins (more than one additional table). The data dictionary provides information about updatable columns in the USER|ALL|DBA_UPDATABLE_COLUMNS view. First we check this out for the very first example in this blog posting ...
.
select table_name, column_name, insertable, updatable, deletable 
from user_updatable_columns 
where table_name='EMP_RESEARCH';

TABLE_NAME                     COLUMN_NAME                    INS UPD DEL
------------------------------ ------------------------------ --- --- ---
EMP_RESEARCH                   EMPNO                          YES YES YES
EMP_RESEARCH                   ENAME                          YES YES YES
EMP_RESEARCH                   JOB                            YES YES YES
EMP_RESEARCH                   SAL                            YES YES YES
EMP_RESEARCH                   DEPTNO                         YES YES YES
Die gleiche Abfrage für das letzte Beispiel mit den Aggregatsfunktionen ...
The same query for the last view having the aggregate functions ...
TABLE_NAME                     COLUMN_NAME                    INS UPD DEL
------------------------------ ------------------------------ --- --- ---
EMP_AGG                        DEPTNO                         NO  NO  NO
EMP_AGG                        DNAME                          NO  NO  NO
EMP_AGG                        SUM_SAL                        NO  NO  NO
EMP_AGG                        AVG_SAL                        NO  NO  NO
EMP_AGG                        HEADCOUNT                      NO  NO  NO
Ein solches NO ist jedoch noch nicht das letzte Wort: Mit einem INSTEAD-OF Trigger könnt Ihr jede beliebige View updatefähig machen - im Trigger wird der Datenbank gesagt, was sie im Update-Fall zu tun hat. Ein solcher Trigger könnte so aussehen ...
Such a NO is indeed not the last word. Coding an INSTEAD-OF Trigger makes every view updatable: The trigger code tells the database what to do with a DML operation on the view. Such a trigger might look as follows ...
INSTEAD-OF Trigger können nur für Views erzeugt werden, werden aber wie ein gewöhnlicher Tabellen-Trigger geschrieben. Im Trigger-Body fängt man die DML-Operation quasi ab und implementiert das, was eigentlich getan werden soll. Schauen wir nochmal in die View USER_UPDATABLE_COLUMNS ...
INSTEAD-OF triggers cannot be created for tables - they're only allowed for views. The trigger "catches" the original DML operation and executes the code in its body instead. The dictionary reflects the existence of the INSTEAD-OF trigger as the following output indicates ...
TABLE_NAME                     COLUMN_NAME                    INS UPD DEL
------------------------------ ------------------------------ --- --- ---
EMP_AGG                        DEPTNO                         YES YES YES
EMP_AGG                        DNAME                          YES YES YES
EMP_AGG                        SUM_SAL                        NO  NO  NO
EMP_AGG                        AVG_SAL                        NO  NO  NO
EMP_AGG                        HEADCOUNT                      NO  NO  NO
Und probieren wir es aus ...
Now we can try it ...
insert into emp_agg values (50, 'MYDEPT', 20000, 200, 90)
/

select * from dept
/

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 MYDEPT
So kann man mit Views wirklich vollständige Schnittstellen für Tabellen schaffen. Eine bestimmte Datensicht kann per View bereitgestellt und DML-Anweisungen auf diese Sicht mit INSTEAD-OF Triggern entsprechend verarbeitet werden. Ein sehr schönes Anwendungsbeispiel ist der Oracle Workspace Manager, der jüngst in der APEX Community beschrieben wurde. Der Workspace Manager erlaubt das Implemetieren langlaufender Transaktionen und unterschiedlicher Versionen von Tabellendaten.
Putting it all together we can say that views are very powerful in the Oracle database. Using INSTEAD-OF triggers views can be used as specific interfaces for every kind of dataset. DML operations can be "catched" by INSTEAD-OF triggers. A nice application of this concept is Oracle Workspace manager which allows to implement long running transactions and different versions of table data.

Kommentare:

ruepprich hat gesagt…

Sehr interessant. Das werde ich mal ein bisschen ausprobieren.
Gruss
Christoph

Anonym hat gesagt…

Vielen Dank für diesen Beitrag. Er hat genau mein Anliegen beantwortet und im detail erklärt. Weiter so!

Thorsten Kettner hat gesagt…

Wow, sehr klar und gut erklärt. Der Artikel hat mir sehr geholfen. Vielen Dank.

Anonym hat gesagt…

Danke, gute Vorlage. Aber ich glaube statt

delete from dept where deptno = :new.deptno;

müsste es

delete from dept where deptno = :OLD.deptno;

heissen.

Grüße
Conni

Carsten Czarski hat gesagt…

Hallo Conni,

Das stimmt. Das kommt von "Copy & Paste" ....

Danke und beste Grüße

Carsten

Anonym hat gesagt…

Herzlichen Dank für den Beitrag. Hat mir heute den Tag gerettet.

Andreas

Beliebte Postings