CREATE PROCEDURE ステートメント

CREATE PROCEDURE ステートメントは、戻り値のないサブルーチンであるプロシージャーを定義します。

構文

注:
  1. NAMESPACE および NAME 文節が使用された場合は、 値は暗黙的に定数かつタイプ CHARACTER (または CHAR) です。

CONSTANT ステートメントの使用に関する情報については、DECLARE ステートメントを参照してください。

ProcedureName を使用してプロシージャー名を、 ParameterList を使用してプロシージャーのパラメーターを提供します。 プロシージャーは、単一 ESQL ステートメント Statement を使用してインプリメントします。 このステートメントは、複合ステートメント (たとえば BEGIN ... END;) であってもかまいません。 また、Statement の代わりに EXTERNAL 文節を使用することができます。 EXTERNAL 文節は、それを含んでいるノードのデータ・ソース・プロパティーによって識別されるデータベース内のストアード・プロシージャーとして、 名前付きルーチンを呼び出します (外部ストアード・プロシージャーを参照してください)。

ESQL プロシージャーおよびパラメーターは、ESQL 関数とは異なり、IN パラメーターの他に、 OUT および INOUT パラメーターを持つことができます。 プロシージャーは、これにより呼び出し元に複数の値を戻すことができます。

モジュールで定義されたプロシージャーは現行ノードでのみ有効な、ローカルなものです。 同じプロシージャーを複数のノードで使用するには、それをスキーマに定義します。

多重定義の内部プロシージャーはサポートされません。 (多重定義プロシージャーは、同一のブローカー・スキーマではあるものの、 パラメーターの数が異なる、またはタイプの異なるパラメーターを有する別のプロシージャーと同一名を持つプロシージャーです。)プロシージャーが多重定義であることをブローカーが検出すると、例外が生じます。

プロシージャーに渡される OUT パラメーターは (内部であれ外部であれ)、 プロシージャーが受け取るときには常に、適正なタイプの NULL 値を含んでいます。 このことは、CALL 前のその値にかかわりなく生じます。

IN および INOUT パラメーターは、プロシージャーが受け取るときに NULL であることもあります。 たとえば、次はプロシージャー testProc に NULL を渡します。

DECLARE nullChar CHARACTER; 	CALL testProc( nullChar ); 	 

このことが生じるのは、CALL の前に nullChar が割り当てられなかったためです。

外部ストアード・プロシージャー

ストアード・プロシージャーを呼び出すには、 プロシージャーを ESQL でデータベース内、およびブローカーで定義する必要があります。ストアード・プロシージャーを C のような言語で書くときには、 NULL 標識を使用して、プロシージャーが確実にデータを正しく処理できるようにする必要があります。

EXTERNAL 文節では、修飾名または非修飾名を指定できます (修飾子はプロシージャーが定義されているデータベース・スキーマの名前です)。 スキーマ名を指定しない場合、 デフォルト・スキーマとしてデータベース接続ユーザー名が使用されます。 そのスキーマに必要なプロシージャーが存在しない場合には、 明示的なスキーマ名を指定する必要があります。

CREATE PROCEDURE ..... EXTERNAL NAME "mySchema.myProc";

プロシージャーが Oracle パッケージに属する場合には、 次の形式で明示的な修飾名を提供しなければなりません。

...EXTERNAL NAME "mySchema.myPackage.myProc"

動的スキーマ名を指定する場合以外は、 スキーマ、パッケージ、またはプロシージャー名に SQL ワイルドカードは使用できません。 ワイルドカード (1 つ以上の文字を表わすパーセント、 単一文字を表わす下線) は、 データベース・エスケープ文字が各ワイルドカードの前に組み込まれるように、 ブローカーが変更します。 ですから、データベースはリテラル文字としてこれらを受け取ります。

以下の文節はブローカーによって変更され、 mySchema.Proc¥_ はデータベースに渡されます (データベース・エスケープ文字は、 バックスラッシュであると想定しています)。

...EXTERNAL NAME "mySchema.Proc_"

プロシージャーの作成時に、単一パーセント文字でデータベース・スキーマ名を指定する場合、 ブローカーは特殊なケースとして扱い、ランタイム時に名前を解決します。 以下の文節は有効で、動的データベース・スキーマを識別します。

...EXTERNAL NAME "%.myProc"

この技法を使用する場合には、 このプロシージャーを呼び出して適切なスキーマを識別するのに使用する、 EXTERNAL SCHEMA 文節を CALL ステートメントに組み込む必要があります。

例 1: 外部ストアード・プロシージャーは、 Oracle および DB2 でストアード・プロシージャーを定義して、 呼び出す方法を例示しています。 データベース定義はデータベースによって異なりますが、ESQL は同じです。 ESQL でパラメーターに与えられる名前は、 データベース側で与えられる名前と必ずしも一致している必要はありません。 しかしながら、プロシージャーが持つ外部名は、パッケージまたはコンテナー指定も含めて、 データベースで定義された名前と一致している必要があります。 ExternalRoutineName に ID には許可されていない文字が含まれている場合は、 引用符で囲んでください。

すべての外部プロシージャーには、次の制約事項があります。

例 1: 外部ストアード・プロシージャー

ESQL Definition:
DECLARE inputParm CHARACTER;
DECLARE outputParm CHARACTER;
DECLARE inputOutputParm CHARACTER;

SET inputParm = 'Hello';
SET inputOutputParm = 'World';
CALL swapParms( inputParm, outputParm, inputOutputParm );

CREATE PROCEDURE swapParms (
    IN parm1 CHARACTER,
    OUT parm2  CHARACTER,
    INOUT parm3 CHARACTER
) EXTERNAL NAME dbSwapParms;

このストアード・プロシージャーを DB2 に登録するには、 以下のスクリプトをファイル (たとえば、 test1.sql) にコピーして実行します。

    db2 -td@ -vf test1.sql from DB2 command prompt.

-- DB2 Example Stored Procedure
DROP PROCEDURE dbSwapParms @                                       
CREATE PROCEDURE dbSwapParms
( IN in_param CHAR(32), 
    OUT out_param CHAR(32),
    INOUT inout_param CHAR(32))
LANGUAGE SQL
  BEGIN
        SET out_param = inout_param;  
        SET inout_param = in_param;
END @

このストアード・プロシージャーを Oracle に登録するには、 以下のスクリプトをファイル (たとえば、 test1.sql) にコピーして実行します。

     sqlplus <userid>/<password>  @test1.sql  
CREATE OR REPLACE PROCEDURE dbSwapParms
( in_param IN VARCHAR2 ,
    out_param OUT VARCHAR2, 
    inout_param IN OUT VARCHAR2 )
AS
  BEGIN
    out_param := inout_param;    
    inout_param := in_param;
END;
/

例 1 の予期される結果

次の結果を予期することができます。

  1. IN パラメーターの値は変更されない (定義によって変更することはできない)。
  2. OUT パラメーターの値は「World」になる。
  3. INOUT パラメーターの値は「Hello」に変更される。

例 2: ESQL プロシージャー

次の例は、例 1: 外部ストアード・プロシージャーと同じプロシージャーですが、 ESQL 内部プロシージャーとしてインプリメントされています。 このプロシージャーの CALL 構文も、結果と同様、同じです。

CREATE PROCEDURE swapParms (
    IN parm1 CHARACTER,
    OUT parm2  CHARACTER,
    INOUT parm3 CHARACTER )
  BEGIN
      SET parm2 = parm3;
      SET parm3 = parm1;
 END;

例 3: CREATE PROCEDURE の再帰的使用

次のプロシージャー例は、ツリーを解析し、 指定された開始点とそれより下のすべての場所を訪問し、 それが見つけたものを報告します。

 SET OutputRoot.MQMD = InputRoot.MQMD;

    DECLARE answer CHARACTER;
    SET     answer = '';

    CALL navigate(InputRoot.XML, answer);
    SET OutputRoot.XML.Data.FieldNames = answer;


    CREATE PROCEDURE navigate (IN root REFERENCE, INOUT answer CHARACTER)
    BEGIN
        SET answer = answer || 'Reached Field... Type:' || CAST(FIELDTYPE(root) AS CHARACTER)||
        ': Name:' || FIELDNAME(root) || ': Value :' || root || ': ';

        DECLARE cursor REFERENCE TO root;
        MOVE cursor FIRSTCHILD;
        IF LASTMOVE(cursor) THEN
            SET answer = answer || 'Field has children... drilling down ';
    ELSE             SET answer = answer || 'Listing siblings... ';
    END IF;

        WHILE LASTMOVE(cursor) DO
            CALL navigate(cursor, answer);
            MOVE cursor NEXTSIBLING;
    END WHILE;

        SET answer = answer || 'Finished siblings... Popping up ';
  END;

次の入力メッセージを与えた場合、

<Person><Name>John Smith</Name><Salary period='monthly' taxable='yes'>-1200</Salary></Person>

プロシージャーは次の出力を生成します。この出力は手操作でフォーマット設定されています。

  Reached Field... Type:16777232: Name:XML: Value :: Field has children... drilling down 
    Reached Field... Type:16777216: Name:Person: Value :: Field has children... drilling down 
    Reached Field... Type:16777216: Name:Name: Value :John Smith: Field has children... drilling down 
  Reached Field... Type:33554432: Name:: Value :John Smith: Listing siblings... Finished siblings... Popping up
  Finished siblings... Popping up 
    Reached Field... Type:16777216: Name:Salary:Value :-1200: Field has children... drilling down 
  Reached Field... Type:50331648: Name:period: Value :monthly: Listing siblings... Finished siblings... Popping up
  Reached Field... Type:50331648: Name:taxable: Value :yes: Listing siblings... Finished siblings... Popping up 
  Reached Field... Type:33554432: Name:: Value :-1200: Listing siblings... Finished siblings... Popping up 
  Finished siblings... Popping up 
    Finished siblings... Popping up 
    Finished siblings... Popping up

関連概念
ESQL

関連タスク
ESQL の開発
ストアード・プロシージャーの呼び出し

関連資料
構文の設定
ESQL のステートメント
CALL ステートメント