はじめに
Oracle 10gのSQLの新機能のmodel句について、入門事項からOracle Technology Network（US）でよく見かける問題の解法まで、SQLのイメージを交えて解説します。本稿ではHelloWorldなどのmodel句のサンプルと、集計行の追加を行う方法を扱います。
対象読者
- OracleのSQLで
model句を使いたい方 - OracleのSQLの理解を深めたい方
必要な環境
本稿で扱うSQLは、Oracle 11g（11.1.0.6.0）で動作確認しました。
1. model句とは
model句はOracle 10gのSQLの新機能で、select文で手続き型言語の配列のように、SQLの結果セットを扱うことができます。having句の次にmodel句が評価されますので、model句を含めたselect文の評価順序は、下記となります。
1. from句 2. where句 (結合条件) 3. start with句 4. connect by句 5. where句 (行のフィルタ条件) 6. group by句 7. having句 8. model句 9. select句 10. union、minus、intersectなどの集合演算 11. order by句
model句のメリット
Oracle 9iまでは、分析関数を何度も使ったり、union allを使ったり、親言語（C#やJavaなど）やPL/SQLで求めたりしていた値を、SQLで容易に求めることができるようになります。
model句の使用例で代表的なのは、SQLで下記のようなことを行うケースです。
- 集計行の追加
- 行列変換
- 前後の行の値を使った計算
RegExp_Replace関数の繰り返し実行
2. HelloWorldなどのサンプル
まずは、HelloWorldなどのサンプルを見てみましょう。
select ArrValue,soeji
from (select 'abcdefghijklmn' as ArrValue,
1 as soeji from dual)
model
dimension by(soeji)
measures(ArrValue)
rules(ArrValue[1] = 'Hello World');
| ArrValue | soeji |
| Hello World | 1 |
それぞれの文法について説明しましょう。
model
model句を使用する際のキーワードです。model句を使用するには必須です。
dimension by
dimensionは、次元という意味です。PL/SQLやC#、Java、Rubyなどにおける、配列の添字だと理解しておいて問題ないでしょう。Fortranではdimensionキーワードを使って配列を定義するようです。model句を使用するには必須です。
measures
配列が、どんな値の配列かを指定します。model句を使用するには必須です。
rules
配列の値を操作する式を記述します。
サンプルの解説
それでは、サンプルを解説しましょう。まずインラインビューの実行結果を見てみましょう。
select 'abcdefghijklmn' as ArrValue, 1 as soeji from dual;
| ArrValue | soeji |
| abcdefghijklmn | 1 |
次に、
model dimension by (soeji) measures(ArrValue)
によって、soejiを添字として、ArrValueを配列値とした、配列が作成されるイメージです。そして、rules(ArrValue[1] = 'Hello World')によって、ArrValue[1]の値が、Hello Worldに上書きされます。
サンプル2も見てみましょう。
select ArrValue,soeji
from (select 'abcdefghijklmn' as ArrValue,
1 as soeji from dual)
model
dimension by(soeji)
measures(ArrValue)
rules(ArrValue[1] = 'Hello World',
ArrValue[2] = 'Hello model');
| ArrValue | soeji |
| Hello World | 1 |
| Hello model | 2 |
rulesは、デフォルトでupsert（あればupdate、なければinsert）です。なので、ArrValue[1] = 'Hello World'によって、ArrValue[1]がupdateされ、ArrValue[2] = 'Hello Model'によって、ArrValue[2]がinsertされます。
サンプル3も見てみましょう。
select ArrValue,soeji
from (select 'abcdefghijklmn' as ArrValue,
1 as soeji from dual)
model
dimension by(soeji)
measures(ArrValue)
rules(ArrValue[3] = 'Hello Oracle');
| ArrValue | soeji |
| abcdefghijklmn | 1 |
| Hello Oracle | 3 |
model句では、配列の添字は連続してなくても問題ありません。SQLのイメージは次のようになります。dimension by(soeji)に対応する配列の添字を紫色でイメージしてます。
サンプル4も見てみましょう。
select ArrValue,soeji
from (select 'abcdefghijklmn' as ArrValue,
1 as soeji from dual)
model return updated rows
dimension by(soeji)
measures(ArrValue)
rules(ArrValue[4] = 'Hello CodeZine');
| ArrValue | soeji |
| Hello CodeZine | 4 |
model return updated rowsとすると、rules句によって、更新（updateかinsert）された行のみ出力することができます。
