Oracleのmodel句

Oracleのmodel句（1）
――model句のサンプルと集計行の追加方法

model句の入門事項

2010/02/01 14:00

　Oracle 10gのSQLの新機能のmodel句について、入門事項からOracle Technology Network（US）でよく見かける問題の解法まで、SQLのイメージを交えて解説します。本稿ではHelloWorldなどのmodel句のサンプルと、集計行の追加を行う方法を扱います。

Page 1
Page 2

はじめに

　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文の評価順序は、下記となります。

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などのサンプルを見てみましょう。

サンプル1

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も見てみましょう。

サンプル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も見てみましょう。

サンプル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のイメージ

　サンプル4も見てみましょう。

サンプル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');

出力結果