はじめに
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)
に対応する配列の添字を紫色でイメージしてます。
![SQLのイメージ SQLのイメージ](http://cz-cdn.shoeisha.jp/static/images/article/3091/fig01a.gif)
サンプル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)された行のみ出力することができます。