PL/SQL（PL/pgSQL）ことはじめ

はじめに

アドベントカレンダーのネタ探しに邁進している今日、SQL実践入門を読みました。

とっても読みやすかった。とってもわかりやすかった。とっても勉強になった。
そして私の推しであるウインドウ関数がとっても褒められていて、私の推し活は間違っていなかったんだと喜ばしかったです。

それはさておき、この本からは「SQLは手続き型ではない」「手続き型から脱却しなさい」と聞こえます。
この声を聞いて、以下の文言を思い出しました。

PL/SQLは、SQLの手続き型拡張機能としてオラクル社が提供する言語であり、

なるほど、SQLが手続き型じゃないからこその「拡張」なのか、と。

そこで(?)、PL/SQLに入門します。

参考：
PL/pgSQL入門
【postgreSQL】動的SQLをpsqlだけで動かす
【PL/pgSQL入門】PL/pgSQLを勉強したのでまとめてみた

PL/SQLとは

SQLでの表現が難しい、以下を可能にします。

• 条件分岐
• ループ
• 例外処理
• 処理の再利用

DB操作に最適化したプログラミング言語、という理解でいいのではないかと思います。

使い所

PL/SQLは平たくいうとプログラミング言語です。
また、通常DBはそれ単体で使うことはなく、DBを利用するアプリケーションが存在します。
このアプリケーションは別のプログラミング言語で作られているのが一般的でしょう。
様々な制約の違いはあれど、PL/SQLでできることは他のプログラミング言語でもできます。
では、PL/SQLはいつ使うべきなのでしょうか？

一言でまとめると、「アプリケーション側で実現しようとすると”問題”がある場合」です。

大量データを処理する場合

単純なUPDATE文だったとしても、それをアプリケーション側から実行しようとした場合、「アプリ→DB」の通信が発生します。
一度に大量データを送信して通信回数を節約しようとする場合、通信帯域を圧迫することになりますし、大量データを一度に保持するのでメモリも大幅に使用してしまいます。
あるいは一回のデータ量は少なくして通信回数を増やす場合、一回の通信にはミリ秒のオーダーとはいえ時間がかかります。ちりつもで実行速度が問題になります。

ここでPL/SQLはどうかというと、DB内部で実行されるものですので、上記に記した通信に関する問題が一切なくなります。
また、DBと密に連携が取れるものですので、インデックスの利用などの最適化がしやすいのも利点です。

トランザクションの制御を厳密に実施したいとき

金融系や在庫管理ではトランザクション管理のミスや考慮もれ、障害発生時などに発生する不整合が致命的な問題になります。
アプリケーション側でも様々優秀なフレームワークが開発されており、個々の開発者がほとんど悩まずトランザクション管理を実装できる状況ではありますが、それでも「厳密に」実装するにはアプリケーション側からでは担保しきれない部分があります。
特にアプリケーションサーバーとデータベースサーバーが別々である昨今のシステムでは、アプリケーション側でのトランザクション管理には限界があります。

そこでPL/SQLの出番です。
やはりDB内部で実行できるというのが大きな利点です。サーバー間のやり取りという不安定な要素を取り除くことができます。
DBのロックや隔離も自在に取り扱うことができるため、アプリ経由と比べて排他制御も思いのまま実装できます。
また、一連の処理を原子的にまとめることもできるため、並列処理や突然の障害にも強い設計とすることができます。

データの制約の実装

複数のアプリケーションから同じデータベースにアクセスする場合、データの制約を共有することが難しかったりします。
そこで、PL/SQLを使って「ストアドファンクション」としてデータに対する固有の計算ロジックや生合成チェックを実装すれば、複数のアプリケーションで同じロジックを利用することができます。

トリガー

「このテーブルのこのカラムが更新されたら、こっちのテーブルも更新したい」
ということはよくあることで、この場合はDBの「トリガー」を利用するのが一般的です。
そして、「こっちのテーブルも更新したい」の部分はPL/SQLで実装されます。

アプリ側でテーブルに対する更新の監視を実装することを想像してください。面倒なのが明らかです。
そして実装もれが発生することも明らかです。
トリガーを使わない手はありませんね。

使うべきでない所

アプリでやるべきところはアプリで、DB（PL/SQL）が得意なところはDBで、というのが基本です。

ドメインロジック

ドメインロジックはアプリの中核であり、アプリが持つべきロジックです。
アプリの至る所で参照されるルールといえるので、これをDBに置いてしまうと中核の情報が散らばってしまうことになります。

複雑なビジネスロジック

PL/SQLは条件分岐とループを扱えるので、あらゆるロジックを実現可能です。
しかしその表現力はアプリケーションの実装に使われる多くのプログラミング言語に劣るのではないでしょうか。
それもそのはず、あくまでDB操作に最適化された言語だからです。ロジックの表現は「最低限」で良いはずです。

したがって、どんなロジックでも実現はできてしまいますが、可読性・変更容易性をはじめとした保守性も鑑みて、アプリとどちらで実装するべきかを検討するべきでしょう。

アプリ向けのデータ処理

例えばJSONの解析などです。PL/SQLのデータ処理はDBに特化していますから、DBにフィットしないデータ構造の取り扱いはアプリ側で実施するべきです。

DBに関係しない処理

例えばWebアプリケーションであれば、フロントに返すレスポンスを組み立てる処理などです。
DBから取得したデータを返す処理でも、レスポンスの組み立てはDBがなくても実行できます。
このような処理はPL/SQLの役割ではありません。

使い方

セットアップ済みなのがPostgreSQLしかなかったので、PostgreSQL用の手続き型拡張である「PL/pgSQL」を使います。

構文は「PL/SQL」とは異なりますが、できることはほぼ同じだと思います。

実行方法

\iに続けて、SQLを記載したファイル名を入力することで、このファイルを読み込んで実行してくれます。

# \i {作成したSQLファイル名}.sql

基本構文

sample.sql

DO $$
BEGIN
    -- ここに処理を書く
END;
$$;

DOで始めると無名関数（使い捨ての関数）として実行してくれます。
関数のはじめと終わりは$$で囲みます。
処理のはじめと終わりはBEGIN ... END;とします。

Hello, World

helloworld.sql

DO $$
BEGIN
    RAISE NOTICE 'Hello, World';
END;
$$;

# \i helloworld.sql
NOTICE:  Hello, World
DO

コンソール出力はRAISEを使います。
以下のログレベルを指定できます。
DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION

変数

variables.sql

DO $$
DECLARE
    v_num integer := 10;
BEGIN
    RAISE NOTICE '変数の値は%です', v_num;
    RAISE NOTICE '1足すと%です', v_num + 1;
END;
$$;

# \i variables.sql
NOTICE:  変数の値は10です
NOTICE:  1足すと11です
DO

変数はBEGINの前に宣言します。変数宣言はDECLAREで始めます。
RAISE内で出力する文字列に変数を埋め込むには%を使用します。

条件分岐

if.sql

DO $$
DECLARE
    v_num integer := 1;
BEGIN
    IF v_num > 0 THEN
        RAISE NOTICE '+';
    ELSIF v_num = 0 THEN
        RAISE NOTICE '0';
    ELSIF v_num < 0 THEN
        RAISE NOTICE '-';
    ELSE
        RAISE NOTICE 'v_numはnull';
    END IF;
END;
$$;

# \i if.sql
NOTICE:  +
DO

条件分岐はIF ~ THEN ELSIF ~ THEN ELSE ~という構文です。
（ELSIFって見慣れないですよね）

ループ

ループの終了条件がわかっている場合

while文みたいなものです。

loopexit.sql

DO $$
DECLARE
    i integer := 1;
BEGIN
    LOOP
        EXIT WHEN i > 5;
        RAISE NOTICE '%', i;
        i := i + 1;
    END LOOP;
END;
$$;

# \i loopexit.sql
NOTICE:  1
NOTICE:  2
NOTICE:  3
NOTICE:  4
NOTICE:  5
DO

LOOPで初めて、EXIT WHENの後に終了条件を書きます。
ループの最後はEND LOOPで終わります。

ループ回数やループ対象がわかっている場合

for文です。

for.sql

DO $$
DECLARE
    i integer := 1;
BEGIN
    FOR i IN 1..5 LOOP
        RAISE NOTICE '%', i;
    END LOOP;
END;
$$;

# \i for.sql
NOTICE:  1
NOTICE:  2
NOTICE:  3
NOTICE:  4
NOTICE:  5
DO

FORで初めて、ループする対象を指定し、LOOPを書きます。
ループの最後はEND LOOPで終わります。

例外処理

exception.sql

DO $$
DECLARE
    v_num integer := 0;
BEGIN
    v_num := 10 / v_num;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'error';
END;
$$;

# \i exception.sql
NOTICE:  error
DO

例外をキャッチしたい位置にEXCEPTIONを書きます。
扱いたい例外の名称（上記の例ではdevision_by_zero）の後、THENと書き、その例外用の処理を書きます。

例外の名称は以下で確認できます。

SQLの結果を変数として扱う

selectinto.sql

DO $$
DECLARE
    v_name text;
BEGIN
    SELECT name INTO v_name FROM users WHERE id = 1;
    RAISE NOTICE '%', v_name;
END;
$$;

# \i exception.sql
NOTICE:   田中
DO

SELECT句のカラム名に続けてINTO {変数名}とすることで、取得した値を変数に入れることができます。

おわりに

ふとPL/SQLを書こうと思うと、毎度「あれ、どうやって書くんだっけ」となります。
OracleとPostgresで記法が違うことも厄介な点です。
この記事は、相変わらずスラスラPL/SQLをかけない未来の自分へ捧ぐ覚書です。