Upgrade to Pro — share decks privately, control downloads, hide ads and more …

PostgreSQLで手続き言語を動かす PL/pgSQL入門

PostgreSQLで手続き言語を動かす PL/pgSQL入門

まぐろ

June 23, 2024
Tweet

More Decks by まぐろ

Other Decks in Programming

Transcript

  1. ◦×ゲームといえども…… • 指定した位置に◦と×を交互に記入 • すでに◦または×が記入された位置には新しく記入することは できない • 縦横斜めのどれか1列に、先に同じマークを記入した方が勝ち • 記入する場所がなくなり、どちらも1列も揃えていない場合は

    引き分け • 画面に現在の◦×の状況を表示 • 少なくともこのくらいのルールがあるが、これらすべてを単純 なSQLで実現するのは相当難しい…… • SQLでプログラミングができれば……
  2. SQLで手続き処理を書く言語 – PL/pgSQL • SQLはプログラミング言語ではなく問い合わせ言語であり、SQL を拡張し、手続き処理をできるようにしたのがPL/pgSQL • PostgreSQLで手続き処理を作成する際に最もよく使用される言 語であり、ストアドファンクション・ストアドプロシージャの 作成にも使用される

    • PLはProcedural Language(手続き型言語)の略 • OracleのPL/SQLを参考にしており、書き方がよく似ている • 実はPL/pgSQL 以外にもPostgreSQLで手続き処理を書く言語はあ りますが、デフォルトでは使用できず、拡張機能として作成さ れているものもあるため、今回は説明を省きます
  3. ストアドファンクション/プロシージャ • DB内に関数(function)や手続(procedure)をプログラミング して保存(stored)しておき、必要時に呼び出すことで決まっ た処理をさせることができます • 主要なRDBMS製品には搭載されており、PostgreSQLにも機能が 存在し、PL/pgSQLで作成されます • 特に有名で、使用されていることが多いのはOracle

    • 特に、昔からOracleを使い倒しているところでは、必ずと言っていいほ ど使われている印象です • バッチ処理だけではなくオンライン処理もストアドで書かれていて、 アプリ側にはデータに関する処理がほとんどないところもありました
  4. ストアドファンクション/プロシージャ • ストアドファンクション • SQLで実行する関数を作成する • ◦×ゲームもストアドファンクションで作成しました • PostgreSQL 10まではこれしかありませんでした

    • ストアドプロシージャ • SQLから呼び出す関数ではなく一連の手続きを定義する • PostgreSQL 11から実装されました • 機能や定義の方法だけではなく、呼び出し方もストアドファンクショ ンとは異なります
  5. 準備:◦×ゲーム用テーブルの作成 CREATE TABLE marubatsu_field ( row_id integer NOT NULL CHECK

    (1 <= row_id AND row_id <= 3), column_id integer NOT NULL CHECK (1 <= column_id AND column_id <= 3), mark char(1), PRIMARY KEY (row_id, column_id) ); 1レコードが◦×のマス一つを表しています。 mark (◦か×か) column_id (列番号) row_id (行番号) null 1 1 ◦ 2 1 null 3 1 × 1 2 null 2 2 … 3 2 1 ◦ 1 × 2 3 ◦×ゲーム表示イメージ
  6. CREATE FUNCTION  CREATE FUNCTION構文の基本。オプションはたくさんありますが、よく使うも のを抜き出すと以下のようになります。 CREATE OR REPLACE FUNCTION

    marubatsu(rownum integer, columnnum integer) RETURNS text AS $$ 関数本文 ←ここをPL/pgSQLで記述する $$ LANGUAGE plpgsql;
  7. CREATE FUNCTIONの要素  CREATE FUNCTION:ストアドファンクションを作成するCREATE文。「CREATE OR REPLACE」とすることで、既存の関数を上書きできます。UPDATEはありません。  関数名:関数の名前です。◦×ゲームでは「marubatsu」としています。既存 の名前と重複しても、引数が異なる場合は別の関数として作成されます。

     引数:◦×ゲームでは「rownum」 と「columnnum」の2つのintegerを引数にし ています。引数の名前は実は必須ではありませんが、関数本文の中で変数とし て使用できるので便利です。引数のデータ型の指定は必須です。  戻り値:戻り値が1つでも「RETURNS」。戻り値はデータ型を指定します。 ◦×ゲームではtext型を戻り値としています。戻り値が不要な関数の場合、void を指定します。  LANGUAGE:ストアドファンクションをプログラミングする言語です。 PL/pgSQLの場合は「plpgsql」と指定します。
  8. PL/pgSQLの構造  PL/pgSQLは構造がはっきりしています。メジャーなプログラミング言語に慣れ ている人には面倒かもしれません。  宣言部:変数を宣言する。変数が不要な場合、記述は不要。  実行部:実際に行う処理を記述する。  例外処理部:実行部で発生した例外を

    処理する記述をする。 例外が発生しない、またはそのまま 例外を呼び出し元に返してもいい場合 記述は不要。  ブロックは入れ子にすることも可能。 例外の処理を限定的にできる。
  9. ◦×ゲームの宣言部 DECLARE row_count integer; init_row_num CONSTANT integer := 3; init_column_num

    CONSTANT integer := 3; sente_mark marubatsu_field.mark%TYPE; gote_rec RECORD; display_cur CURSOR FOR SELECT row_id, column_id, COALESCE(mark, ' ') AS mark FROM marubatsu_field ORDER BY row_id, column_id; display_text text := '';
  10. ◦×ゲームの実行部(引数チェック) BEGIN -- 実行部の開始 -- 引数チェック IF rownum < 1

    OR rownum > 3 THEN RAISE EXCEPTION '行指定の数字が有効範囲外を指定して います。'; ELSIF columnnum < 1 OR columnnum > 3 THEN RAISE EXCEPTION '列指定の数字が有効範囲外を指定して います。'; END IF;
  11. ◦×ゲームの実行部(引数チェック)  原則、変数は宣言部で宣言しなければ実行部で使用することはできません。と 言いましたが、「rownum」と「columnnum」は宣言部では宣言していませんが、 引数の名前は宣言部で宣言しなくても実行部で使用できます。  条件分岐としてはIFまたはCASE文が使えます。  IF 条件文

    THEN 処理; ELSIF 条件 THEN 処理; END IF; とつなげます。セミコロンを付ける位置に気をつけてください。PL/pgSQLでは 文の最後、ブロックの最後にセミコロンを付けるのが原則です。  2つ以上の条件はANDやORでつなげられます。  RAISEはPL/pgSQLで使用し、メッセージを報告し、エラーを発生することができ ます。RAISE EXCEPTIONでいきなり例外を発生させることになります。
  12. ◦×ゲームの実行部(テーブル初期化) -- 行が初期化行数じゃなければ初期化 SELECT COUNT(*) INTO row_count FROM marubatsu_field; IF

    row_count != (init_row_num * init_column_num) THEN DELETE FROM marubatsu_field; FOR i IN 1 .. init_row_num LOOP FOR j IN 1 .. init_column_num LOOP INSERT INTO marubatsu_field (row_id, column_id) VALUES (i, j); END LOOP; END LOOP; END IF;
  13. ◦×ゲームの実行部(テーブル初期化)  「SELECT カラム名 INTO 変数名」で、テーブルをSELECTした結果を変数に格納 できます。ただし、この取り出し方では1行のみ取得可能で、2行目以降は破棄 されます。ここではCOUNT(*)の結果を取得しており絶対に1行しか返らないこ とがわかっているので問題ありません。 

    FOR文には様々な書き方がありますが、基本は FOR ループカウンタ変数 IN カウント開始値 .. カウント終了値 LOOP 処理; END LOOP; と書きます。「..」も構文の一つです。  原則、変数は宣言部で宣言しなければ実行部で使用することはできません。と 言いましたが、FOR文のループカウンタ変数は宣言無しで使用可能です。ただ し、ループカウンタ変数はFOR文の中でしか使用できません。
  14. ◦×ゲームの実行部(先手の手番) -- 先手が選んだ位置に◦を入れる SELECT mark INTO sente_mark FROM marubatsu_field WHERE

    row_id = rownum AND column_id = columnnum; IF sente_mark IS NOT NULL THEN -- すでにマークが入っていたらメッセージ RAISE EXCEPTION '指定された位置はすでにマークされていま す。'; ELSE UPDATE marubatsu_field SET mark = '◦' WHERE row_id = rownum AND column_id = columnnum; END IF;
  15. ◦×ゲームの実行部(後手の手番) -- 後手がマークされていないところをランダムで選択して×を入れる BEGIN -- サブブロック開始 SELECT * INTO gote_rec

    FROM marubatsu_field WHERE mark is null ORDER BY random() limit 1; IF NOT FOUND THEN -- 全部埋まっていたら例外を発生させる RAISE EXCEPTION SQLSTATE 'ABCDE' USING MESSAGE = 'もう書き込める場所があ りません。'; ELSE UPDATE marubatsu_field SET mark = '×' WHERE row_id = gote_rec.row_id AND column_id = gote_rec.column_id; END IF; EXCEPTION -- 全部埋まっていた場合に発生した例外をキャッチしてメッセージを表示 WHEN SQLSTATE 'ABCDE' THEN RAISE NOTICE '%', SQLERRM; END; -- サブブロック終了
  16. ◦×ゲームの実行部(後手の手番)  実行部や例外処理部の中にBEGIN~END;を入れサブブロックとすることができ ます。  主に、サブブロック内で例外が発生しても予期した例外であればきちんと処理 をして、サブブロック外の処理を続行するような場合に使用します。  ORDER BY

    rondom() LIMIT 1でランダムにソートして1行のみ取得することでラン ダムに1行取得を実現しています。この方法自体は普通のSQLでも実行できます。  「FOUND」は特殊な変数です。定義不要で使用でき、直前のSELECTでレコード が取得できた場合はtrue、レコードが1件もない場合はfalseになります。Linuxコ マンドの「$?」とかWindowsコマンドの「%errorlevel%」と同じようなイメージ です。  gote_recはRECORD型の変数です。RECORD型は宣言した時点では内部構造が決 まっておらず、代入された時点でそのレコードと同じ構造に定義されます。  「レコード型変数.カラム名」の形で取得したカラムの値を参照できます。
  17. ◦×ゲームの実行部(後手の手番例外処 理)  RAISE EXCEPTIONで例外を発生させ、EXCEPTIONブロックで例外をキャッチして います。  SQLSTATEはHTTPステータスコードのSQL版のようなもので、様々な値が規定さ れていますが、今回は「ABCDE」という独自のSQLSTATEを指定して例外を発生 させています。

     WHEN SQLSTATE '~' で指定したSQLSTATEの例外の処理を記述できます。逆に言 うと、それ以外の例外はキャッチできません。できるだけ例外をキャッチした い場合、WHEN OTHERSという指定でほとんどの例外をキャッチできます。  SQLERRMは特殊な変数です。宣言無しで使用でき、エラーメッセージが格納さ れます。  例外処理部を持つブロックは処理性能が遅くなります。むやみに例外処理をし ないようにしましょう。
  18. ◦×ゲームの実行部(表示テキスト) -- 全てのレコードを取得して、表示用のテキストを作成する FOR rec IN display_cur LOOP CASE rec.column_id

    WHEN 1 THEN display_text := display_text || ' ' || rec.mark; WHEN 2 THEN display_text := display_text || ' | ' || rec.mark; WHEN 3 THEN display_text := display_text || ' | ' || rec.mark || ' ' || chr(10); END CASE; END LOOP; -- 表示用のテキストを返す RETURN display_text; END; -- 実行部の最後
  19. 呼び出し方  CREATE FUNCTIONで作成するのは関数です。COUNT()やMAX()などの関数と同じ と思ってください。  呼び出し方もそれらの関数と同じです。つまり呼び出し方は次のようになりま す。 SELECT marubatsu(1,

    2);  ちなみにINSERTでもUPDATEでも、関数が使えるSQLなら呼び出しは可能ですが、 SELECTで実行されるのが一般的です。  SELECTで関数を呼び出して、関数の中でINSERTしたりUPDATEしたりしているの は違和感があるといえばありますが…。
  20. ストアドのメリット • SQLの処理の中に複雑な処理を組み込むことができる • 同じRDBMS製品であれば、バージョンアップやOSの移行をして も互換性が維持されることが多く、移植に有利 • PostgreSQL上で動くものなので、Windowsで動くならLinuxでも、RDSで も動く •

    処理をDBに任せることで、APサーバのリソースを節約できる • 呼び出し側は簡潔な記述で同じ処理を呼び出すことができる • 呼び出し側は処理の結果のみを受け取ることができ、受け取っ たデータに複雑な処理をする必要がない
  21. できないこと  ストアドファンクションでできないことの代表として、関数内でのトランザク ションがあります。基本的にはCOMMITもROLLBACKもできません。 SELECT 関数名(引数);  言われてみれば↑こういう関数の中でトランザクションが始まったり終わった りするのは変です。 

    トランザクションの中で関数を使ったSQLを実行することはできますが、関数 で例外が発生した場合、そのトランザクションはアボートされ関数外で行った 処理もロールバックされます。
  22. CREATE PROCEDURE  FUNCTIONではなくPROCEDURE。  ストアドプロシージャには戻り値がなく、RETURNS句もありません。 処理本文中にRETURNも不要です。 CREATE OR REPLACE

    PROCEDURE 関数名 ( [ [ 引数名 ] 引数のデータ型 [, ...] ] ) RETURNS 戻り値のデータ型 AS $$ 手続き本文 ←ここをPL/pgSQLで記述する $$ LANGUAGE plpgsql; ←PL/pgSQLであることを明示する