ストアド プロシージャは、データベースの最適化と再利用性の向上に役立ちます。同じクエリを実行する代わりに、必要なときにいつでも呼び出すストアド プロシージャとしてタスクを作成できます。この記事を最後まで読むと、ストアド プロシージャについてすべて学ぶことができます。
PostgreSQL でのストアド プロシージャの操作
PostgreSQL ユーザーなら、PostgreSQL 関数がトランザクションを実行しないことに気づいたかもしれません。トランザクションを作成することは可能ですが、トランザクションをコミットしたり、以前の状態にロールバックしたりすることはできません。ただし、ストアド プロシージャを使用すると、これらの制限が回避されます。
PostgreSQL でストアド プロシージャを作成するための基本的な構文は次のとおりです。
プロシージャの作成または置換 プロシージャ名(
パラメータのデータ型
)
言語 plpsql;
AS $$
宣言する
データ型の場合の変数
始める
論理
終わり;
$$
指定された構文で注目すべき重要な点は、ストアド プロシージャに使用する名前である「procedure_name」、含めるパラメータとそのデータ型、および主に SQL ステートメントであるロジックです。
PostgreSQL でストアド プロシージャを作成する方法を理解するのに役立つ 3 つの例を示します。
例 1: 数値の 2 乗を計算するストアド プロシージャ
最初の例では、出力を端末に出力する方法として「RAISE NOTICE」ステートメントを使用するストアド プロシージャを作成します。ストアド プロシージャは、呼び出し時に指定された整数値を受け取り、その 2 乗を計算します。
ストアド プロシージャの作成方法は次のとおりです。
パラメータに「num1」という名前を付けます。これは整数です。ロジック部分では、「num1」の二乗を取得し、二乗変数として格納する方法を定義します。コマンドを実行すると、ストアド プロシージャが正常に作成されたことを確認する「CREATE PROCEDURE」出力が表示されます。
次のタスクは、プロシージャを呼び出して、予期される引数を与えることです。
CALL プロシージャ名(引数);ストアド プロシージャが実行されたことを示す CALL 出力が得られ、予期した出力が得られます。この場合、追加した引数の 2 乗です。
例 2: テーブル エントリに値を挿入するストアド プロシージャ
次の 2 つの例は、データベース テーブルを操作するストアド プロシージャを作成する方法を示しています。作業する「students」テーブルをすぐに作成しましょう。
この例では、ユーザーが新しく作成されたテーブルに値を挿入できるストアド プロシージャを作成します。ストアド プロシージャを呼び出すときに引数として追加されることが予想されるパラメーターをどのように指定するかに注目してください。さらに、追加された引数を受け取り、「students」テーブルに対して INSERT SQL ステートメントを実行するロジックを定義します。
次のコマンドを実行して、利用可能なストアド プロシージャを確認できます。
\df次の出力からわかる最初のストアド プロシージャは、前に作成した「add_student」です。
次に、ストアド プロシージャを呼び出して実行しましょう。次の図は、空のテーブルがある様子を示していますが、ストアド プロシージャを呼び出して最初の生徒を追加しています。
テーブル内の値をリストすると、call プロシージャ コマンドで追加した引数がテーブル内の最初の生徒の値になっていることに注目してください。これが、テーブルに値を挿入するストアド プロシージャを作成する方法です。
ストアド プロシージャを作成するときは、エラーを避けるために、指定するパラメーターがテーブル内で予期されるものと一致する必要があることに注意してください。また、データ型が一致している必要があります。
例 3: テーブル エントリを更新するストアド プロシージャ
次に、テーブル エントリを更新する別のストアド プロシージャを作成しましょう。テーブルの値を簡単に更新する方法が必要な場合は、次のように更新ストアド プロシージャを作成できます。
WHERE キーワードを使用して更新する列を指定し、SET キーワードを使用して新しい値を指定します。次に、COMMIT キーワードを追加して変更を永続化する必要があります。
update ストアド プロシージャを呼び出して、予期される引数「student_id」と新しいコースを追加しましょう。
テーブル内のエントリをリストすると、対象とした特定の学生向けの更新されたコースがあることを確認できます。これが更新ストアド プロシージャの仕組みです。
結論
PostgreSQL では任意のストアド プロシージャを作成できます。必要なのは、従うべき構文を理解し、ストアド プロシージャのロジックを定義することだけです。そこからストアド プロシージャを呼び出し、期待どおりに実行されたことを確認します。この投稿では、PostgreSQL のストアド プロシージャについて説明し、その作成方法の例を示しました。