複合 (compound) ステートメント
複合ステートメントは、他のステートメントを 1 つの SQL プロシージャーの中にグループとしてまとめます。 複合ステートメントによって、SQL 変数、カーソル、および条件ハンドラーを宣言することができます。
構文
.-NOT ATOMIC-. >>-+--------+--BEGIN--+------------+----------------------------> '-label:-' '-ATOMIC-----' >--+---------------------------------------+--------------------> | .-----------------------------------. | | V | | '---+-SQL-variable-declaration--+-- ; +-' +-SQL-condition-declaration-+ +-return-codes-declaration--+ '-INCLUDE-statement---------' >--+--------------------------------------+---------------------> | .----------------------------------. | | V | | '---+-DECLARE CURSOR-statement-+-- ; +-' '-INCLUDE-statement--------' >--+---------------------------------+--------------------------> | .-----------------------------. | | V | | '---+-handler-declaration-+-- ; +-' '-INCLUDE-statement---' .---------------------------------. V | >----+-----------------------------+-+--END--+-------+--------->< '-SQL-procedure-statement-- ; ' '-label-' SQL-variable-declaration .-,-----------------. V | |--DECLARE----SQL-variable-name-+-------------------------------> .-DEFAULT NULL------------------------. >--+-data-type--+-------------------------------------+-+-------| | +-CONSTANT NULL-----------------------+ | | +-DEFAULT--constant--+--------------+-+ | | | | (1) | | | | | '-NOT NULL-----' | | | '-CONSTANT--constant------------------' | | .-DEFAULT NULL-. | +-array-type-name--+--------------+------------------+ '-RESULT_SET_LOCATOR VARYING-------------------------' SQL-condition-declaration |--DECLARE--SQL-condition-name----------------------------------> .-VALUE-. .-SQLSTATE--+-------+-. >--CONDITION--FOR--+---------------------+--string-constant-----| return-codes-declaration |--DECLARE------------------------------------------------------> .-DEFAULT--'00000'---------. >--+-SQLSTATE--+-CHARACTER(5)-+--+--------------------------+-+--| | '-CHAR(5)------' '-DEFAULT--string-constant-' | | .-DEFAULT--0----------------. | '-SQLCODE--+-INTEGER-+--+---------------------------+------' '-INT-----' '-DEFAULT--integer-constant-' handler-declaration |--DECLARE--+-CONTINUE-+--HANDLER FOR---------------------------> +-EXIT-----+ '-UNDO-----' (2) >--+-specific-condition-value-+------SQL-procedure-statement----| '-general-condition-value--' specific-condition-value .-,-------------------------------. V .-VALUE-. | |----+-SQLSTATE--+-------+--string-+-+--------------------------| '-SQL-condition-name----------' general-condition-value .-,--------------------. V (3) | |----+-SQLEXCEPTION-+-----+-------------------------------------| +-SQLWARNING---+ '-NOT FOUND----' data-type |--+-built-in-type------+---------------------------------------| '-distinct-type-name-'
- DEFAULT 文節と NOT NULL 文節は、どちらの順序で指定しても構いません。
- 特定条件値 と一般条件値 を同一の ハンドラー宣言に同時に指定することはできません。
- 同じ文節を複数回指定することはできません。
説明
- label
- 複合ステートメント のラベルを指定します。終了ラベルを指定する場合、開始ラベルと同じにしなければなりません。このラベル名は、ルーチン名または同じ有効範囲内の別のラベルと同じものにすることはできません。 詳しくは、SQL ラベルの参照を参照してください。
- ATOMIC
- ATOMIC は、複合ステートメント 内の 処理されない例外条件により複合ステートメント がロールバックされることを示します。 ATOMIC を指定した場合は、複合ステートメント内 で COMMIT または ROLLBACK ステートメントを指定することは できません (ROLLBACK TO SAVEPOINT を指定できます)。
- NOT ATOMIC
- NOT ATOMIC は、複合ステートメント 内で処理されない例外条件がある場合に複合ステートメント をロールバックしないことを示します。NOT ATOMIC は、SQL トリガーの最外部の複合ステートメント内に
指定されている場合は、ATOMIC として処理されます。
ATOMIC が SQL プロシージャーまたは関数で指定されている場合、当該ルーチンのデータ種別は MODIFIES SQL DATA でなければなりません。
- SQL-variable-declaration
- 複合ステートメント に対してローカルな変数を宣言します。
- SQL-variable-name
- ローカル SQL 変数の名前を定義します。データベース・マネージャー は、区切り文字のない SQL 変数名はすべて大文字に変換します。 同じ複合ステートメント の内部にある別の SQL 変数と同じ名前を指定しないでください。ただし、その複合ステートメント の内部でネストされている複合ステートメント 内の宣言は例外です。列名やパラメーター名と同じ名前を SQL 変数に付けることもしないでください。同一ステートメント内に同名の列が複数個ある場合に、SQL 変数名がどの ように解決されるかについては、SQL パラメーターおよび変数の参照を参照してください。 変数名は 'SQL' で始まっていてはなりません。
- data-type
- 変数のデータ・タイプを指定します。データ・タイプの説明については、CREATE TABLEを参照してください。
データ・タイプ がグラフィック・ストリング・データ・タイプの場合は、 UTF-16 または UCS-2 データを指す CCSID 1200 または 13488 を指定してください。 CCSID が指定されていない場合は、グラフィック・ストリング変数の CCSID は、そのジョブに関連付けられている DBCS CCSID です。
- 配列タイプ名
- この SQL 変数は、CREATE TYPE (配列) ステートメントで定義する配列である、ということを指定します。
- DEFAULT constant または NULL
- SQL 変数のデフォルト値を定義します。指定された定数は、割り当ておよび比較で説明している割り当て規則に従って、その変数に割り当てることができる値を表している必要があります。 変数が初期化されるのは、その変数が宣言されている複合ステートメント が入力されるときです。デフォルト値の指定がない場合は、SQL 変数は NULL に初期化されます。 XML タイプの SQL 変数でデフォルト値を指定することはできません。 配列タイプ の SQL 変数は、常に NULL に初期化されます。
- NOT NULL
- SQL 変数に NULL 値が入るのを防ぎます。NOT NULL を指定しないことは、その変数がヌルであってもよいことを意味します。XML タイプの SQL 変数で NOT NULL を指定することはできません。
- CONSTANT constant または NULL
- SQL 変数に変更できない固定値があることを指定します。 CONSTANT を使用して定義された SQL 変数は、すべての割り当て操作のターゲットとして使用できません。 指定された定数は、割り当ておよび比較で説明している割り当て規則に従って、その変数に割り当てることができる値を表している必要があります。
- RESULT_SET_LOCATOR VARYING
- 結果セット・ロケーター変数のデータ・タイプを指定します。
- SQL-condition-declaration
- 条件名と対応する SQLSTATE 値を宣言します。
- SQL-condition-name
- 条件の名前を指定します。 条件名は、自身の宣言がある複合ステートメント の内部で固有の名前でなければなりません (ただし、複合ステートメント の内部にネストされている複合ステートメント 内の宣言は例外です)。
- FOR SQLSTATE string-constant
- この条件に関連する SQLSTATE を指定します。 ストリング定数 は、5 文字で指定しなければなりません。 SQLSTATE クラス (最初の 2 文字) は「00」であってはなりません。
- return-codes-declaration
- GET DIAGNOSTICS や空の複合ステートメント 以外の SQL ステートメントの実行後に、診断域の最初の条件で設定される SQLSTATE と SQLCODE という特殊な SQL 変数を宣言します。
SQLSTATE 変数および SQLCODE 変数は
、SQL プロシージャー、SQL 関数、または SQL トリガーの
最外部の複合ステートメント の中でのみ
宣言できます。
SQLSTATE および SQLCODE 特殊変数は、GET DIAGNOSTICS 以外の直前の SQL ステートメントを処理した結果得られる SQL 戻りコードを取得する手段としてのみ使用されます。SQLSTATE および SQLCODE 値を使用しようとする場合は、即時にそれらの値を他の SQL 変数に保管して、次の SQL ステートメントの実行後に戻される SQL 戻りコードでそれらの値が置き換えられないようにしてください。SQLSTATE を処理するハンドラーが定義される場合、割り当てがそのハンドラーの最初のステートメントである場合、割り当てステートメントを使用してその SQLSTATE (または関連した SQLCODE) 値を別の SQL 変数に保管することができます。
これらの変数に値を割り当てることは、禁止されてはいませんが、お勧めしません。 これらの特殊変数への割り当ては、条件ハンドラーによって無視されます。SQLSTATE および SQLCODE 特殊変数は NULL に設定できません。
- DECLARE CURSOR ステートメント
- ルーチン本体でカーソルを宣言します。
カーソル名は、複合ステートメント 内部で固有の名前でなければなりません。ただし、
複合ステートメント の内部にネストされて
いる複合ステートメント の宣言を除きます。
カーソル名 は、それが宣言されて いる複合ステートメント 内でのみ 参照することができます。これには、その複合ステートメント 内にネストされている任意の複合ステートメント を含みます。
このカーソルをオープンするには OPEN ステートメントを使用し、このカーソルを使用して行を読み取るには FETCH ステートメントを 使用します。 カーソルが SQL プロシージャー内にあり、結果セットとして使用する場合、- カーソルを宣言する際に WITH RETURN を指定する
- DYNAMIC RESULT SETS 文節にゼロ以外の値を指定して使用し、プロシージャーを作成する
- 複合ステートメント 内で CLOSE ステートメントを指定しない。
カーソルの宣言について詳しくは、DECLARE CURSORを参照してください。
- handler-declaration
- ハンドラー、つまり複合ステートメント 内で
例外条件または完了条件が発生したときに実行される SQL プロシージャー・ステートメント を指定します。
条件ハンドラー宣言は、同じ条件値または SQLSTATE 値を複数回参照することはできず、 また、1 つの SQLSTATE 値と、それと同じ SQLSTATE 値を表す条件名を参照することもできません。 SQLSTATE 値のリストおよび詳細については、「SQL メッセージおよびコード」トピック集を参照してください。
さらに、1 つの複合ステートメントで複数の条件ハンドラーが宣言される場合、2 つの条件ハンドラー宣言が以下のものを指定することはできません。
- 同一の一般条件カテゴリー、または
- 同一の特定条件 (SQLSTATE 値として、またはそれと同一の値を表す条件名として)
条件ハンドラーは、それが宣言されている複合ステートメント (ネストされた複合ステートメントを含む) 内のハンドラー宣言 に従う SQL プロシージャー・ステートメント のセットに対してアクティブです。
ある条件のハンドラーは、ネストされた複合ステートメントの複数のレベルに存在することがあります。 例えば、複合ステートメント n1 に別の 複合ステートメント n2 が含まれ、 それにさらに別の複合ステートメント n3 が含まれている場合を想定してください。 例外条件が n3 内で生じると、 n3 内のアクティブなハンドラーが最初にその条件を処理することを許可されます。 適切なハンドラーが n3 に存在しない場合、 その条件は n2 に再通知されて、 n2 内のアクティブなハンドラーがその状態を処理できるようになります。 適切なハンドラーが n2 に存在しない場合、 その条件は n1 に再通知されて、 n1 内のアクティブなハンドラーがその状態を処理できるようになります。 n1 内に適切なハンドラーがない場合、その状態は未処理と見なされます。
条件ハンドラーには以下の 3 つのタイプがあります。
- CONTINUE
- 条件ハンドラーが活動化され、正常に完了した後、例外を起 こしたステートメントの後の SQL ステートメントに制御が戻されることを指定します。 IF、 CASE、 FOR、 WHILE、 または REPEAT の中で比較を実行しているとき にエラーが発生すると、それに対応する END IF、 END CASE、 END FOR、 END WHILE、または END REPEAT の後のステートメントに制御が戻されます。
- EXIT
- 条件ハンドラーが活動化され、正常に完了した後、条件ハンドラーを宣言した複合ステートメントの終わりに制御が戻されることを指定します。
- UNDO
- 条件ハンドラーが活動化されると、複合ステートメント によって加えられた変更がロールバックされることを指定します。
このハンドラーが正常に完了すると、複合ス
テートメント の終わりに制御が戻されます。
UNDO を指定する場合は、ATOMIC を指定する必要があります。
UNDO は、SQL 関数または SQL トリガーの 最外部の複合ステートメント の中には 指定できません。
このハンドラーが活動化される条件は以下のとおりです。
- SQLSTATE string
- 特定の SQLSTATE が発生したときにハンドラーを呼び出すことを指定します。SQLSTATE クラス (最初の 2 文字) は「00」であってはなりません。
- SQL-condition-name
- 条件名に関連した特定の SQLSTATE が発生したときにハンドラーを呼び出すことを指定します。SQL 条件名は、SQL 条件宣言 の中で既に定義されていなければなりません。
- SQLEXCEPTION
- 例外条件が発生したときにハンドラーを呼び出すことを指定します。 例外条件は、最初の 2 文字が '00'、'01'、または '02' ではない SQLSTATE 値によって表されます。
- SQLWARNING
- 警告条件が発生したときにハンドラーを呼び出すことを指定します。 警告条件は、最初の 2 文字が '01' である SQLSTATE 値によって表されます。
- NOT FOUND
- NOT FOUND 条件が発生したときにハンドラーを呼び出すことを指定します。 NOT FOUND 条件は、最初の 2 文字が '02' である SQLSTATE 値によって表されます。
注
ネストする複合ステートメント: 複合ステートメントはネストすることができます。 ネストされた複合ステートメントを使用して、変数定義、条件名、条件ハンドラー、およびカーソルの有効範囲を複合ステートメント 内のステートメントのサブセットに指定することができます。 これにより、各 SQL プロシージャー・ステートメントに対する処理を単純化できます。 ネストされた複合ステートメントのサポートにより、条件ハンドラーの宣言内で複合ステートメントを使用できるようになります。
条件ハンドラー: 複合ステートメント 内の条件ハンドラーは、外部 SQL アプリケーション・プログラムで使用される WHENEVER ステートメントとほぼ同じです。 例外、警告、または Not Found 条件が発生すると自動的に制御を得るように、条件ハンドラーを定義できます。条件ハンドラーの本体には、その条件ハンドラーが活動化されるときに実行されるコードが含まれています。SQL ステートメントの処理のためにデータベース・マネージャーが戻す例外、警告、または Not Found 条件の結果として、条件ハンドラーを活動化することができます。または、プロシージャー本体内で SIGNAL または RESIGNAL ステートメントが発行された結果を、活動化の条件とすることができます。
条件ハンドラーは複合ステートメント内で宣言され、その条件ハンドラーが宣言される複合ステートメント内のすべての条件ハンドラー宣言の後に続く、一連の SQL プロシージャー・ステートメント に対してアクティブになります。もっと具体的には、条件ハンドラー宣言 H の有効範囲は、H が表示される複合ステートメント内に含まれている条件ハンドラー宣言の後に続く SQL プロシージャー・ステートメント のリストです。したがって、H の有効範囲には、 条件ハンドラー H の本体中に含まれるステートメントは入りません。 つまり、条件ハンドラーは条件ハンドラー自体の本体中で生じる条件を処理できないことになります。 同様に、同一の複合ステートメント中で H1 と H2 という 2 つの条件ハンドラーが宣言されている場合、H1 は H2 の本体中で生じる条件を処理できず、H2 は H1 の本体中で生じる条件を処理できません。
条件ハンドラーの宣言は、それを活動化する条件、条件ハンドラーのタイプ (CONTINUE、EXIT、または UNDO)、およびハンドラーのアクションを指定します。条件ハンドラーのタイプにより、ハンドラー・アクションが正常に完了した後に制御がどこに戻されるかが決まります。
条件ハンドラーの活動化: SQL プロシージャー・ステートメント の処理で、正常終了以外の条件が発生すると、その条件を処理できる条件ハンドラーが有効範囲内にある場合、その条件を処理するためにその条件ハンドラーが活動化されます。
複合ステートメントがネストされているルーチンでは、特定の条件を処理できる条件ハンドラーが、ネストされている複合ステートメントの複数のレベルに存在する場合があります。活動化される条件ハンドラーは、条件が検出された有効範囲の最も内側で宣言される条件ハンドラーです。そのネスト・レベルの複数の条件ハンドラーが条件を処理できる場合、活動化される条件ハンドラーは、その複合ステートメントで宣言された最も適したハンドラーです。
最も適したハンドラーとは、該当の例外条件または完了条件の SQLSTATE に 最も一致度の高い複合ステートメント に定義 されているハンドラーです。
例えば、最も内側の複合ステートメントが SQLSTATE 22001 に対する特定のハンドラーとともに、SQLEXCEPTION に対するハンドラーも宣言する場合、SQLSTATE 22001 が検出されるときに、SQLSTATE 22001 に対する特定ハンドラーが最も適したハンドラーです。この場合、その特定ハンドラーが活動化されます。
条件ハンドラーがアクティブ化されると、その条件ハンドラーのアクションが実行されます。ハンドラー・アクションが正常に完了するか、未処理の警告を出して完了する場合、診断域がクリアされ、条件ハンドラーのタイプ (CONTINUE、EXIT、または UNDO ハンドラー) により、どこに制御が戻されるかが決まります。さらに、ハンドラーが正常に完了するか、未処理の警告で完了する場合、SQLSTATE および SQLCODE SQL 変数がクリアされます。
ハンドラー・アクションが正常に完了しないときに、ハンドラー・アクションで検出された条件に対して適切なハンドラーが存在する場合、その条件ハンドラーが活動化されます。それ以外の場合、条件ハンドラー内で検出される条件は処理されません。
- 未処理条件が例外である場合、失敗したステートメントを含む SQL プロシージャー、SQL 関数、または SQL トリガーは、未処理例外条件で終了します。
- 未処理条件が警告または Not Found 条件である場合、次のステートメントに移って処理が続けられます。 次の SQL ステートメントの処理により、診断域内の未処理条件に関する情報が上書きされ、未処理条件の形跡が存在しなくなることに注意してください。
ネストされた複合ステートメントと一緒に SIGNAL または RESIGNAL ステートメントを使用する場合の考慮事項: 条件ハンドラーで指定された SQL プロシージャー・ステートメント が、例外 SQLSTATE を持つ SIGNAL または RESIGNAL ステートメントのどちらかである場合、複合ステートメントは、指定された例外を出して終了します。 これは、この条件ハンドラー、または同じ複合ステートメント内の別の条件ハンドラーが CONTINUE を指定する場合であっても起こります。これらの条件ハンドラーがこの例外の有効範囲内にないからです。複合ステートメントが別の複合ステートメント内にネストされている場合、上位の複合ステートメントにある条件ハンドラーが例外の有効範囲内にあるため、それらのハンドラーがその例外を処理する場合があります。
SQL パラメーターおよび SQL 変数中の NULL 値: SQL パラメーターまたは SQL 変数の値が NULL で、標識変数が許可されていない SQL ステートメント (CONNECT あるいは DESCRIBE など) に使用されている場合、エラーが戻されます。
オープン・カーソルに対する影響: 何らかの理由で複合ステートメント を終了した後、その複合ステートメントで宣言されるすべてのオープン・カーソルはクローズされます。ただし、結果セットを戻すように宣言される場合、または *ENDACTGRP が指定されている場合を除きます。
SQLSTATE および SQLCODE SQL 変数に関する考慮事項: 複合ステートメント自体は、SQLSTATE および SQLCODE SQL 変数に影響を与えません。 ただし、複合ステートメント内にある SQL ステートメントは、SQLSTATE 変数 および SQLCODE SQL 変数に影響する可能性があります。複合ステートメントの終わりで、SQLSTATE および SQLCODE SQL 変数は、その複合ステートメント内で実行され、SQLSTATE および SQLCODE SQL 変数を変更した最後の SQL ステートメントの結果を反映します。SQLSTATE および SQLCODE 変数が複合ステートメント内で変更されなかった場合、それらの変数には、複合ステートメントが入力されたときと同じ値が入っています。
例
以下のアクションを実行する、複合ステートメントのあるプロシージャー本体を作成します。
- SQL 変数を宣言します。
- 従業員の給与を IN パラメーターで判別される部門に戻すカーソルを宣言します。
- 値 6666 を OUT パラメーター medianSalary に割り当てる 条件 NOT FOUND (ファイルの終わり) のための EXIT ハンドラーを宣言します。
- 指定の部門に属する従業員の数を選択して、SQL 変数 v_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 salary 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