はじめに
前回に引き続き、ストアドプロシージャの開発に入門していく。
今回はIN, OUT, INOUTパラメータについて学ぶ。
環境
Windows 11 Professional
PostgreSQL 17
Docker 4.36.0 (175267)IN, OUT, INOUTパラメータとは
| パラメータの種類 | 説明 | 主な使用目的 | 制約/注意点 |
|---|---|---|---|
IN | - 呼び出し元からプロシージャに値を渡すための入力パラメータ。 | プロシージャ内での処理や条件判断のための値を渡す。 | デフォルトのモードであり、明示的にINを指定しなくても使用可能。 |
| - プロシージャ内部で変更されても、呼び出し元には影響を与えない（値渡し）。 | |||
OUT | - プロシージャから呼び出し元に値を返すための出力パラメータ。 | 結果や計算された値などを呼び出し元に返す。 | プロシージャの実行中にOUTパラメータに値を設定しない場合、NULLが返される。 |
- プロシージャ内部でOUTパラメータに値を設定すると、呼び出し元がその値を取得できる。 | 複数のOUTパラメータを定義可能だが、呼び出し時には戻り値としてそれぞれの値が返される。 | ||
INOUT | - 入力と出力の両方に使用されるパラメータ。 | 呼び出し元から値を受け取り、プロシージャ内部で処理後、その値を変更して返す。 | 呼び出し時に値を渡す必要がある。プロシージャ内で新しい値が設定されなかった場合、元の値がそのまま返される。 |
| - 呼び出し元の変数を直接変更するような振る舞いを実現する。 | INOUTパラメータを多用すると、コードの可読性が低下する可能性がある。 |
ポイント
IN: データを渡す時に設定OUT: 結果を返す時に設定INOUT: 呼び出し元とプロシージャ間で双方向のデータ交換が可能。
では、次の項からIN,OUT,INOUTパラメータを使ったプロシージャを作成してみる。
todoのステータスを変更するプロシージャ
todo_doneというものを作成する。
動作仕様
todo_doneは、指定された todo_id を持つ todos テーブルのレコードのis_completedフィールドをtrueに更新し、その結果の状態をOUTパラメータstatusして返す。
引数
以下の表形式に整理しました：
| 引数名 | モード | 型 | 説明 |
|---|---|---|---|
todo_id | IN | bigint | 更新対象のtodosテーブルレコードを特定するための一意の識別子。 |
status | OUT | boolean | 更新後のis_completedフィールドの値を返す。対象レコードがない場合はNULLが返される可能性あり。 |
プロシージャ
CREATE OR REPLACE PROCEDURE todo_done(
IN todo_id bigint,
OUT status boolean
)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE todos
SET is_completed = true
WHERE id = todo_id;
-- 更新された行の状態を確認してOUTパラメータに設定
SELECT is_completed
INTO status
FROM todos
WHERE id = todo_id;
END;
$$;呼び出しの方法について
-- OUTパラメータにはnullを入れておく。
CALL todo_done(1, null);
-- もしくはプロシージャを使って下記で確認ができる。
DO $$
DECLARE
todo_id bigint := 1;
result boolean := false;
BEGIN
CALL todo_done(todo_id, result);
RAISE INFO 'Todo ID: %, Is Completed: %', todo_id, result;
END;
$$;
/**
Output例: INFO: Todo ID: 1, Is Completed: t
*/- CALL | PostgreSQL 16.4文書
https://www.postgresql.jp/docs/16/sql-call.html
ここに記載されている通り、OUTパラメータについては返すのみの値ではあるが、省略はできないので 値を捨てるのであればNULLと書くのが慣例のようだ。
引数は、OUTパラメータを含む、デフォルトのないプロシージャパラメータすべてに対して与えなければなりません。 しかしながら、OUTパラメータに一致する引数は評価されませんので、それらに対してはNULLとだけ書くのが慣例です。 (OUTパラメータに対して何か他のものを書くと、PostgreSQLの将来のバージョンで互換性の問題が起きるかもしれません。)
todoのレコードを削除するプロシージャ
todo_deleteを作成する
動作仕様
todo_deleteは、指定されたtodo_idを持つtodosテーブルの行を削除し、削除が成功したかどうかをOUTパラメータresultにboolean値として返す。
引数
| 引数名 | モード | 型 | 説明 |
|---|---|---|---|
todo_id | IN | bigint | 削除対象のtodosテーブルレコードを特定するための一意の識別子。 |
result | OUT | boolean | 削除が成功した場合はTRUE、削除対象が存在しない場合はFALSEを返す。 |
関数
CREATE OR REPLACE PROCEDURE todo_delete(
IN todo_id bigint,
OUT result boolean
)
LANGUAGE plpgsql
AS $$
DECLARE
deleted_count int;
BEGIN
DELETE FROM todos
WHERE id = todo_id
RETURNING 1 INTO deleted_count;
-- 削除された行が存在した場合はTRUE、そうでなければFALSEを設定
IF deleted_count > 0 THEN
result := true;
ELSE
result := false;
END IF;
END;
$$;呼び出しの方法について
-- OUTパラメータにはnullを入れておく。
CALL todo_delete(1, null);
-- もしくはプロシージャを使って下記で確認ができる。
DO $$
DECLARE
todo_id bigint := 1;
result boolean := false;
BEGIN
CALL todo_delete(todo_id, result);
RAISE INFO 'Todo ID: %, deleted result: %', todo_id, result;
END;
$$;
/**
Output例: INFO: Todo ID: 1, deleted result: f
*/おまけ: 簡単な例
INパラメータの例
CREATE OR REPLACE PROCEDURE greet_person(IN name text)
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Hello, %!', name;
END;
$$;呼び出し
-- 呼び出し例
CALL greet_person('Alice');
-- 結果: NOTICE: Hello, Alice!OUTパラメータの例
CREATE OR REPLACE PROCEDURE get_square(IN input_number int, OUT square int)
LANGUAGE plpgsql
AS $$
BEGIN
square := input_number * input_number;
END;
$$;呼び出し
CALL get_square(4, null);
-- 結果: 16INOUTパラメータの例
CREATE OR REPLACE PROCEDURE increment_value(INOUT value int)
LANGUAGE plpgsql
AS $$
BEGIN
value := value + 1;
END;
$$;呼び出し
DO $$
DECLARE
val int := 10;
BEGIN
CALL increment_value(val);
RAISE NOTICE 'Incremented Value: %', val;
CALL increment_value(val);
RAISE NOTICE 'Incremented Value: %', val;
CALL increment_value(val);
RAISE NOTICE 'Incremented Value: %', val;
END;
$$;
/**
結果
NOTICE: Incremented Value: 11
NOTICE: Incremented Value: 12
NOTICE: Incremented Value: 13
*/複数のOUTパラメータの例
CREATE OR REPLACE PROCEDURE calculate_area_and_perimeter(
IN radius double precision,
OUT area double precision,
OUT perimeter double precision
)
LANGUAGE plpgsql
AS $$
BEGIN
-- 面積と周囲の長さを計算
area := pi() * radius * radius;
perimeter := 2 * pi() * radius;
END;
$$;呼び出し
CALL calculate_area_and_perimeter(5, null, null);
/* 下記でもよい。 */
DO $$
DECLARE
area double precision;
perimeter double precision;
BEGIN
-- プロシージャを呼び出す
CALL calculate_area_and_perimeter(5, area, perimeter);
RAISE NOTICE 'area : %, perimeter : %', area , perimeter;
END
$$;これ、areaとperimeterの変数が OUTパラメータに指定した際に書き換わるのはなぜと思ったので調べてみた。
(※INOUTじゃないとだめなのかと思ってた。)
OUTパラメータは、プロシージャや関数内で値を設定した後、その値を呼び出し元に返すために使われる。INOUTパラメータと異なり、INパラメータは呼び出し元からプロシージャに渡される引数としての値のみだが、OUTパラメータはプロシージャ内で計算した結果や処理結果を呼び出し元に返すために使われる。
※OUTパラメータが動作する理由
OUTパラメータは、プロシージャの実行中に値を設定するため、呼び出し元の変数を**結果を受け取る場所」**として扱う。呼び出し元の変数が、プロシージャ内で計算された結果で上書きされる。
DOブロックの中でareaとperimeterはプロシージャが返す値を格納するために使われ、プロシージャ内でその値が設定されることで呼び出し元の変数が更新される。
当然だが、INOUTで設定している increment_valueのプロシージャは、入力でもあり出力でもあるからINOUTとなっているのだ。OUTだと、入力としての機能がなくなり、値を渡せなくなるため正しい結果を返さなくなる。
※変数の状態を関数内で変えるような動作をさせたい場合は、INOUTを使う必要があるということだろう。
参考
CREATE PROCEDURE | PostgreSQL 16.4文書
https://www.postgresql.jp/docs/16/sql-createprocedure.htmlCALL | PostgreSQL 16.4文書
https://www.postgresql.jp/docs/16/sql-call.html6.4. 更新された行のデータを返す | PostgreSQL 16.4文書
https://www.postgresql.jp/docs/16/dml-returning.htmlChatGPTの会話
https://chatgpt.com/share/67556a5f-7a2c-8001-80d4-86d150d52f58
おわりに
IN,OUT,INOUTについて勉強した。
なんかこの辺の挙動が良くわからなかったのでちょっと理解できてよかった。
次回は、cursorを勉強する。