Caché SQL の基礎
この章では、Caché SQL の機能の概要を示します。特に SQL 標準では取り上げられていないものや Caché の統一データ・アーキテクチャに関連する情報を示します。この章では、SQL について事前の知識があることが前提となっており、SQL の概念や構文の概要は対象外となっています。
以下の項目について説明します。
テーブル
Caché SQL では、データはテーブル内に存在します。各テーブルには、多数の列が定義されます。各テーブルには、0 行以上のデータ値が含まれます。以下の用語は、ほぼ同じ意味となります。
データ用語 | リレーショナル・データベース用語 | Caché 用語 |
---|---|---|
database (データベース) | schema (スキーマ) | package (パッケージ) |
table (テーブル) | persistent class (永続クラス) | |
field (フィールド) | column (列) | property (プロパティ) |
record (レコード) | row (行) |
詳細は、"Caché オブジェクトの使用法" の “永続オブジェクトの概要” の章にある “既定の SQL プロジェクションの概要” のセクションを参照してください。
テーブルには、基本テーブル (データを含むテーブルで、通常は単にテーブルと呼ばれます) とビュー (1 つまたは複数のテーブルに基づいた論理ビューを表します) という、2 種類の基本となるタイプがあります。
テーブルを定義する方法の詳細は、“テーブルの定義” の章を参照してください。
ビューを定義する方法の詳細は、“ビューの定義” の章を参照してください。
テーブルに対するクエリをより効率的にするには、テーブルにインデックスを定義します。“Caché SQL 最適化ガイド” の “インデックスの定義と構築” の章を参照してください。
参照の整合性を強化するには、テーブルに外部キーとトリガを定義します。“外部キーの定義” および “トリガの定義” の章を参照してください。
スキーマ
SQL スキーマによって、関連する一連のテーブル、ビュー、ストアド・プロシージャ、およびキャッシュ・クエリをグループ分けすることができます。スキーマは特定のネームスペースに対して定義されます。テーブル名はスキーマ内で一意である必要があるため、スキーマを使用することで、テーブル・レベルでの名前の競合を防ぐことができます。SQL スキーマは、Caché オブジェクトのパッケージに対応しています。スキーマからパッケージへのマッピングについては、"SQL からクラス名への変換" で詳しく説明します。
アプリケーションは、複数のスキーマでテーブルを指定できます。
SQL 名は、修飾、未修飾のどちらも指定できます。修飾名では、スキーマを指定します(schema.name)。未修飾名では、スキーマを指定しません (name)。スキーマを指定しない場合、Caché は以下の手法のいくつかを組み合わせて、スキーマを提供します。
-
作成およびクエリ操作の場合、Caché は管理ポータルの SQL 構成設定で定義されるように、システム全体のデフォルト SQL スキーマ名を使用します。この既定はすべてのネームスペースに適用されます。詳細は、"システム全体のデフォルト・スキーマ" を参照してください。
-
クエリ操作の場合、Caché はシステム全体のデフォルト・スキーマ名または現在のネームスペース内のユーザ指定のスキーマのリストを使用します。このリストは、スキーマ名の番号付き検索パスを構成する場合があります。ダイナミック SQL および埋め込み SQL 内のスキーマのリストを指定するには、異なる手法が使用されます。
ネームスペース内のすべての既存のスキーマを表示するには、以下の操作を実行します。
-
管理ポータルで [システム・エクスプローラ] を選択し、[SQL] (システム, SQL) を選択します。ページ上部の [切り替え] オプションを使ってネームスペースを選択します。利用可能なネームスペースのリストが表示されます。ネームスペースの選択後に、画面の左側にある [スキーマ] ドロップダウン・リストを選択します。これには、現在のネームスペースのスキーマのリストと、各スキーマに関連付けられているテーブルまたはビューがあるかどうかを示すブーリアン・フラグが表示されます。
-
このリストからスキーマを選択すると、[スキーマ] ボックスに表示されます。上記と同様に、ドロップダウン・リストでは、テーブル、システム・テーブル、ビュー、プロシージャ、またはスキーマに属するすべてを選択できます。このオプションの設定後、対応するフォルダを開き、メンバ項目を表示します。メンバ項目がない場合は、フォルダを開くと空白ページが表示されます (対応するスキーマごとのオプションを選択していない場合またはすべてを選択した場合は、フォルダを表示するとネームスペース全体のメンバ項目が表示されます)。
-
リストのメンバ項目のいずれかを選択すると、項目の [カタログの詳細] が表示されます。詳細で、項目のクエリ・キャッシュを選択できます。
クエリ
Caché SQL 内では、クエリを使用してテーブル内のデータを表示したり、変更したりします。大別すると、クエリにはデータの検索 (SELECT 文) とデータの変更 (INSERT 文、UPDATE 文、DELETE 文) の 2 種類があります。
SQL クエリは、さまざまな方法で使用できます。
-
ObjectScript で埋め込み SQL を使用できます。
-
ObjectScript または Caché Basic で、ダイナミック SQL を使用できます。
-
CREATE PROCEDURE または CREATE QUERY を使用して作成したストアド・プロシージャを呼び出します。
-
クラス・クエリを使用できます。詳細は、"Caché オブジェクトの使用法" の “クラス・クエリの定義と使用” を参照してください。
-
他のさまざまな環境から ODBC インタフェースまたは JDBC インタフェースを使用できます。
SELECT クエリについては、このドキュメントの "データベースの問い合わせ" の章で説明しています。
クエリは、Caché オブジェクトまたは ObjectScript ルーチンの一部です。
特権
Caché SQL では、特権を使用して、テーブルやビューなどへのアクセスを制限できます。一連のユーザおよびロールを定義して、それぞれに対し (読み取り、書き込みなど) 特権を付与できます。“ユーザ、ロール、特権” の章を参照してください。
データ表示オプション
Caché SQL では、SelectMode オプションを使用して、データの表示方法や保存方法を指定します。使用可能なオプションは、Logical、Display、および ODBC です。データは、内部的には Logical モードで保存されますが、上記のどのモードでも表示できます。データ型クラスはすべて、LogicalToDisplay()、LogicalToODBC()、DisplayToLogical()、および ODBCToLogical() メソッドを使用することで、内部の Logical 形式と、Display 形式または ODBC 形式間の変換を定義できます。SQL SelectMode が Display である場合、LogicalToDisplay 変換が適用され、返り値は表示用にフォーマットされます。既定の SQL SelectMode は Logical です。そのため、既定では、返り値は、そのストレージ形式で表示されます。
SelectMode は、クエリ結果セットのデータを表示する形式に影響し、WHERE 節などでデータ値を指定する形式にも影響します。Caché では、格納モードおよび指定された SelectMode に基づいて、適切な変換方式が適用されます。指定されたデータ値と SelectMode が一致しないと、エラーが生じるか誤った結果となる可能性があります。例えば、DOB が $HOROLOG 論理形式で格納される日付であり、WHERE 節で WHERE DOB > 2000–01–01 (ODBC 形式) と指定されている場合は、SelectMode = ODBC ならば、意図した結果が返されます。SelectMode = Display ならば、SQLCODE -146 [入力された日付を妥当な日付論理値に変換できません] が返されます。SelectMode = Logical ならば、2000–01–01 を論理データ値として解釈しようとするため、0 行が返されます。
ほとんどのデータ型で、3 つの SelectMode モードは、同じ結果を返します。以下のデータ型は、SelectMode オプションによる影響を受けます。
-
日付、時刻、およびタイムスタンプのデータ型。InterSystems SQL は、多くの日付、時刻、およびタイムスタンプのデータ型 (%Library.DateOpens in a new tab、%Library.TimeOpens in a new tab、%Library.TimeStampOpens in a new tab、%Library.FilemanDateOpens in a new tab、%Library.FilemanTimeStampOpens in a new tab、および %MV.DateOpens in a new tab) をサポートしています。これらのデータ型は、%Library.TimeStampOpens in a new tab を除いて、Logical モード、Display モード、および ODBC モードで異なる表現を使用します。Caché では、これらの複数のデータ型で日付を $HOROLOG 形式で保存します。この Logical モードの内部表現は、任意の開始日 (1840 年 12 月 31 日) からの日数を示す整数値、コンマ (区切り記号)、および当日の午前 0 時からの経過秒数を示す整数値で構成されます。Display モードでは、日付と時刻は一般に、データ型の FORMAT パラメータで指定された形式、または %SYS.NLS.FormatOpens in a new tab で現在のロケールの既定に設定された日付と時刻の形式で表示されます。アメリカ・ロケールの既定の形式は、DD/MM/YYYY hh:mm:ss です。ODBC モードでは、日付と時刻は常に YYYY-MM-DD mm:mm:ss という形式で表わされます。また、%Library.TimeStampOpens in a new tab データ型も、この ODBC 形式を Logical モードと Display モードに使用します。
-
%List データ型。Caché Logical モードでは、2 つの出力不能文字 (リスト内の最初の項目の前で使用されるものと、リスト項目間の区切り記号として使用されるもの) を使用してリストが格納されます。ODBC SelectMode では、リスト項目は、リスト項目間の区切り記号としてコンマを使用して表示されます。Display SelectMode では、リスト項目は、リスト項目間の区切り記号として空白を使用して表示されます。
-
VALUELIST および DISPLAYLIST を指定するデータ型。Display モードにしているときに、フィールドに DISPLAYLIST があるテーブルに値を挿入する場合は、入力した表示値が DISPLAYLIST に含まれる項目のいずれかと完全に一致している必要があります。
-
空の文字列、および空の BLOB (ストリーム・フィールド)。Logical モードの空の文字列および BLOB は、非表示文字 $CHAR(0) により表されます。Display モードでは、空の文字列 ("") により表されます。
以下のように SQL SelectMode を指定します。
-
現在のプロセスでは、$SYSTEM.SQL.SetSelectMode()Opens in a new tab を使用します。
-
Caché SQL シェル・セッションでは、SET SELECTMODE コマンドを使用します。
-
管理ポータルの “クエリの実行” ユーザ・インタフェース (システム, SQL) からのクエリ結果セットでは、[表示モード] ドロップダウン・リストを使用します。
-
ダイナミック SQL %SQL.Statement インスタンスでは、%SelectMode プロパティを使用します。
-
ダイナミック SQL %ResultSet インスタンスでは、%Library.ResultSet.RuntimeModeOpens in a new tab プロパティを使用します。
-
埋め込み SQL では、ObjectScript #SQLCompile Select プリプロセッサ指示文の設定を使用します。この指示文では、4 つ目の値である Runtime が許可されます。この値は、選択モードを RuntimeModeOpens in a new tab プロパティ設定の値に (Logical、Display、ODBC のいずれにも) 設定します。RuntimeMode の既定値は Logical です。
-
SQL コマンド CREATE QUERY、CREATE METHOD、CREATE PROCEDURE、および CREATE FUNCTION では、SELECTMODE キーワードを使用します。
-
SQL クエリ内の個々の列では、%EXTERNAL 関数、%INTERNAL 関数、および %ODBCOUT 関数を使用します。
-
Zen レポートでは、<report> 要素の runtimeMode 属性を使用します。
データ照合
照合は、値を並べる方法と比較する方法を指定するもので、Caché SQL と Caché オブジェクトのどちらにも欠かせない要素です。
照合タイプは、フィールド/プロパティ定義の一部として指定できます。特に指定がない場合、文字列フィールド/プロパティは、ネームスペースのデフォルトの照合に設定されます。デフォルトでは、文字列に対するネームスペースのデフォルトの照合は SQLUPPER に設定されています。SQLUPPER 照合では、並べ替えと比較のために文字列は大文字に変換されます。そのため、特に指定がない場合、文字列の並べ替えと比較では大文字と小文字が区別されません。
照合タイプはインデックス定義の一部として指定できます。また、インデックス付きフィールドの照合タイプを使用することもできます。
SQL クエリでは、フィールド名に照合関数を適用することで、フィールド/プロパティに定義されている照合タイプをオーバーライドできます。ORDER BY 節では、クエリの結果セットの順序を指定できます。指定した文字列フィールドが SQLUPPER として定義されている場合、クエリ結果の順序では大文字と小文字が区別されなくなります。
詳細は、"Caché SQL の使用法" の “照合” の章を参照してください。
SQL の実行
Caché は、SQL コードを記述し、実行する多くの方法をサポートしています。これには、以下のものがあります。
-
埋め込み SQL : ObjectScript コードに埋め込んだ SQL コード。
-
ダイナミック SQL : %SQL.StatementOpens in a new tab クラスを使用して、ObjectScript または Caché Basic から実行する SQL コード。
-
Execute() メソッド : %SYSTEM.SQLOpens in a new tab クラスの Execute()Opens in a new tab メソッドを使用して SQL コードを実行します。
-
SQL コードを含む ストアド・プロシージャ : CREATE PROCEDURE または CREATE QUERY を使用して作成します。
-
SQL シェル : ターミナル・インタフェースから実行する SQL 文。
-
[クエリ実行] インタフェース : 管理ポータルから実行する SQL 文。
Caché オブジェクト (クラスおよびメソッド) は、以下の作業に使用できます。