SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

Oracleのmodel句

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

rules句の繰り返し機能

  • X ポスト
  • このエントリーをはてなブックマークに追加

ダウンロード SourceCode (1.1 KB)

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

  • X ポスト
  • このエントリーをはてなブックマークに追加

はじめに

 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;

次のページ
2. 期間内の日付一覧の作成

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
Oracleのmodel句連載記事一覧
この記事の著者

山岸 賢治(ヤマギシ ケンジ)

趣味が競技プログラミングなWebエンジニアで、OracleSQLパズルの運営者。AtCoderの最高レーティングは1204(水色)。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/5154 2010/05/28 14:00

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング