新しい業界標準「SQL99」詳細解説

オブジェクト指向、Javaを取り入れた
新しい業界標準「SQL99」詳細解説

第一章　高度なデータ操作（2）

高度なリレーショナル操作

　SQLでは、以前よりも複雑で高度なリレーショナル操作が可能になっている。これにより、1つのSQL文で高度な処理が表現できるようになった。そうした新機能について紹介する。

■共通表式 WITH句

　SELECT文の結果も「表」なので、表名を書くところにはSELECT文をそのまま書くことができる。これはすでにSQL92が導入している「表式」という概念で、SELECT文のFROM句の中にもSELECT文を書くことができる。次の例は販売実績表から地域ごとに月・製品別売上額のその地域の売上に占める比率（百分率）を求めている。比率を計算するための分母（その地域の売上額合計）をFROM句中のSELECT文で計算していることに注目していただきたい。

SELECT S.地域名,S.月,S.製品名, 

            CAST（S.売上額 AS DECIMAL（5,2））*100/T.合計売上額

            FROM 販売実績表 S,

            （SELECT 地域名, SUM（売上額） FROM 販売実績表 GROUP BY 地域名）

            AS T（地域名,合計売上額）

            WHERE S.地域名=T.地域名

            ORDER BY S.地域名,S.月,S.製品名;

　これによって、別にビューを定義することなく1個のSELECT文で済ませることができた。しかし結合や副照会などのように同じ表を1個のSELECT文中で何度も参照したい場合は、やはりその回数分だけ繰り返し書かなければならないという煩雑さがある。

　そこでSQL99は表式の概念を拡張して、一度記述すれば済むWITH句を導入した。WITH句に記述したものを「共通表式」と呼び、そのSQL文中に限り繰り返し使用できる。前例をWITH句を使って書き換えた例を示す。FROM句のSELECT文をそのままWITH句に移すだけだが、すっきりと見やすくなったと思う。なおWITH句には、このSQL文だけに有効な（一時的な）表の名前と、それに続く括弧の中には同様の列名を指定する。

WITH 地域別合計売上額表（地域名,合計売上額） AS （SELECT 地域名, SUM（売上額） FROM 販売実績表 GROUP BY 地域名） SELECT S.地域名,S.月,S.製品名, S.売上額 CAST（S.売上額 AS DECIMAL（5,2））*100/T.合計売上額 AS 売上比率 FROM 販売実績表 S, 地域別合計売上額表 T WHERE S.地域名=T.地域名 ORDER BY S.地域名,S.月,S.製品名;

　実行に際しては、WITH句のSELECT文が先に実行されて、結果行が一時的に名前を付けた表に保管される。それが新たな表として最後のSELECT文で参照できると考えればよい。その概念を図2に示した。そしてこのWITH句の共通表式をさらに拡張したのが、次に述べたい再帰SQLである。

図2 共通表式を使用したSELECT文の例

■再帰SQL

　製造業のアプリケーションでよく使われる表に、部品構成表というものがある。それはある製品がどのような部品がいくつで構成されているかを表わしていて、生産や部品調達の計画を立てるときなどに使われる。部品構成表で注目すべき点は、部品が別の部品を子部品として持ち、さらにその子部品が別の子部品を持っているかもしれず、その部品構成の深さ（構成レベルとも言う）は固定ではなく、製品や部品によって可変であるということだ。図3に簡単な自転車の部品構成表の例を示す。

図3 自転車の部品構成表の例

　ここでは、ある自転車がフレーム、ハンドルそれぞれ各1個と、、ブレーキ2個と車輪2個から構成されている。フレームはサドル1個とペダル2個から構成されている。ペダルはさらに留め具を3個必要としている。ハンドルや車輪についてもそれぞれ子部品がある。

　このように部品の構成を調べることを構成展開と言うが、特に注意していただきたいのは留め具がペダルとハンドルに共通部品として使用されているので、1台の自転車を生産するのに共通部品の留め具がいったい全体で何個必要なのかが、部品構成表を見ただけではすぐにはわからない点だ。また例に挙げていないが、製品によっては構成展開の深さがもっと深いかも知れない。

　さて、自転車を構成する全部品と各部品の所要数の一覧表を作成することを考えたい。そのためには次のように親品番の比較値に「自転車」を指定してSELECT文を実行すればよい。

SELECT 親品番, 子品番, 所要数 FROM 部品構成表

            WHERE 親品番='自転車';

　結果行の子品番は、「ハンドル」と「フレーム」「ブレーキ」「車輪」である。

　次に、「ハンドル」を同じ部品構成表の親品番の比較値に設定して2回目のSELECT文を実行する。

SELECT 親品番, 子品番, 所要数 FROM 部品構成表

            WHERE 親品番=‘ハンドル';

　今度の結果行の子品番は、「グリップ」、「留め具」であり、また次に「グリップ」を親品番の比較値に設定して3回目のSELECT文を実行する。この場合はこれ以上、子部品がないので、「留め具」を親品番とする子品番があるかどうかの4回目のSELECT文を実行する。この場合も見つからないので、「ハンドル」の次の「フレーム」を親品番とする子品番を探す5回目のSELECT文を実行する。

　このようにして全部の子部品について、もうこれ以上その下に子部品がなくなるまで繰り返せば、全部品を取り出し、部品の所要量を計算することができる。しかしこの手順でわかるように、そのためには部品の数だけSELECT文を発行しなければならない。また、すでに述べたように構成の深さが一定でないのでどうしてもプログラムが複雑にならざるをえなかった。

　SQL99では、共通表式のところで紹介したWITH句を使用することによってSQL文1個で部品展開を行うことができる。製品から子部品を求めることを特に部品構成の正展開処理と言う。

照会1:

            WITH RECURSIVE 中間表（親品番, 子品番, 所要数） AS

            （ SELECT PP.親品番, PP.子品番, PP.所要数 FROM 部品構成表 PP 

            WHERE PP.親品番='自転車' 

            UNION ALL

            SELECT PP.親品番, PP.子品番, PP.所要数*中間表.所要数 

            FROM 中間部品表, 部品構成表 pp 

            WHERE pp.親品番=中間表.子品番

            ）

            SELECT 親品番, 子品番, 所要数

            FROM 中間表;

　WITH句の次にRECURSIVEと書くので、特にこれを再帰SQL文とか、再帰照会(再帰問合わせ)と呼ぶ。図4に再帰SQLの概念を示す。どのように部品構成表が再帰的にアクセスされるのかの概略を図の（1）から（4）の処理に分解して図示した。

初回に、このSELECT文を1回だけ実行して、その結果行をWITH句に宣言した中間表(任意の名前でよい)に保管する。つまりレベル2のすべての子部品が保管される。
二回目に、（1）で保管した中間表の行の子部品(レベル2)を部品構成表の親部品とする結合操作のSELECT文を実行して、レベル3の子部品を求める。必要であれば、レベル2の部品の所要数と直属のレベル3の子部品の所要数をかければ、そのレベルまでのその子部品の総所要数を計算できる。
レベル3の子部品を中間表に追加する。この（2）～（3）の操作を、これ以上の子部品が見つからなくなるまで繰り返す。最終的に、すべての子部品が中間表に挿入されることになる。
中間表から全部の行を取り出す。

図4 再帰SQLを利用した部品構成の正展開例

　この構成品の展開で気がつくのは、同じ部品「留め具」が何回か現れることだが、それらを集約し、製品全体での所要量を計算したい場合は、照会2ような再帰SQL文を書けばよい。結果を図5に示した。照会1との違いは、GROUP BY句を使用することによって子部品単位に集約して所要数の和を計算させているところにある。

照会2:

            WITH RECURSIVE 中間表（親品番, 子品番, 所要数） AS

            （ SELECT PP.親品番, PP.子品番, PP.所要数 FROM 部品構成表 PP 

            WHERE PP.親品番='製品A' 

            UNION ALL

            SELECT PP.親品番, PP.子品番, PP.所要数*中間表.所要数 

            FROM 中間表, 部品構成表 PP 

            WHERE PP.品番=中間表.品番

            ）

            SELECT 子品番, SUM（所要数） AS 総所要数

            FROM 中間表

            GROUP BY 子品番;

図5　部品構成の所要量展開例

<参考>
　SQL92で可能になった外結合(アウタージョイン)を利用すると1回のSELECT文で全部品を横並びで取り出すことができる。

SELECT L1.親品番 AS 品番（レベル1）, L2.子品番 AS 品番（レベル2）
, L3.子品番 AS 品番（レベル3）, L4.子部品 AS 品番（レベル4）
, L5.子部品 AS 品番（レベル5）
FROM 部品構成表 L1 LEFT OUTER JOIN 部品構成表 L2 ON L1.子品番=L2.親品番
LEFT OUTER JOIN 部品構成表 L3 ON L2.子品番=L3.親品番
LEFT OUTER JOIN 部品構成表 L4 ON L3.子品番=L4.親品番
LEFT OUTER JOIN 部品構成表 L5 ON L3.子品番=L5.親品番
WHERE L1.親品番=‘自転車';

この左外結合の結果を表3に示す。しかし、構成展開の深さがたかだか5レベルまでとわかっているならば上記のSELECT文で対応できるが、深さが不明な場合は、予想される最大数の深さだけ外結合をFROM句に指定しなければならない。それは静的SQLを使う限りFROM句に書ける表の数が固定だからである。

表3　外結合（アウタージョイン）を利用した部品の構成展開例

品番（レベル1）	品番（レベル2）	品番（レベル3）	品番（レベル4）	品番（レベル5）
自転車	ハンドル	グリップ	─	─
自転車	ハンドル	留め具	─	─
自転車	フレーム	サドル	─	─
自転車	フレーム	ペダル	留め具	─
自転車	ブレーキ	─	─	─
自転車	車輪	スポーク	─	─

備考：この例では、‘-’はナルを表し、そのレベルに子部品が見つからなかったことを示す。例えば、グリップの下の品番には子部品がない。

　もうひとつ再帰SQLの応用例を紹介したい。図6の右側は、航空路線の経路を表わし、都市間の数字は運賃等の諸費用を表わしたものである。それをリレーショナルの表で表現したものを図の左側に示した。

図6　航空経路の例

　問題は、パリからサンフランシスコまでの乗り継ぎのある経路のうち、最少費用の経路を探したいとする。それには次のような再帰SQLを実行すれば良い。

照会3:

            WITH RECURSIVE 経路表（出発地, 目的地, 経路, 合計費用, 乗継回数)

            （SELECT 出発地, 目的地, 出発地||'.'||目的地, 経路, 費用, 0 

            FROM 航空便表

            WHERE 出発地='パリ'

            UNION ALL

            SELECT in.出発地, out.目的地, in.経路||'.'||out.目的地,

            in.合計費用+out.費用, in.乗継回数+1 

            FROM 経路表 in, 航空便表 out

            WHERE in.目的地=out.出発地 

            AND in.目的地<>'パリ'） <---- 無限ループ防止のための述語

            

            SELECT 経路, 合計費用, 乗継回数

            FROM 経路表

            WHERE 目的地='サンフランシスコ'

            ORDER BY 合計費用;

　ところで、もし航空便表の目的地にパリの便が含まれていると無限ループになるので、WITH句中のUNIONの後のSELECT文の述語に無限ループ防止のための述語を入れた。再帰SQLにはこのように無限ループにに陥る危険性があるが、回避するのはユーザの責任である。
　この例が示すように、再帰SQLは幾つかに分岐している不定の数の経路の最短経路や最長経路などを分析するのに威力を発揮する。複数かつ可変の段階から成る生産工程や、プロジェクトの作業行程の工数最適化の分析にも応用できる便利な機能であることがご理解いただけると思う。

とびら　新しい業界標準「SQL99」詳細解説

第一章　高度なデータ操作

SQL99の背景と特徴
SQL99の主な機能強化
　　スキーマ定義の新機能
　　データ操作と演算子の新機能
　　整合性の新機能
　　セキュリティ（機密保護）の新機能
　　トランザクション管理の新機能
　　クライアント／サーバの新機能
高度なリレーショナル操作
　　共通表式 WITH句
　　再帰SQL
OLAPによる分析手順
　　ROLLUP
　　CUBE
　　GROUPING SETS
ユニオン（UNION）経由の更新
　　結合（JOIN）経由の更新

第二章　柔軟さを増したデータ構造

ユーザー定義可能な新しいデータ
　　新しい組込みデータ型
　　真理値型(BOOLEAN型)
　　配列型(ARRAY型)
LOBとは
　　LOBデータ型の定義
　　LOBデータ型の取り扱い
　　LOBロケータの使用
　　HOLD LOCATORとFREE LOCATOR
　　LOBの挿入(更新)と検索方法の拡張
ユーザー定義型
　　ユーザー定義DISTINCT型
ユーザー定義関数
 関数のオーバーロード
オブジェクトリレーショナル
　　ユーザー定義構造型と列オブジェクト
　　ユーザー定義構造型と行オブジェクト
副表（サブテーブル）と継承（インヘリタンス）
　　副型を持つ列オブジェクト
　　行オブジェクトと経路式
オブジェクトビュー
トリガ

第三章　SQLJと今後の標準化動向

クライアント/サーバ環境のための機能強化
　　ストアドプロシージャ
　　新しいプロシージャ言語
静的埋め込みSQLを実現する「SQLJ」
　　パフォーマンスの向上と移植性
　　SQLJのコンパイル
　　SQLJの記述方法
　　SQLJ 対 JDBC
　　イテレータの使用
　　SQLデータ型としてのJavaクラス
オブジェクトリレーショナル機能の応用例
　　SQL/MM全文検索(フルテキスト)
　　SQL/MM地理情報(スペーシャル)
今後のSQL標準化動向
　　コレクション型の拡張
　　オブジェクトリレーショナル機能の拡張
　　SQL/MED
　　OLAP機能の拡張
　　自由度が高まるデータアクセス

「Master of IP Network総合インデックス」

Master of IP Network フォーラム新着記事

完全HTTPS化のメリットと極意を大規模Webサービス――ピクシブ、クックパッド、ヤフーの事例から探る （2017/7/13）
　2017年6月21日、ピクシブのオフィスで、同社主催の「大規模HTTPS導入Night」が開催された。大規模Webサービスで完全HTTPS化を行うに当たっての技術的、および非技術的な悩みや成果をテーマに、ヤフー、クックパッド、ピクシブの3社が、それぞれの事例について語り合った
ソラコムは、あなたの気が付かないうちに、少しずつ「次」へ進んでいる （2017/7/6）
　ソラコムは、「トランスポート技術への非依存」度を高めている。当初はIoT用格安SIMというイメージもあったが、徐々に脱皮しようとしている。パブリッククラウドと同様、付加サービスでユーザーをつかんでいるからだ
Cisco SystemsのIntent-based Networkingは、どうネットワークエンジニアの仕事を変えるか （2017/7/4）
　Cisco Systemsは2017年6月、同社イベントCisco Live 2017で、「THE NETWORK. INTUITIVE.」あるいは「Intent-based Networking」といった言葉を使い、ネットワークの構築・運用、そしてネットワークエンジニアの仕事を変えていくと説明した。これはどういうことなのだろうか
ifconfig　～（IP）ネットワーク環境の確認／設定を行う （2017/7/3）
　ifconfigは、LinuxやmacOSなど、主にUNIX系OSで用いるネットワーク環境の状態確認、設定のためのコマンドだ。IPアドレスやサブネットマスク、ブロードキャストアドレスなどの基本的な設定ができる他、イーサネットフレームの最大転送サイズ（MTU）の変更や、VLAN疑似デバイスの作成も可能だ。