はじめに

本記事では、Vertica 11.1から、使用可能になったストアド・プロシージャをご紹介します。ストアド・プロシージャは、Verticaに加工したデータを追加や更新したい場合などに有効です。以前は、データの加工をおこなう場合、ETLツール等を使用していましたが、今後はストアド・プロシージャについて使用可能です。

ストアド・プロシージャの利用方法

ストアド・プロシージャの作成

ストアド・プロシージャの作成は、CREATE PROCEDURE 文を使用します。

構文


CREATE ~ の各パラメータの説明
パラメータ説明
OR REPLACE同じ名前のプロシージャがすでに存在する場合、置き換えます。
既存のプロシージャに対する権限を持つユーザーとロールは、置換後も保持されます。

このオプションは、IF NOT EXISTS オプションと同時使用はできません。
IF NOT EXISTS同じ名前のプロシージャが存在する場合、作成されません。
同じ名前のプロシージャが存在し、このオプションを省略して作成した場合、VerticaはROLLBACKエラーメッセージを生成します。
どちらの場合も、同じ名前のプロシージャが存在する場合、プロシージャは作成されません。

このオプションは、OR REPLACE オプションと同時使用はできません。
[ データベース名 ].スキーマ名.プロシージャを作成するデータベースとスキーマを指定します。

デフォルトのスキーマはpublicです。
データベース名を指定する場合は、接続しているデータベース名を指定する必要があります。
プロシージャ名作成するプロシージャの名前を記述します。
プロシージャの名前は、SQL識別子の規則に準拠している必要があります。
引数リストカンマ区切りで、複数のパラメータを以下の形式で記述します。

 [ 引数モード ]  引数名  データ型


引数モード
現在サポートされているのは、INモードのみです。
INモードは、プロシージャ実行時に呼び出し側から値を受け取るモードです。
※OUT,INOUTモードは将来サポートされる予定です。

◇ 引数名
引数の名前です。

データ型
引数のデータ型を指定します。
※以下のデータ型は現在サポートされていないため、使用できません。
 ・DECIMAL
 ・NUMERIC
 ・NUMBER
 ・MONEY
 ・UUID
 ・GEOGRAPHY
 ・GEOMETRY
 ・Complex types
言語名以下のいずれかからプロシージャの言語を指定します。

・PLvSQL
・PLpgSQL

(デフォルトはPLvSQL)
SECURITY { DEFINER | INVOKER }プロシージャが呼び出された際に使用する権限を次のいずれかから決定します。

DEFINER:プロシージャ作成者の権限でプロシージャを実行
INVOKER:プロシージャを実行したユーザの権限でプロシージャを実行

※DEFINERの場合、プロシージャの実行は呼び出し元ではなく、プロシージャの作成者によって、実行されているように見えます。

(デフォルトはINVOKER)

AS $$ ~ の説明
<<ラベル>>

ラベルは、ブロック内で宣言された変数名を修飾する場合や、EXIT文でブロックから抜ける場合に使用します。
パラメータ説明
ラベルブロックにラベルを指定する場合は、DECLAREブロックの前にラベルを指定します。
Endの後のラベルは、先頭のラベルと同名にする必要があります。

ラベルは、大文字と小文字の区別はしません。

・内側のブロックで外側のブロックの変数を利用する場合の例


・EXIT文でブロック自体から抜ける場合の例


DECLARE

DECLAREブロックの宣言部は、変数やカーソルなどの定義を記述します。

変数名 [ CONSTANT ] データ型 [ NOT NULL ] [ := { 式 | SQLステートメント } ];
パラメータ説明
変数名以下の要件を満たしている変数名を指定します。
 ・SQL識別子の規則に準拠していること
 ・予約キーワードは使用不可
 ・同ブロック内に重複した変数名がないこと
CONSTANT変数を定数(不変)として定義します。
定数変数の値は、初期化中にのみ設定可能です。
データ型変数のデータ型を指定します。
以下のデータ型を除いたデータ型をサポートします。
 ・GEOMETRY
 ・GEOGRAPHY
 ・Complex types

オプションで、特定列のデータ型を参照することも可能です。

(例)
 <変数名> <表名>.<列名>%TYPE; 
NOT NULL変数がNULLを保持することができないよう定義します。

変数にNOT NULLが定義された場合、その変数は初期化する必要があります。
NULLを代入することはできません。
:= { 式 | SQLステートメント }式またはSQLステートメントを使用して変数を初期化します。
変数がNOT NULLで定義されている場合、式を使用する必要があります。

特定のブロック内で定義された変数は、順番に実行されます。
そのため、前に定義した変数を新しい変数を、定義する際に使用することができます。
(例)
DECLARE
x int :=3;
y int :=x;

BEGIN ~ END

BEGINブロックの実行部には、手続き型言語を使用して、プロシージャで実行する処理を記述します。
BEGIN~ENDが1つの処理単位となります。

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


作成したプロシージャを実行する際は、CALL文を使用します。

構文

パラメータ説明
[データベース名].スキーマ名実行するプロシージャのデータベース名とスキーマ名を指定します。

デフォルトのスキーマはpublicです。
データベース名を指定する場合は、接続しているデータベース名を指定する必要があります。
プロシージャ名 実行するプロシージャの名前です。
プロシージャの名前は、SQL識別子の規則に準拠している必要があります。
引数リストプロシージャに渡す引数を記述します。

引数のデータ型はプロシージャ作成時に指定したINモードのデータ型に対応します。
複数指定する場合はカンマで区切ります。

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

実行例1

引数を出力する TEST_PROC() プロシージャを作成します。


作成した TEST_PROC() プロシージャを実行します。


実行例2

employee表のある特定の従業員の給料(sal)を、1.2倍にする SALUP_PROC() プロシージャを作成します。


employee表の構成、現在格納されているデータを確認します。


SALUP_PROC() プロシージャを実行します。


no1の従業員の給与が、1.2倍になっていることが確認できます。


参考情報

Stored Procedures
https://www.vertica.com/docs/11.1.x/HTML/Content/Authoring/ExtendingVertica/StoredProcedures/StoredProcedures.htm

CREATE PROCEDURE (Stored)
https://www.vertica.com/docs/11.1.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATEPROCEDURE_stored.htm

CALL
https://www.vertica.com/docs/11.1.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CALLSTOREDPROCEDURE.htm

外部プロシージャ
http://vertica-tech.ashisuto.co.jp/cre_procedure/

検証バージョンについて

この記事の内容は、Vertica 11.1で確認しています。

更新履歴

2022/06/15 本記事を公開