16.20 - 定位置カーソル - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL ストアド プロシージャおよび埋め込みSQL

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
プログラミング リファレンス
featnum
B035-1148-162K-JPN

ANSI/ISO SQL規格では、更新可能(定位置)カーソルを定義しています。これは、アプリケーションがクエリーのためのカーソルを定義することができ、その同じカーソルを使用して結果行を更新できることを意味します。

ストアド プロシージャから行を更新または削除するための定位置カーソルの使用

ストアド プロシージャでFORループ カーソルを使用して行を更新または削除するための一般的な処理フローは、次のとおりです。
  1. 適切なカーソル指定でFOR文を指定する。
  2. FOR文の各繰返しで1つの行を取り出す。

    すると、カーソルは応答セット内の次の行をポイントします。

  3. UPDATE文、またはDELETE文とともにWHERE CURRENT OF句を使用して、取り出した行を更新または削除する。
  4. 最終行が取り出されるまでFORの繰返しループを続行する。
  5. FOR文を終了してカーソルをクローズする。
ストアド プロシージャでDECLARE CURSORカーソルを使用して行を更新または削除するための一般的な処理フローは、次のとおりです。
  1. 適切なカーソル指定でDECLARE CURSOR文を指定する。
  2. OPEN cursor_name文でカーソルを開く。
  3. FETCH文を実行して、応答セットから1度に1行ずつ取り出す。

    次のカーソル移動は、指定するスクロール可能性オプションによって異なります。

    オプション 次のカーソル移動
    FIRST 結果セット内の最初の行
    NEXT 結果セット内の次の行
  4. UPDATE文、またはDELETE文とともにWHERE CURRENT OF句を使用して、取り出した行を更新または削除する。
  5. CLOSE cursor_name文を実行してカーソルを閉じる。

Preprocessor2で行を更新するためのカーソルの使用

  1. SELECT文のカーソルを宣言する。
  2. OPEN文を使用してカーソルをオープンする。
  3. FETCH文を使用して行を取り出す。
  4. UPDATE文、またはDELETE文とともにWHERE CURRENT OF句を使用して、取り出した行を更新または削除する。
  5. CLOSE文を使用してカーソルをクローズする。

Preprocessor2のSELECT AND CONSUME文用の定位置カーソル

定位置カーソルでのSELECT AND CONSUME文は無効です。

SELECT AND CONSUME文を使用してキュー テーブルから行を選択するとき、システムは自動的にその行を消費します。したがって、いったん選択され消費された行は削除または更新できないため、定位置カーソルでSELECT AND CONSUME文を実行しても意味がありません。つまり、定位置カーソルから実行されるすべてのSELECT AND CONSUME文は失敗します(たとえ行を削除または更新しない場合でも)。

TRANSACTまたは-trプリプロセッサ宣言をANSIに設定してコードをプリコンパイルするとき、Preprocessor2アプリケーションでコード作成されるすべてのカーソルはデフォルトで定位置カーソルになります。つまり、ANSI形式の埋め込みSQLアプリケーションではカーソルからSELECT AND CONSUME文を実行できません。

定位置カーソルをサポートする機能

ANSI/ISO SQL:2011規格の定位置カーソルの機能を有効にする機能がいくつか提供されています。これには以下のものが含まれます。
  • DELETEおよびUPDATE文のWHERE CURRENT OF句
  • LOCKINGリクエスト修飾子のFOR CHECKSUM句(埋め込みSQLのみ)
  • SELECT文のFOR UPDATE句(ストアド プロシージャのみ)

WHERE CURRENT OF句

定位置カーソルを宣言すると、WHERE CURRENT OF句で、そのカーソルが指す行に対するDELETEおよびUPDATE文が実行できるようになります。

例えば、以下のDELETE文は、現在の顧客行をx01というカーソルから削除します。

EXEC SQL
DELETE FROM customer
WHERE CURRENT OF x01;

FOR CHECKSUM句

定位置カーソルは、リソース ロック レベルを認識しません。代わりにそれは、カーソルが関係するすべてのアクションは単一トランザクション内で行なわれ、トランザクションの終了がオープンされたカーソルをクローズすると仮定します。

ANSI/ISO SQL:2011規格へのTeradata拡張機能である、LOCKINGリクエスト修飾子のFOR CHECKSUM句が、この機能に追加されています。

LOCKINGリクエスト修飾子を指定していないと、すべてのSELECT文でREADレベルのロックが使用されます。定位置更新および削除は両方とも、デフォルトのWRITE重大度行ハッシュ ロックになります。

FOR CHECKSUM句は、ストアド プロシージャではサポートされません。

CHECKSUMロックの動作方法

CHECKSUMロックは、ACCESSロックと似ていますが、カーソルを通して更新が行なわれているときに別のユーザーかセッションによってカーソル内の行が変更されているかどうかをテストできるように、結果テーブルの行にチェックサムを追加します。

アプリケーションがACCESSロックを指定して、それからカーソルUPDATEまたはDELETEを発行する場合、変更対象の行は、最初のアプリケーションが行を読み取り、それがカーソルUPDATEまたはDELETE文を発行するまでの間に、別のアプリケーションによって変更されてしまっている可能性があります。

行が現在のアプリケーションによって最後に読み取られてから、別のアプリケーションがその行を更新したためにチェックサムが変わる場合、現在のアプリケーションはエラーを受け取ります。

次に示すCHECKSUMロックの要件がいずれも満たされていない場合、システムは必ずアプリケーションにエラーを返します。
  • ロックされたオブジェクトはテーブルでなければならない。
  • LOCKINGリクエスト修飾子の後に、定位置カーソルSELECTが続かなければならない。
  • LOCKINGリクエスト修飾子で指定したテーブルは、それに続くSELECT文のFROM句で参照されるテーブルと同じでなければならない。

CHECKSUMロックは、定位置カーソルによって開かれるSELECT文とともに使用される場合のみ有効です。

例: CHECKSUM付きのLOCKING

この例では、tと名付けられたテーブルにCHECKSUMロックを使用しています。

LOCKING TABLE t
FOR CHECKSUM
SELECT i, text
FROM t;

定位置カーソルの使用ルール

定位置のUPDATEおよびDELETEは、それらが使用するカーソルを開いたSELECTと同じトランザクション内にあることが必要です。

以下の項目は更新できません。
  • 動的カーソル
  • 複文リクエスト
以下の項目は、定位置カーソルによって制御されるSQL文では許可されていません。
  • トリガーが定義されているテーブル
  • 複数の実テーブル間の結合
  • DISTINCTキーワード
  • GROUP BY句
  • HAVING句
  • WITH句
  • ORDER BY句
  • 集約演算子
  • 集合演算子
  • 相関サブクエリー
  • 以下のいずれかがある選択リスト: 重複列名、式、または関数

ストアド プロシージャでは、DECLARE CURSOR文にFOR UPDATE句を指定すると、定位置カーソルを定義できます。FOR UPDATE句を指定しないと、システムはカーソルが更新不可能であるという警告を返します。

現在取り出している行の複数のUPDATE、または現在取り出している行のDELETEが後続する複数のUPDATEは、許可されています。

定位置の更新または削除は、カーソルを定義したSELECT文と同じトランザクション内になければなりません。

アプリケーションが、無効なSELECTによって選択された行に対して、定位置のUPDATEまたはDELETEを試行すると、システムはエラーを返し、影響を受けたトランザクションをロールバックします。

プログラムが、WHERE CURRENT OF句を使用して非定位置カーソルに対する行のUPDATEまたはDELETEを試行すると、システムはカーソルが更新可能でないことを通知するエラーを返します。

次の表では、定位置カーソルが有効かどうかを、それが作成されたセッション モードを基準に説明しています。
セッション モード 有効性
ANSI 有効
Teradata 無効

定位置カーソルのパフォーマンス最適化の指針

行ハッシュ ロック

小規模なデータの集合には定位置更新および削除を使用する必要がありますが、それは単に、大規模なデータの集合に対してカーソル更新を使用する必要がある行ハッシュ ロックの数が理由です。課せられる行ハッシュ ロックが多すぎる場合、トランザクションは失敗し、ロック テーブル オーバーフロー エラーでアボートします。

定位置カーソルを使用する場合には長時間トランザクションを避けるか、またはCHECKSUMロックを使用して、自分のアプリケーションが使用するテーブルを他のアプリケーションが読み取りまたは更新不可にする可能性があるロック対立を避けます。CHECKSUMロックは、ストアド プロシージャではサポートされていないことに注意してください。

行ハッシュ ロックの数が多くなりすぎ、ロック テーブル オーバーフロー エラーが起きる場合、Teradata Databaseはエラーを受け取るSQLアプリケーションに対してトランザクション レベル アボートを発行します。

カーソル対立

カーソル対立は、単一トランザクション内でも起きる可能性があります。このような対立は、システムが次のようにカーソルを開いたときに発生します。
  • システムが、あるトランザクション内で同時に同じテーブルに対するカーソルを開き、そのカーソルの1つが、もう1つのカーソルによって定位置更新または削除リクエストの対象に現在なっているテーブル内の行に対して、定位置更新または削除を試行する場合。
  • システムがあるテーブルに対するカーソルを開き、そのテーブルに対して検索更新または検索削除を実行してから、カーソルがそのテーブルに対して定位置更新または削除の実行を試行する場合。
  • システムがあるテーブルに対するカーソルを開き、そのカーソルを介した更新または削除を実行してから、同じテーブルに対して検索更新または検索削除の実行を試行する場合。

これら3つの状況で、システムはカーソル対立の警告を返しますが、要求された削除または更新は実行されます。

関連トピック

以下に関する詳細な情報
  • SELECT AND CONSUMEについては、<Teradata Vantage™ SQLデータ定義言語の構文規則および例、B035-1144>と<Teradata Vantage™ SQLデータ操作言語、B035-1146>を参照してください。
  • カーソルとPreprocessor2については、カーソルルールおよび<Teradata® Preprocessor2埋め込みSQLプログラマ ガイド、B035-2446>を参照してください。
  • 埋め込みSQLクライアント アプリケーションに定位置カーソルを実装する例については、<Teradata® Preprocessor2埋め込みSQLプログラマ ガイド、B035-2446>を参照してください。
  • ストアド プロシージャで配置されたカーソルの実装例については、FORを参照してください。