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 |
Default value | Default value if the input parameter is not specified |
Nullable |
This parameter is optional. Pass |
プロシージャのINOUT
パラメータに値が割り当てられていない場合、入力に割り当てられた値が保持されます。値が割り当てられていないOUT/RETURNパラメータは、デフォルトのNULL
。INOUT
/ 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
OUT
とINOUT
のパラメータ値を取得するもう一つの方法は以下のとおりです:
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
OUT
、INOUT
、ReturnValue
の各パラメータは、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
既存の仮想プロシージャを変更するステートメントは、作成時に使用するものと非常に似ています。基本的には、CREATE
をALTER
に置き換えて、新たにプロシージャを定義するだけです。
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 ブログの使用事例に焦点を当てた一連の投稿です:
- SQL Stored Procedures
- Real-World Use Cases and Benefits
- Use cases for Stored Procedures in CData Virtuality