Stored Procedureについて紹介します

2022年、技術職としてシナジーマーケティングに新卒入社した前田です。業務でStored Procedureを使用することがあり、知見が深まったため記事にします。

前田侑磨（まえだゆうま）

2022年、シナジーマーケティングに新卒入社

※記事内で紹介するコードはPostgreSQL バージョン11を使用しています。

Stored Procedureとは

まず最初に、Stored Procedureとは何なのか。直訳すると...Stored(保存された)Procedure(手順)です。

RDBMSに保存する「ストアドプログラム」という機能の一種で、データベースに対する命令を1つにまとめて保存したものです。

Stored Procedureの特徴

Stored Procedureには以下の特徴があります。

複雑なSQL文の呼び出しを、論理的に一つの処理単位にまとめて、簡単にその名前で呼び出せる
一つのプロシージャには、複数のSQL文が含まれていたり、繰り返しや条件分岐などの制御構造をもつこともある
引数をとって処理をしたり、処理結果を返すことも可能
Stored Procedureで使われる言語は、RDBMSによって異なる

4点目について補足です。 Stored ProcedureはSQLで記述されますが、SQL92ではStored Procedureについて明確に定義されておらず、SQL99で明確に定義されました（後に紹介するStored FuctionもSQL99で定義されました）
そのため、それぞれのRDBMS製品ではStored Procedureを記述するためのSQLを独自の拡張SQLとして定義しています。
※独自の拡張SQLとして作成された結果、RDBMS間で互換性が失われてしまいました。

Stored ProcedureとStored Function

Stored Procedureと似た用語として、Stored Functionが存在します。
大きな特徴は戻り値があるかどうかです。
Stored Procedureは戻り値がなく、Stored Functionは戻り値があります。

Stored Procedureは戻り値がないため、実行された後の結果を直接取得することはできません。そのため、必要な情報を出力するためには、Stored Procedure内でSELECT文等を使用し、一時テーブルのデータを取得する必要があります。

他にも以下のような違いがあります。

	Stored Function	Stored Procedure
戻り値	ある	ない
実行方法	SELECTで実行	CALLで実行
トランザクション	利用できない	利用可能（ロールバックなど...）

FunctionとProcedureで書き方も異なります。

-- Function
CREATE OR REPLACE FUNCTION hello_func()
  RETURNS VOID AS $$
BEGIN
  RAISE NOTICE 'HELLO WORLD';
END;
$$ LANGUAGE plpgsql;

SELECT hello_func();

-- Procedure
CREATE OR REPLACE PROCEDURE hello_proc()
AS $$
BEGIN
  RAISE NOTICE 'HELLO WORLD';
END;
$$ LANGUAGE plpgsql;

CALL hello_proc();

この記事ではProcedureに焦点を当てます。

Stored Procedureの基本的な使い方

まず初めに、基本的なProcedureの使い方をご紹介します。

Procedureの作成

CREATE OR REPLACE PROCEDURE test()
AS $$
DECLARE
  -- 変数の宣言を記載する
BEGIN
  -- 手続き内容を記載する
EXCEPTION
  -- 例外処理を記載する
END; -- PL/pgSQLブロックの終了
$$ LANGUAGE plpgsql; -- 言語を指定する

CREATE OR REPLACE PROCEDURE ○○
- Procedureを作成もしくは更新するためのコマンドです。○○部分にProcedure名を指定します。（今回の場合はtest）
AS $$...$$
- PL/pgSQLブロックと呼ばれるプロシージャの実際のコードを指定します。$$で囲まれた部分にコードを記述します。
DECLARE
- 変数を宣言します。
BEGIN～END;
- Procedureの実行内容を記載します。
EXCEPTION
- 例外処理を記述します。
LANGUAGE plpgsql
- Procedureの言語を指定します。今回はPL/pgSQL言語を使用しています。

Procedureの実行

CALL プロシージャ名（引数）;

Procedureの確認

\df

Procedureの削除

DROP PROCEDURE 関数名;

以上が基本的なStored Procedureの使い方です。
では、次になぜ業務で使うことになったかをご紹介します。

Stored Procedureで〇〇してみた

実際にStored Procedureを作成してみました。
これから学習される方はぜひ参考にしてみてください。

まずはテーブルを作成します

CREATE TABLE animals (id SERIAL, name varchar(10));
CREATE TABLE users (id SERIAL, score int);

以下のテーブルを使用し、作業を進めます。

postgres=# \dt
                List of relations
 Schema |    Name     | Type  |      Owner
--------+-------------+-------+------------------
 public | animals     | table | postgres
 public | users       | table | postgres
(2 rows)

1. Stored ProcedureでSQL文をまとめてみた

タイトル通り、Stored ProcedureでSQL文をまとめてみました。
今回は例として動物の名前を格納するanimalsテーブルを使ってデータの追加・更新作業を行っていきます。
まとめる前のSQL文が以下になります

INSERT INTO animals (id, name) VALUES (1, 'cat'); --animalsテーブルにデータを追加
INSERT INTO users (id, score) VALUES (1, 5);  --usersテーブルにデータを更新

上記のSQLを実行するには二回INSERTする必要がありますね。これをStored Procedureを用いると以下のように書くことができます。

CREATE OR REPLACE PROCEDURE insert_data()
AS $$
BEGIN
  -- 手続き内容を記載する
  INSERT INTO animals (id, name) VALUES (1, 'cat');

  INSERT INTO users (id, score) VALUES (1, 5);

END; 
$$ LANGUAGE plpgsql;

\dfで今回作成したProcedureを確認し、CALLで実行してみます。

postgres=# \df
                          List of functions
 Schema |    Name     | Result data type | Argument data types | Type
--------+-------------+------------------+---------------------+------
 public | insert_data |                  |                     | proc
(1 row)

postgres=# CALL insert_data();
CALL
postgres=#

実行できました。

SELECTで複数テーブルに追加されているかを確認します。

postgres=# SELECT * FROM animals;
 id | name
----+------
  1 | cat
(1 row)

postgres=# SELECT * FROM users;
 id | score
----+-------
  1 |     5
(1 row)

postgres=#

無事追加されています。

このように、Stored Procedureを用いることで複数テーブルへの追加を一度に行うことができます。

2. Stored Procedureでトランザクション管理してみた

Stored Procedureでトランザクション管理を行ってみます。
今回は1から10の範囲内の得点をusersテーブルに追加する作業を行います。
一度usersテーブルにレコードを追加した後で、範囲外の数値の場合はロールバックさせるように実装しています。
※ 範囲外の数値の場合は INSERT しないように実装することも可能ですが、今回はロールバックの挙動を確認するため、あえてこのような実装にしています。

CREATE OR REPLACE PROCEDURE insert_number(NEW_NUMBER int)
AS $$
DECLARE
  -- 変数の宣言を記載する
BEGIN
  INSERT INTO users (score) VALUES (NEW_NUMBER);
  RAISE INFO '%を追加しました', NEW_NUMBER;

  IF NEW_NUMBER < 1 OR NEW_NUMBER > 10 THEN
    RAISE EXCEPTION '1~10の値を入力してください';
  END IF;

-- 例外が発生した場合の処理
EXCEPTION WHEN others THEN
  RAISE NOTICE 'ロールバックします';
  RAISE; 
END;
$$ LANGUAGE plpgsql;

1~10以外のNEW_NUMBERが指定された際、RAISE EXCEPTIONを使うことで処理を中断（ロールバック）させています。では、作成したProcedureをCALLで実行してみましょう。

postgres=# CALL insert_number(1);
INFO:  1を追加しました
CALL
postgres=# SELECT * FROM users;
 id | score
----+-------
  1 |     1
(1 row)

1の場合はテーブルに追加されています。
次に、1~10の範囲外の数字を入力し実行してみます。

postgres=# CALL insert_number(11);
INFO:  11を追加しました
NOTICE:  ロールバックします
ERROR:  1~10の値を入力してください
postgres=# SELECT * FROM users;
 id | score
----+-------
  1 |     1
(1 row)

範囲外の数字を入力するとロールバックされ、テーブルには登録されていません。

トランザクションに関する詳細はマニュアルをご確認ください。

まとめ

今回はStored Procedureの基本的な使い方について紹介しました。
Stored Procedureを使おうか悩んでいる方の助けになれば幸いです。

読んでいただき、ありがとうございました。

シナジーマーケティング株式会社では一緒に働く仲間を募集しています。

TECHSCORE BLOG

クラウドCRMを提供するシナジーマーケティングのエンジニアブログです。