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