私はビューにINSTEAD OFトリガーを付ければ、INSERT/UPDATE/DELETE操作を(表に行うのと同様に)実行できるので、それで出来ると思っていたのですが、実際やってみたところ、うまく行きませんでした。役に立つ情報かもしれないので、うまく行かなかった方法とうまく行く方法を以下に説明します。
例として使う表とビュー
ID列とNAME列(名前)を持つ表TST_PERSONSと、同じくID列とDOMICILE列(本籍)を持つ、表TST_PERSON_DOMICILESがあるとします。表の定義は以下です。
CREATE TABLE "TST_PERSONS"
( "ID" NUMBER,
"NAME" VARCHAR2(80),
PRIMARY KEY ("ID")
USING INDEX ENABLE
)
/
CREATE TABLE "TST_PERSON_DOMICILES"
( "ID" NUMBER,
"DOMICILE" VARCHAR2(80),
PRIMARY KEY ("ID")
USING INDEX ENABLE
)
/
そして、この２つの表を結合してビューTST_PERSONS_Vとします。DOMICILEは必ずしもすべてのTST_PERSONS表の行に紐づく行があるとは限らないため、外部結合(left outer join)としています。
CREATE OR REPLACE FORCE VIEW "TST_PERSONS_V" ("ID", "NAME", "DOMICILE") AS
select p.id, p.name, d.domicile
from tst_persons p left outer join tst_person_domiciles d
on p.id = d.id
/
このビューを、対話グリッドによって表と同様に操作する、というのが課題です。
INSTEAD OFトリガー
まず最初にINSERT/UPDATE/DELTE操作を行うINSTEAD OFトリガーを書きました。以下のトリガーになります。IDの値が与えられていない場合、TST_PERSONS_SEQシーケンスから自動的に採番するようにしています。
create or replace trigger tst_persons_v_trig
instead of insert or update or delete on tst_persons_v
for each row
declare
l_id number;
begin
if deleting then
delete from tst_person_domiciles where id = :old.id;
delete from tst_persons where id = :old.id;
elsif inserting then
if :new.id is null then
l_id := tst_persons_seq.nextval;
else
l_id := :new.id;
end if;
insert into tst_persons(id, name) values(l_id, :new.name);
if :new.domicile is not null then
insert into tst_person_domiciles(id, domicile) values(l_id, :new.domicile);
end if;
elsif updating then
update tst_persons set name = :new.name where id = :old.id;
merge into tst_person_domiciles d
using (select :old.id as id, :new.domicile as domicile from dual) n
on (d.id = n.id)
when matched then
update set d.domicile = n.domicile
when not matched then
insert (id, domicile) values(n.id, n.domicile);
end if;
end;
対話グリッドを作成する - その１
作成したビューを指定して対話グリッドを作成しました。ROWIDはないので、主キー列としてIDを選択しています。
作成した対話グリッドを操作すると、更新(UPDATE)、削除(DELETE)は問題ありませんが、行の追加(INSERT)を行うとORA-22816: RETURNING句ではサポートされていない機能です。とエラーが通知されます。ビューを対象にしたSQLにRETURNING句を付けられないことが原因です。
このエラーを回避するため、対話グリッドのデータベース操作を行うプロセスの設定に含まれる挿入後に主キーを返すをOFFにします。
エラーは回避され、ビューに対する操作は出来るようになりましたが、行の追加を行った後、保存をクリックすると対話グリッド上から追加した行が消えてしまいます。画面をリロードすると表示されるため、データベースに対する操作は行われています。
この動作は外部結合を持つビューがソースになっていることが理由ではなく、挿入後に主キーを返すをOFFにした場合の既定の動作です。単一の表であっても、この指定をOFFにすると同じ動作になります。
動的アクションを追加する
毎回リロードするのを回避するため、動的アクションを追加することにしました。対話グリッドには色々なイベントが定義されていますが、その中の保存[対話グリッド]というイベントで、対話グリッドのリフレッシュを行うアクションを設定します。
主キーの値を指定する
ビューではROWIDが取れないため、ユニークな値であるIDをTST_PERSONS_Vビューの主キーとして設定していますが、主キーについて、トリガーによる自動採番やOracle 12cから使用できるIdentity Columnを使っていないケースはあります。むしろ、そちらの方が多いでしょう。作成しているトリガーのinsertingに記述したコードでは、外部からIDが与えられる場合は、そちらの値を使用するように記述してあります。ですので、作成した対話グリッドのIDを非表示からテキスト・フィールドに変更し、入力可能にしてみます。
行の追加をクリックすると空白行が挿入されますが、IDにはt1002が設定されています。これは対話グリッドが認識しているユニークなカラムに自動的に設定される値で、カラムのデフォルトや動的アクションを使用しても変更することはできません。入力されたt1002といった値を手作業で変更すれば、変更したデータが保存されますが、それは期待している動作ではありません。
対話グリッドに与えるユニークな列(ROWIDまたは主キー列)は、必ず非表示で、かつ、自動的に生成される値である必要があります。
ROWIDをビューに追加する
TST_PERSONS表には必ず行が存在するので、そのROWIDをビューから見えるように新たなビューTST_PERSONS_R_Vを以下のように定義します。
CREATE OR REPLACE FORCE VIEW "TST_PERSONS_R_V" ("RID", "ID", "NAME", "DOMICILE") AS
select p.rowid as rid, p.id, p.name, d.domicile
from tst_persons p left outer join tst_person_domiciles d
on p.id = d.id
/
ROWIDは予約語であるため、ビューからはROWIDの代わりにRIDとしてTST_PERSONS表のROWIDを参照できるようにしています。ID自体が主キーであることには変わりなく、またNOT NULLの列であるため、INSTEAD OFトリガーは対象とするビューを変えるだけで使用できます。ビューのトリガーはビュー定義が変わる（CREATE OR REPLACE VIEWが実行される)と無くなってしまうので、注意しましょう。せっかく記述したトリガー、それも結構な量のコードが無くなっていると気が遠くなります。
定義を変えて作成したビューTST_PERSONS_R_Vを操作する対話グリッドのページを作成します。主キー列としてRIDを指定します。
先ほどの対話グリッドと同様に、プロセスの挿入後に主キーを返すをOFFにして、エラーの発生を回避します。RIDを非表示のユニーク列として扱うことにより、ID列の入力が可能になりました。また列の値のデフォルトを設定すると、行の追加を行った時点で値が設定されます。
上記では、ID列のデフォルトとして、タイプをPL/SQLファンクション本体、その内容として
return tst_persons_seq.nextvalを設定しています。タイプとしてSQL問合わせを選んでselect tst_persons_seq.nextval from dualを設定しても同じデフォルト値が設定されます。なぜか、タイプを順序として、TST_PERSONS_SEQを指定してもデフォルトの値が設定されません。ワークアラウンドはありますので、特には困らないのですが、困らないから誰も気にしていないのかもしれません。
期待通りに動作する設定
期待通りに動作する対話グリッドの設定の手順を示します。まず、対話グリッドを作成する際のソースの定義です。ビューではなくSQLを入力しています。INSTEAD OFトリガーを作る必要が無くなるため、ビューの作成は必須ではありません。問い合わせ自体は、ビューを定義したSQLと同じです。
select p.rowid rid, p.id, p.name, d.domicile from tst_persons p left outer join tst_person_domiciles d on p.id = d.id
対話グリッドがリージョンとして配置されたページが作成されたら、左ペインでプロセス・ビューを表示させてプロセス - 対話グリッド・データの保存を選択します。プロパティ・エディタからプロセスの設定に含まれるターゲット・タイプをPL/SQL Codeに設定し、挿入/更新/削除するPL/SQLコードを記述します。
記述したコードは以下です。ほとんどINSTEAD OFトリガーと同じです。
begin
case :APEX$ROW_STATUS
when 'C' then
if :ID is null then
:ID := tst_persons_seq.nextval;
end if;
insert into tst_persons(id, name) values(:ID, :NAME)
returning rowid into :ROWID;
if :DOMICILE is not null then
insert into tst_person_domiciles(id, domicile) values(:ID, :DOMICILE);
end if;
when 'U' then
update tst_persons set name = :NAME where id = :ID;
merge into tst_person_domiciles d
using (select :ID as id, :DOMICILE as domicile from dual) n
on (d.id = n.id)
when matched then
update set d.domicile = n.domicile
when not matched then
insert (id, domicile) values(n.id, n.domicile);
when 'D' then
delete from tst_person_domiciles where id = :ID;
delete from tst_persons where id = :ID;
end case;
end;
行の追加を行ったときに、そのまま行が残るようにするための部分が以下になります。
insert into tst_persons(id, name) values(:ID, :NAME) returning rowid into :ROWID;
作成された対話グリッドにはROWIDの項目が追加されています。しかし、このビューにはROWIDはなく、RID列としてその値を参照できるようにしています。ですので、ROWIDをRIDに紐付けます。レンダリング・ビューから対話グリッドのリージョンを探し、その列に含まれるROWIDを選択します。そのソースのデータベース列として設定されているROWIDをRIDに変更して保存します。
ID列のデフォルトも設定しておきます。設定内容は説明済みですが、既存の値の重複コピーはOFFにします。主キーなので、値が一意である必要があるためです。
これで、単一の表を基に対話グリッドを作成した場合と同じ動作をするようにできました。今回はビューを対象にしていますが、色々な応用ができると思います。
完
