ストアドプロシージャーの実行の例

データベース用 Anypoint Connector (Database Connector) の Stored procedure 操作では、データベースでストアドプロシージャーを呼び出します。ストアドプロシージャーで 1 つまたは複数の ResultSet インスタンスが返される場合、パフォーマンスやメモリの問題につながる可能性のある結果の先制消費を回避するため、ストリーミングが自動的に割り当てられます。

次の例は、ストアドプロシージャーのコール可能なステートメントと日付、および [Input parameters (入力パラメーター)]、[Output parameters (出力パラメーター)]、[In out parameters (入出力パラメーター)] 項目を設定してストアドプロシージャーを呼び出す方法を示しています。

ストアドプロシージャーのコール可能なステートメント

Database Connector は、次の形式のコール可能なステートメントの使用をサポートしています。

{ call procedureName(:param1, :param2, …, :paramN) }

:paramN は、ストアドプロシージャー宣言の Nth パラメーターと一致します。ストアドプロシージャーの名前と最初の括弧の間に空白がないことを確認してください。すべての JDBC ドライバーが JDBC 仕様形式に準拠している必要がありますが、一部のドライバーには独自の仕様があります。ストアドプロシージャーをコールする方法についての詳細は、JDBC ドライバーのドキュメントを確認してください。たとえば、Snowflake JDBC ドライバーでは、次のように中括弧 { および } を使用せずにコール可能なステートメントを設定する必要があります。

call procedureName(:param1, :param2, …, :paramN)

ストアドプロシージャーにパラメーターを渡す

パラメーターをストアドプロシージャーに渡す場合、次のようにします。

ストアドプロシージャーのすべてのパラメーター (デフォルト値が設定される省略可能なパラメーターも含む) を配置する。
ストアドプロシージャーで定義した順序でパラメーターを配置する。
パラメーターをパラメーター化し、文字列や数値などをハードコード化しない。

次の例は、SYSTEM.employees という名前のテーブルと createEmployee という名前のストアドプロシージャーを作成するときに初期化された Oracle データベースを示しています。

    CREATE TABLE SYSTEM.employees(
        employee_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
        employee_name VARCHAR2(100),
        employee_age INTEGER,
        employee_birthday TIMESTAMP,
        PRIMARY KEY(employee_id)
    );


    CREATE PROCEDURE createEmployee(e_name VARCHAR2, e_age NUMBER, e_birth_date DATE) AS
    BEGIN
        INSERT INTO SYSTEM.employees(employee_name, employee_age, employee_birthday) VALUES(e_name, e_age, e_birth_date);
    END;

Database Connector はコール可能なステートメントでの名前付きパラメーターをサポートしていないため、名前でパラメーターを一致させようとしても機能しません。例:

{ call createEmployee(e_age ⇒ :age, e_birth_date ⇒ :date, e_name ⇒ :name) }

パラメーターは、適切な順序で指定する必要があります。例:

{ call createEmployee(:name, :age, :date) }.

ストアドプロシージャーのデータの使用

Database Connector は、エンジン固有の組み込み関数の使用をサポートしていません。たとえば、Oracle データベースでプロシージャーをコールする前に日付を特定の形式に変更する場合、次のアプローチは機能しません。

{ call createEmployee(e_name ⇒ :name, employee_age ⇒ :age, e_birth_date ⇒ TO_DATE(:date, 'YYYY-MM-DD HH:mm:ss')) }.

コール可能なステートメントを呼び出す前に、すべてのデータ変換で DataWeave を使用します。前の例を使用して、次の変換を実行します。

Studio のフローで、[Stored procedure] 操作を選択します。
操作設定画面で、実行する SQL クエリを [SQL Query Text (SQL クエリテキスト)] 項目に設定します。例:

{ call createEmployee(:name, :age, :date) }
[Input parameters (入力パラメーター)] 項目を次の DataWeave コードに設定します。

%dw 2.0
  output application/json
  fun format(d: DateTime) = d as String { format: "yyyy-MM-dd HH:mm:ss" }
  ---
  { 'date': format(|2019-10-31T13:00:00.000Z|), 'name': 'rick', 'age': 60 }

[source,xml,linenums]

次のスクリーンショットは、Studio の設定を示しています。

Figure 1. ストアドプロシージャーの日付の設定

XML エディターでは、DataWeave 変換を使用した <db:input-parameters> は次のように記述されます。

<db:stored-procedure doc:name="Create Employee" config-ref="Database_Config">
    <db:sql>{ call createEmployee(:name, :age, :date) }</db:sql>
		<db:input-parameters>
		 	<![CDATA[#[%dw 2.0
				output application/json
				fun format(d: DateTime) = d as String { format: "yyyy-MM-dd HH:mm:ss" }
				---
				{ 'date': format(|2019-10-31T13:00:00.000Z|), 'name': 'rick', 'age': 60 }
			]]]>
		</db:input-parameters>
	</db:stored-procedure>

Stored Procedure 操作の [Input Parameters (入力パラメーター)] 項目を設定する

[Input parameters (入力パラメーター)] 項目で、JDBC プリペアードステートメントに設定する入力パラメーターの名前をキーとするマップを作成する値を指定します。SQL テキスト内でコロンプレフィックスを使用して各パラメーターを参照します (例: where id = :myParamName)。マップの値には、各パラメーターの実際の割り当てが含まれます。パラメーターに値を渡す場合、次のようにします。

一重引用符を使用して各パラメーターを定義する (例: 'paramName': value)。
デフォルト値には、DataWeave または変数を使用する。これにより、null または空の文字列を渡しても、Mule Runtime Engine によってストアドプロシージャーのデフォルト値が転送されなくなります。
入力値を入力するときはパラメーターを使用する必要があります。Stored Procedure 操作内の値をハードコードしないでください。

[Input parameters (入力パラメーター)] 項目を設定する手順は、次のとおりです。

Studio のフローで、[Stored procedure] 操作を選択します。
操作設定画面で、実行する SQL クエリを [SQL Query Text (SQL クエリテキスト)] 項目に設定します。例:

{ call doubleMyInt(:myInt) }
[Input parameters (入力パラメーター)] 項目を {'myInt' : payload} に設定します。

XML エディターでは、<db:stored-procedure> および <db:input-parameters> 設定は次のように記述されます。

<db:stored-procedure config-ref="dbConfig">
    <db:sql>{ call doubleMyInt(:myInt) }</db:sql>
    <db:input-parameters>
        #[{'description' : payload}]
    </db:input-parameters>
</db:stored-procedure>

Stored Procedure 操作の [In Out Parameters (入出力パラメーター)] 項目を設定する

[In out parameters (入出力パラメーター)] 項目で、JDBC プリペアードステートメントに設定するパラメーター (入力と出力の両方) の名前をキーとするマップを作成する値を指定します。SQL テキスト内でコロンプレフィックスを使用して各パラメーターを参照します (例: where id = :myParamName)。マップの値には、各パラメーターの実際の割り当てが含まれます。

[In out parameters (入出力パラメーター)] 項目を設定する手順は、次のとおりです。

Studio のフローで、[Stored procedure] 操作を選択します。
操作設定画面で、実行する SQL クエリを [SQL Query Text (SQL クエリテキスト)] 項目に設定します。例:

{ call doubleMyInt(:myInt) }
[In out parameters (入出力パラメーター)] 項目を Edit inline に設定します。
プラス記号 (+) をクリックして、新しい値を追加します。
新しいウィンドウで、[Key (キー)] 項目を myInt、[Value (値)] 項目を 3 に設定します。

次のスクリーンショットは、Studio の設定を示しています。

「In out parameters (入出力パラメーター)」項目を使用した Stored procedure 操作の設定

Figure 2. [In Out parameters (入出力パラメーター)] を使用したストアドプロシージャーの設定

XML エディターでは、<db:stored-procedure> および <db:in-out-parameter> 設定は次のように記述されます。

<db:stored-procedure config-ref="dbConfig">
  <db:sql>{ call doubleMyInt(:myInt) }</db:sql>
  <db:in-out-parameters>
      <db:in-out-parameter key="myInt" value="3"/>
  </db:in-out-parameters>
</db:stored-procedure>

Stored Procedure 操作の [Input Parameters (入力パラメーター)] 項目と [Output Parameters (出力パラメーター)] 項目を設定する

[Output parameters (出力パラメーター)] 項目で、JDBC プリペアードステートメントに設定する値のリストを指定します。SQL テキスト内でコロンプレフィックスを使用して各パラメーターを参照します (例: call multiply(:value, :result))。

[Input parameters (入力パラメーター)] 項目と [Output parameters (出力パラメーター)] 項目を設定する手順は、次のとおりです。

Studio のフローで、[Stored procedure] 操作を選択します。
操作設定画面で、実行する SQL クエリを [SQL Query Text (SQL クエリテキスト)] 項目に設定します。例:

{ call multiplyInts(:int1, :int2, :result1, :int3, :result2) }
[Input parameters (入力パラメーター)] 項目を次のように設定します。
```
{
    'int1' : 3,
    'int2' : 4,
    'int3' : 5
}
```
[Output parameters (出力パラメーター)] 項目を Edit inline に設定します。
プラス記号 (+) をクリックして、新しい値を追加します。
新しいウィンドウで、[Key (キー)] 項目を result1、[Type (型)] 項目を INTEGER に設定します。
ステップ 5 と 6 を繰り返して新しい値を追加し、[Key (キー)] 項目を result2、[Type (型)] 項目を INTEGER に設定します。

次のスクリーンショットは、Studio の設定を示しています。

「Input parameters (入力パラメーター)」項目と「Output parameters (出力パラメーター)」項目を使用した Stored procedure 操作の設定

Figure 3. [Input parameters (入力パラメーター)] と [Output parameters (出力パラメーター)] を使用したストアドプロシージャーの設定

XML エディターでは、<db:input-parameters> および <db:output-parameters> 設定は次のように記述されます。

<db:stored-procedure config-ref="dbConfig">
    <db:sql>{ call multiplyInts(:int1, :int2, :result1, :int3, :result2) }</db:sql>
    <db:input-parameters>
        #[{
            'int1' : 3,
            'int2' : 4,
            'int3' : 5
        }]
    </db:input-parameters>
    <db:output-parameters>
        <db:output-parameter key="result1" type="INTEGER"/>
        <db:output-parameter key="result2" type="INTEGER"/>
    </db:output-parameters>
</db:stored-procedure>

結果セットを返す

結果セットを返すストアドプロシージャーをコールすると、結果セットはデフォルトでペイロードに割り当てられます。返される結果セットを変更するには、Stored procedure 操作設定の [Advanced (詳細)] タブで [Target Variable (対象変数)] または [Target Value (対象値)] を設定します。

次の例は、languages という名前のテーブルと、結果セットを返す myProc という名前のストアドプロシージャーを作成するときに初期化される MSSQL データベースを示しています。

CREATE TABLE LANGUAGES (NAME VARCHAR(128), SAMPLE_TEXT VARCHAR(max));

CREATE PROCEDURE myProc AS
   SELECT * FROM LANGUAGES l;
RETURN;

Stored procedure 操作設定画面で、[SQL Query Text (SQL クエリテキスト)] を { call myProc() } に設定します。
[Advanced (詳細)] タブで、[Target Variable (対象変数)] を aResult に設定します。

「Target Variable (対象変数)」項目を使用した Stored procedure 操作の設定

XML エディターでは、<db:stored-procedure> 設定は次のように記述されます。

<db:stored-procedure config-ref="MsSQL" target="aResult">
  <db:sql ><![CDATA[{ call myProc() }]]></db:sql>
</db:stored-procedure>

ストアドプロシージャーの実行の例

ストアドプロシージャーのコール可能なステートメント

ストアドプロシージャーにパラメーターを渡す

ストアドプロシージャーのデータの使用

Stored Procedure 操作の [Input Parameters (入力パラメーター)] 項目を設定する

Stored Procedure 操作の [In Out Parameters (入出力パラメーター)] 項目を設定する

Stored Procedure 操作の [Input Parameters (入力パラメーター)] 項目と [Output Parameters (出力パラメーター)] 項目を設定する

結果セットを返す

関連情報