データベースの変更
既存のテーブルに対して SQL 文を使用するか、対応する永続クラスに対して ObjectScript 操作を使用して、InterSystems IRIS® データ・プラットフォーム・データベースの内容を変更することができます。READONLYOpens in a new tab として定義されている永続クラス (テーブル) を変更することはできません。
SQL コマンドを使用すると、データの整合性が自動的に維持されるようになります。SQL コマンドはアトミック (全か無か) 処理です。テーブルにインデックスが定義されている場合は、SQL は変更を自動的に更新します。データや参照整合の制約が定義されている場合は、SQL は自動的にそれらを実行します。定義されたトリガがある場合、これらのアクションを実行すると、対応する挿入、更新、または削除トリガがプルされます。
データの挿入
テーブルにデータを挿入するには、SQL 文を使用するか、永続クラスのプロパティを設定して保存します。
SQL を使用したデータの挿入
INSERT 文は、SQL テーブルに新規のレコードを挿入します。単一のレコードまたは複数のレコードを挿入できます。
以下の例では、単一のレコードを挿入します。これは、単一レコードを挿入するために使用可能ないくつかの構文形式の 1 つです。
INSERT INTO MyApp.Person
(Name,HairColor)
VALUES ('Fred Rogers','Black')
以下の例では、既存のテーブルからデータのクエリを実行して、複数のレコードを挿入します。
INSERT INTO MyApp.Person
(Name,HairColor)
SELECT Name,Haircolor FROM Sample.Person WHERE Haircolor IS NOT NULL
INSERT コマンドを使用してさまざまなタイプのデータを挿入する方法の詳細は、"INSERT" のコマンド・リファレンス・ページを参照してください。
INSERT OR UPDATE 文を発行することもできます。この文は、そのレコードがまだ存在していない場合に、SQL テーブルに新規のレコードを挿入します。そのレコードが存在する場合は、この文は、提供されたフィールド値でそのレコード・データを更新します。
オブジェクト・プロパティを使用したデータの挿入
ObjectScript を使用して、1 つまたは複数のデータ・レコードを挿入できます。既存の永続クラスのインスタンスを作成し、1 つまたは複数のプロパティ値を設定してから、%Save() を使用してデータ・レコードを挿入します。
以下の例では、単一のレコードを挿入します。
SET oref=##class(MyApp.Person).%New()
SET oref.Name="Fred Rogers"
SET oref.HairColor="Black"
DO oref.%Save()
以下の例では、複数のレコードを挿入します。
SET nom=$LISTBUILD("Fred Rogers","Fred Astare","Fred Flintstone")
SET hair=$LISTBUILD("Black","Light Brown","Dark Brown")
FOR i=1:1:$LISTLENGTH(nom) {
SET oref=##class(MyApp.Person).%New()
SET oref.Name=$LIST(nom,i)
SET oref.HairColor=$LIST(hair,i)
SET status = oref.%Save() }
UPDATE 文
UPDATE 文は、SQL テーブルの既存の 1 つ以上のレコードの値を変更します。
UPDATE MyApp.Person
SET HairColor = 'Red'
WHERE Name %STARTSWITH 'Fred'
INSERT または UPDATE 時の計算フィールドの値
計算フィールドを定義するとき、そのフィールドのデータ値を計算するコードを指定できます。このデータ値は、行の挿入、更新、またはその両方が行われるとき、またはクエリが実行されるときに計算されます。以下のテーブルに、各タイプの計算処理に必要なキーワード、およびフィールド/プロパティ定義の例を示します。
計算タイプ | 指定する DDL SQL キーワード | 指定する永続クラスのキーワード |
---|---|---|
INSERT のみ |
COMPUTECODE Birthday VARCHAR(50) COMPUTECODE {SET {Birthday}=$PIECE($ZDATE({DOB},9),",")_" changed: "_$ZTIMESTAMP } |
SqlComputeCode および SqlComputed Property Birthday As %String(MAXLEN = 50) [ SqlComputeCode = {SET {Birthday}=$PIECE($ZDATE({DOB},9),",")_" changed: "_$ZTIMESTAMP}, SqlComputed ]; |
UPDATE のみ |
DEFAULT、COMPUTECODE、および COMPUTEONCHANGE Birthday VARCHAR(50) DEFAULT ' ' COMPUTECODE {SET {Birthday}=$PIECE($ZDATE({DOB},9),",")_" changed: "_$ZTIMESTAMP } COMPUTEONCHANGE (DOB) |
N/A |
INSERT と UPDATE の両方 |
COMPUTECODE および COMPUTEONCHANGE Birthday VARCHAR(50) COMPUTECODE {SET {Birthday}=$PIECE($ZDATE({DOB},9),",")_" changed: "_$ZTIMESTAMP } COMPUTEONCHANGE (DOB) |
SqlComputeCode、SqlComputed、および SqlComputeOnChange Property Birthday As %String(MAXLEN = 50) [ SqlComputeCode = {SET {Birthday}=$PIECE($ZDATE({DOB},9),",")_" changed: "_$ZTIMESTAMP}, SqlComputed, SqlComputeOnChange = DOB ]; |
クエリ |
COMPUTECODE および CALCULATED または TRANSIENT Birthday VARCHAR(50) COMPUTECODE {SET {Birthday}=$PIECE($ZDATE({DOB},9),",")_" changed: "_$ZTIMESTAMP } CALCULATED |
SqlComputeCode、SqlComputed、および Calculated または Transient Property Birthday As %String(MAXLEN = 50) [ SqlComputeCode = {SET {Birthday}=$PIECE($ZDATE({DOB},9),",")_" changed: "_$ZTIMESTAMP}, SqlComputed, Calculated]; |
DDL DEFAULT キーワードは、挿入時のデータ値の計算より優先されます。DEFAULT は空の文字列などのデータ値を取る必要があり、NULL にはできません。永続クラス定義では、挿入時に InitialExpression プロパティ・キーワードは SqlComputed データ値をオーバーライドしません。
DDL COMPUTEONCHANGE キーワードは、1 つのフィールド名、またはコンマで区切られたフィールド名のリストを取ることができます。これらのフィールド名は、更新時にこのフィールドの計算をトリガするフィールドを指定します。リストされたフィールド名はテーブルに存在する必要がありますが、計算コードに出現しなくてもかまいません。実際のフィールド名を指定する必要があります。アスタリスク構文は指定できません。
COMPUTECODE および COMPUTEONCHANGE を使用する代わりに、ON UPDATE キーワード句を使用して、レコードが変更されるときにフィールドをリテラルまたはシステム変数 (現在のタイムスタンプなど) に設定できます。ON UPDATE 句は INSERT 時と UPDATE 時に変更します。UPDATE 時にのみ変更するには、DEFAULT 句と ON UPDATE 句を使用します。
DDL CALCULATED または TRANSIENT キーワードは、フィールドがクエリによってアクセスされるたびにデータ値を計算します。フィールドを選択リストで指定する必要はありません。例えば、SELECT Name FROM MyTable WHERE LENGTH(Birthday)=36 は条件式を評価する前に、Birthday フィールドを計算します。管理ポータルの [テーブルを開く] オプションでクエリが実行され、CALCULATED および TRANSIENT データ値が計算されます。
計算フィールドの制限事項 :
-
UPDATE では更新しない : UPDATE ではレコード内のフィールドに前の値と同じ値を提供し、実際にはレコードを更新しません。レコードに対して実際の更新が行われない場合、COMPUTEONCHANGE は呼び出されません。レコードに対して実際の更新が行われない場合でも、ON UPDATE は更新処理時に呼び出されます。レコードが実際に更新されたかどうかに関係なく、更新時に常に計算フィールドを再計算する場合は、更新トリガを使用します。
-
計算フィールドに対するユーザ指定の明示的な値 :
-
INSERT : INSERT 時には、COMPUTECODE、DEFAULT、または ON UPDATE フィールドに常に明示的な値を指定できます。InterSystems SQL は常に、生成された値ではなく明示的な値を取ります。
-
UPDATE COMPUTEONCHANGE : UPDATE 処理では COMPUTEONCHANGE フィールドに明示的な値を指定できます。InterSystems SQL は常に、計算された値ではなく明示的な値を取ります。
-
UPDATE ON UPDATE : UPDATE 処理では ON UPDATE フィールドに明示的な値を指定できません。InterSystems SQL はユーザが指定した値を無視し、ON UPDATE で生成された値を取ります。ただし、InterSystems SQL は明示的な値に対してフィールド検証を実行し、例えば、指定された値が最大データ・サイズより大きい場合は、SQLCODE -104 エラーを生成できます。
-
CALCULATED または TRANSIENT : INSERT または UPDATE 処理では CALCULATED または TRANSIENT フィールドに明示的な値を指定できません。CALCULATED または TRANSIENT フィールドはデータを格納しないためです。ただし、InterSystems SQL は明示的な値に対してフィールド検証を実行し、例えば、指定された値が最大データ・サイズより大きい場合は、SQLCODE -104 エラーを生成できます。
-
データの検証
この章で説明する挿入操作と更新操作では、データ検証が自動的に実行されます。これにより、テーブルに無効なデータが格納されるのを防ぐことができます。その他の方法でテーブルに格納されたデータは、検証されない可能性があります。$SYSTEM.SQL.Schema.ValidateTable() メソッドを使用して、テーブルのデータを検証できます。テーブル名は修飾 ("schema.table")、未修飾 ("table") のどちらでもかまいません。未修飾のテーブル名は既定のスキーマ名を取ります。スキーマ検索パスの値は使用されません。
ValidateTable() は、テーブルのデータで見つかった各検証の問題に対する行を含む結果セットを返します。このメソッドは、以下のデータ検証を実行します。
-
データ型 IsValid() メソッドを使用して、各データ値をフィールドのデータ型に照らして検証します。
-
必須制約があるフィールドに Null 値がないことを検証します。
-
一意制約があるフィールドに重複する値がないことを検証します。
-
外部キー・フィールドが、参照されるテーブルの有効な行を参照していることを検証します。
検証結果セットは、%sqlcontext オブジェクトに保持されます。DO %sqlcontext.%Display() は、次のターミナルの例に示すように、データ検証結果を現在のデバイスに表示します。
USER>DO $SYSTEM.SQL.Schema.ValidateTable("Sample.MyTable")
USER>DO %sqlcontext.%Display()
Dumping result #1
Row(s) With an Issue Field Or Constraint Name Error
%ID 3 Home_City Cannot be null
%ID 14 Home_City Cannot be null
%ID 10 Home_City Cannot be null
%ID 13 Home_City Cannot be null
%ID 6 Home_PostalCode Value is invalid: BadZip
%ID 8 Home_PostalCode Value is invalid: WhoKnows
%ID 9 Home_PostalCode Value is invalid: BadZip
%ID 10 Home_PostalCode Value is invalid: WhoKnows
%ID 11 Home_PostalCode Value is invalid: BadZip
%ID 9 Home_State Cannot be null
%ID 3 Home_State Cannot be null
%ID 10 Home_State Cannot be null
12 Rows(s) Affected
USER>
フィールドはアルファベット順で示されます。この例で、Home_City および Home_State フィールドは必須の値の検証に失敗しました。Home_PostalCode (%Integer データ型) フィールドはデータ型の検証に失敗しました。
このデータ検証処理を SQL から呼び出すこともできます。それには、次の例に示すように、ValidateTable ストアド・プロシージャを呼び出します。
CALL %SYSTEM.ValidateTable('Sample.MyTable')
シャード・テーブルの場合、ValidateTable() をシャード・マスタ・テーブルで呼び出す必要があります。
ValidateTable() はロックを実行しません。このため、同時処理を行うライブ・システム上のテーブルに対して実行した場合、誤検出エラー・レポートを受け取る可能性があります。
DELETE 文
DELETE 文は、SQL テーブルから既存の 1 つ以上のレコードを削除します。
DELETE FROM MyApp.Person
WHERE HairColor = 'Aqua'
TRUNCATE TABLE コマンドを発行して、テーブルからすべてのレコードを削除することもできます。DELETE を使用して、テーブルからすべてのレコードを削除することもできます。DELETE (既定) は削除トリガをプルします。一方、TRUNCATE TABLE は削除トリガをプルしません。DELETE を使用してすべてのレコードを削除しても、テーブル・カウンタはリセットされません。一方、TRUNCATE TABLE ではカウンタはリセットされます。
トランザクション処理
トランザクションは、1 つの作業単位を構成する一連のデータ変更文 (INSERT、UPDATE、DELETE、INSERT OR UPDATE、および TRUNCATE TABLE) です。
SET TRANSACTION コマンドを使用して、現在のプロセスのトランザクション・パラメータを設定することができます。START TRANSACTION コマンドを使用しても、同じパラメータを設定することができます。これらのトランザクション・パラメータは、明示的に変更されるまで複数のトランザクションにわたり有効です。
START TRANSACTION コマンドで、明示的にトランザクションを開始します。一般的に、このコマンドはオプションです。トランザクションの %COMMITMODE が IMPLICIT または EXPLICIT の場合は、トランザクションは最初のデータベース変更処理で自動的に開始されます。トランザクションの %COMMITMODE が NONE の場合は、START TRANSACTION を明示的に指定してトランザクション・プロセスを開始する必要があります。
トランザクションが成功したら、変更を暗黙的 (自動的) または明示的にコミットできます。COMMIT 文を使用してそのデータ変更をデータベースに永久的に追加して、リソースを解放する必要があるかどうかは、%COMMITMODE の値で決定します。
トランザクションが失敗した場合、そのデータ修正を元に戻す ROLLBACK 文を使用して、データベースに反映されないようにします。
SQL が管理ポータルの [SQLクエリ実行] インタフェースを使用して実行されている場合、SQL のトランザクション文はサポートされません。このインタフェースは SQL コードを開発するときのテスト環境として使用することを意図したもので、実際のデータを変更するためのものではありません。
トランザクションとセーブポイント
InterSystems SQL では、完全トランザクション処理とセーブポイントを使用したトランザクション処理の 2 種類のトランザクション処理を実行できます。完全トランザクション処理の場合、トランザクションは START TRANSACTION 文で (明示的または暗黙的に) 開始され、COMMIT 文でトランザクションを (明示的または暗黙的に) 完了して実行されたすべての処理をコミットするか、ROLLBACK 文でトランザクション中に実行されたすべての処理を戻すまで、続きます。
セーブポイントを使用する場合、InterSystems SQL はトランザクション内でレベルをサポートします。START TRANSACTION 文で、(明示的にまたは暗黙的に) トランザクションを開始します。トランザクション内で SAVEPOINT を指定して、プログラム内の 1 つ以上の名前付きセーブポイントを指定します。1 つのトランザクションに最大 255 個までの名前付きセーブポイントを指定できます。セーブポイントを追加すると、$TLEVEL トランザクション・レベル・カウンタがインクリメントされます。
-
COMMIT は、トランザクションの間に実行されたすべての作業をコミットします。セーブポイントは無視されます。
-
ROLLBACK は、トランザクションの間に実行されたすべての作業をロールバックします。セーブポイントは無視されます。
-
ROLLBACK TO SAVEPOINT pointname により、pointname で指定された SAVEPOINT から実行されたすべての処理をロールバックし、内部トランザクション・レベル・カウンタを当該のセーブポイント・レベル数だけデクリメントします。例えば、svpt1 と svpt2 という 2 つのセーブポイントを設定して、svpt1 にロールバックする場合、ROLLBACK TO SAVEPOINT svpt1 により svpt1 から実行された処理を戻し、この場合は、トランザクション・レベル・カウンタを 2 つデクリメントします。
非トランザクション操作
トランザクションが有効な間でも、以下の操作はトランザクションに含まれないのでロールバックできません。
-
IDKey カウンタのインクリメントは、トランザクション操作ではありません。IDKey は $INCREMENT (または $SEQUENCE) によって自動的に生成されます。そこでは SQL トランザクションとは別にカウントを保持しています。例えば、IDKeys が 17、18、および 19 のレコードを挿入し、この挿入をロールバックすると、挿入される次のレコードの IdKey は 20 になります。
-
クエリ・キャッシュの作成、変更、削除は、トランザクションの操作ではありません。したがって、クエリ・キャッシュがトランザクション中に削除された場合、そのトランザクションはロールバックし、クエリ・キャッシュは削除されたままの状態で (リストアされずに) ロールバック・オペレーションになります。
-
トランザクション中に発生する DDL 操作やテーブル・チューニング操作は、一時ルーチンを作成したり実行する場合があります。この一時ルーチンは、クエリ・キャッシュと同様に処理されます。つまり、一時ルーチンの作成、コンパイル、および削除はトランザクションの一部として処理されません。一時ルーチンの実行は、トランザクションの一部であると見なされます。
ロールバックされるまたはロールバックされない非 SQL 項目については、ObjectScript の "TROLLBACK" コマンドを参照してください。
トランザクションでのロック
トランザクションは、ロックを使用して一意のデータ値を保護します。例えば、処理が一意のデータ値を削除した場合、この値はこのトランザクションの有効期間内はロックされます。したがって、最初のトランザクションが終了するまで、別の処理でこの同じ一意のデータ値を使用してレコードを挿入することはできません。これは、一意性制約を持つフィールドに結果的に重複値を持たせるロールバックを防ぎます。これらのロックは、INSERT 文、UPDATE 文、INSERT OR UPDATE 文、および DELETE 文に %NOLOCK 制限引数が含まれている場合を除き、これらの文によって自動的に適用されます。
トランザクション・サイズの制限
ジャーナル・ファイルに利用できるスペースのほかには、トランザクションで指定できる処理数に制限はありません。InterSystems IRIS には自動ロック・エスカレーションがあるため、通常、ロック・テーブルのサイズが制限を課すことはありません。
既定では、テーブルごとに 1000 ロックのロックしきい値があります。1 つのテーブルは、現在のトランザクションに対して 1,000 個の一意データ値ロックを持つことが可能です。1,001 番目のロック処理は、トランザクションの有効期間内はロック・テーブルに対してそのテーブル用のロックをエスカレートします。
このロックしきい値は、以下のいずれかの方法で変更できます。
-
$SYSTEM.SQL.Util.SetOption("LockThreshold")Opens in a new tab メソッドを呼び出します。このメソッドは、現在のシステム全体の値と構成ファイルの設定の両方を変更します。現在のロック制御のしきい値を確認するには、$SYSTEM.SQL.Util.GetOption("LockThreshold")Opens in a new tab メソッドを使用します。
-
管理ポータルに移動します。[システム管理] から、[構成]、[SQL およびオブジェクトの設定]、[SQL] の順に選択します。この画面で、[ロックしきい値] の現在の設定を表示および編集できます。
強制終了できるサブノード (子テーブル) の数に制限はありません。すべてのサブノードの強制終了はジャーナルに記録されるので、ロールバックできます。
コミットされていないデータの表示
クエリ発行のプロセスに対して SET TRANSACTION または START TRANSACTION を設定することで、読み取り分離レベルを指定できます。
-
ISOLATION LEVEL READ UNCOMMITTED: データに対するコミットされていない挿入、更新、および削除は、別のユーザによるクエリ (読み取りのみ) アクセスで表示できます。トランザクションが指定されていない場合は、これが既定になります。
-
ISOLATION LEVEL READ VERIFIED: データに対するコミットされていない挿入、更新、および削除は、別のユーザによるクエリ (読み取りのみ) アクセスで表示できます。クエリ条件で使用されるデータとクエリで表示されるデータを再チェックできます。
-
ISOLATION LEVEL READ COMMITTED: コミットされていない挿入および更新によるデータへの変更は、クエリ結果セットには表示されません。クエリ結果セットには、コミット済みの挿入および更新のみが含まれます。ただし、コミットされていない削除によるデータへの変更は、クエリ結果セットに表示されます。
SELECT コマンド節である、集約関数、DISTINCT 節、GROUP BY 節、または %NOLOCK キーワードを指定した SELECT は、現在の分離レベルに関係なく、コミットされていないデータを必ず返します。詳細は、"分離レベル" を参照してください。
ObjectScript トランザクション・コマンド
ObjectScript と SQL のトランザクション・コマンドは完全に互換性があり、置き換え可能ですが、以下の例外があります。
ObjectScript TSTART と SQL START TRANSACTION はどちらも、トランザクションが進行中でない場合にトランザクションを開始します。ただし、START TRANSACTION では、入れ子になったトランザクションはサポートされません。そのため、入れ子になったトランザクションが必要な場合 (または必要になる可能性がある場合) には、トランザクションを TSTART で始めることをお勧めします。SQL 標準との互換性が必要な場合は、START TRANSACTION を使用してください。
ObjectScript トランザクション処理は、入れ子になったトランザクションを限定的にサポートします。SQL トランザクション処理はトランザクション内のセーブポイントをサポートします。