e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第73回 「ビューを通してのデータ更新」

2013.10.07

こんにちは。インストラクターの蓑島です。

今回と次回でビューを通してデータを更新することをテーマに解説します。
ビューは本来、問い合わせのためのものですが、ビューを通して更新を行うことで、問い合わせの対象と、更新の対象をビューに統一できるので、アプリケーションの構築をより簡単にすることができます。
しかしビューを通してのデータの更新は常に可能というわけではありません。
データ更新が可能であるためには、条件があります。条件に反する場合はビューを通してのデータ更新は、原則、できません。 しかし、そういった場合でも、次回解説予定の「INSTEAD OF トリガー」を用いれば、ビューを通してのデータ更新は可能となります。
今回は、「INSTEAD OF トリガー」は触れずに、一般的にビューを通してのデータ更新についての原則を簡単に解説します。

最初に基本的なところから確認ですが、皆さんご存知のように、ビューはSELECT文を元に定義するデータベースオブジェクトですね。
いわばSELECT文の結果を表のように見せかけている仮想的な表です。
ビューに対して問い合わせ(SELECT)を行うと、内部的には表に対する問い合わせに変換されます。 ビューに対しての更新(DML文:INSERT,UPDATE, DELETE)も同様に表に対するDML文に内部的に変換されるのですが、しかし、複雑なSELECT文を元に定義されたビューの場合は、表に対するDML文に変換することができず、エラーとなります。

それでは、どのようなビューに対する、どのようなDML文がエラーとなるのかを以下にまとめます。
ざっくりと以下の3点のようになります。

1. 式を元にした列をもつビューでは、その列を更新できない
2. 外部キー制約による親表、子表を結合したビューで、親表側の列を更新することはできない
3. データを集約するようなSELECT文を元にしているビューではDMLは実行できない

ではそれぞれ以下に解説します。

-------------------------------------------------------------
1. 式を元にした列をもつビューでは、その列を更新できない
-------------------------------------------------------------
これは、ビューの列が元の表のデータをそのまま表示していない場合は、その列は更新できないというものです。

例えば、以下のようなビューがあります。

1
2
3
4
5
CREATE OR REPLACE VIEW V_TEST1
AS
SELECT EMPNO, ENAME, LOWER (ENAME) AS LOW_NAME FROM EMP;
 
ビューが作成されました。

実際に問い合わせます。

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT * FROM V_TEST1;
 
      EMPNO ENAME      LOW_NAME
---------- ---------- ----------
       7369 SMITH      smith
       7499 ALLEN      allen
       7521 WARD       ward
       7566 JONES      jones
       7654 MARTIN     martin
       7698 BLAKE      blake
       7782 CLARK      clark
       7788 SCOTT      scott
(以下略)

このビューの2列目は、EMP表のENAME列をそのまま加工せずに表示してますので、更新可能です。
実際に行ってみます。

1
2
3
UPDATE V_TEST1 SET ENAME = 'SMITH2' WHERE EMPNO = 7369;
 
1行が更新されました。

ご覧のようにビューでUPDATE文が実行できましたが、これにより実際にはEMP表のデータが更新されました。

1
2
3
4
5
SELECT ENAME FROM EMP WHERE EMPNO = 7369;
 
ENAME
----------
SMITH2

しかしこのビューの3列目(LOW_NAME列)は、EMP表のENAME列をそのまま表示しているわけではありません。
LOWER関数により、小文字に変換して表示しているわけです。
ビューに対するそのような列へのDMLはエラーとなります。

1
2
3
4
UPDATE V_TEST1 SET LOW_NAME = 'SMITH3' WHERE EMPNO = 7369;
 
行1でエラーが発生しました。:
ORA-01733: ここでは仮想列は使用できません。

このエラーは納得できるところと思います。

では次に2つめです。

-------------------------------------------------------------
2. 外部キー制約による親表、子表を結合したビューで、親表側の列を更新することはできない
-------------------------------------------------------------
親表、子表を結合したビューでは、親表の1行のデータが、ビューでは複数行に表示されます。そのような列に対してはDMLは実行できません。

例えば、EMP表(子)とDEPT表(親)を結合したビューを作ります。

1
2
3
4
5
6
7
CREATE OR REPLACE VIEW V_OYA_KO
AS
SELECT E.EMPNO, E.ENAME, D.DNAME
FROM  EMP E , DEPT D
WHERE E.DEPTNO = D.DEPTNO
 
ビューが作成されました。

問い合わせてみます。

1
2
3
4
5
6
7
8
SELECT * FROM V_OYA_KO;
 
      EMPNO ENAME      DNAME
---------- ---------- --------------
       7782 CLARK      ACCOUNTING
       7839 KING       ACCOUNTING
       7934 MILLER     ACCOUNTING
       7566 JONES      RESEARCH

このビューの1列目と2列目は、EMP表(子)の列ですから更新可能です。

1
2
3
4
5
UPDATE V_OYA_KO
SET  ENAME = 'king'
WHERE  EMPNO = 7839;
 
1行が更新されました。

しかし3列目はDEPT表(親)の列ですから更新できません。

1
2
3
4
5
6
7
UPDATE V_OYA_KO
SET  DNAME = UPPER (DNAME)
WHERE EMPNO = 7389
/
 
行2でエラーが発生しました。:
ORA-01779: キー保存されていない表にマップする列は変更できません

このようにエラーとなります。
3列目の部門名(DNAME列)のACCOUNTIGNという値は親表であるDEPT表ではただ1行にしか存在しないデータですが、子表と結合したビューでは複数行に表示されます。 そのような列はビューを通しては更新できないわけです。
このことも、直感的に納得できる部分と思います。ちなみに上のエラーメッセージで、「キー保存されていない表」とは、親表のことです。

では最後です。

-------------------------------------------------------------
3. データを集約するようなSELECT文を元にしているビューではDMLは実行できない
-------------------------------------------------------------
これは、表の複数行がビューの1行に集約されるようなビューでは、DMLはできないということです。

例えば、社員表に対して部門ごとの給与合計を表示する以下のようなビューを作ります

1
2
3
4
5
6
7
CREATE OR REPLACE VIEW V_GSAL
AS
SELECT DEPTNO, SUM (SAL) AS GSAL
FROM   EMP
GROUP  BY DEPTNO;
 
ビューが作成されました。

このビューを問い合わせます。

1
2
3
4
5
6
7
SELECT * FROM V_GSAL;
 
     DEPTNO       GSAL
---------- ----------
         30       9400
         20      10875
         10       9029

部門ごとの給与合計ですね。 ここで、DEPTNO=10の行のGSAL列を2倍に更新してみます。

1
2
3
4
5
6
UPDATE V_GSAL
SET GSAL = GSAL * 2
WHERE  DEPTNO = 10;
 
行1でエラーが発生しました。:
ORA-01732: このビューではデータ操作が無効です

このようにエラーとなります。
これはこのビューの1行がEMP表の複数行に対応するので、更新操作ができないわけですね。このことも感覚的に理解できると思います。

このように、ビューを通してデータを更新できないケースは、いずれも、直感的に理解できるとことであり、考え方としてとくに難しくはありません。
(参考 データディクショナリ USER_UPDATABLE_COLUMNSビューを参照すれば、ビューのどの列が更新可能か調べることができます)

上記の基本を踏まえていればビューを通してデータ更新も可能ですから、問い合わせの対象と更新の対象をビューに一元化することで、アプリケーションも構築しやすくなります。

しかし、普通なら更新できないようなビューであっても、更新が可能であれば、時として便利なことがあるのではないでしょうか?

例えば、上記の3.の例で、もしこのビューで、DEPTNO=10の行の給与合計列(GSAL列)を2倍に更新することにより、社員表の部門10の社員の給与がすべて2倍に更新されれば、つじつまが合うし、大変便利な場合があると思いませんか?

(例)

1
2
3
4
5
6
7
SELECT * FROM V_GSAL;
 
     DEPTNO       GSAL
---------- ----------
         30       9400
         20      10875
         10       9029  ←この行

上記のビューの行に対して、部門給与合計列(GSAL列)を2倍したとします。
UPDATE V_GSAL SET GSAL = GSAL * 2 WHERE DEPTNO = 10;

そうすると実際にはエラーですが、エラーにならずにEMP表の部門DEPTNO=10の社員の給与(SAL)列の値が一律2倍になる、というようなことができればつじつまもあうし、便利ですよね。

そこで、次回ご紹介する、「INSTEAD OFトリガー」を使えば、こんな「倍返し?」みたいな更新も可能になります。
これはビューに対するトリガーです。

それでは今回はここまでとします。次回は「INSTEAD OF トリガー」をご紹介します。ご期待ください。

先頭へ戻る