ストアド・プロシージャの利用方法（Vertica11.1 新機能）

1 はじめに
2 ストアド・プロシージャの利用方法
- 2.1 ストアド・プロシージャの作成
- 2.2 ストアド・プロシージャの実行
3 ストアド・プロシージャの実行例
4 参考情報
5 検証バージョンについて
6 更新履歴

はじめに

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

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

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

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

構文

CREATE [ OR REPLACE ] PROCEDURE [ IF NOT EXISTS ]               
 [ [ データベース名 ]. スキーマ名. ] プロシージャ名( [ 引数リスト ] )  
 [ LANGUAGE 言語名 ]                                                  
 [ SECURITY { DEFINER | INVOKER } ]                                 
AS $$ [ <<ラベル>> ]
[ DECLARE
　 　　/* 宣言部 */ ]
BEGIN
　 　　/* 実行部 */
END　[　ラベル　];
$$;

CREATE [ OR REPLACE ] PROCEDURE [ IF NOT EXISTS ]

[ [ データベース名 ]. スキーマ名. ] プロシージャ名( [ 引数リスト ] )

[ LANGUAGE 言語名 ]

[ SECURITY { DEFINER | INVOKER } ]

AS $$ [ <<ラベル>> ]

[ DECLARE

　　　/* 宣言部 */ ]

BEGIN

　　　/* 実行部 */

END　[　ラベル　];

$$;

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の後のラベルは、先頭のラベルと同名にする必要があります。ラベルは、大文字と小文字の区別はしません。

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

<<outer_block>>
DECLARE
  x INT;
BEGIN
  <<inner_block>>
  DECLARE
    x INT;
  BEGIN
    x := 5;               --inner_block の x 値を指す
    outer_block.x := 10;  --outer_block の x 値を指す
  END inner_block;
END outer_block;

<<outer_block>>

DECLARE

x INT;

BEGIN

<<inner_block>>

DECLARE

x INT;

BEGIN

x := 5; --inner_block の x 値を指す

outer_block.x := 10; --outer_block の x 値を指す

END inner_block;

END outer_block;

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

<<test_block>>
BEGIN
 IF i > 100 THEN
   EXIT test_block;   -- test_block のブロックから抜ける
 END IF;
END test_block;

<<test_block>>

BEGIN

IF i > 100 THEN

EXIT test_block; -- test_block のブロックから抜ける

END IF;

END test_block;

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文を使用します。

構文

CALL [ [ データベース名. ] スキーマ名. ] プロシージャ名( [ 引数リスト ] );

1	CALL [ [ データベース名. ] スキーマ名. ] プロシージャ名( [ 引数リスト ] );

パラメータ	説明
[データベース名].スキーマ名	実行するプロシージャのデータベース名とスキーマ名を指定します。デフォルトのスキーマはpublicです。データベース名を指定する場合は、接続しているデータベース名を指定する必要があります。
プロシージャ名	実行するプロシージャの名前です。プロシージャの名前は、SQL識別子の規則に準拠している必要があります。
引数リスト	プロシージャに渡す引数を記述します。引数のデータ型はプロシージャ作成時に指定したINモードのデータ型に対応します。複数指定する場合はカンマで区切ります。

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

実行例1

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

SQL=> CREATE OR REPLACE PROCEDURE TEST_PROC(x INT,y VARCHAR)
SQL-> LANGUAGE PLvSQL AS $$
SQL$> BEGIN
SQL$>  RAISE NOTICE '1つ目の引数は %',x;
SQL$>  RAISE NOTICE '2つ目の引数は %',y;
SQL$> END;
SQL$> $$;

SQL=> CREATE OR REPLACE PROCEDURE TEST_PROC(x INT,y VARCHAR)

SQL-> LANGUAGE PLvSQL AS $$

SQL$> BEGIN

SQL$> RAISE NOTICE '1つ目の引数は %',x;

SQL$> RAISE NOTICE '2つ目の引数は %',y;

SQL$> END;

SQL$> $$;

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

SQL=> call TEST_PROC(1,'テスト');
NOTICE 2005:  1つ目の引数は 1
NOTICE 2005:  2つ目の引数は テスト
 TEST_PROC
-----------
         0
(1 row)

SQL=> call TEST_PROC(1,'テスト');

NOTICE 2005: 1つ目の引数は 1

NOTICE 2005: 2つ目の引数はテスト

TEST_PROC

-----------

(1 row)

実行例2

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

/*クエリの戻り値の管理が不要な場合、PERFORMコマンドで結果を破棄する必要があります。
  updateをした場合更新した行数が戻ってくるため、PERFORMコマンドを使用します。*/
SQL=> CREATE OR REPLACE PROCEDURE SALUP_PROC(x INT)
SQL-> LANGUAGE PLvSQL AS $$
SQL$> DECLARE
SQL$>  y INT;
SQL$> BEGIN
SQL$>   y := select sal*1.2 from employee where no=x;
SQL$>   PERFORM UPDATE employee set sal=y where no=x;
SQL$> END;
SQL$> $$;

/*クエリの戻り値の管理が不要な場合、PERFORMコマンドで結果を破棄する必要があります。

updateをした場合更新した行数が戻ってくるため、PERFORMコマンドを使用します。*/

SQL=> CREATE OR REPLACE PROCEDURE SALUP_PROC(x INT)

SQL-> LANGUAGE PLvSQL AS $$

SQL$> DECLARE

SQL$> y INT;

SQL$> BEGIN

SQL$> y := select sal*1.2 from employee where no=x;

SQL$> PERFORM UPDATE employee set sal=y where no=x;

SQL$> END;

SQL$> $$;

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

SQL=> \d employee
                                     List of Fields by Tables
 Schema |  Table   | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+----------+--------+-------------+------+---------+----------+-------------+-------------
 public | employee | no     | int         |    8 |         | t        | t           |
 public | employee | name   | varchar(80) |   80 |         | f        | f           |
 public | employee | job    | varchar(80) |   80 |         | f        | f           |
 public | employee | sal    | int         |    8 |         | f        | f           |

SQL=> select * from employee order by no;
 no |   name    |    job     | sal
----+-----------+------------+------
  1 | sato      | engineer   | 2500
  2 | takahashi | salesman   | 1600
  3 | itou      | salesman   | 1250
  4 | yamada    | manager    | 5000
  5 | suzuki    | salesman   | 3200
  6 | tanaka    | engineer   | 2850
  7 | yamamoto  | accounting | 1500
  8 | sasaki    | engineer   | 2600
  9 | kimura    | accounting | 2000
(9 rows)

SQL=> \d employee

List of Fields by Tables

--------+----------+--------+-------------+------+---------+----------+-------------+-------------

public | employee | no | int | 8 | | t | t |

public | employee | name | varchar(80) | 80 | | f | f |

public | employee | job | varchar(80) | 80 | | f | f |

public | employee | sal | int | 8 | | f | f |

SQL=> select * from employee order by no;

no | name | job | sal

----+-----------+------------+------

1 | sato | engineer | 2500

2 | takahashi | salesman | 1600

3 | itou | salesman | 1250

4 | yamada | manager | 5000

5 | suzuki | salesman | 3200

6 | tanaka | engineer | 2850

7 | yamamoto | accounting | 1500

8 | sasaki | engineer | 2600

9 | kimura | accounting | 2000

(9 rows)

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

SQL=> call SALUP_PROC(1);
 SALUP_PROC
------------
          0
(1 row)

SQL=> call SALUP_PROC(1);

SALUP_PROC

------------

(1 row)

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

SQL=> select * from employee order by no;
 no |   name    |    job     | sal
----+-----------+------------+------
  1 | sato      | engineer   | 3000 ★
  2 | takahashi | salesman   | 1600
  3 | itou      | salesman   | 1250
  4 | yamada    | manager    | 5000
  5 | suzuki    | salesman   | 3200
  6 | tanaka    | engineer   | 2850
  7 | yamamoto  | accounting | 1500
  8 | sasaki    | engineer   | 2600
  9 | kimura    | accounting | 2000
(9 rows)

SQL=> select * from employee order by no;

no | name | job | sal

----+-----------+------------+------

1 | sato | engineer | 3000 ★

2 | takahashi | salesman | 1600

3 | itou | salesman | 1250

4 | yamada | manager | 5000

5 | suzuki | salesman | 3200

6 | tanaka | engineer | 2850

7 | yamamoto | accounting | 1500

8 | sasaki | engineer | 2600

9 | kimura | accounting | 2000

(9 rows)

参考情報

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　本記事を公開

投稿タグ: ストアド・プロシージャ, ストアド, プロシージャ, stored procedures, stored, procedure

MENU

ストアド・プロシージャの利用方法（Vertica11.1 新機能）

はじめに

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

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

構文

CREATE ～の各パラメータの説明

AS $$ ～の説明

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

構文

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

実行例1

実行例2

参考情報

検証バージョンについて

更新履歴

カテゴリー

最近の投稿

MENU

ストアド・プロシージャの利用方法（Vertica11.1 新機能）

はじめに

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

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

構文

CREATE ～ の各パラメータの説明

AS $$ ～ の説明

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

構文

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

実行例1

実行例2

参考情報

検証バージョンについて

更新履歴

カテゴリー

最近の投稿

CREATE ～の各パラメータの説明

AS $$ ～の説明