SELECT文およびDML文の内部取得の回避

B.6 SELECT文およびDML文の内部取得の回避

次の推奨事項を遵守することによって、SELECT文、SELECT INTO文およびDML文における参照の内部取得を防止できます。

文内の各表に対して一意の別名を指定します。
文で参照される項目を所有しているスキーマと同じ名前を表の別名に指定しないようにします。
文内の列の各参照を適切な表の別名で修飾します。

例B-7では、スキーマhrが表tab1およびtab2を所有しています。表tab1にはtab2という名前の列があり、その抽象データ型(ADT)は属性aを持っています。表tab2にはaという名前の列はありません。推奨事項に反して、この問合せでは、表tab1に別名hrを指定して表tab2を参照しています。そのため、この問合せ内の参照hr.tab2.aは、表tab1、列tab2、属性aに解決されます。その後、この例では、列aを表tab2に追加しています。今度は、問合せ内の参照hr.tab2.aは、スキーマhr、表tab2、列aに解決されます。表tab2の列aは、表tab1の列tab2の属性aへの参照を取得します。

ここでのトピック

例B-7 属性の参照の内部取得

CREATE OR REPLACE TYPE type1 AS OBJECT (a NUMBER);
/
DROP TABLE tab1;
CREATE TABLE tab1 (tab2 type1);
INSERT INTO tab1 (tab2) VALUES (type1(10));

DROP TABLE tab2;
CREATE TABLE tab2 (x NUMBER);
INSERT INTO tab2 (x) VALUES (10);

/* Alias tab1 with same name as schema name,
   a bad practice used here for illustration purpose.
   Note lack of alias in second SELECT statement. */

SELECT * FROM tab1 hr
WHERE EXISTS (SELECT * FROM hr.tab2 WHERE x = hr.tab2.a);

結果:

TAB2(A)
---------------
 
TYPE1(10)
 
1 row selected.

表tab2(スキーマhrに属する)への列aの追加:

ALTER TABLE tab2 ADD (a NUMBER);

ここでこの問合せを実行すると、hr.tab2.aは、スキーマhr、表tab2、列aに解決されます。この内部取得を回避するために、問合せに推奨事項を適用します。

SELECT * FROM hr.tab1 p1
WHERE EXISTS (SELECT * FROM hr.tab2 p2 WHERE p2.x = p1.tab2.a);

B.6.1 属性およびメソッドへの参照の修飾

表の要素の属性やメソッドを参照するには、表に別名を付け、その別名を使用して、属性やメソッドに対する参照を修飾します。

例B-8の表tbl1には、属性xを持つADTであるデータ型t1の列col1があります。この例は、tbl1.col1.xへの適切な参照と適切でない参照をいくつか示しています。

例B-8 ADT属性の参照の修飾

CREATE OR REPLACE TYPE t1 AS OBJECT (x NUMBER);
/
DROP TABLE tb1;
CREATE TABLE tb1 (col1 t1);

次のINSERT文の参照では、列リストがないため、別名は不要です。

BEGIN
  INSERT INTO tb1 VALUES ( t1(10) );
  INSERT INTO tb1 VALUES ( t1(20) );
  INSERT INTO tb1 VALUES ( t1(30) );
END;
/

属性xへの次の参照では、エラーORA-00904が発生します。

UPDATE tb1 SET col1.x = 10 WHERE col1.x = 20;

UPDATE tb1 SET tb1.col1.x = 10 WHERE tb1.col1.x = 20;

UPDATE hr.tb1 SET hr.tb1.col1.x = 10 WHERE hr.tb1.col1.x = 20;

DELETE FROM tb1 WHERE tb1.col1.x = 10;

属性xへの表の別名を使用した次の参照は、適切です。

UPDATE hr.tb1 t SET t.col1.x = 10 WHERE t.col1.x = 20;

DECLARE
  y NUMBER;
BEGIN
  SELECT t.col1.x INTO y FROM tb1 t WHERE t.col1.x = 30;
END;
/

DELETE FROM tb1 t WHERE t.col1.x = 10;

B.6.2 行の式への参照の修飾

行の式は、表の別名への参照として解決する必要があります。行の式は、UPDATE文のSET句で使用したり、SQLファンクションREFまたはVALUEのパラメータにすることができます。

例B-9の表ot1は、属性xを持つADTであるデータ型t1の要素のスタンドアロンのネストした表です。この例は、行の式への適切な参照と適切でない参照をいくつか示しています。

例B-9 行の式への参照の修飾

CREATE OR REPLACE TYPE t1 AS OBJECT (x number);
/
DROP TABLE ot1;
CREATE TABLE ot1 OF t1;

BEGIN
  INSERT INTO ot1 VALUES (t1(10));
  INSERT INTO ot1 VALUES (20);
  INSERT INTO ot1 VALUES (30);
END;
/

次の参照では、エラーORA-00904が発生します。

UPDATE ot1 SET VALUE(ot1.x) = t1(20) WHERE VALUE(ot1.x) = t1(10);

DELETE FROM ot1 WHERE VALUE(ot1) = (t1(10));

表の別名を使用した次の参照は、適切です。

UPDATE ot1 o SET o = (t1(20)) WHERE o.x = 10;

DECLARE
  n_ref  REF t1;
BEGIN
  SELECT REF(o) INTO n_ref FROM ot1 o WHERE VALUE(o) = t1(30);
END;
/

DECLARE
  n t1;
BEGIN
  SELECT VALUE(o) INTO n FROM ot1 o WHERE VALUE(o) = t1(30);
END;
/

DECLARE
  n NUMBER;
BEGIN
  SELECT o.x INTO n FROM ot1 o WHERE o.x = 30;
END;
/

DELETE FROM ot1 o WHERE VALUE(o) = (t1(20));