INSERT

INSERT OR UPDATE

INSERT OR UPDATE 文は、INSERT 文のバリアントであり、挿入操作と更新操作の両方を実行します。この文は最初に、挿入操作を実行しようとします。UNIQUE KEY 違反が原因で挿入要求に失敗した場合 (いずれかの一意キーのフィールドについて、この挿入対象として指定された行と同じ値を持つ既存の行が存在する場合)、挿入要求はその行に対する更新要求に自動的に変化し、INSERT OR UPDATE が指定されたフィールド値を使用して既存の行を更新します。

テーブルと列の引数

table 引数を指定すると、テーブルに対して、直接挿入、ビュー経由での挿入、またはサブクエリによる挿入を実行できます。ビュー経由で挿入する場合は、"CREATE VIEW" で説明されているように、必要条件や制限事項に従います。以下は、table 引数の代わりにサブクエリを使用した INSERT の例です。

INSERT INTO (SELECT field1 AS ff1 FROM MyTable) (ff1) VALUES ('test')

サブクエリのターゲットは、ビューのクエリが更新可能であるかを判定する際に使用される条件と同じ条件に従って、更新可能であることが要求されます。更新可能でないビューまたはサブクエリを使用して INSERT を実行しようとすると、SQLCODE -35 エラーが生成されます。

table 引数でテーブル値関数や JOIN 構文を指定することはできません。

column リスト引数を省略すると、INSERT は、すべての列が列番号順で挿入されるものと判断します。列のリストを指定すると、個々の値は、列のリスト内の列の名前と位置的に一致する必要があります。

特権

テーブルに 1 つ以上のデータ行を挿入するには、そのテーブルに対するテーブルレベルの特権か列レベルの特権が必要です。

値の割り当て

レコードを挿入する際に、指定された列にさまざまな方法で値を割り当てることができます。未指定の列には、既定値が定義されている必要があります。

• SET キーワードを使用して、1 つ以上の column = scalar-expression のペアをコンマ区切りリストとして指定します。以下はその例です。

  SET StatusDate='05/12/06',Status='Purged'
  

  

• VALUES キーワードを使用して、列のリストとそれに対応するスカラ式のリストを指定します。以下はその例です。

  (StatusDate,Status) VALUES ('05/12/06','Purged')
  

  

  スカラ式の値を列のリストに割り当てる場合、指定する列ごとにスカラ式が必要です。

• 列リストを指定せずに VALUES キーワードを使用する場合、行の列の列順と暗黙的に対応するスカラ式のリストを指定します。以下はその例です。

  VALUES ('Fred Wang',65342,'22 Main St. Anytown MA','123-45-6789')
  

  

  値は列番号順に指定する必要があります。ユーザ指定の値を受け付けるベース・テーブルのすべての列に対して値を指定する必要があります。INSERT では、列の順序を使用する場合、定義されたフィールドの既定値を使用できません。RowID 列はユーザが指定できないので、この構文には含まれません。

• 列リストを指定せずに VALUES キーワードを使用する場合、行の列の列順と暗黙的に対応するスカラ式の動的なローカル配列を指定します。以下はその例です。

  VALUES :myarray()
  

  

  この値の割り当ては、埋め込み SQL からホスト変数を使用することでのみ実行できます。その他すべての値の割り当てと異なり、この使用方法では (実行時に配列を追加することで) 挿入する列の指定を実行時まで遅らせることができます。その他すべてのタイプの挿入では、挿入する列をコンパイル時に指定する必要があります。

  値は列番号順に指定する必要があります。ユーザ指定の値を受け付けるベース・テーブルのすべての列に対して値を指定する必要があります。INSERT では、列の順序を使用する場合、定義されたフィールドの既定値を使用できません。指定する配列値は array(2) から開始する必要があります。列 1 は RowID フィールドです。RowID フィールドに値を指定することはできません。詳細は、"Caché SQL の使用法" の “埋め込み SQL の使用法” の章の “添え字付き配列としてのホスト変数” を参照してください。

列名および対応するデータ値を指定する場合には、既定値が定義された列を省略できます。INSERT を指定すると、ストリーム・フィールドを含むほとんどのフィールド・データ型の既定値を挿入できます。

列名を指定しない場合、データ値の配列は定義された列リストに対応している必要があります。ユーザが指定できるベース・テーブルのすべての列に対して値を指定する必要があります。定義された既定値を使用することはできません (列の値に空文字列を指定することは当然できます)。

指定したテーブルのために定義されているすべての列名および列番号をリストするには、"Caché SQL の使用法" の “テーブルの定義” の章の "列の名前と番号" を参照してください。

SQLCODE エラー

既定では、INSERT は、全か無かのイベントです。行がすべて挿入されるか、まったく挿入されないかのいずれかです。Caché は、SQLCODE ステータス変数を返し、INSERT の成功もしくは失敗を示します。テーブルに行を挿入するには、以下に示すような、テーブル、フィールド名、およびフィールド値の要件をすべて満たしている必要があります。

テーブル ：

• テーブル名が存在している必要があります。存在しないテーブルに対して挿入を実行すると、SQLCODE -30 エラーが返されます。INSERT はコンパイル時にテーブルの存在を確認するため、単一のコンパイルされた SQL プログラム (埋め込み SQL プログラムなど) でテーブルを作成し (CREATE TABLE を使用)、そのテーブルに値を挿入することはできません。

• テーブルを READONLY として定義することはできません。ReadOnly テーブルを参照する INSERT をコンパイルしようとすると、SQLCODE -115 エラーが発生します。このエラーは実行時にのみ発生するのではなく、コンパイル時にも発生するようになったことに注意してください。"Caché オブジェクトの使用法" の "永続クラスのその他のオプション" の章で READONLY オブジェクトの説明を参照してください。

• ビュー経由でテーブルを更新する場合、ビューを WITH READ ONLY として定義することはできません。これを実行しようとすると、SQLCODE -35 エラーが返されます。詳細は、"CREATE VIEW" コマンドを参照してください。

フィールド名 ：

• フィールドが存在している必要があります。存在しないフィールドに対して挿入を実行すると、SQLCODE -29 エラーが返されます。指定したテーブルのために定義されているすべてのフィールド名をリストするには、"Caché SQL の使用法" の “テーブルの定義” の章の "列の名前と番号" を参照してください。

• すべての必須フィールドが挿入処理に指定されている必要があります。必須フィールドの値を指定せずに行を挿入すると、SQLCODE -108 エラーが返されます。

• 挿入に重複するフィールド名を含めることはできません。同じ名前の 2 つのフィールドを含む行を挿入しようとすると、SQLCODE -377 エラーが返されます。

• 挿入に READONLY として定義されているフィールドを含めることはできません。ReadOnly フィールドを参照する INSERT をコンパイルしようとすると、SQLCODE -138 エラーが発生します。このエラーは実行時にのみ発生するのではなく、コンパイル時にも発生するようになったことに注意してください。

フィールド値 ：

• 各フィールドの値がデータ型の妥当性検証に合格する必要があります。不適切なフィールド値をフィールドのデータ型に挿入しようとすると、SQLCODE -104 エラーが返されます。例えば、誤ったデータ型のデータ値や、定義された最大データ・サイズよりも大きなデータ値を挿入しようとした場合などです。また、ODBC やJDBC を介して無効な DOUBLE 数値が挿入される場合もあります。これは、指定されたデータ値のみに適用されます。フィールドの DEFAULT 値 (指定されている場合) はデータ型の妥当性検証に合格する必要はありません。

• NOT NULL として定義されたフィールドに NULL を指定することはできません。NULL を指定すると、必須フィールドを指定していないことを示す SQLCODE -108 エラーが返されます。

• フィールド値は一意制約に従う必要があります。一意制約のあるフィールドに重複フィールド値を挿入すると、SQLCODE -119 エラーが返されます。このエラーは、フィールドに UNIQUE データ制約がある場合、またはフィールドのグループに複数フィールドでの一意制約が適用されている場合に返されます。このエラーは、重複値を指定した場合や、値を指定しなかったためにフィールドの DEFAULT が再度使用され、重複値が指定された場合に発生します。SQLCODE -119 %msg 文字列には、一意制約に違反するフィールドおよび値が含まれています。例えば、“[テーブル 'SQLUser.MyKids'、制約 'MYKIDS_UNIQUE1'、フィールド KidName="Molly Bloom"：一意性チェックに失敗しました]” になります。

• 数値はキャノニック形式で挿入されますが、先頭と末尾の 0 や、先頭の複数の符号を付けて指定できます。ただし、SQL では、2 つの連続したマイナス記号は、1 行コメント文字として解析されます。したがって、先頭に 2 つの連続したマイナス記号を付けて数値を指定しようとすると、SQLCODE -12 エラーになります。

• 既定では、システム生成値が格納される RowID、IDKey、IDENTITY などのフィールドを、値の挿入対象に指定することはできません。既定では、これらのフィールドのいずれかに非 NULL フィールド値を挿入すると、SQLCODE -111 エラーが返されます。これらのフィールドのいずれかに NULL を挿入すると、Caché はその NULL をシステム生成値でオーバーライドします。したがって、挿入処理は成功し、エラー・コードは発行されません。

  データ型 ROWVERSION のフィールドを定義すると、行が挿入された場合、システムで生成されたカウンタ値がそれに自動的に割り当てられます。ROWVERSION フィールドに値を挿入すると、SQLCODE -138 エラーが返されます。

  ユーザが指定した値を受け取る IDENTITY フィールドを作成することができます。%SYSTEM.SQLOpens in a new tab クラスの SetIdentityInsert()Opens in a new tab メソッドを設定すると、IDENTITY フィールドの既定の制約をオーバーライドし、IDENTITY フィールドに一意の整数値を挿入できるようになります (GetIdentityInsert()Opens in a new tab メソッドを呼び出すことにより、この制約の現在の設定を返すことができます)。IDENTITY フィールドに値を挿入すると IDENTITY カウンタが変更され、後続のシステム生成値が、このユーザ指定の値からインクリメントを開始するようになります。IDENTITY フィールドに NULL を挿入すると、SQLCODE -108 エラーが発生します。

  IDKey データには次の制限があります。1 つのインデックスに複数の IDKey フィールドがある場合は、各フィールドの区切り文字列として “||” (二重の垂直バー) が使用されています。したがって、IDKey フィールドのデータにはこの文字列を使用できません。

• 挿入では、外部キーの参照整合性に違反している値を持つフィールドを扱うことはできません。この制限を回避するには、%NOCHECK 制限引数を指定するか、その外部キーの定義で NOCHECK キーワードを使用する必要があります。外部キーの参照整合性に違反して挿入を実行すると、SQLCODE -121 エラーが返されます。

• フィールドの値をサブクエリにすることはできません。サブクエリをフィールドの値に指定しようとすると、SQLCODE -144 エラーが返されます。

INSERT DEFAULT VALUES

フィールド値がすべてそのフィールドの既定値に設定されている行をテーブルに挿入できます。定義済みの既定値を持つフィールドは、その値に設定されます。定義された既定値がないフィールドは、NULL に設定されます。これには、以下のコマンドを使用します。

INSERT INTO Mytable DEFAULT VALUES

NOT NULL 制約を指定して定義され、DEFAULT が定義されていないフィールドの場合、この操作は SQLCODE -108 で失敗します。

UNIQUE 制約を指定して定義されているフィールドは、この文を使用して挿入できます。フィールドが DEFAULT 値なしで UNIQUE 制約を指定して定義されている場合は、繰り返し呼び出すことで、この UNIQUE フィールドが NULL に設定されている行が複数挿入されます。フィールドが UNIQUE 制約と DEFAULT 値を指定して定義されている場合、この文は 1 回しか使用できません。2 回目の呼び出しは、SQLCODE -119 で失敗します。

DEFAULT VALUES は、カウンタ・フィールドにシステム生成の整数値を持つ行を挿入します。これらには RowID が含まれるほか、オプションで IDENTITY フィールド、%Counter フィールド、および ROWVERSION フィールドが含まれます。

カウンタ値の挿入

テーブルでは、オプションで 1 つのフィールドを IDENTITY として定義できます。既定では、テーブルに行が挿入されると常に、このフィールドは自動的にインクリメントされるテーブル・カウンタから整数を受け取ります。既定では、挿入によってこのフィールドの値を指定することはできません。ただし、この既定は構成可能です。更新操作によって IDENTITY フィールドの値を変更することはできません。このカウンタは TRUNCATE TABLE 操作によってリセットされます。

テーブルでは、オプションで 1 つ以上のフィールドをデータ型 SERIAL (%Library.CounterOpens in a new tab) として定義できます。既定では、テーブルに行が挿入されると常に、このフィールドは自動的にインクリメントされるテーブル・カウンタから整数を受け取ります。ただし、ユーザは挿入中にこのフィールドの整数値を指定して、テーブル・カウンタの既定をオーバーライドできます。更新操作によって %Counter フィールドの値を変更することはできません。このカウンタは TRUNCATE TABLE 操作によってリセットされます。

テーブルでは、オプションで 1 つのフィールドをデータ型 ROWVERSION として定義できます。このフィールドが定義されている場合、挿入操作により、ネームスペース全体の RowVersion カウンタの整数がこのフィールドに自動的に挿入されます。更新操作により、この整数が、現在のネームスペース全体の RowVersion カウンタ値で自動的に更新されます。ROWVERSION フィールドには、ユーザ定義の値、計算値、または既定値を挿入することはできません。このカウンタはリセットできません。

計算値の挿入

COMPUTECODE を指定して定義されているフィールドは、そのフィールドが CALCULATED でない限り、INSERT 操作の一部として値を挿入できます。CALCULATED フィールドに値を指定した場合、またはこのフィールドにデフォルト値がある場合は、INSERT ではこの明示的な値が保存されます。それ以外の場合は、フィールド値が以下のように計算されます。

• COMPUTECODE：値は、計算されて INSERT 時に保存されます。UPDATE 時には変更されません。

• COMPUTEONCHANGE の COMPUTECODE：値は、計算されて INSERT 時に保存されます。UPDATE 時には再計算されて保存されます。

• DEFAULT と COMPUTEONCHANGE の COMPUTECODE：既定値は、INSERT 時に保存されます。値は、UPDATE 時に計算されて保存されます。

• CALCULATED または TRANSIENT が指定された COMPUTECODE：このフィールドには値が格納されないので、値の INSERT は実行できません。値は照会されるときに計算されます。ただし、計算済みフィールドに値を挿入しようとすると、Caché は指定された値に対する検証を実行し、値が無効であればエラーを発行します。値が有効である場合、Caché は挿入操作を実行せず、SQLCODE エラーを発行せず、ROWCOUNT をインクリメントします。

計算コードにプログラミング・エラーがある場合 (例えば、ゼロでの除算)、INSERT 操作は SQLCODE -415 エラーで失敗します。

DISPLAY から LOGICAL へのデータ変換

データは LOGICAL モード形式で格納されます。例えば、日付は日数を示す整数値として格納されます。LOGICAL モード形式でない入力データは、LOGICAL モード形式への変換が必要です。コンパイルされた SQL では、DISPLAY 形式または ODBC 形式から LOGICAL 形式への入力値の自動変換をサポートします。入力データの自動変換には、コンパイル時には SQL が RUNTIME モードを指定することと、実行時には SQL が LOGICAL モード環境で実行することの 2 つの要素が必要です。

• 埋め込み SQL では、#SQLCompile Select=runtime を指定すると、Caché により、入力値を表示形式から LOGICAL モード格納形式に変換するコードで SQL 文がコンパイルされます。Caché は、このモード変換を単一の値と値の配列の両方に対して実行します。詳細は、"Caché ObjectScript の使用法" の “ObjectScript マクロとマクロ・プリプロセッサ” の章にある "#SQLCompile Select" を参照してください。

• SQL の CREATE FUNCTION、CREATE METHOD、または CREATE PROCEDURE 文では、SELECTMODE RUNTIME を指定すると、Caché により、入力値を表示形式から LOGICAL モード格納形式に変換するコードで SQL 文がコンパイルされます。

入力データは、DISPLAY 形式 (6/17/2011 など)、ODBC 形式 (2011-06-17 など)、または LOGICAL 形式 (62259 など) のいずれかの形式となります。SQL 実行環境が LOGICAL モードの場合は、データは LOGICAL 形式で格納されます。これは、すべての Caché SQL 実行環境の既定のモードです。

以下のように、SQL 実行環境で、選択モードを LOGICAL に明示的に設定できます。

• ObjectScript プログラムで、またはターミナル・インタフェースから、$SYSTEM.SQL.SetSelectMode(0)Opens in a new tab メソッドを呼び出します。

• ダイナミック SQL で、%SelectMode 0Opens in a new tab を指定します。

• SQL シェルから、SET SELECTMODE LOGICAL を指定します。

• 管理ポータルから、システム, SQL, インタフェースを選択し、[表示モード] ドロップダウン・リストを使用して [論理モード] を指定します。

SERIAL データ型の INSERT フィールド

INSERT 操作では、SERIAL データ型のフィールドに以下のいずれかの値を指定でき、その結果は以下のようになります。

• 値なし、0 (ゼロ)、または非数値 ： Caché では、指定された値が無視され、その代わり、このフィールドの現在のシリアル・カウンタ値が 1 ずつインクリメントされ、インクリメント後の整数がフィールドに挿入されます。

• 正の整数値 ： Caché では、フィールドにユーザ指定の値が挿入され、このフィールドのシリアル・カウンタ値がこの整数値に変更されます。

したがって、SERIAL フィールドには、一連のインクリメンタル整数値が含まれます。これらの値は、連続または一意とは限りません。例えば、SERIAL フィールドでは、1、2、3、17、18、25、25、26、27 といった一連の値が有効です。連続する整数は、Caché で生成されるか、またはユーザが指定します。連続しない整数はユーザが指定します。SERIAL フィールドの値を一意にする場合には、そのフィールドに UNIQUE 制約を適用する必要があります。

制限引数

restriction 引数を使用するには、現在のネームスペースに対応する admin-privilege が必要となります。詳細は "GRANT" を参照してください。

restriction 引数を指定すると、以下のように処理を制限します。

• %NOCHECK — 外部キーの参照整合性チェックは実行されません。データ型、最大長、データ制約などの検証条件に関して、列データの検証も実行されません。ビュー経由で INSERT を実行する際、ビューの WITH CHECK OPTION 検証は実行されません。

  Note:

  %NOCHECK を使用すると、無効なデータが発生する場合があるため、この制約引数の使用は、信頼性の高いデータ・ソースから挿入または更新を一括で実行する場合に限定してください。

• %NOLOCK — INSERT 時に行をロックしません。単独のユーザ/処理がデータベースを更新する際にのみ使用します。

• %NOINDEX — インデックス・マップは INSERT 処理中には設定されません。

• %NOTRIGGER — ベース・テーブル・トリガは INSERT 処理中にはかかりません。

複数の restriction 引数を順不同で指定できます。複数の引数は、空白で区切られます。

参照整合性

%NOCHECK を指定しない場合、Caché ではシステム構成設定を使用して外部キーの参照整合性チェックを実行するかどうかが決まります。システムの既定値は以下のように設定できます。

• $SYSTEM.SQL.SetFilerRefIntegrity()Opens in a new tab メソッド呼び出し。

• 管理ポータルに進み、システム, 構成, 一般SQL設定 を選択します。[INSERT、UPDATE、DALETE の外部キーについて参照整合性チェックを実行する] の現在の設定を表示して編集します。既定は “はい” です。この設定を変更すると、変更後に開始される新しいプロセスは、新しい設定になります。

この設定は、NOCHECK キーワードを使用して定義した外部キーには適用されません。

INSERT 操作時に、すべての外部キー参照について、参照されるテーブルの該当する行に対する共有ロックが取得されます。この行は参照整合性チェックと行の挿入が完了するまでロックされます。その後、ロックは解除されます (トランザクションの終了までロック状態が継続することはありません)。このロックによって、参照整合性チェックから挿入操作の完了までの間、参照先の行が変更されないようになります。

ただし、%NOLOCK restriction 引数を指定した場合は、指定したテーブルにも、参照先のテーブル内にある対応する外部キー行にも、ロックは実行されません。

子テーブルの挿入

子テーブルへの INSERT 操作時には、親テーブル内の対応する行に共有ロックがかかります。この行は、子テーブル行の挿入中はロックされます。その後、ロックは解除されます (トランザクションの終了までロック状態が継続することはありません)。これにより、参照される親の行がこの挿入操作の間に変更されることがなくなります。

リスト構造

Caché は、リスト構造のデータ型 %List (データ型クラス %Library.List) をサポートしています。これは圧縮バイナリ形式であり、Caché SQL で対応するネイティブなデータ型にマップしません。また、データ型 VARBINARY に対応しており、その MAXLEN の既定値は 32749 です。このため、ダイナミック SQL では、型 %List のプロパティ値を設定するときに、INSERT も UPDATE も使用できません。詳細は、このドキュメントのリファレンスの "データ型" を参照してください。

ストリーム・データ

単一の INSERT を使用して、ストリーム値を含む複数の行を挿入することはできません。ストリーム・データを含む行は、一度に 1 行ずつ挿入する必要があります。

ストリーム・フィールドには以下のタイプのデータ値を挿入できます。

• ストリーム・オブジェクトへのオブジェクト参照 (oref)。Caché は、このオブジェクトを開いて、そのコンテンツを新しいストリーム・フィールドにコピーします。例えば以下のようになります。

      set oref=##class(%Stream.GlobalCharacter).%New()
      do oref.Write("Technique 1")
  
      //do the insert; use an actual oref
      &sql(INSERT INTO MyStreamTable (MyStreamField) VALUES (:oref))
  

  

• ストリームの文字列版 oref。以下はその例です。

      set oref=##class(%Stream.GlobalCharacter).%New()
      do oref.Write("Technique 2")
  
      //next line converts oref to a string oref
      set string=oref_""
  
      //do the insert
      &sql(INSERT INTO MyStreamTable (MyStreamField) VALUES (:string))
  

  

• 1 や -1 などの数値。

• 最初の文字が数値ではない文字列リテラル。以下はその例です。

      set literal="Technique 3"
  
      //do the insert; use a string
      &sql(INSERT INTO MyStreamTable (MyStreamField) VALUES (:literal))
  

  

  最初の文字が数値の場合、SQL はリテラルを文字列形式の oref と解釈します。例えば、値 2@User.MyClass は文字列リテラルではなく、文字列版の oref と見なされます。

適切に定義されていないストリーム値を挿入すると、SQLCODE -412 エラー ： 一般的なストリーム・エラーですが返されます。

Microsoft Access

INSERT を使用して、Microsoft Access を使用する Caché テーブルにデータを追加するには、テーブルの RowID をプライベートとしてマークするか、1 つ以上の追加のフィールドに一意のインデックスを定義します。

INSERT クエリ結果

SELECT 文との組み合わせにより、1 つの INSERT を使用して複数の行をテーブルに挿入することができます。SELECT が 1 つのテーブルの複数の行から列データを抽出し、INSERT がこの列データを格納した新しい対応する行を別のテーブル内に作成します。ただし、データにストリーム値が含まれる場合、この方法を使用して複数の行を挿入することはできません。

挿入される行の数を制限するには、SELECT 文内で TOP 節を指定します。また、SELECT 文で ORDER BY 節を使用して、TOP 節で選択する行を指定することもできます。

SELECT で INSERT を使用すると、%ROWCOUNT 変数は挿入した行の数に設定されます (0 または正の整数)。

以下の例では、2 つの埋め込み SQL プログラムを使用して INSERT のこの使用方法を示します。最初の例では、CREATE TABLE を使用して、新しいテーブル SQLUser.MyStudents を作成しています。2 番目の例では、Sample.Person から抽出されたデータをこのテーブルに生成しています (また、$SYSTEM.SQL.QueryToTable()Opens in a new tab メソッドを使用することで、単一の処理で既存のテーブル定義から新しいテーブルを作成し、その既存テーブルからデータを挿入することができます)。

この動作を実際に示すために、1 つ目の埋め込み SQL プログラムを実行してから、2 つ目を実行してください。(埋め込み SQL では参照されるテーブルが既に存在していなければ INSERT 文をコンパイルすることができないため、ここでは 2 つの埋め込み SQL プログラムを使用する必要があります)。

以下のプログラムでは、2 つの保存済みデータ・フィールドと 1 つの計算フィールドがある MyStudents テーブルを作成します。

   ZNSPACE "Samples"
   WRITE !,"Creating table"
  &sql(CREATE TABLE SQLUser.MyStudents (
    StudentName VARCHAR(32),
    StudentDOB DATE,
    StudentAge INTEGER COMPUTECODE {SET {StudentAge}=
                       $PIECE(($PIECE($H,",",1)-{StudentDOB})/365,".",1)}
                       CALCULATED )
    )
  IF SQLCODE=0 {
    WRITE !,"Created table, SQLCODE=",SQLCODE }
  ELSEIF SQLCODE=-201 {
    WRITE !,"Table already exists, SQLCODE=",SQLCODE }

以下のプログラムでは、INSERT を使用して、クエリ結果を MyStudents テーブルに生成します。StudentAge フィールドは計算されるため、このフィールドに値を指定することはできません。その値は、MyStudents テーブルが照会されるたびに計算されます。

  ZNSPACE "Samples"
  WRITE !,"Populating table with data"
  NEW SQLCODE,%ROWCOUNT,%ROWID
  &sql(INSERT INTO SQLUser.MyStudents (StudentName,StudentDOB)
     SELECT Name,DOB
     FROM Sample.Person WHERE Age <= '21')
  IF SQLCODE=0 {
    WRITE !,"Number of records inserted=",%ROWCOUNT
    WRITE !,"Row ID of last record inserted=",%ROWID }
  ELSE {
    WRITE !,"Insert failed, SQLCODE=",SQLCODE }

この INSERT プログラムを複数回実行しても成功しますが、一般に、望ましくない結果が起こるということを覚えておいてください。プログラムを実行するたびに、Name フィールドと DOB フィールドの同一値のレコード・セット (%ROWCOUNT) がもう 1 つ SQLUser.MyStudents に生成され、自動的に各レコードに一意の行 ID (%ROWID) が割り当てられます。

データを表示するには、管理ポータルで SAMPLES ネームスペースの [グローバル] オプションを選択します。"SQLUser.MyStudentsD" までスクロールし、[データ] オプションをクリックします。

以下のプログラムでは、MyStudents テーブルのデータを表示してから、このテーブルを削除します。

SELECT * FROM SQLUser.MyStudents ORDER BY StudentAge

  &sql(DROP TABLE SQLUser.MyStudents)
  IF SQLCODE=0 {WRITE !,"Table deleted" }
  ELSE {WRITE !,"SQLCODE=",SQLCODE," ",%msg }

既定では、INSERT クエリ結果の操作はアトミック処理です。指定された行がすべてテーブルに挿入されるか、1 行も挿入されないかのいずれかです。例えば、指定された行のいずれかの挿入が外部キーの参照整合性に違反する場合、INSERT は失敗し、1 行も挿入されません。この既定は、以下のように変更可能です。

アトミック性

既定では、INSERT、UPDATE、DELETE、および TRUNCATE TABLE はアトミック処理として実行されます。INSERT は、正常に完了するか、すべての操作がロールバックされるかのいずれかです。指定した行のいずれかを挿入できない場合、指定した行は 1 行も挿入できずにデータベースは INSERT を発行する前の状態に戻ります。

現在のプロセスに対するこの既定は、SET TRANSACTION %COMMITMODE を呼び出すことによって SQL 内で変更できます。現在のプロセスに対するこの既定は、SetAutoCommit()Opens in a new tab メソッドを呼び出すことによって ObjectScript 内で変更できます。以下のオプションを使用できます。

• IMPLICIT または 1 (自動コミットがオン) — 上記のように、これが既定の動作です。INSERT ごとに個別のトランザクションが構成されます。

• EXPLICIT または 2 (自動コミットがオフ) — 進行中のトランザクションがない場合は、INSERT コマンドによってトランザクションは自動的に開始されます。ただし、COMMIT または ROLLBACK で明示的にトランザクションを終了する必要があります。EXPLICIT モードでは、トランザクションあたりのデータベース操作の数は、ユーザ定義です。

• NONE または 0 (トランザクションなしを自動化しない) — INSERT を呼び出してもトランザクションは開始されません。INSERT 操作の失敗により、指定された行の一部が挿入されたり挿入されなかったりすることで、データベースが整合性のない状態になる可能性があります。このモードでトランザクションのサポートを提供するには、START TRANSACTION を使用してトランザクションを開始し、COMMIT または ROLLBACK を使用してトランザクションを終了する必要があります。

現在のプロセスのアトミック性設定を確認するには、以下の ObjectScript の例のように、GetAutoCommit()Opens in a new tab メソッドを使用します。

  DO $SYSTEM.SQL.SetAutoCommit($RANDOM(3))
  SET x=$SYSTEM.SQL.GetAutoCommit()
  IF x=1 {
    WRITE "Default atomicity behavior",!
    WRITE "automatic commit or rollback" }
  ELSEIF x=0 {
    WRITE "No transaction initiated, no atomicity:",!
    WRITE "failed DELETE can leave database inconsistent",!
    WRITE "rollback is not supported" }
  ELSE { WRITE "Explicit commit or rollback required" }

トランザクションでのロック

%NOLOCK を指定しない場合、INSERT、UPDATE、および DELETE 操作時に自動的にレコードに標準のロックがかかります。影響を受ける各レコード (行) は、現在のトランザクションが継続している間はロックされます。

既定のロックしきい値は、テーブルごとに 1000 ロックです。つまり、トランザクションの間にテーブルから 1000 件を超えるレコードを挿入すると、ロックのしきい値に到達し、Caché は自動的にロック・レベルをレコード・ロックからテーブル・ロックに上げます。これによってトランザクション時に、ロック・テーブルをオーバーフローすることなく、大規模な挿入を実行できます。

Caché は、以下の 2 つのロック・エスカレーション策のどちらかを適用します。

• “E” タイプのロック・エスカレーション：Caché は、次のことに該当する場合にこのタイプのロック・エスカレーションを使用します。(1) クラスで %CacheStorage が使用されている (これは、管理ポータルの SQL スキーマ表示で [カタログの詳細] から判断できます)。(2) クラスで、IDKey インデックスが指定されていないか、単一プロパティの IDKey インデックスが指定されている。“E” タイプのロック・エスカレーションについては、"Caché ObjectScript リファレンス" の LOCK コマンドで説明されています。

• 従来の SQL ロック・エスカレーション：クラスで “E” タイプのロック・エスカレーションが使用されない理由は、マルチプロパティの IDKey インデックスの存在にあると考えられます。この場合は、%Save ごとにロック・カウンタがインクリメントされます。つまり、トランザクション内の単一オブジェクトを 1001 回保存を行うと、Caché はロックのエスカレーションを試みます。

どちらのロック・エスカレーション策の場合も、$SYSTEM.SQL.GetLockThreshold()Opens in a new tab メソッドを使用して、現在のシステム全体用ロックしきい値を決定できます。既定は 1000 です。このシステム全体用ロックしきい値は構成可能です。

• $SYSTEM.SQL.SetLockThreshold()Opens in a new tab メソッドを使用します。

• 管理ポータルを使用する。システム, 構成, 一般SQL設定 に移動します。[ロックしきい値] の現在の設定を表示して編集します。既定は 1000 ロックです。この設定を変更すると、変更後に開始される新しいプロセスは、新しい設定になります。

ロックしきい値を変更するには、%Admin Manage Resource の USE 許可が必要です。Caché は、ロックしきい値の変更を現在のプロセスすべてに即座に適用します。

結果として、自動ロック・エスカレーションでは、デッドロックの状況が起こる可能性があります。つまり、テーブル・ロックへのエスカレーションを試みたときに、テーブル内のレコード・ロックを保持する別プロセスとの競合が起こる可能性があります。これを避けるための方策としては、次のいくつかが考えられます。 (1) ロック・エスカレーションがトランザクション内で起こる可能性が低くなるように、ロック・エスカレーションのしきい値を上げる。(2) ロック・エスカレーションが即座に起こるように、ロック・エスカレーションのしきい値を大幅に下げる。これにより、別プロセスが同一テーブル内のレコードをロックする機会が少なくなります。(3) トランザクションが継続している間はテーブル・ロックを適用し、レコード・ロックは実行しない。これは、LOCK TABLE、UNLOCK TABLE (テーブル・ロックがトランザクションの終了まで持続するよう、IMMEDIATE キーワードはなし) の順に指定することで、トランザクション開始時に実行できます。その後、%NOLOCK オプションを使用して挿入を実行します。

自動ロック・エスカレーションは、ロック・テーブルのオーバーフローを防ぐことを目的としています。ただし、大量の挿入などを実行したために <LOCKTABLEFULL> エラーが発生した場合は、INSERT によって SQLCODE -110 エラーが発行されます。

トランザクションでのロックの詳細は、"Caché SQL の使用法" の “データベースの変更” の章にある "トランザクション処理" を参照してください。

行レベル・セキュリティ

Caché の行レベル・セキュリティにより、行セキュリティの定義で行への次回のアクセスが許可されていない場合でも、INSERT を使用して行を追加できるようになります。INSERT により行への今後の SELECT アクセスが妨げられないようにするには、WITH CHECK OPTION を持つビューで INSERT を実行することをお勧めします。詳細は、"CREATE VIEW" を参照してください。