ストアドプロシージャの使い方（PostgreSQL）

ストアドプロシージャとは

　データベースに自分で定義して（データベースサーバに貯めて（stored））、後で使うことができる手続き（procedure、戻り値のない関数）です。使用することで、何回も同じSQL文を打つ必要がなくなったり、クライアントとサーバの間の通信量を減らしたりできます。

実行例

　あるテーブルの数値を取り出して（SELECT文を実行して）計算し、結果を別のテーブルに格納（INSERT文を実行）する例を見てみましょう。
　以下の2つのテーブル（operandテーブルとanswerテーブル）を使用します。operandテーブルのoperand1をoperand2で割った商を、answerテーブルのquotient列に入れて、余りを、answerテーブルのremainder列に入れるストアドプロシージャを作成してみましょう。

operandテーブル

id	operand1	operand2
1	1	5
2	2	5
3	3	5
4	4	5
5	5	5

answerテーブル

id	quotient	remainder

ストアドプロシージャの定義

　まずストアドプロシージャを作成します。PL/pgSQLの文法に関してはPostgreSQLのマニュアルを見てください。

CREATE PROCEDURE div_proc(int) AS $$     --引数に計算したい行のidを入れる
DECLARE             --変数の宣言
ope1  int;           --operandテーブルのopernd1を入れるための変数
ope2  int;           --operandテーブルのopernd1を入れるための変数
BEGIN               --手続き内容を以下に書く
SELECT operand1 INTO ope1 FROM operand WHERE id = $1;    --operand1をope1に入れる
SELECT operand2 INTO ope2 FROM operand WHERE id = $1;    --operand2をope2に入れる
INSERT INTO answer VALUES ($1, div(ope1, ope2), mod(ope1, ope2));   --計算結果をanswerテーブルに入れる
END;               --手続き内容終わり
$$
LANGUAGE plpgsql;　--言語を指定

CREATE PROCEDURE

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

　作成したストアドプロシージャを利用します。関数を使う時は、SELECTを使用しますが、ストアドプロシージャの場合はCALLを使用します。

書き方：CALL ストアドプロシージャ名;

postgres=# CALL div_proc(1);
CALL
postgres=# CALL div_proc(2);
CALL
postgres=# CALL div_proc(3);
CALL
postgres=# CALL div_proc(4);
CALL
postgres=# CALL div_proc(5);
CALL

answerテーブル

id	quotient	remainder
1	0	1
2	0	2
3	0	3
4	0	4
5	1	0

今回のストアドプロシージャの利点

・長いSQL文を何回も書く必要がなくなりました。
・SELECT文の実行とINSERT文の実行を1回にまとめて行え、通信回数を減らせました（idを知っていることが前提ですが・・・）。

まとめ

　ストアドプロシージャを使用することで実行が楽にできるようになり、クライアントとサーバー間の通信回数を減らすことができました。
読んでくださり、ありがとうございました！

参考

PostgreSQL11のPL/pgSQLのマニュアル
https://www.postgresql.jp/document/11/html/plpgsql.html