Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

Oracleのmodel句(2)
(行の補完)

rules句の繰り返し機能

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2010/05/28 14:00

ダウンロード SourceCode (1.1 KB)

 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_objectsall_catalogdictといった特に権限がなくてもアクセスできるデータディクショナリを使って連番表を作成することが多いですが、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のイメージ
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;

  • LINEで送る
  • このエントリーをはてなブックマークに追加

著者プロフィール

バックナンバー

連載:Oracleのmodel句
All contents copyright © 2005-2018 Shoeisha Co., Ltd. All rights reserved. ver.1.5