Db2 10.5 for Linux, UNIX, and Windows
コンパウンド SQL (コンパイル済み) ステートメント
コンパウンド SQL (コンパイル済み) ステートメントには、SQL 制御ステートメントおよび SQL ステートメントを含めることができます。コンパウンド SQL (コンパイル済み) ステートメントを使用すると、変数、条件、カーソル、およびハンドラーに関してローカルな有効範囲を指定して実行する一連の SQL ステートメントにより、プロシージャー・ロジックを実装できます。
呼び出し
このステートメントはトリガー、SQL 関数、または SQL プロシージャーに組み込んだり、動的 SQL ステートメントを使用して発行したりすることができます。 このステートメントは、動的に作成できる実行可能ステートメントです。
許可
select-statement を使用する cursor-value-constructor を指定している SQL-variable-declaration の場合、ステートメントの許可 ID が select-statement の実行に必要な特権を持つ必要があります。『SQL 照会』の許可セクションを参照してください。
ステートメントの許可 ID によって保持される特権には、コンパウンド・ステートメントに指定されている SQL ステートメントを呼び出すために必要なすべての特権も含まれていなければなりません。
コンパウンド・ステートメントの本体内で指定された SQL オブジェクトについては、PUBLIC グループ特権のみが考慮されます。
構文
.-NOT ATOMIC-. >>-+--------+--BEGIN--+------------+----------------------------> '-label:-' '-ATOMIC-----' >--+-----------------------------+------------------------------> | .-------------------------. | | V | | '---| type-declaration |--;-+-' >--+-----------------------------------------+------------------> | .-------------------------------------. | | V | | '---+-| SQL-variable-declaration |-+--;-+-' +-| condition-declaration |----+ '-| return-codes-declaration |-' >--+----------------------------------+-------------------------> | .------------------------------. | | V | | '---| statement-declaration |--;-+-' >--+---------------------------------+--------------------------> | .-----------------------------. | | V | | '---DECLARE-CURSOR-statement--;-+-' >--+----------------------------------+-------------------------> | .------------------------------. | | V | | '---| procedure-declaration |--;-+-' >--+--------------------------------+---------------------------> | .----------------------------. | | V | | '---| handler-declaration |--;-+-' >--+------------------------------------+--END--+-------+------>< | .--------------------------------. | '-label-' | V | | '---| SQL-procedure-statement |--;-+-' type-declaration |--DECLARE--+-TYPE--type-name-+--AS--+-| array-type-definition |----+--| '-type-name--TYPE-' +-| distinct-type-definition |-+ '-| row-type-definition |------' array-type-definition .-2147483647-------. |--| data-type1 |--ARRAY--[--+------------------+--]------------| +-integer-constant-+ '-| data-type2 |---' data-type1 |--+-| built-in-type |------+-----------------------------------| +-| anchored-data-type |-+ '-row-type-name----------' built-in-type |--+-+-SMALLINT----+----------------------------------------------------------------------+--| | +-+-INTEGER-+-+ | | | '-INT-----' | | | '-BIGINT------' | | .-(5,0)-------------------. | +-+-+-DECIMAL-+-+--+-------------------------+-----------------------------------------+ | | '-DEC-----' | | .-,0-------. | | | '-+-NUMERIC-+-' '-(integer-+----------+-)-' | | '-NUM-----' '-,integer-' | | .-(53)------. | +-+-FLOAT--+-----------+--+------------------------------------------------------------+ | | '-(integer)-' | | | +-REAL------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+-' | | .-(34)-. | +-DECFLOAT--+------+-------------------------------------------------------------------+ | '-(16)-' | | .-(1)------------------------. | +-+-+-+-CHARACTER-+--+----------------------------+----------+--+------------------+-+-+ | | | '-CHAR------' '-(integer-+-------------+-)-' | | (1) | | | | | | +-OCTETS------+ | '-FOR BIT DATA-----' | | | | | '-CODEUNITS32-' | | | | | '-+-VARCHAR----------------+--(integer-+-------------+-)-' | | | | '-+-CHARACTER-+--VARYING-' +-OCTETS------+ | | | | '-CHAR------' '-CODEUNITS32-' | | | | .-(1M)-----------------------------. | | | '-+-CLOB------------------------+--+----------------------------------+------------' | | '-+-CHARACTER-+--LARGE OBJECT-' '-(integer-+---+-+-------------+-)-' | | '-CHAR------' +-K-+ +-OCTETS------+ | | +-M-+ '-CODEUNITS32-' | | '-G-' | | .-(1)------------------------. | +-+-GRAPHIC--+----------------------------+------+-------------------------------------+ | | '-(integer-+-------------+-)-' | | | | +-CODEUNITS16-+ | | | | '-CODEUNITS32-' | | | +-VARGRAPHIC--(integer-+-------------+-)-------+ | | | +-CODEUNITS16-+ | | | | '-CODEUNITS32-' | | | | .-(1M)-----------------------------. | | | '-DBCLOB--+----------------------------------+-' | | '-(integer-+---+-+-------------+-)-' | | +-K-+ +-CODEUNITS16-+ | | +-M-+ '-CODEUNITS32-' | | '-G-' | | .-(1M)-------------. | +-+-BLOB----------------+--+------------------+----------------------------------------+ | '-BINARY LARGE OBJECT-' '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------------+-----------------------------------------------------+ | +-TIME-------------------------+ | | | .-(--6--)-------. | | | '-TIMESTAMP--+---------------+-' | | '-(--integer--)-' | +-XML----------------------------------------------------------------------------------+ +-BOOLEAN------------------------------------------------------------------------------+ '-CURSOR-------------------------------------------------------------------------------' anchored-data-type .-DATA TYPE-. .-TO-. |--ANCHOR--+-----------+--+----+--+-variable-name-------------------------+--| +-table-name.column-name----------------+ | .-OF-. | '-ROW--+----+--+-table-name-----------+-' +-view-name------------+ '-cursor-variable-name-' data-type2 |--+-+-INTEGER-+-----------------------------------+------------| | '-INT-----' | +---+-VARCHAR----------------+----(--integer--)-+ | '-+-CHARACTER-+--VARYING-' | | '-CHAR------' | '-| anchored-non-row-data-type |----------------' anchored-non-row-data-type .-DATA TYPE-. .-TO-. |--ANCHOR--+-----------+--+----+--+-variable-name----------+----| '-table-name.column-name-' distinct-type-definition |--| source-data-type |--WITH WEAK TYPE RULES--+----------+-----> '-NOT NULL-' >--+----------------------------------+-------------------------| '-CHECK--(--| check-condition |--)-' source-data-type |--+-| built-in-type |--------------+---------------------------| '-| anchored-non-row-data-type |-' row-type-definition .-,--------------------------. V | |--ROW--+-(----field-name--| data-type3 |-+--)-+----------------| '-| anchored-row-data-type |-----------' data-type3 |--+-| built-in-type |--------------+---------------------------| +-| anchored-non-row-data-type |-+ '-distinct-type-name-------------' anchored-row-data-type .-DATA TYPE-. .-TO-. |--ANCHOR--+-----------+--+----+--+-variable-name-------------------------+--| | .-OF-. | '-ROW--+----+--+-table-name-----------+-' +-view-name------------+ '-cursor-variable-name-' SQL-variable-declaration .-,-----------------. V | |--DECLARE----SQL-variable-name-+-------------------------------> .-DEFAULT NULL-----------------------------------------. (2) >--+-| data-type4 |--+------------------------------------------------------+-----+--| | +-CONSTANT NULL----------------------------------------+ | | '-+-DEFAULT--+--+-constant---------------------------+-' | | '-CONSTANT-' '-(--| cursor-value-constructor |--)-' | '-RESULT_SET_LOCATOR--VARYING--------------------------------------------------' data-type4 |--+-| built-in-type |------+-----------------------------------| +-| anchored-data-type |-+ | (3) | +-array-type-name--------+ +-cursor-type-name-------+ +-distinct-type-name-----+ | (4) | '-row-type-name----------' cursor-value-constructor |--CURSOR--+--------------------------------------------+--| holdability |--FOR--+-select-statement---+--| | .-,--------------------------------. | | (5) | | V | | '-statement-name-----' '-(----| cursor-parameter-declaration |-+--)-' cursor-parameter-declaration |--parameter-name--| data-type5 |-------------------------------| data-type5 |--+-built-in-type------------------+---------------------------| +-| anchored-non-row-data-type |-+ '-distinct-type-name-------------' holdability .-WITHOUT HOLD-. |--+--------------+---------------------------------------------| '-WITH HOLD----' condition-declaration |--DECLARE--condition-name--CONDITION--+-----------------------------------------------+--| | .-VALUE-. | | .-SQLSTATE--+-------+-. | '-FOR--+---------------------+--string-constant-' statement-declaration .-,--------------. V | |--DECLARE----statement-name-+--STATEMENT-----------------------| return-codes-declaration |--DECLARE------------------------------------------------------> .-DEFAULT '00000'----------. >--+-SQLSTATE--+-CHARACTER(5)-+--+--------------------------+-+--| | '-CHAR(5)------' '-DEFAULT--string-constant-' | | .-DEFAULT 0-----------------. | '-SQLCODE--+-INTEGER-+--+---------------------------+------' '-INT-----' '-DEFAULT--integer-constant-' procedure-declaration |--DECLARE--+-PROCEDURE--procedure-name-+-----------------------> '-procedure-name--PROCEDURE-' >--(--+---------------------------+--)--| SQL-procedure-body |--| | .-,---------------------. | | V | | '---parameter-declaration-+-' SQL-procedure-body |--SQL-procedure-statement--------------------------------------| handler-declaration |--DECLARE--+-CONTINUE-+--HANDLER--FOR--------------------------> +-EXIT-----+ '-UNDO-----' >--+-| specific-condition-value |-+--| SQL-procedure-statement |--| '-| general-condition-value |--' specific-condition-value .-,----------------------------------------. V .-VALUE-. | |----+-SQLSTATE--+-------+--string-constant-+-+-----------------| '-condition-name-----------------------' general-condition-value .-,----------------. V | |----+-SQLEXCEPTION-+-+-----------------------------------------| +-SQLWARNING---+ '-NOT FOUND----' SQL-procedure-statement |--+--------+--SQL-statement------------------------------------| '-label:-'
注:
- FOR BIT DATA 節とその後に続く他の列制約とは、任意の順序で指定できます。FOR BIT DATA 節をストリング単位 CODEUNITS32 とともに指定することはできません (SQLSTATE 42613)。
- data-type4 が CURSOR 組み込みタイプまたは cursor-type-name を指定している場合は、NULL または cursor-value-constructor のみ指定できます。array-type-name または row-type-name には DEFAULT NULL のみ明示的に指定できます。
- array-type-name には DEFAULT NULL のみ明示的に指定できます。
- row-type-name には DEFAULT NULL のみ明示的に指定できます。
- statement-name は cursor-parameter-declaration が指定されている場合、 指定することができません。
説明
- label
- コード・ブロックのラベルを定義します。 開始ラベルを指定した場合、そのラベルを使用して、 コンパウンド・ステートメントで宣言する SQL 変数を修飾することができます。 また、開始ラベルは LEAVE ステートメントで指定することもできます。 終了ラベルを指定する場合、そのラベルは開始ラベルと同じでなければなりません。
- ATOMIC または NOT ATOMIC
- ATOMIC は、コンパウンド・ステートメントで未処理の例外条件が発生したときに、
そのコンパウンド・ステートメント内のすべての SQL ステートメントをロールバックします。
NOT ATOMIC は、コンパウンド・ステートメントで未処理の例外条件が発生しても、 そのコンパウンド・ステートメントをロールバックしません。
モジュール内にない動的に作成されるコンパウンド・ステートメントまたは SQL 関数で ATOMIC キーワードが指定されると、コンパウンド・ステートメントはコンパウンド SQL (インライン化) ステートメントとして処理されます。
モジュール表関数の関数本体で使用されるコンパウンド・ステートメントは、NOT ATOMIC としてのみ定義できます。
- type-declaration
- コンパウンド・ステートメントに対してローカルなユーザー定義データ・タイプを宣言します。
- type-name
- ローカルなユーザー定義データ・タイプの名前を指定します。この名前は、現行コンパウンド・ステートメントの中で宣言されたその他のタイプと同じにすることはできません (SQLSTATE 42734)。 非修飾の type-name には、いずれかの CREATE TYPE ステートメントで記述された制限と同じ制限があります (SQLSTATE 42939)。
- array-type-definition
- type-name に関連付ける配列データ・タイプの属性を指定します。 構文エレメントの説明については、『CREATE TYPE (配列)』を参照してください。 row-type-name は、既に宣言されていて、現在のコンパウンド SQL (コンパイル済み) ステートメントのスコープに含まれる宣言済み行タイプを参照できます。 anchored-data-type 節で指定される variable-name は、現在のコンパウンド SQL (コンパイル済み) ステートメントのスコープに含まれるローカル変数を参照できます。
- distinct-type-definition
- type-name に関連付ける、緩やかに型付けされた特殊タイプのソース・タイプとオプションのデータ・タイプ制約を指定します。構文エレメントの詳細な説明については、『CREATE TYPE (特殊)』を参照してください。anchored-non-row-data-type 節で指定されている variable-name は、現在のコンパウンド SQL (コンパイル済み) ステートメントの有効範囲内のローカル変数を参照できます。アンカーである variable-name または column-name のデータ・タイプは、組み込みデータ・タイプでなければなりません。
- row-type-definition
- type-name に関連付ける行データ・タイプのフィールドを指定します。 構文エレメントの全説明については、『CREATE TYPE (行)』を参照してください。 anchored-non-row-data-type 節または anchored-row-data-type 節で指定される variable-name は、現在のコンパウンド SQL (コンパイル済み) ステートメントのスコープに含まれるローカル変数を参照できます。
- SQL-variable-declaration
- コンパウンド・ステートメントに対してローカルな変数を宣言します。
- SQL-variable-name
- ローカル変数の名前を定義します。 すべての SQL 変数名は大文字に変換されます。 この名前は、同じコンパウンド・ステートメント内にある別の SQL 変数と同じにすることはできず、 パラメーター名と同じにすることもできません。 SQL 変数名は、列名と同じであってはなりません。SQL 変数および列参照と同じ名前の ID が SQL ステートメントに含まれている場合、 その ID は列と解釈されます。 変数が宣言されているコンパウンド・ステートメントにラベルを付けると、変数に対する参照をラベルで修飾できます。例えば、ラベル C を付けたコンパウンド・ステートメント中で変数 V を宣言すると、その変数を C.V として参照できます。
- data-type4
- 変数のデータ・タイプを指定します。
構造化タイプまたは参照タイプを指定することはできません (SQLSTATE 429BB)。
- built-in-type
- 組み込みデータ・タイプを指定します。BOOLEAN および CURSOR (表には指定できない) を除く各組み込みデータ・タイプの詳細な説明は、『CREATE TABLE』を参照してください。トリガーまたは関数内で使用される、あるいはスタンドアロン・ステートメントとして使用されるコンパウンド SQL (コンパイル済み) ステートメント内では、XML データ・タイプは指定することはできません (SQLSTATE 429BB)。コンパウンド SQL (コンパイル済み) ステートメントが SQL プロシージャー本体で使用される場合、XML データ・タイプは指定されます。
- BOOLEAN
- Boolean を示します。
- CURSOR
- カーソルを示します。
- anchored-data-type
- SQL 変数のデータ・タイプを決定するために使用される別のオブジェクトを指定します。
アンカー・オブジェクトのデータ・タイプには、データ・タイプを直接指定する際に (行の場合は行タイプを作成する際に) 適用されるのと同じ制限があります。
- ANCHOR DATA TYPE TO
- データ・タイプの指定にアンカー・データ・タイプを使用することを示します。
- variable-name
- SQL 変数、SQL パラメーター、またはグローバル変数を指定します。参照される変数のデータ・タイプが、SQL-variable-name のデータ・タイプとして使用されます。
- table-name.column-name
- 既存の表またはビューの列名を指定します。列のデータ・タイプが、SQL-variable-name のデータ・タイプとして使用されます。
- ROW OF table-name または view-name
- table-name で識別される表、または view-name で識別されるビューの列名および列データ・タイプを基にした名前とデータ・タイプを含むフィールドの行になるように指定します。SQL-variable-name のデータ・タイプは、名前の付いていない行タイプです。
- ROW OF cursor-variable-name
- cursor-variable-name で識別されるカーソル変数のフィールド名およびフィールド・データ・タイプを基にした名前とデータ・タイプを含めて、フィールドの行を指定します。
指定するカーソル変数は、以下のいずれかのエレメントでなければなりません (SQLSTATE 428HS)。
- 厳密に型付けされたカーソル・データ・タイプの SQL 変数またはグローバル変数
- すべての結果列が名前指定されている select-statement を指定した CONSTANT 節を使用して作成または宣言された、緩やかに型付けされたカーソル・データ・タイプの SQL 変数またはグローバル変数
- array-type-name
- ユーザー定義の配列タイプの名前を指定します。配列データ・タイプは、ローカルに宣言されたデータ・タイプ、スキーマ・データ・タイプ、またはモジュール・データ・タイプにすることができます。
- cursor-type-name
- カーソル・タイプの名前を指定します。カーソル・データ・タイプは、スキーマ・データ・タイプまたはモジュール・データ・タイプにすることができます。
- distinct-type-name
- 特殊タイプの名前を指定します。特殊データ・タイプは、スキーマ・データ・タイプまたはモジュール・データ・タイプにすることができます。宣言された変数の長さ、精度、および位取りは、それぞれ特殊タイプのソース・タイプの長さ、精度、および位取りになります。
- row-type-name
- ユーザー定義の行タイプの名前を指定します。行データ・タイプは、ローカルに宣言されたデータ・タイプ、スキーマ・データ・タイプ、またはモジュール・データ・タイプにすることができます。変数のフィールドは、行タイプのフィールドです。
- DEFAULT または CONSTANT
- コンパウンド SQL (コンパイル済み) ステートメントが参照される際の SQL 変数の値を指定します。いずれも指定されない場合は、SQL 変数のデフォルトは NULL 値です。array-type-name または row-type-name の指定時には、DEFAULT NULL のみ明示的に指定できます。
- DEFAULT
- SQL 変数のデフォルトを定義します。 コンパウンド SQL (コンパイル済み) ステートメントが参照されると、この変数は初期化されます。デフォルト値は、その変数のデータ・タイプと割り当てに互換性があるものでなければなりません。
- CONSTANT
- SQL 変数に変更できない固定値があることを指定します。 CONSTANT を使用して定義された SQL 変数は、すべての割り当て操作のターゲットとして使用できません。 固定値は、その変数のデータ・タイプと割り当てに互換性があるものでなければなりません。
- NULL
- SQL 変数のデフォルト値として NULL を指定します。
- constant
- SQL 変数のデフォルト値として定数を指定します。data-type4 が CURSOR 組み込みタイプまたは cursor-type-name を指定している場合は、constant を指定できません (SQLSTATE 42601)。
- cursor-value-constructor
- cursor-value-constructor には、SQL 変数に関連付けられている select-statement を指定します。cursor-value-constructor をカーソル変数に割り当てると、そのカーソル変数の基礎カーソルが定義されます。
- (cursor-parameter-declaration, ...)
- 各パラメーターの名前およびデータ・タイプを含む、カーソルの入力パラメーターを指定します。
名前付き入力パラメーターは、select-statement も cursor-value-constructor に指定する場合にだけ指定できます (SQLSTATE 428HU)。
- parameter-name
- select-statement 内で SQL 変数として使用するためにカーソル・パラメーターの名前を指定します。この名前は、カーソルの他のすべてのパラメーター名と同じにすることはできません。また、この名前は、列名がパラメーター名の前に解決されるため、select-statement で使用できるすべての列名と同じにならないように選択しなければなりません。
- data-type5
- select-statement で使用されるカーソル・パラメーターのデータ・タイプを指定します。構造化タイプおよび参照タイプを指定することはできません (SQLSTATE 429BB)。
- built-in-type
- 組み込みデータ・タイプを指定します。各組み込みデータ・タイプの詳細な説明は、『CREATE TABLE』を参照してください。 BOOLEAN および CURSOR 組み込みタイプを指定することはできません (SQLSTATE 429BB)。
- anchored-non-row-data-type
- カーソル・パラメーターのデータ・タイプを決定するために使用される別のオブジェクトを指定します。アンカー・オブジェクトのデータ・タイプには、データ・タイプを直接的に指定する際に適用されるのと同じ制限があります。
- ANCHOR DATA TYPE TO
- データ・タイプの指定にアンカー・データ・タイプを使用することを示します。
- variable-name
- ローカルの SQL 変数、SQL パラメーター、またはグローバル変数を指定します。参照される変数のデータ・タイプが、カーソル・パラメーターのデータ・タイプとして使用されます。
- table-name.column-name
- 既存の表またはビューの列名を指定します。列のデータ・タイプが、カーソル・パラメーターのデータ・タイプとして使用されます。
- distinct-type-name
- 特殊タイプの名前を指定します。distinct-type-name がスキーマ名なしで指定される場合、SQL パスでスキーマを検索することにより、特殊タイプは解決されます。
- holdability
- コミット操作の結果としてカーソルをクローズすることを回避するかどうかを指定します。詳しくは、『DECLARE CURSOR』を参照してください。デフォルトは WITHOUT HOLD です。
- WITHOUT HOLD
- コミット操作の結果としてカーソルをクローズすることを回避しません。
- WITH HOLD
- 複数の作業単位を通してリソースを維持します。 コミット操作の結果としてカーソルをクローズすることを回避します。
- select-statement
- カーソルの SELECT ステートメントを指定します。 詳しくは、『select-statement』を参照してください。cursor-parameter-declaration が cursor-value-constructor に含まれている場合は、select-statement にはローカルの SQL 変数またはルーチンの SQL パラメーターを含めてはなりません (SQLSTATE 42704)。
- statement-name
- カーソルの準備済み select-statement を指定します。準備済みステートメントの説明については『PREPARE』を参照してください。ターゲットのカーソル変数には、厳密に型付けされたユーザー定義のカーソル・タイプのデータ・タイプがあってはなりません (SQLSTATE 428HU)。statement-name を指定する場合は、名前付き入力パラメーターを cursor-value-constructor に指定してはなりません (SQLSTATE 428HU)。
- RESULT_SET_LOCATOR VARYING
- 結果セット・ロケーター変数のデータ・タイプを指定します。
- condition-declaration
- 条件名を宣言します。オプションで、関連付ける SQLSTATE 値を指定できます。
- condition-name
- 条件の名前を指定します。条件名は、それが宣言されるコンパウンド・ステートメント内で固有でなければなりません。ただし、そのようなコンパウンド・ステートメント内でネストされたコンパウンド・ステートメント内での宣言は除きます (SQLSTATE 42734)。 条件名は、それが宣言されたコンパウンド・ステートメント内でのみ参照が可能です。コンパウンド・ステートメント内でネストされたコンパウンド・ステートメントも同様です (SQLSTATE 42737)。
- CONDITION FOR SQLSTATE VALUEstring-constant
- 条件に関連付ける SQLSTATE を指定します。 ストリング定数は単一引用符で囲まれている 5 つの文字として指定しなければならず、SQLSTATE クラス (最初の 2 文字) は '00' にすることはできません。この節の指定がない場合、条件に関連付けられた SQLSTATE 値はありません。
- statement-declaration
- コンパウンド・ステートメントの 1 つ以上のローカルの名前を宣言します。 statement-name 内の各名前は、同じコンパウンド・ステートメント内で宣言されている他のステートメント名と同じであってはなりません。
- return-codes-declaration
- SQLSTATE および SQLCODE という特殊変数を宣言します。 これらの変数は、SQL ステートメントの処理後に戻される値に自動的に設定されます。 SQL プロシージャー本体などに、ネストされたコンパウンド SQL (コンパイル済み) ステートメントが存在する場合、SQLSTATE および SQLCODE 変数は両方とも、最外部のコンパウンド・ステートメントでしか宣言できません。 これらの変数は、SQL プロシージャーごとに一度しか宣言できません。
- declare-cursor-statement
- プロシージャー本体で組み込みカーソルを宣言します。ユーザー定義カーソル・データ・タイプの変数は、SQL-variable-declaration ステートメントを使用して宣言されます。
各宣言されたカーソルは、それが宣言されたコンパウンド・ステートメント内で固有の名前を持たなければなりません。ただし、そのようなコンパウンド・ステートメント内でネストされたコンパウンド・ステートメント内での宣言は除きます (SQLSTATE 42734)。カーソルは、それが宣言されたコンパウンド・ステートメント内でのみ参照が可能です。コンパウンド・ステートメント内でネストされたコンパウンド・ステートメントも同様です (SQLSTATE 34000)。
カーソルをオープンする場合は OPEN ステートメントを、 カーソルを使用して行を読み取る場合は FETCH ステートメントを使用します。 SQL プロシージャーからクライアント・アプリケーションに結果セットを戻す場合、 WITH RETURN 節を使用してカーソルを宣言しなければなりません。 以下の例では、クライアント・アプリケーションに結果セットを 1 つ戻します。CREATE PROCEDURE RESULT_SET() LANGUAGE SQL RESULT SETS 1 BEGIN DECLARE C1 CURSOR WITH RETURN FOR SELECT id, name, dept, job FROM staff; OPEN C1; END注: 結果セットを処理する場合、 DB2® コール・レベル・インターフェース (Db2 コール・レベル・インターフェース)、ODBC (Java Database Connectivity)、 JDBC (Java™ Database Connectivity)、 Java Embedded SQL (SQLJ) のいずれかのアプリケーション・プログラミング・インターフェースを使用して、 クライアント・アプリケーションを作成しなければなりません。
カーソルの宣言について詳しくは、『DECLARE CURSOR』を参照してください。
- procedure-declaration
- コンパウンド・ステートメントに対してローカルなプロシージャーを宣言します。
ローカル・プロシージャーの定義には、「CREATE PROCEDURE (SQL)」ステートメントで指定可能なオプションの指定は含まれません。
オプションのデフォルトは、MODIFIES SQL DATA を除き、「CREATE PROCEDURE (SQL)」ステートメントの場合と同じです。
このプロシージャーのデータ・アクセス・レベルは、SQL プロシージャー本体を処理するために必要な最小レベルとなるように自動的に決定されます。
- procedure-name
- ローカル・プロシージャーの名前を定義します。 名前は修飾なしで指定しなければなりません (SQLSTATE 42601)。procedure-name と宣言済みパラメーターの数で構成されるプロシージャー・シグニチャーは、現在のコンパウンド・ステートメント内で固有のものでなければなりません。 現在のコンパウンド・ステートメントが内部でネストされている外部のコンパウンド・ステートメントには、同じ名前のプロシージャーを含めることはできません。
- parameter-declaration
- ローカル・プロシージャーのパラメーターを指定します。 構文エレメントの説明については、『CREATE PROCEDURE (SQL)』を参照してください。 パラメーター・データ・タイプは、現在のコンパウンド・ステートメントのスコープに含まれる、ローカルに宣言されたデータ・タイプにすることができます。
- SQL-procedure-body
- SQL プロシージャーの本体である SQL ステートメントを指定します。 SQL-procedure-body で参照される名前は、既に宣言されていて、ローカル・プロシージャーが宣言されているコンパウンド・ステートメントのスコープに含まれる、宣言済みオブジェクト (宣言済みの変数、データ・タイプ、プロシージャーなど) を参照できます。
- handler-declaration
- コンパウンド・ステートメントで例外または完了条件が発生した場合に実行するハンドラー、および 1 つ以上の SQL-procedure-statements の集合を指定します。SQL-procedure-statement は、ハンドラーが制御を受け取った際に実行するステートメントです。
ハンドラーがアクティブであると見なされるのは、そのハンドラーが宣言されているコンパウンド・ステートメント (ネストされたコンパウンド・ステートメントを含む) 中の、handler-declarations の集合の後の SQL-procedure-statements の集合が実行されている期間中です。
条件処理ルーチンには以下の 3 つのタイプがあります。
- CONTINUE
- ハンドラーが正常に呼び出された後に、 例外を発生したステートメントの後の SQL ステートメントに制御が戻されます。 例外を発生したエラーが FOR、 IF、 CASE、 WHILE、または REPEAT ステートメント (ただし、 それらのいずれかのステートメントの中の SQL-procedure-statement は除く) の場合、制御は、 END FOR、 END IF、 END CASE、 END WHILE、または END REPEAT の後のステートメントに戻されます。
- EXIT
- ハンドラーが正常に呼び出された後に、 ハンドラーを宣言したコンパウンド・ステートメントの最後に制御が戻されます。
- UNDO
- ハンドラーが呼び出される前に、 コンパウンド・ステートメントで行われたあらゆる SQL の変更がロールバックされます。 ハンドラーが正常に呼び出された後に、 ハンドラーを宣言したコンパウンド・ステートメントの最後に制御が戻されます。 UNDO を指定する場合は、 ハンドラーを宣言しているコンパウンド・ステートメントを ATOMIC にしなければなりません。
以下のようなハンドラーをアクティブ化する条件を handler-declaration 中に定義します。
- specific-condition-value
- ハンドラーが、特定条件処理ルーチン であることを指定します。
- SQLSTATE VALUEstring-constant
- ハンドラーが呼び出される SQLSTATE を指定します。 SQLSTATE 値の先頭 2 文字は '00' にしないでください。
- condition-name
- ハンドラーが呼び出される条件名を指定します。 条件名は、条件宣言であらかじめ定義していなければなりません。あるいは、現行のサーバーに存在する条件を識別するものでなければなりません。
- general-condition-value
- ハンドラーが、一般条件処理ルーチン であることを指定します。
- SQLEXCEPTION
- 例外条件が発生した場合に呼び出されるハンドラーを指定します。 例外条件は、最初の 2 文字が '00'、'01'、または '02' ではない SQLSTATE 値で表されます。
- SQLWARNING
- 警告条件が発生した場合に呼び出されるハンドラーを指定します。 警告条件は、最初の 2 文字が '01' の SQLSTATE 値で表されます。
- NOT FOUND
- NOT FOUND 条件が発生した場合に呼び出されるハンドラーを指定します。 NOT FOUND 条件は、最初の 2 文字が '02' の SQLSTATE 値で表されます。
- SQL-procedure-statement
- SQL プロシージャー・ステートメントを指定します。
- label
- SQL プロシージャー・ステートメントのラベルを指定します。 ラベルは、リスト内でネストされたコンパウンド・ステートメントを含め、SQL プロシージャー・ステートメントの リスト内でユニークでなければなりません。 ネストされていないコンパウンド・ステートメントは、同じラベルを使用できることに注意してください。 SQL プロシージャー・ステートメントのリストは、おそらく SQL 制御ステートメントの中にあります。
- SQL-statement
- 以下の例外を除く、すべての実行可能 SQL ステートメント
- ALTER
- CONNECT
- CREATE
- DESCRIBE
- DISCONNECT
- DROP
- FLUSH EVENT MONITOR
- FREE LOCATOR
- GRANT
- REFRESH TABLE
- RELEASE (接続のみ)
- RENAME TABLE
- RENAME TABLESPACE
- REVOKE
- SET CONNECTION
- SET INTEGRITY
- SET PASSTHRU
- SET SERVER OPTION
- TRANSFER OWNERSHIP
以下の実行可能ステートメントは、スタンドアロンのコンパウンド SQL (コンパイル済み) ステートメントではサポートされませんが、SQL 関数、SQL プロシージャー、またはトリガー内で使用されるコンパウンド SQL (コンパイル済み) ステートメントではサポートされます。- 索引、表、またはビューの CREATE
- DECLARE GLOBAL TEMPORARY TABLE
- 索引、表、またはビューの DROP
- GRANT
- ROLLBACK
以下の実行可能ステートメントでないステートメントは、コンパウンド SQL (コンパイル済み) ステートメントでサポートされます。- ALLOCATE CURSOR
- ASSOCIATE LOCATORS
規則
- ATOMIC コンパウンド・ステートメントはネストできません。
- ハンドラーの宣言には、以下の規則が適用されます。
- ハンドラーの宣言では、同一の condition-name または SQLSTATE 値を複数回含めることはできません。 また、SQLSTATE 値と、その同じ SQLSTATE 値を表す condition-name を含めることもできません。
- コンパウンド・ステートメント中で複数の条件処理ルーチンが宣言されている場合、以下の規則が適用されます。
- 2 つのハンドラー宣言に、同一の一般条件カテゴリー (SQLEXCEPTION、 SQLWARNING、 NOT FOUND) を指定することはできません。
- 2 つのハンドラー宣言に、同一の値を表す SQLSTATE 値または condition-name として、 同一の特定条件カテゴリーを指定することはできません。
- 例外または完了条件が発生した場合、その条件に最も適したハンドラーが有効になります。
以下の考慮事項に基づいて、最も適したハンドラーが判別されます。
- ハンドラー宣言 H の有効範囲は、H のあるコンパウンド・ステートメント中に含まれるハンドラー宣言の後の SQL-procedure-statement のリストです。 したがって、H の有効範囲には、 条件処理ルーチン H の本体中に含まれるステートメントは入りません。 つまり、条件処理ルーチンは条件処理ルーチン自体の本体中で生じる条件を処理できないことになります。 同様に、同一のコンパウンド・ステートメント中で H1 と H2 という 2 つのハンドラーが宣言されている場合、H1 は H2 の本体中で生じる条件を処理できず、 H2 は H1 の本体中で生じる条件を処理できません。
- 内側の有効範囲で宣言された specific-condition-value または general-condition-value C のハンドラーは、 外側の有効範囲で宣言された C の別のハンドラーより優先します。
- 条件 C に関する特定ハンドラーと、 同じく C を処理する一般ハンドラーが、 同一の有効範囲内で宣言されている場合は、特定ハンドラーの方が一般ハンドラーより優先します。
- 関連付けられている SQLSTATE 値のないモジュール条件のハンドラーと SQLSTATE 45000 のハンドラーが同じ有効範囲で宣言されている場合、モジュール条件のハンドラーが SQLSTATE 45000 のハンドラーより優先されます。
- コミットまたはロールバック操作が生じた後に、SQL プロシージャー内でデータ・タイプ XML の変数またはパラメーターを参照することは、まずこれらの変数に新しい値を割り当ててからでなければサポートされません (SQLSTATE 560CE)。
- アンカー・データ・タイプの使用: アンカー・データ・タイプは次のオブジェクトを参照できません (SQLSTATE 428HS): ニックネーム、型付き表、型付きビュー、式ベースの索引に関連付けられた統計ビュー、宣言済み一時表、緩やかに型付けされたカーソルに関連付けられた行定義、データベース・コード・ページまたはデータベース照合とは異なるコード・ページまたは照合を使用するオブジェクト。
- 動的に準備または実行されるコンパウンド SQL (コンパイル済み) ステートメントで名前付きパラメーター・マーカーを使用する場合は、各パラメーター・マーカー名が固有でなければなりません (SQLSTATE 42997)。
注
- XML 割り当て: データ・タイプ XML のパラメーターおよび変数に対する割り当ては、参照によって行われます。
CALL ステートメント内でデータ・タイプ XML のパラメーターを SQL プロシージャーに渡すことは、参照によって行われます。 参照によって XML 値を渡すときには、入力ノード・ツリーが XML 引数から直接使用されます。 この直接的な使用により、文書の順序、元のノード ID、およびすべての親プロパティーを含むすべてのプロパティーが保持されます。
例
- 例 1:「Hello」という語を出力する単純なスタンドアロン・コンパウンド・ステートメント
SET SERVEROUTPUT ON; BEGIN CALL DBMS_OUTPUT.PUT_LINE ( 'Hello' ); END - 例 2: staff 内のレコード数をカウントし、その結果を出力する、単純なスタンドアロン・コンパウンド・ステートメント
SET SERVEROUTPUT ON; BEGIN DECLARE v_numRecords INTEGER DEFAULT 1; SELECT COUNT(*) INTO v_numRecords FROM staff; CALL DBMS_OUTPUT.PUT_LINE (v_numRecords); END - 例 3: 以下の処置を実行するコンパウンド SQL (コンパイル済み) ステートメントが含まれている、プロシージャーを作成します。
- SQL 変数を宣言します。
- IN パラメーターによって判別される部門の従業員の給与を戻すカーソルを宣言します。 SELECT ステートメントで、 salary 列のデータ・タイプを DECIMAL から DOUBLE にキャストします。
- 条件 NOT FOUND (ファイル終わり) に EXIT ハンドラーを宣言します。これにより、 値 '6666' が OUT パラメーター medianSalary に割り当てられます。
- 指定された部門の従業員の数を選択して SQL 変数 numRecords に入れます。
- 50% + 1 の従業員が検索されるまで、WHILE ループのカーソルから行を取り出します。
- 給与の中央値を戻します。
CREATE PROCEDURE DEPT_MEDIAN (IN deptNumber SMALLINT, OUT medianSalary DOUBLE) LANGUAGE SQL BEGIN DECLARE v_numRecords INTEGER DEFAULT 1; DECLARE v_counter INTEGER DEFAULT 0; DECLARE c1 CURSOR FOR SELECT CAST(salary AS DOUBLE) FROM staff WHERE DEPT = deptNumber ORDER BY salary; DECLARE EXIT HANDLER FOR NOT FOUND SET medianSalary = 6666; -- initialize OUT parameter SET medianSalary = 0; SELECT COUNT(*) INTO v_numRecords FROM staff WHERE DEPT = deptNumber; OPEN c1; WHILE v_counter < (v_numRecords / 2 + 1) DO FETCH c1 INTO medianSalary; SET v_counter = v_counter + 1; END WHILE; CLOSE c1; END - 例 4: 以下の例は、RESIGNAL の結果として別の条件から UNDO ハンドラーがアクティブ化される場合を仮定して、
実行の流れを図示しています。
CREATE PROCEDURE A() LANGUAGE SQL CS1: BEGIN ATOMIC DECLARE C CONDITION FOR SQLSTATE '12345'; DECLARE D CONDITION FOR SQLSTATE '23456'; DECLARE UNDO HANDLER FOR C H1: BEGIN -- Perform rollback after error, perform final cleanup, and exit -- procedure A. -- ... -- When this handler completes, execution continues after -- compound statement CS1; procedure A will terminate. END; -- Perform some work here ... CS2: BEGIN DECLARE CONTINUE HANDLER FOR D H2: BEGIN -- Perform local recovery, then forward the error -- condition to the outer handler for additional -- processing. -- ... RESIGNAL C; -- will activate UNDO handler H1; execution -- WILL NOT return here. Any local cursors -- declared in H2 and CS2 will be closed. END; -- Perform some more work here ... -- Simulate raising of condition D by some SQL statement -- in compound statement CS2: SIGNAL D; -- will activate H2 END; END