アリエルはどちらかと言うとLAMP派が多いですが、最近はOracleマスターもいます。多様性は良いことです。 Oracleで感心することがふたつあったので、報告します。

ひとつめ。 少し単純化していますが、次のようなテーブルがありました。baseとderivedの列はどちらも同じ名前空間に属する値を参照しています。

SQL> SELECT * FROM xtable;
      BASE,   DERIVED
----------,----------
         1,         2
         3,         6
         4,         8
         2,         4
         4,         9

baseに変化があった場合、derivedにも影響が波及します。 上で例で言えば、1の指す対象(以下obj-1)に変化があれば、obj-2に変化が波及します。その変化はobj-4に波及し、更にobj-8とobj-9にも波及します。 SELECTを繰り返し呼ぶコードを書いた場合、SELECT derived FROM xtable WHERE base=1;の戻り値を使って、SELECT derived FROM xtable WHERE base IN (戻り値);を呼び、またその戻り値を使ってWHERE .. IN ..を呼ぶ再帰コードになります。

こういうテーブル設計が正しいか否かはともかく、ひとつのSQLで書けることをOracleマスターに教えてもらいました。

base=1から始めて波及の全てを知りたい場合、次のように書きます。

SQL> SELECT base,derived FROM xtable START WITH base=1 CONNECT BY PRIOR derived=base;
      BASE,   DERIVED
----------,----------
         1,         2
         2,         4
         4,         8
         4,         9

levelを表示すると、再帰コードと同じことが内部で起きていることが分かりやすくなります。

SQL> SELECT base,derived,level FROM xtable START WITH base=1 CONNECT BY PRIOR derived=base;
      BASE,   DERIVED,     LEVEL
----------,----------,----------
         1,         2,         1
         2,         4,         2
         4,         8,         3
         4,         9,         3

levelを使って、再帰を打ち切ることもできます。

SQL> SELECT base,derived,level FROM xtable WHERE level<=2 START WITH base=1 CONNECT BY PRIOR derived=base;
      BASE,   DERIVED,     LEVEL
----------,----------,----------
         1,         2,         1
         2,         4,         2

再帰が循環して終わらない場合、次のようなエラーになります。

SQL> INSERT INTO xtable values (4,1);
SQL> SELECT * FROM xtable;

      BASE,   DERIVED
----------,----------
         1,         2
         3,         6
         4,         8
         2,         4
         4,         9
         4,         1
  
SQL> SELECT base,derived,level FROM xtable START WITH base=1 CONNECT BY PRIOR derived=base;
ERROR:
ORA-01436: CONNECT BY loop in user data

次のようにnocycleをつけると、循環があっても終わります。

SQL> SELECT base,derived,level FROM xtable START WITH base=1 CONNECT BY NOCYCLE PRIOR derived=base;

      BASE,   DERIVED,     LEVEL
----------,----------,----------
         1,         2,         1
         2,         4,         2
         4,         8,         3
         4,         9,         3
         4,         1,         3

ふたつめ。 次のようなテーブルがありました。

SQL> SELECT * FROM ytable;
   ID,       VAL
-----,----------
    1,       100
    2,       200
    3,       300

ある値のidの行が存在すればその行を書き換え、存在しない場合は新しい行を挿入したくなりました。 こういうテーブル設計が正しいか否かはともかく、mergeでできます。

次のようにid=4,val=400でmergeすると、行が存在しないのでinsertが走って新しい行を挿入します。

SQL> MERGE INTO ytable USING dual ON (ytable.id=4) WHEN MATCHED THEN UPDATE SET val=400 WHEN NOT MATCHED THEN INSERT VALUES (4, 400);
SQL> SELECT * FROM ytable;
   ID,       VAL
-----,----------
    1,       100
    2,       200
    3,       300
    4,       400

この後、id=4,val=450でmergeすると行が存在するので、updateが走って既存行を変更します。

SQL> MERGE INTO ytable USING dual ON (ytable.id=4) WHEN MATCHED THEN UPDATE SET val=450 WHEN NOT MATCHED THEN INSERT VALUES (4, 450);
SQL> SELECT * FROM ytable;
   ID,       VAL
-----,----------
    1,       100
    2,       200
    3,       300
    4,       450

アリエルには、ボラクルとか言ってOracleをバカにする人が多いですが、マスターの言葉に耳を傾けることも必要なようです。

話は飛びますが、「標準技術は良いことだ」と言う人がいます。この言葉自体に異論はありませんが、だからと言って、「独自技術や独自拡張は悪い」と言うのは短絡的過ぎます。 更に、標準技術の対極としてMSを持ち出して、MSの独自技術はけしからん、と言う主張には同意できません。 良い技術もあれば悪い技術もあります。それだけです。第一、MSの独自拡張なんてかわいいもので、GNUの独自拡張の方がずっと過激だったりします。

標準技術に従うことは良いことですが、MSやGNUのような過激派もいないと技術が進歩しない側面があります。Oracleにもそういう側面がありそうです。

独自技術をなんでも受け入れる態度は、独自技術を全否定する態度と変わりません。技術の善し悪しを判断するのは個人の責任です。その結果、残る技術もあれば消える技術もあります。ただ、ぼくは絶対的な価値観の元、残る技術と消える技術が決まるとは思っていません。結果は微妙な歴史の帰結です。ある価値観で残る技術が決まるのはなく、残った技術で価値観が決まると思っています。