SQL プロシージャーの構造

SQL プロシージャーは、いくつかの論理パーツで構成されているので、SQL プロシージャーの開発では、そのようなパーツを構造化フォーマットでインプリメントする必要があります。

そのフォーマットはきわめて単純明快であり、準拠するのも簡単ですが、これは、ルーチンの設計と意味を単純化するためのものです。

SQL プロシージャーの中心は、コンパウンド・ステートメントです。コンパウンド・ステートメントには、キーワード BEGIN および END が結び付けられています。このステートメントは、ATOMIC または NOT ATOMIC のどちらでもかまいません。デフォルトでは、これは NOT ATOMIC です。

コンパウンド・ステートメントでは、SQL ステートメントを使用して、複数のオプションの SQL PL オブジェクトを宣言および参照することができます。下図は、SQL プロシージャー内のコンパウンド・ステートメントの構造化フォーマットを図示しています。


  label:  BEGIN 
    Variable declarations
    Condition declarations
    Cursor declarations
    Condition handler declarations
    Assignment, flow of control, SQL statements and other compound statements
  END label

この図は、1 つ以上のオプションのアトミック・コンパウンド・ステートメント (つまりブロック) で SQL プロシージャーを構成することができ、さらに、1 つの SQL プロシージャー内でのそのようなブロックのネストや逐次取り入れが可能なことを示しています。このようなアトミック・ブロックのいずれでも、オプションの変数、条件、およびハンドラーの宣言に対して規定された順序があります。それらは、SQL 制御のステートメントおよびその他の SQL ステートメントを使ってインプリメントされている手続き型論理と、カーソル宣言の導入の前に出現しなければなりません。 SQL プロシージャー本体内の一連の SQL ステートメントを使用して、どこでカーソルを宣言してもかまいません。

SQL プロシージャーに含まれている多くの SQL 制御ステートメントの場合と同じように、制御フローを明確にするため、SQL プロシージャーのアトミック・ブロックにもラベルを付けることができます。そうすれば、変数の参照および制御ステートメントの参照の転送時に、簡単に正確さを極めることができます。

上にリストした各エレメントを示している SQL プロシージャーの例を以下に示します。

   CREATE PROCEDURE DEL_INV_FOR_PROD (IN prod INT, OUT err_buffer VARCHAR(128))
   LANGUAGE SQL
   DYNAMIC RESULT SETS 1
   BEGIN
   
     DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
     DECLARE SQLCODE integer DEFAULT 0;
     DECLARE NO_TABLE CONDITION FOR SQLSTATE '42704';
     DECLARE cur1 CURSOR WITH RETURN TO CALLER  
                     FOR SELECT * FROM Inv;

     A: BEGIN ATOMIC
          DECLARE EXIT HANDLER FOR NO_TABLE
            BEGIN
               SET ERR_BUFFER='Table Inv does not exist';
            END;

          SET err_buffer = '';

          IF (prod < 200)
            DELETE FROM Inv WHERE product = prod;
          ELSE IF (prod < 400)
            UPDATE Inv SET quantity = 0 WHERE product = prod;
          ELSE
            UPDATE Inv SET quantity = NULL WHERE product = prod; 
          END IF;
        END A;

      B:  OPEN cur1;

   END

SQL プロシージャー内の NOT ATOMIC コンパウンド・ステートメント

上記の例は、NOT ATOMIC コンパウンド・ステートメントを解説するものであり、SQL プロシージャーで使用されるデフォルトのタイプです。コンパウンド・ステートメント内で未処理エラー条件が発生した場合、そのエラーより前に完了したどの作業もロールバックされませんが、コミットもされません。ステートメントのグループをロールバックできるのは、ROLLBACK または ROLLBACK TO SAVEPOINT ステートメントを使用して、作業単位が明示的にロールバックされる場合のみです。また、必要であれば、COMMIT ステートメントを使用して、正常に完了したステートメントをコミットすることもできます。

以下は、NOT ATOMIC コンパウンド・ステートメントを使用した SQL プロシージャーの例です。

  CREATE PROCEDURE not_atomic_proc ()   
  LANGUAGE SQL   
  SPECIFIC not_atomic_proc
   nap:  BEGIN NOT ATOMIC            

   INSERT INTO c1_sched (class_code, day)
     VALUES ('R11:TAA', 1);           
  
   SIGNAL SQLSTATE '70000';   
   
   INSERT INTO c1_sched (class_code, day)               
     VALUES ('R22:TBB', 1);    

  END nap

SIGNAL ステートメントを実行すると、処理されないエラーがこのステートメントで明示的に発行されます。その後即時にプロシージャーから戻ります。プロシージャーから戻った後は、エラーが発生したにもかかわらず、最初の INSERT ステートメントは正常に実行を完了し、行を c1_sched 表に挿入し終わっています。プロシージャーは、行挿入操作をコミットすることもロールバックすることもありません。その操作は、SQL プロシージャー呼び出しの属する作業単位が完了するまで、その状態のままになります。

SQL プロシージャー内の ATOMIC コンパウンド・ステートメント

名前から連想されるとおり、ATOMIC コンパウンド・ステートメントは、単体の統一体と考えることができます。その中で何らかの未処理エラー条件が発生した場合、その時点までに実行されたすべてのステートメントも失敗したとみなされて、ロールバックされます。

ATOMIC コンパウンド・ステートメントを他の ATOMIC コンパウンド・ステートメント内にネストすることはできません。

SAVEPOINT ステートメント、COMMIT ステートメント、または ROLLBACK ステートメントを ATOMIC コンパウンド・ステートメント内部から使用することはできません。これらがサポートされるのは、SQL プロシージャー内の NOT ATOMIC コンパウンド・ステートメントにおいてのみです。

以下は、ATOMIC コンパウンド・ステートメントを使用した SQL プロシージャーの例です。

  CREATE PROCEDURE atomic_proc ()
  LANGUAGE SQL  
  SPECIFIC atomic_proc  
 
  ap:  BEGIN ATOMIC            

     INSERT INTO c1_sched (class_code, day)     
       VALUES ('R33:TCC', 1);           
 
     SIGNAL SQLSTATE '70000';       
   
     INSERT INTO c1_sched (class_code, day)               
       VALUES ('R44:TDD', 1);    

  END ap

SIGNAL ステートメントを実行すると、処理されないエラーがこのステートメントで明示的に発行されます。その後即時にプロシージャーから戻ります。最初の INSERT ステートメントは、正常に実行が完了したにもかかわらずロールバックされるので、このプロシージャーでは表には何も行が挿入されていないという結果になります。

ラベルおよび SQL プロシージャーのコンパウンド・ステートメント

コンパウンド・ステートメントおよびループを含め、選択によってはラベルを使用して、SQL プロシージャー内の任意の実行可能ステートメントに名前を付けることができます。他のステートメント内でラベルを参照することで、コンパウンド・ステートメントまたはループ外部に実行の流れをジャンプさせることができ、さらに、コンパウンド・ステートメントまたはループの先頭にジャンプすることもできます。ラベルを参照できるのは、GOTO、ITERATE、および LEAVE ステートメント内です。

必要があれば、コンパウンド・ステートメントの終わりに、対応するラベルを付けてもかまいません。終わりのラベルを付ける場合、先頭で使用したものと同じラベルでなければなりません。

どのラベルも、SQL プロシージャー本体内の固有ラベルでなければなりません。

また、ラベルを使用して、ストアード・プロシージャー内の複数のコンパウンド・ステートメントで同名の変数が宣言された場合に、紛らわしさが生じないようにすることができます。 SQL 変数の名前を修飾するのに、ラベルを使用することができます。