CData Virtuality Server の仮想プロシージャは、リレーショナルデータベース管理システムのストアドプロシージャに似ています。同じクエリを時々書いて実行する必要がある場合、仮想プロシージャとして保存し、それを呼び出すだけです。仮想プロシージャを使用して、Query やその他のSQL コマンドの実行、一時テーブルの定義、一時テーブルへのデータの追加、結果セットの繰り返し処理、ループの使用、条件ロジックの使用を行うことができます。仮想プロシージャはスクリプトに似ていますが、スクリプトとは異なり、パラメータをサポートしているため、より強力です。

仮想プロシージャは、CData Virtuality Server プロシージャ言語を使用して定義されます。このセクションでは、仮想プロシージャの操作について説明します。プロシージャ言語で使用されるさまざまなステートメントの詳細な説明は、専用のセクションで参照できます。

Creating Virtual Procedures

仮想プロシージャについて留意すべき重要な点がいくつかあります。まず、仮想プロシージャは仮想スキーマ(viewsなど)でのみ作成できます。名前付きまたは匿名のプロシージャにすることができます。名前付きプロシージャはSQL ステートメント内で呼び出すことができますが事前に定義しておく必要があり、匿名プロシージャは定義時に呼び出されます。

第2に、仮想プロシージャはSQL とプロシージャSQL コマンドを含むことができます。プロシージャ本体内では、有効な文であれば何を使ってもかまいません。覚えておくべき重要な点は、プロシージャの中ではセミコロン(;)を区切り文字として使用し、2つのプロシージャはダブルセミコロン(;)で区切ることです。

The ; separator used inside the procedure cannot be configured. The ;; separator, however, can be changed to something else as long as the new separator is different from ;.

第3に、仮想プロシージャは0個以上の入力パラメータを持つことができます。結果を出力することが期待される場合、OUTパラメータかRETURNS()句(後者を使うことを推奨します)のどちらかを含まなければならず、ResultSet戻り値の型を持ちます。

こうして名前付き仮想プロシージャが作成されます:

CREATE [OR REPLACE] [PRIVATE] [VIRTUAL] PROCEDURE <schema_name>.<procedure_name>([[<parameter type>] <name> <data type> [DEFAULT <value>][<nullable>][RESULT], ...]) [RETURNS (<name> <data type> [<nullable>], ... )] AS
BEGIN
<procedure code>;
END;;

The PRIVATE keyword is reserved for internal use and should not be specified for user-defined procedures.

Procedure Parameters

パラメータは、仮想プロシージャを単純なスクリプトよりも強力なものにしています。<parameter type> <parameter name> <data type>  パラメータがどのようなものかを示すために、簡単なプロシージャの例を示します:

CREATE PROCEDURE views.anonymize(IN plain_text STRING NOT NULL) RETURNS (anonymized STRING NOT NULL) AS
BEGIN
SELECT LEFT(plain_text, 1) || '***' || RIGHT(plain_text, 1);
END;;

仮想プロシージャは、0個以上のIN / INOUTパラメータを取ることができ、任意の数のOUTパラメータとオプションのRETURNパラメータを持つこともできます。この場合も、プロシージャが結果を出力すると予想される場合は、OUTパラメータまたはRETURNS()のいずれかを含める必要があります。各入力には、ランタイム処理中に使用される以下の情報が含まれます:

Parameter

Description

Name

Name of the parameter

Data type

Design-time type of the input parameter

Parameter type

The evaluation strategy of the parameter. Can be one of IN , OUT or INOUT. Defaults to IN

Default value

Default value if the input parameter is not specified

Nullable

This parameter is optional. Pass NOT NULL  to reject NULL  values

プロシージャのINOUTパラメータに値が割り当てられていない場合、入力に割り当てられた値が保持されます。値が割り当てられていないOUT/RETURNパラメータは、デフォルトのNULLINOUT / OUT / RETURN出力値は、パラメータのNOT NULLメタデータに対して検証されます。

以下はその例です:

CREATE VIRTUAL PROCEDURE views.proc1(IN i INTEGER DEFAULT '1') RETURNS (i INTEGER) AS BEGIN
SELECT i;
END ;;
 
SELECT * FROM views.proc1();; --returns 1
SELECT * FROM views.proc1(2);; --returns 2

Procedure Out/Return Values

Procedure Definition

SYSADMIN.ProcedureParams.Type

OUT

Out

OUT with RESULT notation

ReturnValue

INOUT

InOut

RETURNS

ResultSet

プロシージャは ReturnValueパラメータを 1 つだけ持つことができます。ReturnValueパラメータは以下のステートメントで取得できます:

CREATE VIRTUAL PROCEDURE views.proc1(OUT i INTEGER RESULT) AS BEGIN
i = 1;
END ;;
 
BEGIN
DECLARE INTEGER var;
var = EXEC views.proc1();
SELECT var;
END;; -- returns 1

OUT , INOUT , ReturnValueパラメータは、以下のステートメントで取得できます:

CREATE VIRTUAL PROCEDURE views.proc_outs(OUT i INTEGER RESULT, OUT j INTEGER, INOUT k INTEGER) AS BEGIN
i = 1;
j = 2;
k = 3;
END ;;
 
SELECT * FROM (EXEC views.proc_outs()) x;; -- returns 1, 2, 3

OUTINOUTのパラメータ値を取得するもう一つの方法は以下のとおりです:

CREATE VIRTUAL PROCEDURE views.proc_in_inout(OUT i INTEGER, INOUT j INTEGER) AS BEGIN
i = 1;
j = 2;
END ;;
 
BEGIN
DECLARE INTEGER var1;
DECLARE INTEGER var2;
EXEC views.proc_in_inout(i => var1, j => var2);
SELECT var1, var2;
END;; -- returns 1, 2

ResultSetパラメータは以下のように求めることができます:

CREATE VIRTUAL PROCEDURE views.proc1() RETURNS(i INTEGER, j INTEGER) AS BEGIN
SELECT 1, 2
UNION
SELECT 3, 4;
END ;;
 
EXEC views.proc1();;
/* returns:
1 2
3 4
*/
 
-- or
SELECT * FROM (EXEC views.proc1()) x;;
-- returns the same

OUTINOUTReturnValueの各パラメータは、RETURNSの表記とともに使用しないでください。もしまだ一緒に使用されているのであれば、以下のステートメントを実行することで値を取得することができます:

CREATE VIRTUAL PROCEDURE views.proc_out_returns(OUT i INTEGER, INOUT j INTEGER) RETURNS(a INTEGER, b INTEGER, c INTEGER) AS BEGIN
i = 1;
j = 2;
SELECT 4, 5, 6;
END ;;
 
EXEC views.proc_out_returns();;
-- returns RETURNS result set: 4 5 6
SELECT * FROM (EXEC views.proc_out_returns()) x;;
-- returns RETURNS result set: 4 5 6
 
BEGIN
DECLARE INTEGER var1;
DECLARE INTEGER var2;
EXEC views.proc_in_inout(i => var1, j => var2);
SELECT var1, var2;
END;; -- returns OUT values 1, 2

Viewing Virtual Procedures

作成されたすべての仮想プロシージャは、SYSADMIN.ProcDefinitions スキーマに格納されます。既存のすべての仮想プロシージャを表示する必要がある場合は、次のコマンドを使用できます(プロシージャが持つ可能性のあるすべてのパラメータをリストしますが、興味のあるパラメータだけを示すこともできます):

SELECT "id", "name", "definition", "creationDate", "lastModifiedDate", "state", "failureReason", "creator", "modifier"
FROM "SYSADMIN.ProcDefinitions";;

仮想プロシージャは、READY(すべてがうまくいっており、プロシージャが使用できることを意味する)またはFAILED、2つの状態のいずれかを持つことができます。指定したプロシージャの状態だけを確認するには、このコマンドを使用します:

SELECT "state" FROM "SYSADMIN.ProcDefinitions" WHERE name = '<procedure_name>';;

Executing Virtual Procedures

仮想プロシージャは、CALLまたはEXECUTEコマンドを使用して実行できます。プロシージャに入力パラメータが定義されている場合は、リストまたはname=value 構文で指定することができます。プロシージャ内の他の列や変数との関連でパラメータ名があいまいな場合は、入力パラメータ名を完全なプロシージャ名でスコープします。

CALLコマンドを使用して仮想プロシージャを実行する方法です:

CALL <schema_name>.<procedure_name>[( ... )];;

仮想プロシージャ呼び出しが結果セットを返すと予想される場合は、他のSELECTと同様に動作するので、SELECTステートメントを使用できる多くの場所でこれを使用できます。この場合、以下のコマンドで手順を実行できます:

SELECT * FROM <schema_name>.<procedure_name>[( ... )]);;

あるいは、以下のコマンドを使うこともできます:

SELECT * FROM (CALL <schema_name>.<procedure_name>[( ... )]) AS x;;

Altering Virtual Procedures

既存の仮想プロシージャを変更するステートメントは、作成時に使用するものと非常に似ています。基本的には、CREATEALTERに置き換えて、新たにプロシージャを定義するだけです。

ALTER [VIRTUAL] PROCEDURE <schema_name>.<procedure_name>( ... ) [RETURNS ( ... )] AS
BEGIN
<procedure code>;
END;;

Deleting Virtual Procedures

これは非常に簡単なコマンドで実行できます:

DROP [VIRTUAL] PROCEDURE <schema_name>.<procedure_name>;;

See Also

Pass Key Value Pairs to a Procedure Using Multi-Dimensional Arraysプロシージャにキーと値のペアを渡すために多次元配列を使用する方法については、次の例を参照してください。

データ仮想化環境におけるプロシージャ型SQL - CData Virtuality ブログの使用事例に焦点を当てた一連の投稿です: