VQL ストアドプロシージャの開発¶
Virtual DataPort にはストアドプロシージャを作成するための手続き型言語があります。ストアドプロシージャでは VQL ステートメントを呼び出し、条件やループなどのエレメントを使用します。これは VQL プロシージャ と呼ばれます。このページでは、その作成方法について説明します。
これらのプロシージャを Java ストアドプロシージャと比較した場合のメリットを以下に示します。
SQL 開発者は、ストアドプロシージャを開発するために Java を習得する必要はありません。
VQL プロシージャを変更するのは、Java プロシージャを変更する場合より簡単です。Java クラスを再コンパイルして .jar ファイルにパッケージ化し、それを再インポートする必要はありません。プロシージャの変更は、Design Studio、Administration Tool、または任意のクライアントアプリケーションで実行できます。
他のユーザーが VQL プロシージャをレビューしやすくなります。なぜなら、.jar ファイルを拡張子付きでダウンロードして開き、Java クラスを別のエディターに抽出する必要がないからです。
注釈
この機能を使用するには、 Denodo Enterprise または Denodo Enterprise Plus のバンドルが必要です。「 Denodo Platform - サブスクリプションバンドル 」も参照してください。
VQL プロシージャを作成するウィザードを開くには、メニュー [File] > [New...] > [Stored procedure] > [VQL stored procedure] をクリックします。
プロシージャをプログラムで (VQL シェルや別のアプリケーションから) 作成するには、 CREATE VQL PROCEDURE
コマンドを使用します。
CREATE [OR REPLACE] VQL PROCEDURE <name:identifier> (<variable:identifier> [IN/OUT/IN OUT:specification_type] <type:datatype> [, <variable:identifier> [IN/OUT/IN OUT] <type:datatype>]* )
AS (
[ <localVariable:identifier> <type:identifier> [; <localVariable:identifier> <type:identifier>]* ]
)
BEGIN
<command1>
<command2>
.
.
.
[EXCEPTION]
<exception handling part>
END
この機能は、ローカル変数が宣言されている部分、またはプロシージャロジックが定義されている部分 (入出力変数の定義の前以外) でのコメントの使用にも対応しています。VQL プロシージャ内では、複数の形式のコメントを使用できます。
単一行コメント: 2 個のハイフン (
--
)、ハッシュ (#
)、またはダブルスラッシュ (//
) でコメントを開始します。行の残りの部分がコメントになります。複数行コメント: スラッシュと 2 個のアスタリスク (
/**
) でコメントを開始し、2 個のアスタリスクとスラッシュ (**/
) で終了します。このタイプのコメントは複数行にまたがることができます。
最後に記述したコメントの形式では、複数行のコメントを定義できます。
データ型
変数の型を以下に示します。
数値: BIGINT、DECIMAL、DOUBLE PRECISION、FLOAT、INT、INTEGER、NUMBER、NUMERIC、REAL、SMALLINT
テキスト: CHAR、NCHAR、NVARCHAR、VARCHAR (この型の変数は、条件ブロック、繰り返しブロック、カーソルを定義するステートメント、DDL ステートメント、変数に代入される値の条件で、 テキスト関数 に対応しています。これらのコマンドはストアドプロシージャの本文に含まれます。各コマンドの詳細については、次のセクションで説明します)。
日付: DATE、TIMESTAMP、TIMESTAMP WITH TIMEZONE、TIMESTAMP WITH LOCAL TIMEZONE、INTERVAL YEAR TO MONTH、INTERVAL DAY TO SECOND
その他の型: BOOL、ROWTYPE
指定の型で、入力変数や出力変数を定義できます。入力変数では、ストアドプロシージャを実行する際に値を代入する必要があります。代入しない場合、指定が OUT であれば、その変数は実行結果として値を返します。
コマンド
VQL プロシージャで使用できるコマンドです (BEGIN
キーワードと END
キーワードの間)。
コマンドのリスト:
variable_name := value
この命令で、ローカル変数の宣言部で宣言された変数に値を代入することができます。変数に代入される値は、その変数と同じ型でなければならず、リテラルや式 (算術演算、値の連結など) を使用できる点が重要です。また、SQL%ROWCOUNT と呼ばれる特殊な変数をローカル変数に代入することもできます。この中には、INSERT、UPDATE、DELETE の実行によって影響を受ける行数が含まれるため、VQL ストアドプロシージャの本文でいずれかのステートメントを定義した後に使用すると便利です。値の代入コマンドの例¶<variableVarchar> := <variable1> || <variable2> <variableNumeric> := <numeric1> + <numeric2> <variable> := function(<literal> | <variableAux>)
IF/THEN/ELSE
If 構造を使用して、ある条件が TRUE のときにコマンドのリストを実行し、条件が FALSE になったときに別のコマンドを実行できます。
IF <condition> THEN
<list of commands>
[ELSE]
<list of commands>
END IF
CASE WHEN
CASE ステートメントには、IF-THEN-ELSE ステートメントの機能があります。いずれかの条件が満たされると、割り当てられたコマンドのリストが実行されます。
CASE [ identifier ]
WHEN condition1 THEN <list of commands>
WHEN condition2 THEN <list of commands>
[ELSE]
<list of commands>
END CASE
LOOP...END LOOP
LOOP ステートメントには、コードの一部を複数回実行する機能があります。オプションの EXIT WHEN ステートメントを使用して、ある条件が満たされたときにループを終了できます。EXIT WHEN では、条件に加えて NOTFOUND 属性を使用できます。この属性では、カーソルがそれ以上データ行を返さないときに、繰り返しループを終了できます。その場合、NOTFOUND 属性は 'true' に設定され、カーソル値が返されるループは終了します。
LOOP
<list of commands>
[EXIT WHEN <condition>/EXIT WHEN <cursorName>%NOTFOUND]
END LOOP
WHILE LOOP
別のタイプのループ。このコマンドは、ループ本体を何回実行するかわからない場合や、ループ本体が 1 回も実行されない可能性がある場合に使用します。
WHILE <condition> LOOP
<list of commands>
END LOOP
FOR LOOP
FOR LOOP ステートメントを使用して、コマンドのリストを指定の回数だけ実行できます。
FOR numericVariable IN inferiorLimit .. superiorLimit LOOP
<list of commands>
END LOOP
INSERT INTO <viewName:identifier> (column1, column2...) VALUES (identifier1/literal1, identifier2/iteral2...)
。INSERT ステートメントは、単一のレコードまたは複数のレコードをテーブルに挿入する場合に使用します。DELETE FROM <viewName:identifier> WHERE <condition>
。DELETE ステートメントは、テーブルから単一のレコードまたは複数のレコードを削除する場合に使用します。UPDATE <viewName:identifier> SET (column1, column2...) = (valueColumn1, valueColumn2...) WHERE <condition>
。UPDATE ステートメントは、テーブルの既存のレコードを更新する場合に使用します。RETURN ROW (variable1, variable2...) VALUES (value1, value2...)
。プロシージャのヘッダーで定義されている OUT または IN OUT 指定型の変数の値を返します。フォーム内の値を別のローカル変数またはリテラル値に割り当てることができます。この命令で、ストアドプロシージャの実行で得られる結果を確認できます。EXECUTE <DDL statement:literal>
。CREATE OR REPLACE VIEW...
やINSERT INTO <view> SELECT...
などの DDL ステートメントを実行します。このコマンドを使用するには、ステートメントをリテラルで記述します (ステートメントを一重引用符 (
'
) で囲み、エスケープします)。
CREATE OR REPLACE VQL PROCEDURE procedureWithExecutionCommand
(oid IN INTEGER)
AS (
varName VARCHAR;
)
BEGIN
EXECUTE 'CREATE OR REPLACE VIEW v2 AS SELECT 1';
END;
また、以下の例のように、
PARAMETERS
句 (EXECUTE <literal> PARAMETERS ( <parameterizedValueName> ) VALUES ( <value> )
) を使用して、ステートメントをパラメータ化することができます。
CREATE OR REPLACE VQL PROCEDURE procedureWithExecutionCommand
(oid IN INTEGER)
AS (
varName VARCHAR;
)
BEGIN
EXECUTE 'CREATE OR REPLACE VIEW v2 AS SELECT :param' PARAMETERS (param) VALUES(oid);
END;
VQL ストアドプロシージャではカーソルもサポートしています。カーソルは SELECT ステートメントを処理し、該当のクエリの結果をカーソルに保存します。ストアドプロシージャでカーソルを使用する手順は以下のとおりです。
宣言エリアでカーソル型の変数を定義します。構文は
CURSOR <cursoName:literal> IS '<SELECT statement>'
となります。クエリをリテラルで示さずにカーソルを作成することもできます。これにより、パラメータ化されたクエリでカーソルを作成できます。例:CURSOR <cursorName:literal> IS 'SELECT * FROM table WHERE x = :param1'
。カーソルを定義した後、SELECT ステートメントの実行で得られる結果を取得するには、最初に
OPEN <cursorName>
でカーソルを開く必要があります。パラメータ化されたクエリを使用して SELECT クエリでカーソルを作成した場合、パラメータ化された値に代入された値を示す必要があります。前の手順の例の場合、OPEN <cursorName> PARAMETERS (param1) VALUES (<literal>)
のようにする必要があります。カーソルを開くと、
FETCH <cursorName> INTO variables/list of variables
コマンドを使用して、得られた結果の取得を開始できます。この命令では、変数のリストにさまざまな列の値を挿入する行の値を取得できます。コマンドの使用が終わったらカーソルを閉じる必要があります。閉じるには
CLOSE <cursorName>
コマンドを使用できます。
注釈
DDL ステートメント (CREATE/DROP や DESCなど) は、カーソルの定義ではサポートされません。サポートされるのは SELECT ステートメントのみです。
VQL ストアドプロシージャでは、独自の例外を宣言できます。宣言するには、以下の手順に従います。
宣言エリアに
EXCEPTION
型の変数を定義します。この変数を定義したら、ストアドプロシージャの本文で
RAISE <variableName>
を使用して例外を発生させます。VQL ストアドプロシージャのオプションの EXCEPTION エリアで、この例外が発生したときに実行されるコマンドを定義します。構文は以下のようになります。
ストアドプロシージャの EXCEPTION セクションの構文¶EXCEPTION WHEN exception1 THEN <list of commands> [ WHEN OTHERS THEN ] <list of commands>
カスタム例外を処理するストアドプロシージャの例:
CREATE OR REPLACE VQL PROCEDURE procedureWithExceptionHandling
(param1 IN VARCHAR, param2 OUT VARCHAR)
AS (
name VARCHAR;
exception1 EXCEPTION;
)
BEGIN
name := 'Test';
IF name = 'Test' THEN
RAISE exception1;
END IF;
EXCEPTION
WHEN exception1 THEN
name := param1;
RETURN ROW (param2) VALUES (name);
END;
トランザクション
VQL ストアドプロシージャではトランザクションを管理できます。
以下は、VQL プロシージャからトランザクションを管理する場合のコマンドです。
BEGIN_TRANSACTION
: トランザクションを開始します。COMMIT
: 現在のトランザクションが終了し、トランザクション中に行われた変更がすべて適用されます。ROLLBACK
: トランザクションが終了し、変更がロールバックされます。
CREATE OR REPLACE VQL PROCEDURE procedureWithTransactionSupport
(param1 IN VARCHAR, param2 OUT VARCHAR)
AS (
name VARCHAR;
)
BEGIN
BEGIN_TRANSACTION;
INSERT INTO idu_view_vql_sp(idtable, name) VALUES (5, 'Name example');
ROLLBACK;
END;
CREATE OR REPLACE VQL PROCEDURE testTransactionsExceptionsHandling(paramName IN INTEGER)
AS (
// Procedure variables
varName VARCHAR;
)
BEGIN
// Procedure body
BEGIN_TRANSACTION;
// If insert fails (for whatever reason), EXCEPTION part will be executed
INSERT INTO testnvarchar_1 (id,charfield) VALUES (3, 'Test');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
「 Virtual DataPort のトランザクション 」のページで、トランザクションの機能について説明しています。
プロシージャの呼び出し
VQL ストアドプロシージャを呼び出すには、他のプロシージャと同じ構文を使用します。
「 Denodo のストアドプロシージャの呼び出し 」を参照してください。
例
例 1
CREATE OR REPLACE VQL PROCEDURE procedure_max_value
(oid IN INTEGER, valormax OUT INTEGER)
AS (
a INTEGER;
b INTEGER;
c INTEGER;
abcmax INTEGER;
)
BEGIN
a:=oid;
b:=(a/5);
c:=(b*7);
IF a > b THEN
IF a > c THEN
abcmax:=a;
ELSE
abcmax:=c;
END IF;
ELSE
IF b > c THEN
abcmax:=b;
ELSE
abcmax:=c;
END IF;
END IF;
RETURN ROW (valormax) VALUES (abcmax);
END;
ストアドプロシージャが呼び出された場合、ユーザーが渡した値に応じて最大値を返すストアドプロシージャ。
例 2
CREATE OR REPLACE VQL PROCEDURE procedureUsingCursors
(idInternet OUT INTEGER, ttime OUT TIMESTAMP, idtax OUT VARCHAR)
AS (
CURSOR cursorData IS 'SELECT iinc_id, ttime, taxid FROM internet_inc WHERE specific_field1 > 1';
rinternet cursorData%ROWTYPE;
)
BEGIN
OPEN cursorData;
LOOP
FETCH cursorData INTO rinternet;
RETURN ROW ( idInternet, ttime, idtax) VALUES (rinternet.iinc_id, rinternet.ttime, rinternet.taxid);
EXIT WHEN cursorData%NOTFOUND;
END LOOP;
CLOSE cursorData;
END
;
この例は、カーソルの使用方法を示しています。プロシージャは、カーソルがそれ以上データ行を返さないと LOOP ステートメントを終了します。
プロシージャ実行時のデバッグ詳細の取得
VQL ストアドプロシージャを実行する際に、実行ステータスに関する情報を段階的に取得する場合は、この特定の機能に関連するログを有効にします。ログを有効にするには、取得する詳細レベルに応じて、VQL ストアドプロシージャに関連するログを INFO または DEBUG に設定します。
CALL logcontroller( 'com.denodo.vdb.engine.storedprocedure.CommandExecutorVisitorImpl', 'DEBUG');