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

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

第二章では、画像や音声なども格納可能なLOB型やユーザーが定義可能なデータ型、そしてオブジェクト機能など、新しいデータ構造を中心に解説する

---

ユーザー定義可能な新しいデータ型

■新しい組込みデータ型

　SQL99のデータ型（データタイプ）は、基本的な組込みデータ型に加えて、ユーザー定義型とオブジェクト指向のための構造型が追加され、合計3種類に分類される（表4）。組込みデータ型には、長大な文字ストリングとバイナリストリングを格納するためのLOB型と、真理値を表現するBOOLEAN型、およびこれまでユーザーからの要求が強かった配列を表現するARRAY型が追加された。ユーザー定義型は、ユーザーがアプリケーションに合った任意のデータ型や複合データ型を定義するための機能拡張であり、構造型はオブジェクト指向を実現するために追加されたものである。ここでは、真理値型と配列型およびLOB型について説明し、ユーザー定義型と構造型については次回に説明する。

表4　SQL99のデータ型

組込みデータ型
文字ストリング型		CHAR、VARCHAR,CLOB(*) NCHAR, NCHAR VARYING, NCLOB(*)
バイナリストリング型		BLOB(*)
ビットストリング型		BIT, BIT VARYING
数値型		INTEGER,SMALLINT,DECIMAL, FLOATなど
真理値型		BOOLEAN (*)
日時型(日付,時刻,時刻印,時間隔)		DATE,TIME,TIMESTAMP,INTERVAL
ユーザ定義型 (*)
DISTINCT型
構造型
構造型 (*)
行型		ROW
参照型		REF
コレクション型:		ARRAY

備考：（*）は SQL99に追加された型を示す。

■真理値型（BOOLEAN型）

　これまでSQL文の中で列値の真偽を判別する目的のためには、「真」と「偽」をそれぞれ数値の「0」か「1」、あるいは「YES」か「NO」かなどの文字ストリングを割り当てて表現せざるをえなかった。SQL99では本来の意味であるTRUE（真）とFALSE（偽）およびUNKNOWN（不明）を値として持つ真理値型が提供される。

　図14に「割引対象」列をBOOLEAN型に指定した例を示した。WHERE句での「割引対象」列の真偽判定は、新しい“IS TRUE”述語や“IS FALSE”述語、あるいは“IS UNKOWN”述語によって評価すること、また「割引対象」列自身が述語になりうるところに特徴がある。

　プログラムの中で真理値型の列の入出力域として割り当てるホスト変数は、Ada言語やPL/I言語のようにBOOLEAN型を組込みデータ型として持っているプログラミング言語の場合はそのデータ型で、そうでないC言語やCOBOL言語は整数型で宣言することになっている。なおこの例では「割引対象」列がナル（NULL）可能であるが、その列がナルだった場合はUNKNOWNとして評価される。

図14　BOOLEAN型の例

■配列型(ARRAY型)

　配列（ARRAY)型はSQL99でサポートされる唯一のコレクション型である。列の中に配列を許すということは、リレーショナルモデルの考え方からすれば、第一正規形に違反した非正規形なのでとても許されないことのように思えるが、ユーザーや市場の強い要求によって実現した。実際、SQL99の関連規格であるSQL/MMは、フルテキストや空間データ情報を表現するのに配列型を多用しているので、一層標準化が急がれた。

　配列型の要素には任意のデータ型を定義できるが、配列型の要素をさらに配列型に定義することはできない。配列の要素数は固定であり、実行時に動的に変更することができないので、想定される最大数を指定する必要がある。取り出すときは、配列要素の何番目であるかを指定するが、複数の要素を正規化した形式で結果行を得ることができる。

図15　配列型（ARRAY型)の例

---

LOBとは

　SQL99で追加されたデータ型のLOBとはラージオブジェクト（Large Object）のことで、通常ギガバイト程度までの大きさのデータを言う。LOBはそのデータ型としてBLOB（Binary Large Object）とCLOB（Character Large Object）の2種類に分類される。

　BLOBは主に音声（オーディオ）や画像（イメージ）データなどバイナリーとして扱うデータを格納する場合に用いられるが、CLOBは文字テキストや文章などの文字データを格納する目的のために使用される。

■LOBデータ型の定義

　 LOBデータの実装方法はDBMSによって異なるが、一般にLOBデータはデータベースの外部に格納されるのではなく、データベースの中に格納され、DBMSによって管理される。LOBデータの大きさはKB、MBやGBといった単位で指定することができ、CREATE TABLE文でのLOB型の列の定義例を次に示す。

CREATE TABLE ペットリスト （ペット番号 CHAR（5） NOT NULL, 名前 VARCHAR（20）, 血統 CLOB（50K）, 写真 BLOB（1M））;

　このようにして定義したLOBデータをどのようにして扱えばよいのだろうか。これから例としてペットに関するデータを扱うペットリスト表をみていこう。

■LOBデータ型の取り扱い

　あとの例で示すように、LOBデータも他のデータ型と同じように、検索、挿入、更新することができる。そのためには当然LOBデータを格納するために、十分に大きなバッファーや入出力域を、SQL文を発行するプログラム中に用意しておく必要がある。しかし数百メガやギガ単位におよぶLOBデータの場合、こうした領域を予約しておくことは非常に困難であり、現実的でない。したがってLOBデータを直接に参照するのは、LOBの大きさが比較的小さく、LOBデータがサーバーからクライアントに送られても、クライアントのメモリーバッファ（例ではホスト変数pedigree）が超過することがない程度のときに限られる方法と言える。

LOBデータの検索： EXEC SQL SELECT ペット番号,名前,血統 INTO :petid, :petname, :pedigree FROM ペットリスト WHERE ペット番号 = 'D1234';

LOBは他のデータ型と同じように、以下のような操作で使用することができる。

• 値の検索
• 値の更新
• LIKE述語
• NULL述語
• SIMILAR述語
• 連結: ストリング1||ストリング2
• SUBSTRING関数
• POSITION関数
• LENGTH関数
• NULLIF/COALESCE関数
• TRIM
• OVERLAY
• CAST
• ユーザー定義関数

　LOBデータはCHARACTERデータ型やバイナリデータ型と同じような操作が可能であるが、以下の操作はLOBデータ列に対しては使用できない。

• 「より大きい」、「より小さい」（>、,>=、<、<=）の比較
• 主キー、ユニークキー、外部キーの設定
• GROUP BY、ORDER BYでの指定
• UNION,、INTERSECT、EXCEPT
• JOINでの結合キーとしての指定

■LOBロケータの使用

　LOBデータを検索したり、挿入、更新するためには、LOBデータを格納するための十分に大きなバッファを用意しておく必要があると述べたが、非常に大きなLOBデータの場合、それは現実的な策とは言えない。ただしLOB列に格納された全てのデータを必要とはせず、その一部のみ検索できればよいというような場合は、次のようにSUBSTR関数などを使用して、LOBデータをいくつかの小さな単位に分けて検索する方法が有効である。

SUBSTRとPOSITION関数を使用した、LOBデータの検索： ペットリスト表の血統列の家系の図以外の部分を検索する SELECT SUBSTR（血統, POSITION （'犬 名' IN 血統）, POSITION （'系 図' IN 血統）- POSITION （'犬 名' IN 血統） ） INTO :pedigree_info FROM ペットリスト WHERE ペット番号='D1234';

■HOLD LOCATORとFREE LOCATOR

　通常、ロケータは作業単位が終了すると暗黙的に解放される。しかしHOLD LOCATORコマンドを使用することによって、コミットが行われてもそのまま情報を保持することが可能である。もはやその情報がいらなくなった場合はFREE LOCATORコマンドによってロケータを解放することもできる。

HOLD LOCATORの例 ： SELECT 血統 INTO :LOB_locator FROM ペットリスト WHERE ペット番号='D1234'; HOLD LOCATOR :LOB_locator; COMMIT; INSERT INTO my_pet VALUES （..., :LOB_locator, ...）;

　 ロケータは作成された時にVALID のマークがつけられるが、トランザクションの終了時点（COMMITやROLLBACKの発行）でINVALIDになる。HOLD LOCATORステートメントは、このロケータに保持可能（holdable）のマークをつける。保持可能とマークされたロケータは、トランザクションをまたがってもVALIDな状態を保つことができるようになるのである。逆にFREE LOCATORステートメントはこのロケータに強制的にINVALIDをマークするものである。なおトランザクションがロールバックされると、全てのロケータはINVALIDな状態になる。

■LOBの挿入(更新)と検索方法の拡張

　SQL99では、LOBデータをストレージ上に展開しないで扱う方法として上記のロケータしか規定していない。しかし既にベンダーではこれ以外にLOBデータをストレージ上に展開しないで扱う方法として、ファイルの内容を直接LOB列に挿入したり、LOB列の内容を直接ファイルに書き出したりする機能を実装している。これはファイル参照変数によって実現される。LOBロケータがLOB値を(含むことなしに)表すのと同様に、ファイル参照変数はファイルを（その内容は含まずに）表す。以下はファイル参照変数を使用した、LOB列へのデータの挿入例である。

ファイル参照変数を使用した、LOB列へのデータの挿入： EXEC SQL BEGIN DECLARE SECTION SQL TYPE IS CLOB_FILE Lob_file; EXEC SQL END DECLARE SECTION; strcpy （Lob_file.name, "INPUT.TXT"）; Lob_file.name_length = strlen（"INPUT.TXT"）; Lob_file.file_options = SQL_FILE_READ; EXEC SQL INSERT INTO ペットリスト（ペット番号,名前,血統） VALUES（:petid, :petname,:Lob_file）;

　イメージやビデオデータなど、データベースに格納する前のLOBデータはファイルの形式で存在していることが多い。そのため、このようにファイルと直接入出力できる機能が非常に便利である。

　ここではLOB列の定義とその操作方法を紹介したが、SQL99ではファイルの内容をLOBデータとして格納するのではなく、外部ファイルとして存在していながらデータベースの管理下で操作を行うこともできるDATALINKというデータ型も提供することを検討していて、SQL/MEDで規定される見込みである。またLOBデータとそれを操作する各種ユーザー定義関数を作成することによって、DBMSはマルチメディアを完全に処理できる管理システムとして発展してきていると言える。

とびら　新しい業界標準「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総合インデックス」

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）