Oracleのmodel句（2）
（行の補完）

rules句の繰り返し機能

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

はじめに

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

対象読者

• OracleのSQLでmodel句を使いたい方
• OracleのSQLの理解を深めたい方

必要な環境

　本稿で扱うSQLは、Oracle 11g R2（11.2.0.1.0）で動作確認しました。

1. 連番表の作成

　1以上の整数の連番が欲しいことは、たまにあります。all_objectsやall_catalogやdictといった特に権限がなくてもアクセスできるデータディクショナリを使って連番表を作成することが多いですが、model句の機能を使って連番表を作成してみます。

forコンストラクトでinを使う方法

select soeji,dummy
  from dual
 model
dimension by (1 as soeji)
measures(0 as dummy)
rules(
dummy[for soeji in(1,2,3,4,5)]= 0);

出力結果

soeji	dummy
1	0
2	0
3	0
4	0
5	0

　処理のイメージを解説すると、まず、model句のdimension by (1 as soeji)とmeasures(0 as dummy)によって、soejiを添字として、0を要素名dummyとして持つ配列を用意してます。次に、model句のrules句でのdummy[for soeji in(1,2,3,4,5)]= 0によって、soejiを添字とした配列の、添字の1,2,3,4,5のdummyに0をupsert（あればupdate、なければinsert）してます。

　SQLのイメージは下図のようになります。dimension by (1 as soeji)に対応する配列の添字を紫色で表現してます。

SQLのイメージ

　下記のように、位置参照を何度も使っても同じ結果を取得できますが、上記のdummy[for soeji in(1,2,3,4,5)]= 0のようにforコンストラクトを使ったほうがシンプルなSQLとなります。

位置参照を何度も使う方法

select soeji,dummy
  from dual
 model
dimension by (1 as soeji)
measures(0 as dummy)
rules(
dummy[1]= 0,
dummy[2]= 0,
dummy[3]= 0,
dummy[4]= 0,
dummy[5]= 0);

forコンストラクトでfrom toを使う方法

　下記のように、forコンストラクトでfrom toを使って、開始値と終了値と増分値を指定することもできます。dummy[for soeji from 1 to 5 increment 1]= 0は、開始値が1で終了値が5で増分値が1なので、soejiを添字とした配列の、添字の1,2,3,4,5のdummyに0がupsert（あればupdate,なければinsert）されます。

forコンストラクトでfrom toを使う方法

select soeji,dummy
  from dual
 model
dimension by (1 as soeji)
measures(0 as dummy)
rules(
dummy[for soeji from 1 to 5 increment 1]= 0);

iterateを使う方法

　下記のように、iterateを使って連番表を作成することもできます。rules iterate (5)と記述すると、rulesが5回評価されます。そして、ruleが1回評価されるごとにインクリメントされる値として、iteration_numberがあります。なお、iteration_numberは、0から開始されます。

　下記のSQLでは、rulesが5回評価されますので、dummy[iteration_number+1] = 0を指定して1から5までの連番を作成してます。

iterateを使う方法

select soeji,dummy
  from dual
 model
dimension by (1 as soeji)
measures(0 as dummy)
rules iterate (5)
(dummy[iteration_number+1] = 0);

　ちなみに、プログラム言語のfor文のループ変数でよく使われるiは、iterationの頭文字が由来だそうです。

Javaのfor文

for (int i = 0; i <= 10; i++)
    System.out.println("Hello World");

再帰with句を使う方法

　下記のSQLのように、Oracle 11g R2の新機能の再帰with句を使用して連番表を作成することもできます。model句と使い分けるといいでしょう。

再帰with句を使う方法

with rec(soeji) as(
select 1 from dual
union all
select soeji+1 from rec
 where soeji+1 <= 5)
select * from rec;